Query of Queries DISTINCT/GROUP BY fails when a query parameter appears in the SELECT list

I encountered a bug during a migration between language major versions to 7.0; next up, 7.1. What I’ve posted below is from LLM analysis. I’ll also post the contents of a CFM illustrating the test cases for reproduction; it should be self-contained, and documents what the test cases are and the expected outcomes.

For this case I worked around it by removing from the SELECT list and injected the data later.

This is my first time submitting a bug report here; I apologize if I’ve filled this out incorrectly or categorized it in the wrong place.

LLM output starts here:

Tested on Lucee 7.0.4.34, Tomcat 11, Java 21, Windows Server 2019.

A Query of Queries throws an IllegalQoQException when all of the following apply:

  • A bound parameter appears in the SELECT list.
  • The query uses DISTINCT or GROUP BY.
  • The native QoQ engine consequently uses its partitioned execution path.

The same parameter works correctly in a non-partitioned SELECT and when used only in the WHERE clause.

Actual behavior

lucee.runtime.exp.IllegalQoQException:
Column [?] not found in query, Columns are [COL1, COL2, COL3]

Expected behavior

The bound parameter should be evaluated as a parameter and returned under its SELECT alias. It should not be interpreted as a source-query column named ?.

Reproduction

A sanitized, self-contained CFML reproduction is included in a code block below.

It creates an in-memory query and should require no datasource or application configuration.

Key results:

  • Parameter in SELECT without DISTINCT: passes
  • DISTINCT without a parameter: passes
  • DISTINCT with parameter in SELECT: fails
  • GROUP BY with parameter in SELECT: fails
  • Same DISTINCT case using queryExecute(): fails
  • DISTINCT with a literal in SELECT: passes
  • DISTINCT with parameter only in WHERE: passes
  • Aggregate without GROUP BY or WHERE: passes because it does not use the affected partitioned path

Suspected source location

core/src/main/java/lucee/runtime/sql/QueryPartitions.java

Method:

QueryPartitions.addRow(...)

On the partitioned path, select-list parameters are represented as ColumnExpression objects whose column name is ?. The method evaluates every ColumnExpression directly against the source query:

value = ((ColumnExpression) columns[cell]).getValue(pc, source, row, null);

That attempts to find a source column named ?.

Other QoQ paths already recognize parameters explicitly:

  • Target-column type creation avoids looking up ?.
  • Final result evaluation recognizes ColumnExpression.isParam().
  • Non-partitioned expression evaluation resolves parameters from the SQL parameter list.

QueryPartitions.addRow() appears to be the missed parameter-aware path.

Possible correction

When a ColumnExpression represents a parameter, evaluate it through the parameter-aware QoQ expression evaluator rather than calling ColumnExpression.getValue() directly against the source query.

Development history

This appears related to, but distinct from, these tickets:

  • LDEV-2128, fixed in 5.3.4.43, established support for select-list parameters in QoQ UNION queries.
  • LDEV-5634, fixed in 7.0.1.94, changed QoQ handling so invalid source columns throw instead of silently returning a default and falling back to HSQLDB.
  • The LDEV-5634 commit changed ColumnExpression.getValue(..., defaultValue) to propagate missing-column exceptions and added IllegalQoQException wrapping in QueryPartitions.addRow().

Before that change, the incorrect lookup of ? in addRow() was apparently masked because the missing-column lookup returned the supplied default. After LDEV-5634, it throws the currently observed exception. This indicates the behavioral regression began in 7.0.1.94.

LDEV-6310 documents another regression introduced by the same LDEV-5634 change. It restored stored-null handling in 7.0.4.32 and 7.1.0.117, but did not address select-list parameters.

Current status

No existing Lucee Jira ticket or forum report was found for this exact DISTINCT/GROUP BY select-list parameter failure.

Source review indicates the affected logic remains present in the current 7.0, 7.1, and 8.0 branches, but only Lucee 7.0.4.34 was execution-tested for this report.

Analysis disclosure

The reproduction, source analysis, development-history review, and duplicate-report search were assisted by Claude Sonnet 5.5 and GPT-5.6 Sol.

