[
https://issues.apache.org/jira/browse/CALCITE-7361?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18104199#comment-18104199
]
Mihai Budiu commented on CALCITE-7361:
--------------------------------------
The trick is to customize your type factory by overloading this method:
{code:java}
@Override
public RelDataType createStructType(
final List<RelDataType> typeList,
final List<String> fieldNameList) {
return super.createStructType(StructKind.PEEK_FIELDS_NO_EXPAND,
typeList, fieldNameList);
} {code}
> 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)
