[ 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)