Possible Bug: QoQ aggregate issue when using WHERE and ORDER BY

language
1

Tested on

  • TryCF Lucee 5.LATEST and 6 BETA (unexpected behavior)
  • Local Lucee 6.0.3.1 (unexpected behavior)
  • TryCF ACF 2023 (working)

I ran into an issue today where I added an ORDER BY to a QoQ I was using to calculating a SUM. It was my mistake as I didn’t need the order at all, but it still produced some pretty unexpected results which got me to test further.

It would appear that when using an ORDER BY with any WHERE clause in the query the aggregate functions (sum) work as if the ORDER BY is a GROUP BY.

I tested it on ACF and I am getting the expected result, but Lucee 5 and 6 gives something unexpected.

<cfscript>
  /*leaving this here to test against a RDBMS as I thought it was related, but it produces the same*/
  /*
    testQuery = queryExecute("
      select 1 as amount, 'a' as relatedData, 1 as unrelatedData
      union select 2 as amount, 'a' as relatedData, 2 as unrelatedData
      union select 3 as amount, 'a' as relatedData, 3 as unrelatedData
    ", {}, {datasource=DSN});
  */

  testQuery = queryNew("amount, relatedData, unrelateddata","integer, varchar, integer");
  testQuery.addRow({amount: 1, relatedData: "a", unrelateddata: 1});
  testQuery.addRow({amount: 2, relatedData: "a", unrelateddata: 2});
  testQuery.addRow({amount: 3, relatedData: "a", unrelateddata: 3});

  writedump(testQuery);

/*Simple Sum Aggregate*/
  sumSimple = queryExecute("select sum(amount) from testQuery", {}, {dbtype="query"});
  writedump(sumSimple); //as expected

/*Sum with a where*/
  SumWithWhere = queryExecute("select sum(amount) from testQuery where 1=1", {}, {dbtype="query"});
  writedump(SumWithWhere); //as expected

/*Sum with an order by*/
  SumWithOrder = queryExecute("select sum(amount) from testQuery order by unrelatedData", {}, {dbtype="query"});
  writedump(SumWithOrder); //as expected

/*Sum with a where AND an order by */
  SumWithOrderAndWhere = queryExecute("select sum(amount) from testQuery where 1=1 order by unrelatedData", {}, {dbtype="query"});
  writedump(SumWithOrderAndWhere); //breaking, behaves as a group by?

/*Updating the ordered value to cause a grouping */
  testQuery.setCell("unrelatedData", 2, 3);
  SumWithOrderAndWhere = queryExecute("select sum(amount) from testQuery where 1=1 order by unrelatedData", {}, {dbtype="query"});
  writedump(SumWithOrderAndWhere); //breaking, behaves as a group by?
</cfscript>

image
image762×1226 38.1 KB

1 Like
2

I know that @Brad_Wood added some low-level QoQ optimizations to vastly improve performance not so long ago (maybe a year or so). Possibly a regression for this odd edge-case. Brad, what version were those added in - maybe JF could use TryCF on the previous version to see if he can still reproduce.

1 Like
3

It’s interesting you say Adobe gives you the “expected” result, because I’d expect that to error :slight_smile: It’s technically invalid to order on a column not in the select list when aggregating, That’s because the query partitioning happens first, and then the order by is applied to the final result. That said, I’m sure several of the more “forgiving” RDMSs prolly allow this the same way they allow ungrouped columns to be selected and just pick the first value.

This bug has been there since my very first rewrite of QoQ when I introduced aggregate functionality. Even though your select doesn’t have a GROUP BY, it still goes through the partitioning process because there is an aggregate in the select. The temporary tables created while the data is partitioned include any additional columns not in the final result, but required for other operations including ordering, So the existence of the ORDER BY gets the unrelatedData column included in the partitioned data, which is fine.

The bug is an if statement trying to detect when you use DISTINCT which also partitions the data, but simply uses ALL COLUMNS for unique-ness. This if statement was getting tripped since there was no group by, so it defaulted the group by columns internally to just all columns being partitioned (which included the unrelatedData column). I’ll send a pull request to Lucee 6 with a fix. I don’t think it will be backported however since it’s not a 5 → 6 regression. (It would have been there since around 5.3.8 I think)

2 Likes
4

Ticket: [LDEV-5031] - Lucee

Pull: LDEV-5031 by bdw429s · Pull Request #2403 · lucee/Lucee · GitHub

@Zackster I added a test case but I’m a little swamped and don’t have the Lucee test harness up and running on my current laptop. Looks like the GitHub actions failed for the pull, but for some unrelated build issue. Can you help see this one through please?

2 Likes
5

In my investigation I got “expected” as does what I wanted it to do then :wink: Yes, if it threw an error that would be better even.

It not something we would usually write, I just got trigger happy fixing a bug with a missing ORDER BY we had which became apparent on Lucee 6 and the included perf. improvements (Lucee 6 - Query of Queries Loses Order From Source Query - #2 by Gavin_Baumanis).

As always @bdw429s, thanks for the detailed explanation and fix!

1 Like