1

I was comparing the following queries in my local CF & Lucee servers. The ColdFusion server throws a QoQ runtime error. However, when I execute the same queries in Lucee, it returns the needed query results. 

<cfquery name="getusers" datasource="myDSN">
    SELECT 
        UC.UserContactName, U.UserID 
    FROM Users U 
        INNER JOIN UserContacts UC ON U.UserID = UC.UserID 
    WHERE U.UserID in (99,52,41,76,15) 
</cfquery>

<cfquery name="getContactName" dbtype="query">
    SELECT UserContactName FROM getusers 
    WHERE U.UserID  = <cfqueryparam value="76" cfsqltype="cf_sql_integer">
</cfquery>

<cfdump var="#getContactName#" />

The CF server throws this error on the above code, because it considers the alias name:

The selected column reference U.UserID does not match any table in the FROM table list.

However, the Lucee server doesn't consider the alias name, ran runs the above without error. Does anyone know why the Lucee server does not consider the QoQ column alias name? Please share your thoughts or suggestions about this.

Community
  • 1
  • 1
Sathish Chelladurai
  • 670
  • 1
  • 8
  • 23

1 Answers1

7

The getusers query results in the columns:

UserContactName | UserID

A QoQ on getusers no longer has any table related information stored. If you reference U (as in U.UserID), ACF expects a (new) reference in the QoQ to be able to resolve what U even means.

Lucee on the other hand stores these information and can resolve it using an alias. You can check the source code how it parses the QoQ statement.

Regardless of the capabilities of Lucee, I suggest to remove former aliases in QoQ statements. Consider debugging a QoQ statement when your query dump doesn't even hint you about stored aliases.

Alex
  • 7,743
  • 1
  • 18
  • 38