[IGNITE-18212] GROUP BY with alias causes column not found in any table error

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.0
• Component/s: sql
• Labels: calcite2-required ignite-3
• Release Note: Column aliases and column ordinals may be used in GROUP BY clause now
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Konstantin Orlov
• Reporter: Pavel Tupitsyn
• Created: 21/Nov/22 16:32
• Updated: 04/Sep/24 16:19
• Resolved: 22/Nov/22 13:19

Description

Query

select _T0.VAL as G0 from PUBLIC.TBL_INT8 as _T0 group by G0 order by (G0) asc

Error

org.apache.calcite.runtime.CalciteContextException: From line 1, column 59 to line 1, column 60: Column 'G0' not found in any table
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5362)
	at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:273)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6511)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$ExtendedExpander.visit(SqlValidatorImpl.java:6740)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$ExtendedExpander.visit(SqlValidatorImpl.java:6685)
	at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6500)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.expandGroupByOrHavingExpr(SqlValidatorImpl.java:6109)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause(SqlValidatorImpl.java:4327)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3701)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validateSelect(IgniteSqlValidator.java:207)
	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64)
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1107)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1078)
	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:248)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1053)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:759)
	at org.apache.ignite.internal.sql.engine.prepare.IgnitePlanner.validateAndGetTypeMetadata(IgnitePlanner.java:210)
	at org.apache.ignite.internal.sql.engine.prepare.PrepareServiceImpl.lambda$prepareQuery$1(PrepareServiceImpl.java:227)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'G0' not found in any table
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
	at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)

Issue Links

• blocks: IGNITE-18215 .NET: Thin 3.0: LINQ: Group by calculated value
• is related to: IGNITE-14885 Calcite engine. Allow grouping by alias or ordinal
• is related to: IGNITE-15583 Calcite. Column not found error with correlated sub query.
• relates to: IGNITE-18083 .NET: Thin 3.0: LINQ: Groupings
• links to: GitHub Pull Request #1364
• links to: GitHub Pull Request #11500
• links to: PR