[jira] [Commented] (CALCITE-6853) Nested window aggregate throws UnsupportedOperationException with the default ValidatorConfig

["Heng Xiao (Jira)" <ji...@apache.org>]

    [ 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)