[CALCITE-4926] 'ORDER BY' misses alias/table of own 'WITH'

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.28.0
• Assignee: Unassigned
• Reporter: Vladimir Steshin
• Created: 08/Dec/21 13:16
• Updated: 27/Mar/22 08:41

Description

"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.

 

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)

Issue Links

• Dependency: IGNITE-15995 Calcite engine. Order by subquery containing WITH clause failed