We just upgraded from 5.3.9 and hit this error on a rather large query

This is the query its failing on, if I remove the distinct, it works ok. Not sure how to make a reproducible test case 😞

<cfquery name="qDistinctFields" dbtype="query">
	select distinct fieldcode, fieldcodelower, fieldname, datatypedefinition, maxlength, fieldtypedefinition, islocked, optionlist, questionlistid, questionlistname,
		isCustomForm, isappform,
		templatelist, 
		questionheadingdisplay, questionheading, sectionheading, pageheadingdisplay, pageheading
	from qDistinctFieldsAll
	where isAppForm = 1
	order by fieldcodelower, fieldname
</cfquery>

out of interest, does group by work

Won’t be able to check the second question until tomorrow now

can you DM me the create table statement?

from oracle?

yeah, I have a local instance

i'll have a play after day$job is over this evening (berlin time)

yeah bet it's the maxlength column name

@bdw429s curious why this feel thru to hsqldb tho

I'm waiting for my bags at an airport in spain so I can't take a look, but setting the system property to disable hsqldb will tell you.

can't quite repo, @Dean can u try with

lucee.qoq.hsqldb.disable set

to false?

@bdw429s @zackster

Ive found if I match the casing of the field

isappform

in the select, it works.

Its the same as the one I posted above

The one you posted above is specifically from hsqldb and was captured prior to you turning on the debug flag

You need to look for a stack trace in the logs where you found the "falling back" message

The debug flag should have put a stack in the log where you found the "falling back message"

I'm not entirely sure Zac didn't just have you disable hsqldb in the first place, which was my suggestion above

Then that just completely removes hsqldb from the picture

trying to find it

There are two different bugs going on here • the native QoQ seems to have an issue recognizing the column names when the case doesn't match • then it falls back to Hsqldb which throws a data truncation error

let me know if thats enough, or you want me to recreate with hsqldb off

Gracias

@zackster Where do you want the fix for this QoQ bug sent?

The repro case for it is simply

qry = queryNew( 'col', 'varchar', [['foo'],['bar']] );

result = queryExecute(sql="
            SELECT  distinct col
            FROM qry
            where COL = 'foo'
        "
        ,params=[]
        ,options={dbtype="query"}
        );

where the case of the column in the select list is different than that in the where clause

Bug has prolly been there for a while, but the hsqldb fallback masked it until the new hsqldb upgrade had its update and failed for its own recent

and 5.4, it's a bug fix, it will be 5.4.2.0

@zackster There's pulls for the native QoQ bug fix for 5.4 and 6.0

teamQoQ!