[HIVE-14022] left semi join should throw SemanticException if where clause contains columnname from right table - ASF JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 2.2.0
Fix Version/s: 2.1.1, 2.2.0
Component/s: None
Labels:
None

Description

Left semi join throws following error if where clause contains column name with table alias

select * from src_emptybucket_partitioned_1 e1 left semi join src_emptybucket_partitioned_3 e3 on e1.age =  e3.age where e1.year = 2015 and e3.year1=2016;
16/06/10 22:37:37 [main]: INFO log.PerfLogger: <PERFLOG method=Driver.run from=org.apache.hadoop.hive.ql.Driver>
16/06/10 22:37:37 [main]: INFO log.PerfLogger: <PERFLOG method=TimeToSubmit from=org.apache.hadoop.hive.ql.Driver>
16/06/10 22:37:37 [main]: INFO log.PerfLogger: <PERFLOG method=compile from=org.apache.hadoop.hive.ql.Driver>
16/06/10 22:37:37 [main]: INFO ql.Driver: We are setting the hadoop caller context from  to hrt_qa_20160610223737_c3821398-d8df-44d8-9dd5-e66c9b7ed7c7
16/06/10 22:37:37 [main]: DEBUG parse.VariableSubstitution: Substitution is on: select * from src_emptybucket_partitioned_1 e1 left semi join src_emptybucket_partitioned_3 e3 on e1.age =  e3.age where e1.year = 2015 and e3.year1=2016
16/06/10 22:37:37 [main]: INFO log.PerfLogger: <PERFLOG method=parse from=org.apache.hadoop.hive.ql.Driver>
16/06/10 22:37:37 [main]: INFO parse.ParseDriver: Parsing command: select * from src_emptybucket_partitioned_1 e1 left semi join src_emptybucket_partitioned_3 e3 on e1.age =  e3.age where e1.year = 2015 and e3.year1=2016
16/06/10 22:37:37 [main]: INFO parse.ParseDriver: Parse Completed
16/06/10 22:37:37 [main]: INFO log.PerfLogger: </PERFLOG method=parse start=1465598257393 end=1465598257397 duration=4 from=org.apache.hadoop.hive.ql.Driver>
16/06/10 22:37:37 [main]: DEBUG ql.Driver: Encoding valid txns info 9223372036854775807:
16/06/10 22:37:37 [main]: INFO log.PerfLogger: <PERFLOG method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver>
16/06/10 22:37:37 [main]: INFO parse.CalcitePlanner: Starting Semantic Analysis
16/06/10 22:37:37 [main]: INFO parse.CalcitePlanner: Completed phase 1 of Semantic Analysis
16/06/10 22:37:37 [main]: INFO parse.CalcitePlanner: Get metadata for source tables
16/06/10 22:37:37 [main]: INFO parse.CalcitePlanner: Get metadata for subqueries
16/06/10 22:37:37 [main]: INFO parse.CalcitePlanner: Get metadata for destination tables
16/06/10 22:37:37 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa sending #194
16/06/10 22:37:37 [IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa got value #194
16/06/10 22:37:37 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getEZForPath took 2ms
16/06/10 22:37:37 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa sending #195
16/06/10 22:37:37 [IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa got value #195
16/06/10 22:37:37 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getEZForPath took 1ms
16/06/10 22:37:37 [main]: DEBUG hdfs.DFSClient: /tmp/hive/hrt_qa/d2568b75-6399-46df-82b9-34ec445e8f64/hive_2016-06-10_22-37-37_392_2780828105665881901-1: masked=rwx------
16/06/10 22:37:37 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa sending #196
16/06/10 22:37:37 [IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa got value #196
16/06/10 22:37:37 [main]: DEBUG ipc.ProtobufRpcEngine: Call: mkdirs took 2ms
16/06/10 22:37:37 [IPC Parameter Sending Thread #0]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa sending #197
16/06/10 22:37:37 [IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa]: DEBUG ipc.Client: IPC Client (147022238) connection to jvaria-hive2-440-5.openstacklocal/172.22.126.47:8020 from hrt_qa got value #197
16/06/10 22:37:37 [main]: DEBUG ipc.ProtobufRpcEngine: Call: getFileInfo took 1ms
16/06/10 22:37:37 [main]: INFO ql.Context: New scratch dir is hdfs://jvaria-hive2-440-5.openstacklocal:8020/tmp/hive/hrt_qa/d2568b75-6399-46df-82b9-34ec445e8f64/hive_2016-06-10_22-37-37_392_2780828105665881901-1
16/06/10 22:37:37 [main]: INFO parse.CalcitePlanner: Completed getting MetaData in Semantic Analysis
16/06/10 22:37:37 [main]: INFO parse.BaseSemanticAnalyzer: Not invoking CBO because the statement has too few joins
16/06/10 22:37:37 [main]: DEBUG hive.log: DDL: struct src_emptybucket_partitioned_1 { string name, i32 age, double gpa}
16/06/10 22:37:37 [main]: DEBUG parse.CalcitePlanner: Created Table Plan for e1 TS[0]
16/06/10 22:37:37 [main]: DEBUG hive.log: DDL: struct src_emptybucket_partitioned_3 { varchar(50) name1, i64 age1, decimal(38,18) gpa1}
16/06/10 22:37:37 [main]: DEBUG parse.CalcitePlanner: Created Table Plan for e3 TS[1]
16/06/10 22:37:37 [main]: DEBUG parse.TypeCheckCtx: Setting error: [Line 1:111 Invalid column reference 'age'] from (. (TOK_TABLE_OR_COL e3) age)
java.lang.Exception
	at org.apache.hadoop.hive.ql.parse.TypeCheckCtx.setError(TypeCheckCtx.java:159)
	at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.processQualifiedColRef(TypeCheckProcFactory.java:1149)
	at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:1257)
	at org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:90)
	at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatchAndReturn(DefaultGraphWalker.java:95)
	at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:79)
	at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:133)
	at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:110)
	at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:213)
	at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:157)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:10756)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10712)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10680)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10674)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.insertSelectForSemijoin(SemanticAnalyzer.java:7704)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinOperator(SemanticAnalyzer.java:7644)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinPlan(SemanticAnalyzer.java:7821)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9821)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9710)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:10353)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:329)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10364)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:211)
	at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:227)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:462)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:318)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1197)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1245)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1134)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1124)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:216)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:168)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:379)
	at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:739)
	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684)
	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:624)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: SemanticException [Error 10002]: Line 1:111 Invalid column reference 'age'
