[jira] [Comment Edited] (HIVE-16907) "INSERT INTO" overwrite old data when destination table encapsulated by backquote
[
https://issues.apache.org/jira/browse/HIVE-16907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16748951#comment-16748951
]
Jesus Camacho Rodriguez edited comment on HIVE-16907 at 1/22/19 5:56 PM:
-
+1, this is not working right now in any case, hence it is better to have a
proper error message.
[~kgyrtkirk], probably we should create a follow-up issue to support dot in
identifiers and update the [identifiers
documentation|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL]
(this
[document|https://issues.apache.org/jira/secure/attachment/12618321/QuotedIdentifier.html]
states that dot will work)?
was (Author: jcamachorodriguez):
+1, this is not working right now in any case, hence it is better to have a
proper error message.
[~kgyrtkirk], probably we should create a follow-up issue to support dot in
identifiers and update the [identifiers
documentation|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL]
(this
[document|https://issues.apache.org/jira/secure/attachment/12618321/QuotedIdentifier.html]
states that dot will work?
> "INSERT INTO" overwrite old data when destination table encapsulated by
> backquote
>
>
> Key: HIVE-16907
> URL: https://issues.apache.org/jira/browse/HIVE-16907
> Project: Hive
> Issue Type: Bug
> Components: Parser
>Affects Versions: 1.1.0, 2.1.1
>Reporter: Nemon Lou
>Assignee: Zoltan Haindrich
>Priority: Major
> Attachments: HIVE-16907.02.patch, HIVE-16907.03.patch,
> HIVE-16907.03.patch, HIVE-16907.03.patch, HIVE-16907.03.patch,
> HIVE-16907.1.patch
>
>
> A way to reproduce:
> {noformat}
> create database tdb;
> use tdb;
> create table t1(id int);
> create table t2(id int);
> explain insert into `tdb.t1` select * from t2;
> {noformat}
> {noformat}
> +---+
> |
> Explain |
> +---+
> | STAGE DEPENDENCIES:
> |
> | Stage-1 is a root stage
> |
> | Stage-6 depends on stages: Stage-1 , consists of Stage-3, Stage-2,
> Stage-4 |
> | Stage-3
> |
> | Stage-0 depends on stages: Stage-3, Stage-2, Stage-5
> |
> | Stage-2
> |
> | Stage-4
> |
> | Stage-5 depends on stages: Stage-4
> |
> |
> |
> | STAGE PLANS:
> |
> | Stage: Stage-1
> |
> | Map Reduce
> |
> | Map Operator Tree:
> |
> | TableScan
> |
> | alias: t2
> |
> | Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column
> stats: NONE
[jira] [Comment Edited] (HIVE-16907) "INSERT INTO" overwrite old data when destination table encapsulated by backquote
[
https://issues.apache.org/jira/browse/HIVE-16907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16721351#comment-16721351
]
Zoltan Haindrich edited comment on HIVE-16907 at 12/14/18 12:34 PM:
looking into the sql2011 standard: it contains what someone would expect:
{code}
.
{code}
and there are some extension how to quote "" if it contains unsafe
characters...
I think this should be fixed; but instead of "accepting"; `db.tbl` it should
be rejected - because accepting that format will get into more trouble
eventually - and a step further away from the standard..
was (Author: kgyrtkirk):
I think this should be fixed; but instead of "accepting"; `db.tbl` it should
be rejected - because accepting that format will probably takes us deeper into
this rabbit hole...
looking into the sql2011 standard: it contains what someone would expect:
{code}
.
{code}
and there are some extension how to quote "" if it contains unsafe
characters...
> "INSERT INTO" overwrite old data when destination table encapsulated by
> backquote
>
>
> Key: HIVE-16907
> URL: https://issues.apache.org/jira/browse/HIVE-16907
> Project: Hive
> Issue Type: Bug
> Components: Parser
>Affects Versions: 1.1.0, 2.1.1
>Reporter: Nemon Lou
>Assignee: Zoltan Haindrich
>Priority: Major
> Attachments: HIVE-16907.1.patch
>
>
> A way to reproduce:
> {noformat}
> create database tdb;
> use tdb;
> create table t1(id int);
> create table t2(id int);
> explain insert into `tdb.t1` select * from t2;
> {noformat}
> {noformat}
> +---+
> |
> Explain |
> +---+
> | STAGE DEPENDENCIES:
> |
> | Stage-1 is a root stage
> |
> | Stage-6 depends on stages: Stage-1 , consists of Stage-3, Stage-2,
> Stage-4 |
> | Stage-3
> |
> | Stage-0 depends on stages: Stage-3, Stage-2, Stage-5
> |
> | Stage-2
> |
> | Stage-4
> |
> | Stage-5 depends on stages: Stage-4
> |
> |
> |
> | STAGE PLANS:
> |
> | Stage: Stage-1
> |
> | Map Reduce
> |
> | Map Operator Tree:
> |
> | TableScan
> |
> | alias: t2
> |
> | Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column
> stats: NONE |
> | Select Operator
> |
> | expressions: id (type: int)
[jira] [Comment Edited] (HIVE-16907) "INSERT INTO" overwrite old data when destination table encapsulated by backquote
[
https://issues.apache.org/jira/browse/HIVE-16907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16084245#comment-16084245
]
Bing Li edited comment on HIVE-16907 at 7/13/17 9:42 AM:
-
[~pxiong] and [~lirui], thank you for your comments.
I tried CREATE TABLE statement in MySQL, and found that it treats the `db.tbl`
as the table name. And "dot" is allowed in the table name.
e.g.
{code:java}
mysql> create table xxx (col int);
mysql> create table test.yyy (col int);
mysql> create table `test.zzz` (col int);
mysql> create table `test.test.tbl` (col int);
mysql> create table test.test.ooo (col int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘.ooo
(col int)’ at line 1
mysql> show tables;
++
| Tables_in_test |
++
| test.test.tbl |
| test.zzz |
| xxx|
| yyy|
++
{code}
Back to Hive, if we would like to make it having the same behavior as MySQL, we
should change the logic of processing it.
My previous patch is NOT enough and can't handle `db.db.tbl` neither.
was (Author: libing):
[~pxiong] and [~lirui], thank you for your comments.
I tried CREATE TABLE statement in MySQL, and found that it treats the `db.tbl`
as the table name. And "dot" is allowed in the table name.
e.g.
{code:java}
mysql> create table xxx (col int);
mysql> create table test.yyy (col int);
mysql> create table `test.zzz` (col int);
mysql> create table `test.test.tbl` (col int);
mysql> show tables;
++
| Tables_in_test |
++
| test.test.tbl |
| test.zzz |
| xxx|
| yyy|
++
{code}
Back to Hive, if we would like to make it having the same behavior as MySQL, we
should change the logic of processing it.
My previous patch is NOT enough and can't handle `db.db.tbl` neither.
> "INSERT INTO" overwrite old data when destination table encapsulated by
> backquote
>
>
> Key: HIVE-16907
> URL: https://issues.apache.org/jira/browse/HIVE-16907
> Project: Hive
> Issue Type: Bug
> Components: Parser
>Affects Versions: 1.1.0, 2.1.1
>Reporter: Nemon Lou
>Assignee: Bing Li
> Attachments: HIVE-16907.1.patch
>
>
> A way to reproduce:
> {noformat}
> create database tdb;
> use tdb;
> create table t1(id int);
> create table t2(id int);
> explain insert into `tdb.t1` select * from t2;
> {noformat}
> {noformat}
> +---+
> |
> Explain |
> +---+
> | STAGE DEPENDENCIES:
> |
> | Stage-1 is a root stage
> |
> | Stage-6 depends on stages: Stage-1 , consists of Stage-3, Stage-2,
> Stage-4 |
> | Stage-3
> |
> | Stage-0 depends on stages: Stage-3, Stage-2, Stage-5
> |
> | Stage-2
> |
> | Stage-4
> |
> | Stage-5 depends on stages: Stage-4
> |
> |
> |
> | STAGE PLANS:
> |
> | Stage: Stage-1
> |
> | Map Reduce
[jira] [Comment Edited] (HIVE-16907) "INSERT INTO" overwrite old data when destination table encapsulated by backquote
[
https://issues.apache.org/jira/browse/HIVE-16907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16084614#comment-16084614
]
Pengcheng Xiong edited comment on HIVE-16907 at 7/12/17 8:18 PM:
-
That is exactly what I am worrying about : Hive may not well support table name
with ".". Could u estimate the work that we need to do if we want to support
this? Thanks.
was (Author: pxiong):
That is exactly what I am worrying about : Hive may not well support table name
with ".". Could u evaluate the work that we need to do if we want to support
this? Thanks.
> "INSERT INTO" overwrite old data when destination table encapsulated by
> backquote
>
>
> Key: HIVE-16907
> URL: https://issues.apache.org/jira/browse/HIVE-16907
> Project: Hive
> Issue Type: Bug
> Components: Parser
>Affects Versions: 1.1.0, 2.1.1
>Reporter: Nemon Lou
>Assignee: Bing Li
> Attachments: HIVE-16907.1.patch
>
>
> A way to reproduce:
> {noformat}
> create database tdb;
> use tdb;
> create table t1(id int);
> create table t2(id int);
> explain insert into `tdb.t1` select * from t2;
> {noformat}
> {noformat}
> +---+
> |
> Explain |
> +---+
> | STAGE DEPENDENCIES:
> |
> | Stage-1 is a root stage
> |
> | Stage-6 depends on stages: Stage-1 , consists of Stage-3, Stage-2,
> Stage-4 |
> | Stage-3
> |
> | Stage-0 depends on stages: Stage-3, Stage-2, Stage-5
> |
> | Stage-2
> |
> | Stage-4
> |
> | Stage-5 depends on stages: Stage-4
> |
> |
> |
> | STAGE PLANS:
> |
> | Stage: Stage-1
> |
> | Map Reduce
> |
> | Map Operator Tree:
> |
> | TableScan
> |
> | alias: t2
> |
> | Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column
> stats: NONE |
> | Select Operator
> |
> | expressions: id (type: int)
> |
> | outputColumnNames: _col0
> |
> | Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column
> stats: NONE |
> | File Output Operator
