QoQ issue with group by alias

language
1

Hi,

I think there is a bug in the latest version of Lucee 5 and also in 6
When you do a QoQ and you group by alias you get only one result, but when you group by the name of the column, you get the correct result, kindly check

<cfset users = queryNew("id, firstname", "integer, varchar", [
{"id":1, "firstname":"Han"},
{"id":2, "firstname":"Han"},
{"id":3, "firstname":"James"}
])>
<cfset writeDump(users)>
<cfquery name="u1" dbtype="query">
    select firstname name from users
    group by name
</cfquery>
<cfset writeDump(u1)>
<cfquery name="u2" dbtype="query">
    select firstname name from users
    group by firstname
</cfquery>
<cfset writeDump(u2)>

Screenshot 2023-09-01 at 12.04.44 PM
Screenshot 2023-09-01 at 12.04.44 PM1160Ă—642 83.2 KB

2

@Zackster Do let me know if i should raise this as a bug

3

let’s see what @Brad_Wood says.

but looks like a bug, adding a join forcing fallback to HSQLDB, behaves the same for both approaches

<cfset users = queryNew("id, firstname", "integer, varchar", [
{"id":1, "firstname":"Han"},
{"id":2, "firstname":"Han"},
{"id":3, "firstname":"James"}
])>

<cfset writeDump(users)>
<cfquery name="u1" dbtype="query">
    select firstname name from users
    group by name
</cfquery>
<cfset writeDump(u1)>
<cfquery name="u2" dbtype="query">
    select firstname name from users
    group by firstname
</cfquery>
<cfset writeDump(u2)>

<cftry>
    <cfquery name="u1" dbtype="query">
        select u1.firstname name 
        from    users u1, users u2
        where   u1.firstname=u2.firstname
        group by name
    </cfquery>
    <cfset writeDump(u1)>
    <cfcatch><cfoutput>#cfcatch#</cfoutput></cfcatch>
</cftry>
<cftry>

<cfquery name="u2" dbtype="query">
    select u1.firstname name 
    from    users u1, users u2
    where   u1.firstname=u2.firstname
    group by firstname
</cfquery>
<cfset writeDump(u2)>

<cfcatch><cfoutput>#cfcatch#</cfoutput></cfcatch>
</cftry>

image
image1366Ă—1784 177 KB

4

Bug reported [LDEV-4680] - Lucee

1 Like
5

This is a duplicate of this question Problems with QoQ after upgrading to 5.4.3.2

The long and short of it is that grouping by an alias is not standard SQL and doesn’t work in most DB’s. HSQLDB somehow has limited support for it that works in some cases. Native QoQ doesn’t error (it prolly should) but also just won’t match anything in the where clause.

My recommendation right now for you is not to group by an alias.

4 Likes
6

Noted… thanks

7

I should clarify, while this report is very similar to the one I linked to, it is slightly different. This report is about using a column alias in the group by and the other one was for using an alias in the where clause.

I THINK this represents the matrix of DB engines that support what

  • Adobe QoQ - alias in group by :white_check_mark: - alias in where :x:
  • Lucee QoQ - alias in group by :x: - alias in where :x:
  • HSQLDB - alias in group by :white_check_mark: - alias in where :white_check_mark: / :x: (Used to *work in 1.8, but no longer in 2?)
  • SQL Server - alias in group by :x: - alias in where :x:
  • MySQL - alias in group by :white_check_mark: - alias in where :x:
  • Postgres - alias in group by :white_check_mark: - alias in where :x:

It is worth noting that even though SQL Server (up to 2019) doesn’t support aliases in the group by, MySQL and Postres do appear to support this.

5 Likes
8

Nice move @bdw429s ! Love to see such a matrix, reminds me of “caniuse”. :clap:

9

In some circumstances SQL Server will allow the use of an alias in the order by clause. If it is a simple alias directly on a column name it should work. If is an alias on the result of one or more TSQL functions, CASE statements, or concatenation of multiple columns, that will not work in the order by clause.

1 Like