Join queries with order by clause doesn't work

[7Luma]

Hi! Join queries with order by clause doesn't work

Affected Version

0.23.0, 0.24.0

Description

Queries like (with join and order by clause):

SELECT "T1"."__time"
FROM "SomeTableA" AS "T1"
JOIN "SomeTableB" AS "T2"
ON "T1"."A" = "T2"."B"
ORDER BY "T1"."__time" ASC

Doesn't work at 0.23.0 and 0.24.0 versions of Druid (but works at 0.22.0). "Doesn't work" means that query processing ends with exception on broker node:

Error: Unknown exception
Cannot convert query parts into an actual query
org.apache.druid.sql.calcite.rel.CannotBuildQueryException

More detailed log here: broker.log

[JLuma]

@gianm Can you please share you thoughts on this issue? Or maybe some info/details is missing? It's looks like a significant bug.

[gianm]

We haven't had a chance to write a unit test demonstrating this problem yet. If you have one you could share, that would certainly accelerate the process, and would be very appreciated! It would go in CalciteJoinQueryTest. If not, that's ok, we will come around to do it on our own.

[abhishekagarwal87]

I think https://github.com/apache/druid/pull/12418 caused it. @somu-imply FYI.

[somu-imply]

I'll add the unit test. Seems like #13173 fixes this. The query under consideration is

select t1.__time 
from foo as t1
JOIN numFoo as t2
on t1.dim2 = t2.dim2
order by t1.__time ASC

I'll update the unit test case with this query

[gianm]

Thanks for looking into it @somu-imply!

[somu-imply]

The test case has been added through #13173