<!---
	Minimal reproduction of the Lucee 7 query of queries (QoQ) failure
	"Column [?] not found in query" when a cfqueryparam is in the SELECT list of a QoQ that
	Lucee runs through its partitioned path (including DISTINCT and GROUP BY).

	Self-contained: builds its own in-memory query, so it needs no datasource, session or
	config. Run it directly on a Lucee instance.

	Observed on the first local Lucee 7 run:
	- Cases 3 and 4 FAIL with "Column [?] not found in query" (DISTINCT and GROUP BY).
	- Cases 1, 2, 5, 7 and 8 PASS. Case 5 (aggregate without GROUP BY) is not affected.
		Case 5 does not enter the partitioned path because it has no GROUP BY or WHERE.
		Case 8 demonstrates a workaround that binds the constant after the QoQ.
	- Case 6 (queryExecute) FAILS like case 3 (the first run was invalid; table name fixed).
	- "--" comments: only a comment at the very END of the SQL (cases 9 and 16) fails, with a
		parse error ("Encountered <EOF>"). Comments inside the select list (cases 10-15, including
		commas, semicolons, quotes, whole-line and commented-out columns) all PASS.
	- Case 17 (DISTINCT with a param only in WHERE) PASSES.
--->
<cfsetting requesttimeout="120">

<cfscript>
	local = {};
	local.sResults = [];
	local.tEngine = "Lucee";
	local.tVersion = server.lucee.version;

	// Include duplicate rows so DISTINCT and GROUP BY have something to collapse.
	local.qSource = queryNew("col1,col2,col3", "varchar,varchar,varchar");

	queryAddRow(local.qSource, 3);
	querySetCell(local.qSource, "col1", "ITEM1", 1);
	querySetCell(local.qSource, "col2", "A", 1);
	querySetCell(local.qSource, "col3", "SHIPPED", 1);
	querySetCell(local.qSource, "col1", "ITEM1", 2);
	querySetCell(local.qSource, "col2", "A", 2);
	querySetCell(local.qSource, "col3", "SHIPPED", 2);
	querySetCell(local.qSource, "col1", "ITEM2", 3);
	querySetCell(local.qSource, "col2", "B", 3);
	querySetCell(local.qSource, "col3", "PENDING", 3);

	local.tSessionId = "REPRO-SESSION-1234";
</cfscript>

<!--- Case 1: no DISTINCT, param in select. Non-partitioned path. Expected PASS everywhere. --->
<cftry>
	<cfquery name="local.qCase1" dbtype="query">
		SELECT
			col1,
			<cfqueryparam value="#local.tSessionId#" cfsqltype="cf_sql_varchar"> AS tSessionId
		FROM local.qSource
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 1, tName = "Param in SELECT, no DISTINCT", tExpected = "PASS",
		bPassed = true, tDetail = "#local.qCase1.recordCount# rows, tSessionId=#local.qCase1.tSessionId#"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 1, tName = "Param in SELECT, no DISTINCT", tExpected = "PASS",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 2: DISTINCT with no param. Expected PASS everywhere (control). --->
<cftry>
	<cfquery name="local.qCase2" dbtype="query">
		SELECT DISTINCT col1, col2
		FROM local.qSource
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 2, tName = "DISTINCT, no param (control)", tExpected = "PASS",
		bPassed = true, tDetail = "#local.qCase2.recordCount# rows"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 2, tName = "DISTINCT, no param (control)", tExpected = "PASS",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 3: THE BUG. DISTINCT with a param in the SELECT list. --->
<cftry>
	<cfquery name="local.qCase3" dbtype="query">
		SELECT DISTINCT
			col1,
			col2,
			<cfqueryparam value="#local.tSessionId#" cfsqltype="cf_sql_varchar"> AS tSessionId
		FROM local.qSource
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 3, tName = "DISTINCT + param in SELECT", tExpected = "FAIL on Lucee 7",
		bPassed = true, tDetail = "#local.qCase3.recordCount# rows, tSessionId=#local.qCase3.tSessionId#"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 3, tName = "DISTINCT + param in SELECT", tExpected = "FAIL on Lucee 7",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 4: GROUP BY with param in select. Also partitioned. --->
<cftry>
	<cfquery name="local.qCase4" dbtype="query">
		SELECT
			col1,
			<cfqueryparam value="#local.tSessionId#" cfsqltype="cf_sql_varchar"> AS tSessionId
		FROM local.qSource
		GROUP BY col1
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 4, tName = "GROUP BY + param in SELECT", tExpected = "FAIL on Lucee 7",
		bPassed = true, tDetail = "#local.qCase4.recordCount# rows, tSessionId=#local.qCase4.tSessionId#"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 4, tName = "GROUP BY + param in SELECT", tExpected = "FAIL on Lucee 7",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 5: aggregate without GROUP BY or WHERE. This uses the non-partitioned path. --->
