[
https://issues.apache.org/jira/browse/CALCITE-6853?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17929362#comment-17929362
]
Heng Xiao commented on CALCITE-6853:
------------------------------------
Let's take the *sum(sum(sal)) over()* as an example, I've tried to debug this,
the point where the exception was thrown is in the convertOver function, where
it wants to convert the `Over SqlBasicCall` to RexNode. And the
SqlToRelConverter assumed that in the validation phase before, this Over
SqlCall was already `derivedType` before. So it calls ensureType, but it turns
out that it can't get the return type from the validator's nodeToType map and
originalExpr map.
{code:java}
//org.apache.calcite.sql2rel.SqlToRelConverter.convertOver
rexAgg =
rexBuilder.ensureType(call.getParserPosition(),
validator().getValidatedNodeType(call), rexAgg, false); {code}
Looks like the validator didn't called deriveType on the Over SqlBasicCall, but
it actually did, it called deriveType on the `expanded` Over SqlBasicCall.
That's why when the identifierExpansion config is set to true, this bug
disappear. I looked at the code the validator will always expand the select
items, the difference identifierExpansion option makes is whether it changes
the select item list of the original SELECT SqlNode to the expanded item list.
{code:java}
// SqlValidatorImpl#validateSelectList()
SqlNodeList newSelectList =
new SqlNodeList(expandedSelectItems, selectItems.getParserPosition());
if (config.identifierExpansion()) {
select.setSelectList(newSelectList);
}{code}
So validator called deriveType on the expanded Over SqlBasicCall. But when
identifierExpansion sets to false, in the conversion phase, it trys to get the
derived type of the un-expanded original Over SqlBasicCall from the validator.
It just couldn't, so it throws the exception.
> Nested window aggregate throws UnsupportedOperationException with the default
> ValidatorConfig
> ---------------------------------------------------------------------------------------------
>
> Key: CALCITE-6853
> URL: https://issues.apache.org/jira/browse/CALCITE-6853
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Heng Xiao
> Assignee: Heng Xiao
> Priority: Minor
>
> I was trying to run TPC-DS with the default ValidatorConfig. And the default
> value of validatorConfig.identifierExpansion is false.
> java.lang.UnsupportedOperationException were thrown when executing TPC-DS
> query12 and some other TPC-DS queries which used nested window aggregate,
> like `sum(sum(ws_ext_sales_price)) OVER (partition BY i_class)`.
> This bug can be easily reproduced using the following testcase
> {code:java}
> // SqlToRelConverterTest
> @Test void testNestedWindowAggWithIdentifierExpansionDisabled(){
> String sql = "select sum(sum(sal)) over() from emp";
> sql(sql)
> .withFactory(f ->
> f.withValidator((opTab, catalogReader, typeFactory, config) -> {
> if (config.conformance().allowGeometry()) {
> opTab =
> SqlOperatorTables.chain(opTab,
> SqlOperatorTables.spatialInstance());
> }
> return SqlValidatorUtil.newValidator(opTab, catalogReader,
> typeFactory, config.withIdentifierExpansion(false));
> }))
> .withTrim(false)
> .ok();
> } {code}
>
> The error stacktrace is as follows.
> {code:java}
> java.lang.UnsupportedOperationException: class
> org.apache.calcite.sql.SqlBasicCall: SUM(SUM(`SAL`)) OVER ()
> at org.apache.calcite.util.Util.needToImplement(Util.java:1112)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType(SqlValidatorImpl.java:1908)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOver(SqlToRelConverter.java:2333)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.access$2200(SqlToRelConverter.java:253)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5701)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:3664)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:3500)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:4739)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:809)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:735)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3915)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:628)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.convertSqlToRel2(AbstractSqlTester.java:544)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertSqlConvertsTo(AbstractSqlTester.java:485)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertConvertsTo(AbstractSqlTester.java:463)
> at
> org.apache.calcite.test.SqlToRelFixture.convertsTo(SqlToRelFixture.java:106)
> at org.apache.calcite.test.SqlToRelFixture.ok(SqlToRelFixture.java:94)
> at
> org.apache.calcite.test.SqlToRelConverterTest.testNestedWindowAggWithIdentifierExpansionDisabled(SqlToRelConverterTest.java:5373){code}
>
> The identifierExpansion config option is suppose to have nothing to do with
> nested window aggregate, so I think this is a bug, and was not tested out
> before due to the Caclite test framework `Fixture` sets this option to true
> as default for test cases.
> {code:java}
> public static final SqlToRelFixture DEFAULT =
> new SqlToRelFixture("?", true, TESTER, SqlTestFactory.INSTANCE, false,
> false, null)
> .withFactory(f ->
> f.withValidator((opTab, catalogReader, typeFactory, config) -> {
> if (config.conformance().allowGeometry()) {
> opTab =
> SqlOperatorTables.chain(opTab,
> SqlOperatorTables.spatialInstance());
> }
> return SqlValidatorUtil.newValidator(opTab, catalogReader,
> typeFactory, config.withIdentifierExpansion(true));
> })
> // ........{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
