[
https://issues.apache.org/jira/browse/HIVE-28088?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825035#comment-17825035
]
okumin commented on HIVE-28088:
-------------------------------
I observe multiple problems.
* `*` resolves a duplicated column name as its internal name
* Some clauses, WHERE, GROUP BY, and JOIN, don't fail when an ambiguous column
is accessed
* Materialized CTEs don't retain duplicated column names
{code:java}
0: jdbc:hive2://hive-hiveserver2:10000/defaul> WITH cte AS (
. . . . . . . . . . . . . . . . . . . . . . .> SELECT 'k1' AS key, 'k2' AS key
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> SELECT * FROM cte;
+----------+------------+
| cte.key | cte._col1 |
+----------+------------+
| k1 | k2 |
+----------+------------+
0: jdbc:hive2://hive-hiveserver2:10000/defaul> WITH cte AS (
. . . . . . . . . . . . . . . . . . . . . . .> SELECT 'k1' AS key, 'k2' AS key
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> SELECT * FROM cte WHERE key =
'k2';
+----------+------------+
| cte.key | cte._col1 |
+----------+------------+
+----------+------------+
0: jdbc:hive2://hive-hiveserver2:10000/defaul> WITH cte AS (
. . . . . . . . . . . . . . . . . . . . . . .> SELECT 'k1' AS key, 'k2' AS key
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> SELECT key FROM cte;
Error: Error while compiling statement: FAILED: SemanticException Ambiguous
column reference: .key (state=42000,code=40000)
0: jdbc:hive2://hive-hiveserver2:10000/defaul> WITH cte AS (
. . . . . . . . . . . . . . . . . . . . . . .> SELECT 'k1' AS key, 'k2' AS key
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> SELECT key, count(*) FROM cte
GROUP BY key;
+------+------+
| key | _c1 |
+------+------+
| k1 | 1 |
+------+------+
0: jdbc:hive2://hive-hiveserver2:10000/defaul> WITH cte AS (
. . . . . . . . . . . . . . . . . . . . . . .> SELECT 'k1' AS key, 'k2' AS key
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> SELECT * FROM cte a JOIN cte b
ON a.key = b.key;
+--------+----------+--------+----------+
| a.key | a._col1 | b.key | b._col1 |
+--------+----------+--------+----------+
| k1 | k2 | k1 | k2 |
+--------+----------+--------+----------+{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)
