julianhyde opened a new issue #9871:
URL: https://github.com/apache/druid/issues/9871
Please provide a detailed title (e.g. "Broker crashes when using TopN query
with Bound filter" instead of just "Broker crashes").
### Affected Version
0.18 (approximately)
### Description
The query
```
SELECT
users.age AS "users.age",
TIME_FORMAT(FLOOR(TIME_PARSE(users.created_at, 'yyyy-MM-dd HH:mm:ss')
TO MONTH), 'yyyy-MM') AS "users.created_at_month",
COUNT(*) AS "users.count"
FROM druid.users AS users
WHERE
(users.age < 30)
GROUP BY users.age ,TIME_FORMAT(FLOOR(TIME_PARSE(users.created_at,
'yyyy-MM-dd HH:mm:ss') TO MONTH), 'yyyy-MM')
ORDER BY TIME_FORMAT(FLOOR(TIME_PARSE(users.created_at, 'yyyy-MM-dd
HH:mm:ss') TO MONTH), 'yyyy-MM') DESC
LIMIT 500
```
gives error
```
Remote driver error: RuntimeException:
org.apache.calcite.runtime.CalciteContextException: From line 17, column 110 to
line 17, column 114: Table 'users' not found -> CalciteContextException: From
line 17, column 110 to line 17, column 114: Table 'users' not found ->
SqlValidatorException: Table 'users' not found
org.apache.calcite.avatica.Helper.createException(org/apache/calcite/avatica/Helper.java:54)
org.apache.calcite.avatica.Helper.createException(org/apache/calcite/avatica/Helper.java:41)
org.apache.calcite.avatica.AvaticaStatement.executeInternal(org/apache/calcite/avatica/AvaticaStatement.java:163)
org.apache.calcite.avatica.AvaticaStatement.executeQuery(org/apache/calcite/avatica/AvaticaStatement.java:227)
java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)
```
The query is generated by Looker. We have found a workaround (write the
`ORDER BY` in terms of an alias from the `SELECT` clause) and this is not a
blocker for us, so logging FYI.
Per SQL standard, the reference to `users.created_at` in the `ORDER BY`
clause should be valid - i.e. the `users` table alias should be in scope - even
if the validator determines that you can't use that particular column because
of the intervening `GROUP BY`.
I believe that the error goes away if you remove `DESC`. This seems
significant.
Because the `ORDER BY` expression is structurally identical to the `GROUP
BY` expression, Calcite should allow it to be used. But maybe some rewrite
(e.g. desugaring to deal with the `DESC`) has made the expression not identical.
(I corresponded with @gianm about this in email. It is entirely possible
that the underlying issue is in Calcite. Credit to @ntkawasaki for tracking
down the bug and finding a workaround; I'm only reporting it and pontificating
on possible causes.)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
