QoQ incompatible data type with subquery IN operator

I’ve run into a QofQ issue with Lucee and using the

The same QofQ has worked on all past versions of Lucee through

returning a single record

With the latest version,, I’m receiving the error “incompatible data type in conversion

Is this a bug?

	myQuery = queryNew("navid, type, url",
			"navid": 200,
			"type": 1,
			"url": "football"
			"navid": 201,
			"type": 1,
			"url": "baseball"
			"navid": 202,
			"type": 1,
			"url": "basketball"
			"navid": 20010,
			"type": 2,
			"url": "offense"
			"navid": 20011,
			"type": 2,
			"url": "defense"
			"navid": 20012,
			"type": 2,
			"url": "goalie"
			"navid": 20210,
			"type": 2,
			"url": "offense"
			"navid": 20211,
			"type": 2,
			"url": "defense"


		query name="myNewQuery" dbtype="query" {
            select navid, type, url
            from myQuery
            where url = 'offense'
            and type = 2
            and left(navid, 3) in (select navid
                                    from myQuery
                                    where type = 1
                                    and url = 'football')


I have also tried changing the first two field types to "integer" in the example with the same result.

myQuery = queryNew(navid, type, url,


OS: Windows Server 2019 (10.0) 64bit
Java Version: 11.0.20 (Eclipse Adoptium) 64bit
Tomcat Version: 9.0.78
Lucee Version: Lucee

as you are comparing a string against a number, try casting the navid to a strong with a + ‘’

that is quite odd

yeah the lucee sql parser doesn’t seem to support cast expressions in subselects

can you file a bug please?

the error being swallowed, the modern parser is falling, then trying to fall back on older parser, then failing the with a cast in the sub select expression (stack is from my 6.0 qoq refactor, so lines numbers are confusing compared to 5.4)

     [java]    [script] Caused by: lucee.runtime.sql.old.ParseException: Encountered "varchar" at line 1, column 37.
     [java]    [script] Was expecting one of:
     [java]    [script]     "," ...
     [java]    [script]     ")" ...
     [java]    [script]     "." ...
     [java]    [script]     "+" ...
     [java]    [script]     "-" ...
     [java]    [script]     "*" ...
     [java]    [script]     "||" ...
     [java]    [script]     "/" ...
     [java]    [script]     "**" ...
     [java]    [script]     "(" ...
     [java]    [script]
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.generateParseException(ZqlJJParser.java:3662)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.jj_consume_token(ZqlJJParser.java:3551)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.FunctionCall(ZqlJJParser.java:1954)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SQLPrimaryExpression(ZqlJJParser.java:1879)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SQLUnaryExpression(ZqlJJParser.java:1820)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SQLExpotentExpression(ZqlJJParser.java:1776)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SQLMultiplicativeExpression(ZqlJJParser.java:1739)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SQLSimpleExpression(ZqlJJParser.java:1698)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SelectItem(ZqlJJParser.java:929)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SelectList(ZqlJJParser.java:894)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SelectWithoutOrder(ZqlJJParser.java:805)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SelectStatement(ZqlJJParser.java:751)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.QueryStatement(ZqlJJParser.java:585)
     [java]    [script]         at lucee.runtime.sql.old.ZqlJJParser.SQLStatement(ZqlJJParser.java:201)
     [java]    [script]         at lucee.runtime.sql.old.ZqlParser.readStatement(ZqlParser.java:77)
     [java]    [script]         at lucee.runtime.db.HSQLUtil.getInvokedTables(HSQLUtil.java:107)
     [java]    [script]         at lucee.runtime.db.HSQLDBHandler.execute(HSQLDBHandler.java:534)
     [java]    [script]         ... 101 more

Report filed:

fixed in

I have enabled the looser backwards compat mode in hsqldb to do less strict type checks, basically allowing cfml style casting so the string matches the number coming back from the sub select

details in ticket [LDEV-4695] - Lucee

1 Like

Actually, thanks to some QA by @cfmitrah, I had made a type in the test case whilst playing around

Turns out, although we tried, we can’t fix this, it’s simply that the underlying hsqldb engine has become stricter since 2009