You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Premal Shah (JIRA)" <ji...@apache.org> on 2016/11/12 09:10:58 UTC
[jira] [Created] (HIVE-15187) CTAS with CBO throws errors
Premal Shah created HIVE-15187:
----------------------------------
Summary: CTAS with CBO throws errors
Key: HIVE-15187
URL: https://issues.apache.org/jira/browse/HIVE-15187
Project: Hive
Issue Type: Bug
Components: CBO
Affects Versions: 2.0.1
Reporter: Premal Shah
If I run a query with CREATE TABLE AS, it breaks with the error below. However, just running the query works if I don't try to create a table from the results. It does not happen to all CTAS queries.
2016-11-10T04:52:27,531 ERROR [cb5f35ff-f404-41ce-aa9b-87708d80b078 main]: ql.Driver (SessionState.java:printError(1038)) - FAILED: SemanticException Line 0:-1 Invalid column reference '$f0'
org.apache.hadoop.hive.ql.parse.SemanticException: Line 0:-1 Invalid column reference '$f0'
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:10527)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10475)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genSelectPlan(SemanticAnalyzer.java:3843)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genSelectPlan(SemanticAnalyzer.java:3622)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPostGroupByBodyPlan(SemanticAnalyzer.java:8895)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:8850)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9703)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9583)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9610)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9583)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9586)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9610)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9596)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:10092)
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:353)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10103)
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:228)
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:239)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:473)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:319)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1249)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1295)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1178)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1166)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:236)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:187)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:782)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:721)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:648)
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)
This breaks.
CREATE TABLE unique_ip_tmp AS
SELECT DISTINCT
new.ip
FROM
(
SELECT COUNT(0) , ip
FROM t1
WHERE dt BETWEEN '2016-11-08' AND '2016-11-08'
GROUP BY ip
UNION ALL
SELECT COUNT(0) , ip
FROM t2
WHERE dt BETWEEN '2016-11-08' AND '2016-11-08'
GROUP BY ip
) new
LEFT JOIN unique_ip old
ON old.ip = new.ip
WHERE
old.ip IS NULL
;
Also, tried this as per [~sershe]'s suggestion
CREATE TABLE unique_ip_tmp AS
SELECT DISTINCT
new.ip
FROM
(
SELECT
COUNT(0) as count ,
ip as ip
FROM t1
WHERE dt BETWEEN '2016-11-08' AND '2016-11-08'
GROUP BY ip
UNION ALL
SELECT
COUNT(0) as count ,
ip as ip
FROM t2
WHERE dt BETWEEN '2016-11-08' AND '2016-11-08'
GROUP BY ip
) new
LEFT JOIN unique_ip old
ON old.ip = new.ip
WHERE
old.ip IS NULL
;
Here, I alias the count(0) and ip columns
If I remove one of the queries in the UNION, it works
CREATE TABLE unique_ip_tmp AS
SELECT DISTINCT
new.ip
FROM
(
SELECT
COUNT(0)
, ip
FROM
map_activity
WHERE
dt BETWEEN '2016-11-08' AND '2016-11-08'
GROUP BY
ip
) new
LEFT JOIN unique_ip old
ON old.ip = new.ip
WHERE
old.ip IS NULL
;
If I create tmp tables from the group by queries and use them, that works too
CREATE TABLE unique_ip_tmp AS
SELECT DISTINCT
new.ip
FROM
(
SELECT * FROM dropme_t1
UNION ALL
SELECT * FROM dropme_t2
) new
LEFT JOIN unique_ip old
ON old.ip = new.ip
WHERE
old.ip IS NULL
;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)