<cftry>
	<cfquery name="local.qCase5" dbtype="query">
		SELECT
			COUNT(*) AS nCount,
			<cfqueryparam value="#local.tSessionId#" cfsqltype="cf_sql_varchar"> AS tSessionId
		FROM local.qSource
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 5, tName = "Aggregate (no GROUP BY) + param in SELECT", tExpected = "PASS (observed)",
		bPassed = true, tDetail = "nCount=#local.qCase5.nCount#, tSessionId=#local.qCase5.tSessionId#"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 5, tName = "Aggregate (no GROUP BY) + param in SELECT", tExpected = "PASS (observed)",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 6: same as case 3 via queryExecute() with a positional "?", to show it is not
	cfquery-tag specific. --->
<cftry>
	<cfset local.qCase6 = queryExecute(
		"SELECT DISTINCT col1, ? AS tSessionId FROM local.qSource",
		[{value = local.tSessionId, cfsqltype = "cf_sql_varchar"}],
		{dbtype = "query"})>
	<cfset arrayAppend(local.sResults, {
		nCase = 6, tName = "DISTINCT + param via queryExecute()", tExpected = "FAIL on Lucee 7",
		bPassed = true, tDetail = "#local.qCase6.recordCount# rows, tSessionId=#local.qCase6.tSessionId#"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 6, tName = "DISTINCT + param via queryExecute()", tExpected = "FAIL on Lucee 7",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 7: DISTINCT with a literal (not a param) in select. Isolates the failure to params. --->
<cftry>
	<cfquery name="local.qCase7" dbtype="query">
		SELECT DISTINCT col1, col2, 'LITERAL' AS tSessionId
		FROM local.qSource
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 7, tName = "DISTINCT + literal in SELECT (isolates params)", tExpected = "PASS",
		bPassed = true, tDetail = "#local.qCase7.recordCount# rows, tSessionId=#local.qCase7.tSessionId#"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 7, tName = "DISTINCT + literal in SELECT (isolates params)", tExpected = "PASS",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 8: THE WORKAROUND. Run DISTINCT without the param, then bind the constant per row. --->
<cftry>
	<cfquery name="local.qCase8" dbtype="query">
		SELECT DISTINCT col1, col2
		FROM local.qSource
	</cfquery>
	<cfset local.nBound = 0>
	<cfloop query="local.qCase8">
		<cfset local.sRow = {col1 = local.qCase8.col1, tSessionId = local.tSessionId}>
		<cfset local.nBound = local.nBound + 1>
	</cfloop>
	<cfset arrayAppend(local.sResults, {
		nCase = 8, tName = "Workaround: no param in QoQ, bind per row afterwards", tExpected = "PASS",
		bPassed = true, tDetail = "#local.qCase8.recordCount# rows, #local.nBound# bound with #local.tSessionId#"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 8, tName = "Workaround: no param in QoQ, bind per row afterwards", tExpected = "PASS",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Case 9: An interior "--" comment and a terminal "--" comment in the same QoQ. The
	terminal comment is expected to cause the observed parse failure. --->
<cftry>
	<cfquery name="local.qCase9" dbtype="query">
		SELECT DISTINCT
			col1, -- ITEM number
			col2
		FROM local.qSource
		WHERE col3 = 'SHIPPED' -- ship status
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 9, tName = """--"" comment inside QoQ SQL", tExpected = "FAIL (observed)",
		bPassed = true, tDetail = "#local.qCase9.recordCount# rows"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 9, tName = """--"" comment inside QoQ SQL", tExpected = "FAIL (observed)",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<!--- Cases 10-16: isolate which placements of "--" comments break a QoQ. Case 9 mixes
	interior and terminal comments. The SQL is built as a string and handed to cfquery. --->
<cfscript>
	local.tNl = chr(10);
	local.aCommentCases =
	[
		{
			nCase = 10,
			tName = "Comment at end of a select-list line, after a comma + space",
			tSql = "SELECT col1, -- note" & local.tNl & "col2" & local.tNl & "FROM local.qSource"
		},
		{
			nCase = 11,
			tName = "Comment directly after a comma, no space (col14 as X,--note)",
			tSql = "SELECT col1,--note" & local.tNl & "col2" & local.tNl & "FROM local.qSource"
		},
		{
			nCase = 12,
			tName = "Whole-line comment inside the select list",
			tSql = "SELECT" & local.tNl & "-- legacy remapping note" & local.tNl
				& "col1, col2" & local.tNl & "FROM local.qSource"
		},
		{
			nCase = 13,
			tName = "Whole-line commented-out column inside the select list",
			tSql = "SELECT" & local.tNl & "--col9 as ITEMAmount," & local.tNl
				& "col1, col2" & local.tNl & "FROM local.qSource"
		},
		{
			nCase = 14,
			tName = "Comment containing a semicolon and commas",
			tSql = "SELECT col1, -- Also, col33 to col34;col34 to col35;" & local.tNl & "col2" & local.tNl
				& "FROM local.qSource"
		},
		{
			nCase = 15,
			tName = "Comment containing double quotes",
			tSql = "SELECT col1, -- named ""ITEM_id""" & local.tNl & "col2" & local.tNl
				& "FROM local.qSource"
		},
		{
			nCase = 16,
			tName = "Comment only at the very end of the SQL",
			tSql = "SELECT col1, col2" & local.tNl & "FROM local.qSource -- end"
		}
	];
</cfscript>

<cfloop array="#local.aCommentCases#" index="local.sCommentCase">
	<cftry>
		<cfquery name="local.qComment" dbtype="query">
			#preserveSingleQuotes(local.sCommentCase.tSql)#
		</cfquery>
		<cfset arrayAppend(local.sResults, {
			nCase = local.sCommentCase.nCase, tName = local.sCommentCase.tName, tExpected = "unknown",
			bPassed = true, tDetail = "#local.qComment.recordCount# rows"})>
		<cfcatch type="any">
			<cfset arrayAppend(local.sResults, {
				nCase = local.sCommentCase.nCase, tName = local.sCommentCase.tName, tExpected = "unknown",
				bPassed = false, tDetail = cfcatch.message})>
		</cfcatch>
	</cftry>
</cfloop>

<!--- Case 17: DISTINCT with the param only in WHERE. Expected to pass because WHERE parameter
	evaluation uses the parameter-aware expression path before rows are partitioned. --->
<cftry>
	<cfquery name="local.qCase17" dbtype="query">
		SELECT DISTINCT col1, col2
		FROM local.qSource
		WHERE col3 = <cfqueryparam value="SHIPPED" cfsqltype="cf_sql_varchar">
	</cfquery>
	<cfset arrayAppend(local.sResults, {
		nCase = 17, tName = "DISTINCT + param only in WHERE", tExpected = "PASS (predicted)",
		bPassed = true, tDetail = "#local.qCase17.recordCount# rows"})>
	<cfcatch type="any">
		<cfset arrayAppend(local.sResults, {
			nCase = 17, tName = "DISTINCT + param only in WHERE", tExpected = "PASS (predicted)",
			bPassed = false, tDetail = cfcatch.message})>
	</cfcatch>
</cftry>

<cfoutput>
	<h2>QoQ cfqueryparam-in-SELECT repro</h2>
	<p>Engine: <strong>#local.tEngine# #local.tVersion#</strong></p>
	<table border="1" cellpadding="4" cellspacing="0">
		<tr>
			<th>Case</th>
			<th>Test</th>
			<th>Expected</th>
			<th>Actual</th>
			<th>Detail</th>
		</tr>
		<cfloop array="#local.sResults#" index="local.sResult">
			<tr>
				<td>#local.sResult.nCase#</td>
				<td>#local.sResult.tName#</td>
				<td>#local.sResult.tExpected#</td>
				<cfif local.sResult.bPassed>
					<td style="color: green;"><strong>PASS</strong></td>
				<cfelse>
					<td style="color: red;"><strong>FAIL</strong></td>
				</cfif>
				<td>#encodeForHtml(local.sResult.tDetail)#</td>
			</tr>
		</cfloop>
	</table>
</cfoutput>

Thanks for the careful report and the self-contained reproduction. Your workaround, leaving the parameter out of the SELECT list and adding the value afterwards, is a good one until this is fixed.

I checked this against the current source, and the analysis holds. On the DISTINCT / GROUP BY path, Lucee treats the bound parameter as if it were a column named “?” and then fails looking that column up in the source query. That’s why a parameter in the SELECT list works without DISTINCT, and why a literal in the same place also works.

This isn’t covered by the older tickets you found. LDEV-2128 was about UNION, and LDEV-5634 is what made the bad lookup start throwing instead of failing silently. I’ve filed Jira for this. We’ll get it fixed in the QoQ engine so DISTINCT and GROUP BY handle select-list parameters the same way the other paths already do.

Thanks again for the excellent write-up.