[HIVE-28222] Ambiguous table alias exception for queries with self joins

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0
• Component/s: CBO
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 26/Apr/24 12:33
• Updated: 26/Apr/24 12:38

Description

Various queries containing more than 2 joins of the same table (self joins) fail at compile time.

A simple reproducer is outlined below.

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;

The query fails with the following stacktrace:

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]

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