[HIVE-8805] CBO skipped due to SemanticException: Line 0:-1 Both left and right aliases encountered in JOIN 'avg_cs_ext_discount_amt'

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.14.0
• Fix Version/s: 1.0.0
• Component/s: CBO
• Assignee: Laljo John Pullokkaran
• Reporter: Mostafa Mokhtar
• Created: 10/Nov/14 18:48
• Updated: 19/Feb/15 18:21
• Resolved: 12/Nov/14 02:42

Description

Query

set hive.cbo.enable=true
set hive.stats.fetch.column.stats=true
set hive.exec.dynamic.partition.mode=nonstrict
set hive.tez.auto.reducer.parallelism=true
set hive.auto.convert.join.noconditionaltask.size=320000000
set hive.exec.reducers.bytes.per.reducer=100000000
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager
set hive.support.concurrency=false
set hive.tez.exec.print.summary=true
explain  
SELECT sum(cs1.cs_ext_discount_amt) as excess_discount_amount
FROM (SELECT cs.cs_item_sk as cs_item_sk,
                             cs.cs_ext_discount_amt as cs_ext_discount_amt
             FROM catalog_sales cs
             JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk)
             WHERE d.d_date between '2000-01-27' and '2000-04-27') cs1
JOIN item i ON (i.i_item_sk = cs1.cs_item_sk)
JOIN (SELECT cs2.cs_item_sk as cs_item_sk,
                          1.3 * avg(cs_ext_discount_amt) as avg_cs_ext_discount_amt
           FROM (SELECT cs.cs_item_sk as cs_item_sk,
                                        cs.cs_ext_discount_amt as cs_ext_discount_amt
                        FROM catalog_sales cs
                        JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk)
                        WHERE d.d_date between '2000-01-27' and '2000-04-27') cs2
                        GROUP BY cs2.cs_item_sk) tmp1
ON (i.i_item_sk = tmp1.cs_item_sk)
WHERE i.i_manufact_id = 436 and
               cs1.cs_ext_discount_amt > tmp1.avg_cs_ext_discount_amt

Exception

14/11/07 19:15:38 [main]: ERROR parse.SemanticAnalyzer: CBO failed, skipping CBO. 
org.apache.hadoop.hive.ql.parse.SemanticException: Line 0:-1 Both left and right aliases encountered in JOIN 'avg_cs_ext_discount_amt'
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.parseJoinCondition(SemanticAnalyzer.java:2369)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.parseJoinCondition(SemanticAnalyzer.java:2293)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.parseJoinCondition(SemanticAnalyzer.java:2249)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinTree(SemanticAnalyzer.java:8010)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9678)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9593)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9619)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9593)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9619)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9606)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10053)
	at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:221)
	at org.apache.hadoop.hive.ql.parse.ExplainSemanticAnalyzer.analyzeInternal(ExplainSemanticAnalyzer.java:74)
	at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:221)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:415)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:303)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1067)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1129)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1004)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:994)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:247)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:199)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:410)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:345)
	at org.apache.hadoop.hive.cli.CliDriver.processReader(CliDriver.java:443)
	at org.apache.hadoop.hive.cli.CliDriver.processFile(CliDriver.java:459)
	at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:739)
	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:677)
	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:616)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:212)

Issue Links

• links to: RB