16/06/10 22:37:37 [main]: ERROR ql.Driver: FAILED: SemanticException [Error 10002]: Line 1:111 Invalid column reference 'age'
org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:111 Invalid column reference 'age'
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:10764)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10712)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10680)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10674)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.insertSelectForSemijoin(SemanticAnalyzer.java:7704)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinOperator(SemanticAnalyzer.java:7644)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinPlan(SemanticAnalyzer.java:7821)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9821)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9710)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:10353)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:329)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10364)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:211)
	at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:227)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:462)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:318)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1197)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1245)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1134)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1124)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:216)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:168)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:379)
	at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:739)
	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684)
	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:624)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:136)

Similar inner join query works without any issues

select * from src_emptybucket_partitioned_1 e1 inner join src_emptybucket_partitioned_3 e3 on e1.age =  e3.age1 where e1.year = 2015 and e3.year1 = 2016;
Query ID = hrt_qa_20160610224945_9e5b40b7-8faf-4ef0-b1e1-741754fe2786
Total jobs = 1
Launching Job 1 out of 1


Status: Running (Executing on YARN cluster with App id application_1464125086069_0317)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      1          1        0        0       0       0
Map 2 ..........   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 6.92 s     
--------------------------------------------------------------------------------
OK
Time taken: 8.761 seconds

Source Tables Schema

desc src_emptybucket_partitioned_1;
OK
name                	string              	                    
age                 	int                 	                    
gpa                 	double              	                    
year                	int                 	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
	 	 
year                	int                 	                    
Time taken: 1.115 seconds, Fetched: 9 row(s)
hive> name1               	varchar(50)         	                    
age1                	bigint              	                    
gpa1                	decimal(38,18)      	                    
year1               	int                 	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
	 	 
year1               	int

Left semi join query would work fine if I remove table alias from the query

hive> select * from src_emptybucket_partitioned_1  left semi join src_emptybucket_partitioned_3 on age =  age1 where year = 2015 and year1=2016;
Query ID = hrt_qa_20160610223932_4bf61489-b0eb-4533-9a24-77060aef417b
Total jobs = 1
Launching Job 1 out of 1


Status: Running (Executing on YARN cluster with App id application_1464125086069_0317)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      1          1        0        0       0       0
Map 2 ..........   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 8.33 s     
--------------------------------------------------------------------------------
OK
Time taken: 14.794 seconds

Left semi join query would also work if I keep column name in where clause from the table on left side of the join

hive> select * from src_emptybucket_partitioned_1 e1 left semi join src_emptybucket_partitioned_3 e3 on e1.age =  e3.age1 where e1.year = 2015;
Query ID = hrt_qa_20160610224621_25158eb4-ca9a-47ea-b33d-ee06f9979dec
Total jobs = 1
Launching Job 1 out of 1


Status: Running (Executing on YARN cluster with App id application_1464125086069_0317)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      1          1        0        0       0       0
Map 2 ..........   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 8.60 s     
--------------------------------------------------------------------------------
OK
Time taken: 10.404 seconds
hive> select * from src_emptybucket_partitioned_1 e1 left semi join src_emptybucket_partitioned_3 e3 on e1.age =  e3.age1 where e3.year1 = 2016;
FAILED: SemanticException [Error 10004]: Line 1:122 Invalid table alias or column reference 'e3': (possible column names are: name, age, gpa, year)

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending

HIVE-14022.patch
16/Jun/16 10:10
3 kB
jcamachorodriguez
HIVE-14022.1.patch
16/Jun/16 14:16
3 kB
jcamachorodriguez

Activity

Comment

This comment will be Viewable by All Users Viewable by All Users

Cancel

left semi join should throw SemanticException if where clause contains columnname from right table

Details

Description

Attachments

Attachments

Activity

People

Dates

Agile

Slack

Issue deployment