[
https://issues.apache.org/jira/browse/CALCITE-7361?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18050436#comment-18050436
]
Mihai Budiu commented on CALCITE-7361:
--------------------------------------
In general struct field name lookup in Calcite does not always do what you
would expect.
Even in the default conformance in SELECT statement you have to qualify struct
names with table names:
SELECT x.a FROM T
does not work, but
SELECT T.x.a FROM T
does.
It may be the same problem. I think that qualified names are assumed to always
start with a relation alias.
> HAVING clause fails to resolve struct field access on aggregate function
> alias with BIG_QUERY conformance
> -----------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7361
> URL: https://issues.apache.org/jira/browse/CALCITE-7361
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: tnakama
> Priority: Major
>
> When using SqlConformanceEnum.BIG_QUERY, referencing a struct field of an
> aggregate function alias in the HAVING clause fails with "Table 'X' not
> found" error, even though isHavingAlias() returns true for this conformance.
> Environment
> - Calcite Version: 1.41.0
> - Java Version: 21
> - SqlConformance: SqlConformanceEnum.BIG_QUERY
> Expected Behavior
> The following query should be valid since:
> 1. BIG_QUERY conformance has isHavingAlias() = true
> 2. BigQuery itself supports referencing SELECT aliases in HAVING clause
> 3. Struct field access syntax (alias.field) is standard for ROW/STRUCT types
> SELECT
> user_id,
> MY_AGGREGATE_FUNCTION(...) AS result -- returns STRUCT<level INTEGER,
> data STRING>
> FROM my_table
> GROUP BY user_id
> HAVING result.level = 2 -- Should work: accessing struct field of alias
> Actual Behavior
> Validation fails with:
> org.apache.calcite.runtime.CalciteContextException: Table 'result' not found
> The compound identifier result.level is being interpreted as table.column
> rather than alias.field.
> Stack Trace
> org.apache.calcite.tools.ValidationException:
> org.apache.calcite.runtime.CalciteContextException: From line 11, column 8 to
> line 11, column 13: Table 'result' not found
> at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:228)
> ...
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line
> 11, column 8 to line 11, column 13: Table 'result' not found
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:6018)
> at
> org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:364)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:7256)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$ExtendedExpander.visit(SqlValidatorImpl.java:7644)
> at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
> ...
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateHavingClause(SqlValidatorImpl.java:5091)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:4129)
> ...
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Table
> 'result' not found
> Minimal Reproduction
> import org.apache.calcite.config.Lex;
> import org.apache.calcite.rel.type.*;
> import org.apache.calcite.schema.SchemaPlus;
> import org.apache.calcite.schema.impl.AbstractTable;
> import org.apache.calcite.sql.*;
> import org.apache.calcite.sql.parser.SqlParser;
> import org.apache.calcite.sql.type.*;
> import org.apache.calcite.sql.validate.SqlConformanceEnum;
> import org.apache.calcite.tools.*;
> import org.apache.calcite.util.Optionality;
> import java.util.List;
> public class HavingStructFieldReproduction {
> public static void main(String[] args) throws Exception {
> // Create a custom aggregate function that returns STRUCT
> SqlAggFunction myAgg = new SqlAggFunction(
> "MY_AGG",
> null,
> SqlKind.OTHER_FUNCTION,
> opBinding -> {
> RelDataTypeFactory tf = opBinding.getTypeFactory();
> return tf.createStructType(
> StructKind.PEEK_FIELDS_DEFAULT, //
> StructKind.FULLY_QUALIFIED also cause issue.
> List.of(
> tf.createSqlType(SqlTypeName.INTEGER),
> tf.createSqlType(SqlTypeName.VARCHAR)
> ),
> List.of("level", "data")
> );
> },
> InferTypes.RETURN_TYPE,
> OperandTypes.ANY,
> SqlFunctionCategory.USER_DEFINED_FUNCTION,
> false,
> false,
> Optionality.FORBIDDEN
> ) {};
> // Create schema with test table
> SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> rootSchema.add("test", new AbstractTable() {
> @Override
> public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.createStructType(
> List.of(
> typeFactory.createSqlType(SqlTypeName.VARCHAR),
> typeFactory.createSqlType(SqlTypeName.INTEGER)
> ),
> List.of("user_id", "value")
> );
> }
> });
> // Configure with BIG_QUERY conformance
> SqlParser.Config parserConfig = SqlParser.config()
> .withConformance(SqlConformanceEnum.BIG_QUERY)
> .withLex(Lex.BIG_QUERY)
> .withCaseSensitive(false);
> FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(parserConfig)
> .operatorTable(SqlOperatorTables.chain(
> SqlStdOperatorTable.instance(),
> SqlOperatorTables.of(myAgg)
> ))
> .defaultSchema(rootSchema)
> .build();
> Planner planner = Frameworks.getPlanner(config);
> // This query should work but fails
> String sql = """
> SELECT user_id, MY_AGG(value) AS result
> FROM test
> GROUP BY user_id
> HAVING result.level = 2
> """;
> SqlNode parsed = planner.parse(sql);
> planner.validate(parsed); // Throws: Table 'result' not found
> }
> }
> Analysis
> The issue occurs in DelegatingScope.fullyQualify() when processing the
> HAVING clause:
> 1. SqlValidatorImpl.validateHavingClause() calls extendedExpand()
> 2. The Expander visits result.level as a SqlIdentifier
> 3. fullyQualify() attempts to resolve result as a table name
> 4. Since no table named result exists, validation fails
> The isHavingAlias() conformance check appears to only handle simple alias
> references (e.g., HAVING alias = value), not struct field access on aliases
> (e.g., HAVING alias.field = value).
> Workaround
> Use a subquery to make the alias visible in WHERE clause:
> SELECT * FROM (
> SELECT user_id, MY_AGG(value) AS result
> FROM test
> GROUP BY user_id
> ) sub
> WHERE sub.result.level = 2
> Related Issues
> - CALCITE-2799: Allow alias in having clause for aggregate functions
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
