Environment
- Lucee Version: 7.1.0.43-BETA
- Java Version: JDK 21
- OS: Windows
- Tested with: -Dlucee.qoq.hsqldb.disable=false, -Dlucee.query.allowemptyasnull=true (no effect)
Description
In a Query of Query (QoQ), when using the || concatenation operator in a WHERE clause, rows where the column value is NULL or empty string (“”) are incorrectly excluded from the result set.
This is a regression from Adobe ColdFusion behavior, where those rows are correctly included.
Reproducer
<cfset testQuery = queryNew("campo", "varchar", [
{campo: ""},
{campo: "123"},
{campo: javaCast("null", "")},
{campo: "456"}
])>
<cfquery name="q1" dbtype="query">
SELECT * FROM testQuery
WHERE ','|| campo ||',' NOT LIKE '%,123,%'
</cfquery>
<cfoutput>
Total rows in source query: #testQuery.recordCount#<br>
Rows after QoQ filter: #q1.recordCount#<br>
<cfloop query="q1">
campo: [#campo#]<br>
</cfloop>
</cfoutput>
Expected result (Adobe ColdFusion behavior):
- Total: 4
- After filter: 3
- Rows returned: , [null], [456]
Actual result (Lucee 7.1):
- Total: 4
- After filter: 2
- Rows returned: [456] only
Rows with NULL and empty string are incorrectly excluded because ','|| NULL ||',' evaluates to NULL instead of ',,', causing NULL NOT LIKE '%,123,%' to return NULL (not TRUE), which silently drops those rows.
Root Cause
The QoQ engine does not handle NULL/empty string in string concatenation consistently with Adobe ColdFusion. In ACF, concatenating NULL with a string treats NULL as empty string, preserving the row in the result set.
Workaround
Normalize NULL values to empty string before the QoQ is executed:
<cfloop list="#qryFrom.columnList#" index="col">
<cfloop from="1" to="#qryFrom.recordCount#" index="row">
<cfif isNull(qryFrom[col][row])>
<cfset querySetCell(qryFrom, col, "", row)>
</cfif>
</cfloop>
</cfloop>
This workaround is not viable when there are hundreds of QoQ statements across a large codebase migrating from Adobe ColdFusion.
Request
Please fix the QoQ engine so that NULL values in string concatenation are treated as empty string, consistent with Adobe ColdFusion behavior. Alternatively, provide a global configuration option (JVM property or admin setting) to enable this behavior.