Lucee cfQuery row limit

I’ve found a critical problem: when you build a select query with a lot of fields (most of them supposing to be large data type), Lucee responds with the specific error: “The value [2147483648] is outside the range that can be represented as an int.”…
I tried changing a lot of bigint fields to int (most of them were “unsigned” because I’m using MariaDB), but the problem itself persists. I was changing a lot of values to 20% under limit of 2,147,483,647 expecting it solves the problem, but it still persists.
My last testing was removing a lot of fields or truncating the super select query to a lot of queries. Well, the truncation itself almost solved my situation. I could go on to my development.
I agree we need to do not overload our processes, but I used Adobe ColdFusion since MX7 and I’ve never had a problem like that! Sometimes I used Lucee, but I’ve never experienced a situation that I needed to avoid overload programming like that. I had some “Java heap space” problems that I needed to increase RAM memory to my VPS and it was solved. Now I’m using a VPS with large RAM memory and I need to face a problem like that…

Would you like to share your query and also your stack?

OS: ???
Java Version: ???
Tomcat Version: ???
Lucee Version: ???

1 Like

That’s the following query:

			tb_persons.pk_id as persons_pk_id
		,	case
				when tb_persons.reg_blocked is null
					then 0
				else 1
			end as persons_info_blocked
		,	case
				when tb_persons.reg_removed is null
					then 0
				else 1
			end as persons_info_removed
		,	tb_persons.info_fullName as persons_info_fullName
		,	tb_persons.info_nickName as persons_info_nickName
		,	tb_genders.pk_id as genders_pk_id
		,	tb_genders.info_icon as genders_info_icon
		,	tb_genders.info_caption as genders_info_caption
		,	tb_genders.info_label as genders_info_label
		,	case
				when tb_genders.fit_female=1
				and tb_genders.fit_male=0
					then 'female'
				when tb_genders.fit_female=0
				and tb_genders.fit_male=1
					then 'male'
				else 'unisex'
			end as genders_info_treatment
		,	vw_citizenship.pk_id as citizenship_pk_id
		,	vw_citizenship.info_name as citizenship_info_name
		,	vw_citizenship.info_label as citizenship_info_label
		,	vw_citizenship.info_code as citizenship_info_code
		,	vw_citizenship.ethnical_female as citizenship_ethnical_female
		,	vw_citizenship.ethnical_male as citizenship_ethnical_male
		,	vw_citizenship.ethnical_unisex as citizenship_ethnical_unisex
		,	tb_persons.birth_date as persons_birth_date
		,	vw_birth.pk_id as birth_pk_id
		,	vw_birth.info_name as birth_info_name
		,	vw_birth.info_label as birth_info_label
		,	vw_birth.info_code as birth_info_code
		,	vw_birth.ethnical_female as birth_ethnical_female
		,	vw_birth.ethnical_male as birth_ethnical_male
		,	vw_birth.ethnical_unisex as birth_ethnical_unisex
		,	tb_roles.pk_id as roles_pk_id
		,	tb_roles.info_caption as roles_info_caption
		,	tb_roles.label_female as roles_label_female
		,	tb_roles.label_male as roles_label_male
		,	tb_roles.label_unisex as roles_label_unisex
		,	case
				when tb_persons.status_warned is null
					then 0
				else 1
			end as persons_status_warned
		,	tb_persons.authentication_email as persons_authentication_email
		,	vw_countryPhone.pk_id as countryPhone_pk_id
		,	vw_countryPhone.info_name as countryPhone_info_name
		,	vw_countryPhone.info_label as countryPhone_info_label
		,	vw_countryPhone.info_code as countryPhone_info_code
		,	vw_countryPhone.info_phone as countryPhone_info_dial
		,	tb_persons.phone_areaCode as persons_phone_areaCode
		,	tb_persons.phone_number as persons_phone_number
		,	tb_persons.phone_extension as persons_phone_extension
		,	tb_persons.phone_mobile as persons_phone_mobile
		,	tb_persons.phone_office as persons_phone_office
		,	tb_persons.phone_dialing as persons_phone_dialing
		,	tb_persons.phone_messaging as persons_phone_messaging
		,	tb_persons.phone_whatsApp as persons_phone_whatsApp
		,	tb_persons.phone_telegram as persons_phone_telegram
		,	case
				when tb_persons.phone_activated is null
					then 0
				else 1
			end as persons_phone_activated
		,	tb_accesses.pk_id as accesses_pk_id
		,	case
				when tb_accesses.reg_injured is null
					then 0
				else 1
			end as accesses_info_injured
		,	case
				when tb_accesses.reg_removed is null
					then 0
				else 1
			end as accesses_info_removed
		,	tb_status_condition_positions.pk_id as status_condition_positions_pk_id
		,	tb_status_condition_positions.title_female as status_condition_positions_title_female
		,	tb_status_condition_positions.title_male as status_condition_positions_title_male
		,	tb_status_condition_positions.title_unisex as status_condition_positions_title_unisex
		,	tb_status_condition_durations.pk_id as status_condition_durations_pk_id
		,	tb_status_condition_durations.info_title as status_condition_durations_info_title
		,	tb_status_conditions.label_female as status_conditions_label_female
		,	tb_status_conditions.label_male as status_conditions_label_male
		,	tb_status_conditions.label_unisex as status_conditions_label_unisex
		,	tb_status_reasons.pk_id as status_reasons_pk_id
		,	tb_status_reasons.info_label as status_reasons_info_label
		,	tb_statuses.info_label as statuses_info_label
		,	tb_statuses.title_female as statuses_title_female
		,	tb_statuses.title_male as statuses_title_male
		,	tb_statuses.title_unisex as statuses_title_unisex
		,	tb_templates.pk_id as templates_pk_id
		,	concat(
			,	tb_templates.info_caption
			,	'.cfm'
			) as templates_info_path
		,	tb_templates.info_label as templates_info_label
		,	tb_statuses.manifest_permanent as templates_info_permanent
		from tb_persons
			inner join tb_genders
				on tb_persons.info_gender=tb_genders.pk_id
			inner join tb_countries as vw_citizenship
				on tb_persons.info_citizen=vw_citizenship.pk_id
			inner join tb_countries as vw_birth
				on tb_persons.birth_country=vw_birth.pk_id
			inner join tb_roles
				on tb_persons.authentication_role=tb_roles.pk_id
			inner join tb_countries as vw_countryPhone
				on tb_persons.phone_countryCode=vw_countryPhone.pk_id
			inner join tb_accesses
				on tb_persons.pk_id=tb_accesses.pk_persons
			inner join tb_sessions
				on tb_accesses.pk_sessions=tb_sessions.pk_id
			inner join tb_statuses
				on tb_accesses.login_position=tb_statuses.pk_positions
				and tb_accesses.login_duration=tb_statuses.pk_durations
				and tb_accesses.login_reason=tb_statuses.pk_reasons
			inner join tb_status_conditions
				on tb_statuses.pk_positions=tb_status_conditions.pk_positions
				and tb_statuses.pk_durations=tb_status_conditions.pk_durations
			inner join tb_status_condition_positions
				on tb_status_conditions.pk_positions=tb_status_condition_positions.pk_id
			inner join tb_status_condition_durations
				on tb_status_conditions.pk_durations=tb_status_condition_durations.pk_id
			inner join tb_status_reasons
				on tb_statuses.pk_reasons=tb_status_reasons.pk_id
			inner join tb_templates
				on tb_statuses.manifest_template=tb_templates.pk_id
			left outer join tb_keepMeConnected
				on tb_accesses.pk_persons=tb_keepMeConnected.pk_persons
				and tb_accesses.login_keepMeConnected=tb_keepMeConnected.pk_id
		where tb_sessions.info_cookie=:v_sessions_info_cookie
,	params={
		,	value:cookie.cfid
,	options={dataSource:'db_anything_mysql'}

OS: Windows Server 2022
Lucee Version:
IIS: 10.0.19041.1

Is that from Lucee or MariaDB?

Is there any indication whether it’s a specific column of the query?