[jira] [Comment Edited] (HIVE-17013) Delete request with a subquery based on select over a view
[
https://issues.apache.org/jira/browse/HIVE-17013?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16169164#comment-16169164
]
Matt McCline edited comment on HIVE-17013 at 9/17/17 2:37 AM:
--
I looked at the code. I'm not familiar with this area of the code.
[~ashutoshc] [~jcamachorodriguez] [~vgarg] can you review ASAP. Thanks.
was (Author: mmccline):
I looked at the code. I'm not familiar with this area of the code. Either
someone else should review it or I can do it as long as some else looks at it
later...
+1 provisional
> Delete request with a subquery based on select over a view
> --
>
> Key: HIVE-17013
> URL: https://issues.apache.org/jira/browse/HIVE-17013
> Project: Hive
> Issue Type: Bug
> Components: Transactions
>Reporter: Frédéric ESCANDELL
>Assignee: Eugene Koifman
>Priority: Blocker
> Attachments: acid_view_bug.q, HIVE-17013.01.patch, HIVE-17013.01.patch
>
>
> Hi,
> I based my DDL on this exemple
> https://fr.hortonworks.com/tutorial/using-hive-acid-transactions-to-insert-update-and-delete-data/.
> In a delete request, the use of a view in a subquery throw an exception :
> FAILED: IllegalStateException Expected 'insert into table default.mydim
> select ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set
> operation.
> {code}
> {code:sql}
> drop table if exists mydim;
> create table mydim (key int, name string, zip string, is_current boolean)
> clustered by(key) into 3 buckets
> stored as orc tblproperties ('transactional'='true');
> insert into mydim values
> (1, 'bob', '95136', true),
> (2, 'joe', '70068', true),
> (3, 'steve', '22150', true);
> drop table if exists updates_staging_table;
> create table updates_staging_table (key int, newzip string);
> insert into updates_staging_table values (1, 87102), (3, 45220);
> drop view if exists updates_staging_view;
> create view updates_staging_view (key, newzip) as select key, newzip from
> updates_staging_table;
> delete from mydim
> where mydim.key in (select key from updates_staging_view);
> FAILED: IllegalStateException Expected 'insert into table default.mydim
> select ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set
> operation.
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Comment Edited] (HIVE-17013) Delete request with a subquery based on select over a view
[
https://issues.apache.org/jira/browse/HIVE-17013?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16074743#comment-16074743
]
Frédéric ESCANDELL edited comment on HIVE-17013 at 7/5/17 1:24 PM:
---
I would like to complete information given in the description of the ticket :
I'm using Hive 1.2.1000.2.6.0.3-8.
I think this bug could come from the patch of this ticket
https://issues.apache.org/jira/browse/HIVE-15970 and more particulary the
snippet of code below :
{code:java}
throw new IllegalStateException("Expected '" + getMatchedText(curNode) + "' to
be in sub-query or set operation.");
{code}
[https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/Context.java]
{code:java}
/**
* The suffix is always relative to a given ASTNode
*/
public DestClausePrefix getDestNamePrefix(ASTNode curNode) {
assert curNode != null : "must supply curNode";
if(curNode.getType() != HiveParser.TOK_INSERT_INTO) {
//select statement
assert curNode.getType() == HiveParser.TOK_DESTINATION;
if(operation == Operation.OTHER) {
//not an 'interesting' op
return DestClausePrefix.INSERT;
}
//if it is an 'interesting' op but it's a select it must be a sub-query
or a derived table
//it doesn't require a special Acid code path - the reset of the code
here is to ensure
//the tree structure is what we expect
boolean thisIsInASubquery = false;
parentLoop: while(curNode.getParent() != null) {
curNode = (ASTNode) curNode.getParent();
switch (curNode.getType()) {
case HiveParser.TOK_SUBQUERY_EXPR:
//this is a real subquery (foo IN (select ...))
case HiveParser.TOK_SUBQUERY:
//this is a Derived Table Select * from (select a from ...))
//strictly speaking SetOps should have a TOK_SUBQUERY parent so
next 6 items are redundant
case HiveParser.TOK_UNIONALL:
case HiveParser.TOK_UNIONDISTINCT:
case HiveParser.TOK_EXCEPTALL:
case HiveParser.TOK_EXCEPTDISTINCT:
case HiveParser.TOK_INTERSECTALL:
case HiveParser.TOK_INTERSECTDISTINCT:
thisIsInASubquery = true;
break parentLoop;
}
}
if(!thisIsInASubquery) {
throw new IllegalStateException("Expected '" + getMatchedText(curNode)
+ "' to be in sub-query or set operation.");
} return DestClausePrefix.INSERT;
}
{code}
was (Author: fescandell):
I would like to complete information given in the description of the ticket :
I'm using Hive 1.2.1000.2.6.0.3-8.
I think this bug could come from the patch of this ticket
https://issues.apache.org/jira/browse/HIVE-15970 et more particulary the
snippet of code below :
throw new IllegalStateException("Expected '" + getMatchedText(curNode) + "' to
be in sub-query or set operation.");
[https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/Context.java]
{code:java}
/**
* The suffix is always relative to a given ASTNode
*/
public DestClausePrefix getDestNamePrefix(ASTNode curNode) {
assert curNode != null : "must supply curNode";
if(curNode.getType() != HiveParser.TOK_INSERT_INTO) {
//select statement
assert curNode.getType() == HiveParser.TOK_DESTINATION;
if(operation == Operation.OTHER) {
//not an 'interesting' op
return DestClausePrefix.INSERT;
}
//if it is an 'interesting' op but it's a select it must be a sub-query
or a derived table
//it doesn't require a special Acid code path - the reset of the code
here is to ensure
//the tree structure is what we expect
boolean thisIsInASubquery = false;
parentLoop: while(curNode.getParent() != null) {
curNode = (ASTNode) curNode.getParent();
switch (curNode.getType()) {
case HiveParser.TOK_SUBQUERY_EXPR:
//this is a real subquery (foo IN (select ...))
case HiveParser.TOK_SUBQUERY:
//this is a Derived Table Select * from (select a from ...))
//strictly speaking SetOps should have a TOK_SUBQUERY parent so
next 6 items are redundant
case HiveParser.TOK_UNIONALL:
case HiveParser.TOK_UNIONDISTINCT:
case HiveParser.TOK_EXCEPTALL:
case HiveParser.TOK_EXCEPTDISTINCT:
case HiveParser.TOK_INTERSECTALL:
case HiveParser.TOK_INTERSECTDISTINCT:
thisIsInASubquery = true;
break parentLoop;
}
}
if(!thisIsInASubquery) {
throw new IllegalStateException("Expected '" + getMatchedText(curNode)
+ "' to be in sub-query or set operation.");
} return DestClausePrefix.INSERT;
}
{code}
> Delete request with a subquery based on select over a view
> --
>
> Key:
[jira] [Comment Edited] (HIVE-17013) Delete request with a subquery based on select over a view
[
https://issues.apache.org/jira/browse/HIVE-17013?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16073900#comment-16073900
]
Carter Shanklin edited comment on HIVE-17013 at 7/4/17 4:43 PM:
[~ekoifman]
I'm seeing this as well.
Oddly even the explain errors out. Any idea what's going on here?
{code}
hive> delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> explain delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> select version();
OK
2.1.0.2.6.1.0-128 rd62a574991e4068c4ae7124aa35f4fbf36a12fc1
Time taken: 0.07 seconds, Fetched: 1 row(s)
hive> select key from updates_staging_view;
OK
1
3
hive> select * from mydim;
OK
3 steve 22150 true
1 bob 95136 true
2 joe 70068 true
Time taken: 0.106 seconds, Fetched: 3 row(s)
{code}
Also:
This query works
delete from mydim where mydim.key in (1, 3);
so it's something related to the subquery.
was (Author: cartershanklin):
[~ekoifman]
I'm seeing this as well.
Oddly even the explain errors out. Any idea what's going on here?
{code}
hive> delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> explain delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> select version();
OK
2.1.0.2.6.1.0-128 rd62a574991e4068c4ae7124aa35f4fbf36a12fc1
Time taken: 0.07 seconds, Fetched: 1 row(s)
hive> select key from updates_staging_view;
OK
1
3
hive> select * from mydim;
OK
3 steve 22150 true
1 bob 95136 true
2 joe 70068 true
Time taken: 0.106 seconds, Fetched: 3 row(s)
{code}
> Delete request with a subquery based on select over a view
> --
>
> Key: HIVE-17013
> URL: https://issues.apache.org/jira/browse/HIVE-17013
> Project: Hive
> Issue Type: Bug
>Reporter: Frédéric ESCANDELL
>Priority: Blocker
>
> Hi,
> I based my DDL on this exemple
> https://fr.hortonworks.com/tutorial/using-hive-acid-transactions-to-insert-update-and-delete-data/.
> In a delete request, the use of a view in a subquery throw an exception :
> FAILED: IllegalStateException Expected 'insert into table default.mydim
> select ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set
> operation.
> {code}
> {code:sql}
> drop table if exists mydim;
> create table mydim (key int, name string, zip string, is_current boolean)
> clustered by(key) into 3 buckets
> stored as orc tblproperties ('transactional'='true');
> insert into mydim values
> (1, 'bob', '95136', true),
> (2, 'joe', '70068', true),
> (3, 'steve', '22150', true);
> drop table if exists updates_staging_table;
> create table updates_staging_table (key int, newzip string);
> insert into updates_staging_table values (1, 87102), (3, 45220);
> drop view if exists updates_staging_view;
> create view updates_staging_view (key, newzip) as select key, newzip from
> updates_staging_table;
> delete from mydim
> where mydim.key in (select key from updates_staging_view);
> FAILED: IllegalStateException Expected 'insert into table default.mydim
> select ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set
> operation.
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Comment Edited] (HIVE-17013) Delete request with a subquery based on select over a view
[
https://issues.apache.org/jira/browse/HIVE-17013?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16073900#comment-16073900
]
Carter Shanklin edited comment on HIVE-17013 at 7/4/17 4:41 PM:
[~ekoifman]
I'm seeing this as well.
Oddly even the explain errors out. Any idea what's going on here?
{code}
hive> delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> explain delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> select version();
OK
2.1.0.2.6.1.0-128 rd62a574991e4068c4ae7124aa35f4fbf36a12fc1
Time taken: 0.07 seconds, Fetched: 1 row(s)
hive> select key from updates_staging_view;
OK
1
3
hive> select * from mydim;
OK
3 steve 22150 true
1 bob 95136 true
2 joe 70068 true
Time taken: 0.106 seconds, Fetched: 3 row(s)
{code}
was (Author: cartershanklin):
[~ekoifman]
I'm seeing this as well.
Oddly even the explain errors out. Any idea what's going on here?
{code}
hive> delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> explain delete from mydim where mydim.key in (select key from
updates_staging_view);
FAILED: IllegalStateException Expected 'insert into table default.mydim select
ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.
hive> select version();
OK
2.1.0.2.6.1.0-128 rd62a574991e4068c4ae7124aa35f4fbf36a12fc1
Time taken: 0.07 seconds, Fetched: 1 row(s)
{code}
> Delete request with a subquery based on select over a view
> --
>
> Key: HIVE-17013
> URL: https://issues.apache.org/jira/browse/HIVE-17013
> Project: Hive
> Issue Type: Bug
>Reporter: Frédéric ESCANDELL
>Priority: Blocker
>
> Hi,
> I based my DDL on this exemple
> https://fr.hortonworks.com/tutorial/using-hive-acid-transactions-to-insert-update-and-delete-data/.
> In a delete request, the use of a view in a subquery throw an exception :
> FAILED: IllegalStateException Expected 'insert into table default.mydim
> select ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set
> operation.
> {code}
> {code:sql}
> drop table if exists mydim;
> create table mydim (key int, name string, zip string, is_current boolean)
> clustered by(key) into 3 buckets
> stored as orc tblproperties ('transactional'='true');
> insert into mydim values
> (1, 'bob', '95136', true),
> (2, 'joe', '70068', true),
> (3, 'steve', '22150', true);
> drop table if exists updates_staging_table;
> create table updates_staging_table (key int, newzip string);
> insert into updates_staging_table values (1, 87102), (3, 45220);
> drop view if exists updates_staging_view;
> create view updates_staging_view (key, newzip) as select key, newzip from
> updates_staging_table;
> delete from mydim
> where mydim.key in (select key from updates_staging_view);
> FAILED: IllegalStateException Expected 'insert into table default.mydim
> select ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set
> operation.
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
