tnakama created CALCITE-7361:
--------------------------------
Summary: 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
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)
