[ https://issues.apache.org/jira/browse/CALCITE-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17512860#comment-17512860 ]
Jiajun Xie commented on CALCITE-4926:
-------------------------------------
If you only want to pass the validation phase, you can add a few lines of code.
{code:java}
private void validateOrderItem(SqlSelect select, SqlNode orderItem) {
+ final SqlValidatorScope orderScope = getOrderScope(select);
switch (orderItem.getKind()) {
case DESCENDING:
validateFeature(RESOURCE.sQLConformance_OrderByDesc(),
@@ -4209,11 +4209,15 @@ private void validateOrderItem(SqlSelect select, SqlNode orderItem) {
validateOrderItem(select,
((SqlCall) orderItem).operand(0));
return;
+ case SCALAR_QUERY:
+ SqlNode operand = ((SqlCall) orderItem).operand(0);
+ if (operand.getKind() == SqlKind.WITH) {
+ validateWith((SqlWith) operand, orderScope);
+ return;
+ }
default:
break;
}
-
- final SqlValidatorScope orderScope = getOrderScope(select);
validateExpr(orderItem, orderScope);
}
{code}
But I don't think it's just about the {*}SqlValidatorImpl{*}, *SqlToRelConverter* also not support `with` in `ORDER BY`.
{code:java}
@Test void testWithInOrderBy() {
final String sql = "WITH t AS (SELECT 1) SELECT * FROM emp ORDER BY (SELECT * FROM t)";
sql(sql).ok();
}
java.lang.AssertionError
at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5148)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:4390)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:749)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:670)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3595)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:590)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertWith(SqlToRelConverter.java:4453)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3609)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:590)
at org.apache.calcite.sql.test.AbstractSqlTester.convertSqlToRel2(AbstractSqlTester.java:536)
at org.apache.calcite.sql.test.AbstractSqlTester.assertSqlConvertsTo(AbstractSqlTester.java:477)
at org.apache.calcite.sql.test.AbstractSqlTester.assertConvertsTo(AbstractSqlTester.java:455)
at org.apache.calcite.test.SqlToRelFixture.convertsTo(SqlToRelFixture.java:106)
at org.apache.calcite.test.SqlToRelFixture.ok(SqlToRelFixture.java:94)
at org.apache.calcite.test.SqlToRelConverterTest.testWithInOrderBy(SqlToRelConverterTest.java:1016) {code}
I'm curious why order by other table field. And will the order of results be stable?
> 'ORDER BY' misses alias/table of own 'WITH'
> -------------------------------------------
>
> Key: CALCITE-4926
> URL: https://issues.apache.org/jira/browse/CALCITE-4926
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.28.0
> Reporter: Vladimir Steshin
> Priority: Minor
>
> "SELECT * FROM emp ORDER BY (WITH t AS (SELECT 1) SELECT * FROM t)"
> fails with:
> "Column 'T' not found in any table".
> Working similar one:
> "WITH t AS (SELECT 1) SELECT * FROM emp ORDER BY (SELECT * FROM t)"
> As I understand, SqlValidatorImpl#OrderExpressionExpander (extends SqlScopedShuttle) hurries to find the alias in the upper, parent query, whole "select * from ...". But this query and the related namespaces has no such alias. Probably, it's better to search order-by's namespace first.
>
> {code:java}
> newValidationError:5266, SqlValidatorImpl (org.apache.calcite.sql.validate)
> fullyQualify:273, DelegatingScope (org.apache.calcite.sql.validate)
> fullyQualify:95, OrderByScope (org.apache.calcite.sql.validate)
> visit:6548, SqlValidatorImpl$OrderExpressionExpander (org.apache.calcite.sql.validate)
> visit:6461, SqlValidatorImpl$OrderExpressionExpander (org.apache.calcite.sql.validate)
> accept:324, SqlIdentifier (org.apache.calcite.sql)
> visitChild:134, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> visitChild:101, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> acceptCall:954, SqlOperator (org.apache.calcite.sql)
> visit:68, SqlShuttle (org.apache.calcite.sql.util)
> visitScoped:64, SqlScopedShuttle (org.apache.calcite.sql.validate)
> visitScoped:6557, SqlValidatorImpl$OrderExpressionExpander (org.apache.calcite.sql.validate)
> visit:54, SqlScopedShuttle (org.apache.calcite.sql.validate)
> visit:37, SqlScopedShuttle (org.apache.calcite.sql.validate)
> accept:161, SqlCall (org.apache.calcite.sql)
> visit:80, SqlShuttle (org.apache.calcite.sql.util)
> visit:41, SqlShuttle (org.apache.calcite.sql.util)
> accept:266, SqlNodeList (org.apache.calcite.sql)
> visitChild:134, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> visitChild:101, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> acceptCall:954, SqlOperator (org.apache.calcite.sql)
> visit:68, SqlShuttle (org.apache.calcite.sql.util)
> visitScoped:64, SqlScopedShuttle (org.apache.calcite.sql.validate)
> visitScoped:6557, SqlValidatorImpl$OrderExpressionExpander (org.apache.calcite.sql.validate)
> visit:54, SqlScopedShuttle (org.apache.calcite.sql.validate)
> visit:37, SqlScopedShuttle (org.apache.calcite.sql.validate)
> accept:161, SqlCall (org.apache.calcite.sql)
> visitChild:134, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> visitChild:101, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> acceptCall:954, SqlOperator (org.apache.calcite.sql)
> visit:68, SqlShuttle (org.apache.calcite.sql.util)
> visitScoped:64, SqlScopedShuttle (org.apache.calcite.sql.validate)
> visitScoped:6557, SqlValidatorImpl$OrderExpressionExpander (org.apache.calcite.sql.validate)
> visit:54, SqlScopedShuttle (org.apache.calcite.sql.validate)
> visit:37, SqlScopedShuttle (org.apache.calcite.sql.validate)
> accept:161, SqlCall (org.apache.calcite.sql)
> go:6474, SqlValidatorImpl$OrderExpressionExpander (org.apache.calcite.sql.validate)
> expandOrderExpr:4222, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateExpr:125, OrderByScope (org.apache.calcite.sql.validate)
> validateExpr:4467, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateOrderItem:4217, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateOrderList:4166, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateSelect:3658, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateImpl:64, SelectNamespace (org.apache.calcite.sql.validate)
> validate:89, AbstractNamespace (org.apache.calcite.sql.validate)
> validateNamespace:1100, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateQuery:1071, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validate:247, SqlSelect (org.apache.calcite.sql)
> validateScopedExpression:1046, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validate:752, SqlValidatorImpl (org.apache.calcite.sql.validate)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)