[
https://issues.apache.org/jira/browse/HIVE-28088?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17820054#comment-17820054
]
okumin commented on HIVE-28088:
-------------------------------
I tested them with various databases.
Spark SQL 3.4.1.
{code:java}
(1)
key key
(2)
[AMBIGUOUS_REFERENCE] Reference `c`.`key` is ambiguous, could be: [`c`.`key`,
`c`.`key`].; line 4 pos 3
(3)
key key
(4)
[AMBIGUOUS_REFERENCE] Reference `c`.`key` is ambiguous, could be: [`c`.`key`,
`c`.`key`].; line 6 pos 15{code}
Trino 413.
{code:java}
(1)
key | key
-----+-----
(0 rows)
(2)
Query 20240223_125426_00032_p9au2 failed: line 5:4: Column 'c.key' is ambiguous
(3)
key | key
-----+-----
(0 rows)
(4)
Query 20240223_125426_00034_p9au2 failed: line 7:16: Column 'c.key' is
ambiguous {code}
PostgreSQL 16.2.
{code:java}
(1)
key | key
-----+-----
(0 rows)
(2)
ERROR: column reference "key" is ambiguous
LINE 4: ON c.key = d.key;
(3)
key | key
-----+-----
(0 rows)
(4)
ERROR: column reference "key" is ambiguous
LINE 6: JOIN cte d ON (c.key = d.key);{code}
MySQL 8.3.
{code:java}
(1)
ERROR 1060 (42S21): Duplicate column name 'key'
(2)
ERROR 1060 (42S21): Duplicate column name 'key'
(3)
ERROR 1060 (42S21): Duplicate column name 'key'
(4)
ERROR 1060 (42S21): Duplicate column name 'key'{code}
> CalcitePlanner fails to resolve column names on materialized CTEs
> -----------------------------------------------------------------
>
> Key: HIVE-28088
> URL: https://issues.apache.org/jira/browse/HIVE-28088
> Project: Hive
> Issue Type: Bug
> Components: CBO
> Affects Versions: 4.0.0-beta-1
> Reporter: okumin
> Assignee: okumin
> Priority: Major
>
> We found a case where CBO fails when it refers to a materialized CTE.
> These are queries to reproduce the issue.
> {code:java}
> DROP TABLE IF EXISTS test;
> CREATE TABLE test (key STRING);
> set hive.optimize.cte.materialize.threshold=1;
> set hive.optimize.cte.materialize.full.aggregate.only=false;
> EXPLAIN CBO WITH materialized_cte AS (
> SELECT a.key, b.key
> FROM test a
> JOIN test b ON (a.key = b.key)
> )
> SELECT *
> FROM materialized_cte c
> JOIN materialized_cte d ON (c.key = d.key); {code}
> CBO fails.
> {code:java}
> +----------+
> | Explain |
> +----------+
> +----------+ {code}
> Error log on HiveServer2.
> {code:java}
> 2024-02-23T04:43:19,065 ERROR [d145a95b-8840-4d2b-9869-a186b4bd82ef
> HiveServer2-Handler-Pool: Thread-555] parse.CalcitePlanner: CBO failed,
> skipping CBO.
> org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSemanticException: Could
> not resolve column name
> at
> org.apache.hadoop.hive.ql.parse.type.RexNodeExprFactory.getPosition(RexNodeExprFactory.java:184)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> h at
> org.apache.hadoop.hive.ql.parse.type.RexNodeExprFactory.createColumnRefExpr(RexNodeExprFactory.java:161)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.type.RexNodeExprFactory.createColumnRefExpr(RexNodeExprFactory.java:97)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.type.JoinCondTypeCheckProcFactory$JoinCondDefaultExprProcessor.processQualifiedColRef(JoinCondTypeCheckProcFactory.java:188)
> ~[hive-exec-4.0.0-beta-2-
> SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.type.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:1414)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.lib.CostLessRuleDispatcher.dispatch(CostLessRuleDispatcher.java:66)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatchAndReturn(DefaultGraphWalker.java:105)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:89)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.lib.ExpressionWalker.walk(ExpressionWalker.java:101)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:120)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.type.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:231)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.type.RexNodeTypeCheck.genExprNodeJoinCond(RexNodeTypeCheck.java:60)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genJoinRelNode(CalcitePlanner.java:2656)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genJoinLogicalPlan(CalcitePlanner.java:2888)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genLogicalPlan(CalcitePlanner.java:5048)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1625)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1569)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.calcite.tools.Frameworks.lambda$withPlanner$0(Frameworks.java:131)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.perform(CalcitePrepareImpl.java:914)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at org.apache.calcite.tools.Frameworks.withPrepare(Frameworks.java:180)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at org.apache.calcite.tools.Frameworks.withPlanner(Frameworks.java:126)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1321)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:570)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:13122)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:465)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:327)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.ExplainSemanticAnalyzer.analyzeInternal(ExplainSemanticAnalyzer.java:180)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at
> org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:327)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at org.apache.hadoop.hive.ql.Compiler.analyze(Compiler.java:224)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at org.apache.hadoop.hive.ql.Compiler.compile(Compiler.java:107)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:519)
> ~[hive-exec-4.0.0-beta-2-SNAPSHOT.jar:4.0.0-beta-2-SNAPSHOT]{code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
