You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2024/04/26 12:39:00 UTC
[jira] [Commented] (HIVE-28222) Ambiguous table alias exception for queries with self joins
[ https://issues.apache.org/jira/browse/HIVE-28222?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17841200#comment-17841200 ]
Stamatis Zampetakis commented on HIVE-28222:
--------------------------------------------
The query fails while trying to transform the optimized AST obtained from the CBO plan to an Operator tree. The final CBO plan that leads to the "problematic" AST is shown below.
{noformat}
2024-04-26T05:15:50,258 DEBUG [798b06bb-715e-4933-87a0-0bfe7dbe4c22 main] translator.PlanModifierForASTConv: Final plan after modifier
HiveProject(a.key=[$0], a.value=[$1], a.block__offset__inside__file=[$2], a.input__file__name=[$3], a.row__id=[$4], a.row__is__deleted=[$5], b.key=[$12], b.value=[$13], b.block__offset__inside__file=[$14], b.input__file__name=[$15], b.row__id=[$16], b.row__is__deleted=[$17
], c.key=[$6], c.value=[$7], c.block__offset__inside__file=[$8], c.input__file__name=[$9], c.row__id=[$10], c.row__is__deleted=[$11])
HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(key=[$0], value=[$1], BLOCK__OFFSET__INSIDE__FILE=[$2], INPUT__FILE__NAME=[$3], ROW__ID=[$4], ROW__IS__DELETED=[$5])
HiveTableScan(table=[[default, t1]], table:alias=[t1])
HiveTableScan(table=[[default, t1]], table:alias=[t1])
HiveTableScan(table=[[default, t1]], table:alias=[t1])
{noformat}
It is worth noticing that a HiveTableScan with the "t1" alias appears three times. The exception is raised when a HiveJoin contains the same table scan multiple times (without an interleaving project) cause the generated AST is ambiguous.
> Ambiguous table alias exception for queries with self joins
> -----------------------------------------------------------
>
> Key: HIVE-28222
> URL: https://issues.apache.org/jira/browse/HIVE-28222
> Project: Hive
> Issue Type: Bug
> Components: CBO
> Affects Versions: 4.0.0
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
> Attachments: cbo_self_join_ambiguous_alias_cte.q, cbo_self_join_ambiguous_alias_mv.q, cbo_self_join_ambiguous_alias_subquery.q
>
>
> Various queries containing more than 2 joins of the same table (self joins) fail at compile time.
> A simple reproducer is outlined below.
> {code:sql}
> create table t1 (key int, value int);
> explain cbo
> select * from
> (select key, value, BLOCK__OFFSET__INSIDE__FILE, INPUT__FILE__NAME, ROW__ID, ROW__IS__DELETED from t1) a join
> (select key, value, BLOCK__OFFSET__INSIDE__FILE, INPUT__FILE__NAME, ROW__ID, ROW__IS__DELETED from t1) b join
> (select key, value, BLOCK__OFFSET__INSIDE__FILE, INPUT__FILE__NAME, ROW__ID, ROW__IS__DELETED from t1) c;
> {code}
> The query fails with the following stacktrace:
> {noformat}
> org.apache.hadoop.hive.ql.parse.SemanticException: Line 0:-1 Ambiguous table alias 't1'
> at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.processTable(SemanticAnalyzer.java:1167) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.processJoin(SemanticAnalyzer.java:1679) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.doPhase1(SemanticAnalyzer.java:1899) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.doPhase1(SemanticAnalyzer.java:2113) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.doPhase1(SemanticAnalyzer.java:1754) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:628) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:13178) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:465) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:327) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.ExplainSemanticAnalyzer.analyzeInternal(ExplainSemanticAnalyzer.java:180) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:327) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.Compiler.analyze(Compiler.java:224) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> at org.apache.hadoop.hive.ql.Compiler.compile(Compiler.java:107) ~[hive-exec-4.1.0-SNAPSHOT.jar:4.1.0-SNAPSHOT]
> {noformat}
> The same problem can be hit in other ways when using materialized views ([^cbo_self_join_ambiguous_alias_mv.q]) and common table expressions ( [^cbo_self_join_ambiguous_alias_cte.q]).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)