[
https://issues.apache.org/jira/browse/HIVE-9481?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eugene Koifman updated HIVE-9481:
---------------------------------
Description:
Given a table FOO(a int, b int, c int), ANSI SQL supports insert into FOO(c,b)
select x,y from T. The expectation is that 'x' is written to column 'c' and
'y' is written column 'b' and 'a' is set to NULL, assuming column 'a' is
NULLABLE.
Hive does not support this. In Hive one has to ensure that the data producing
statement has a schema that matches target table schema.
Since Hive doesn't support DEFAULT value for columns in CREATE TABLE, when
target schema is explicitly provided, missing columns will be set to NULL if
they are NULLABLE, otherwise an error will be raised.
If/when DEFAULT clause is supported, this can be enhanced to set default value
rather than NULL.
Thus, given {noformat}
create table source (a int, b int);
create table target (x int, y int, z int);
{noformat}
{noformat}insert into target(y,z) select * from source;{noformat}
will mean
{noformat}insert into target select null as x, a, b from source;{noformat}
and
{noformat}insert into target(z,y) select * from source;{noformat}
will meant
{noformat}insert into target select null as x, b, a from source;{noformat}
was:
Given a table FOO(a int, b int, c int), ANSI SQL supports insert into FOO(c,b)
select x,y from T. The expectation is that 'x' is written to column 'c' and
'y' is written column 'b' and 'a' is set to NULL, assuming column 'a' is
NULLABLE.
Hive does not support this. In Hive one has to ensure that the data producing
statement has a schema that matches target table schema.
Since Hive doesn't support DEFAULT value for columns in CREATE TABLE, when
target schema is explicitly provided, missing columns will be set to NULL if
they are NULLABLE, otherwise an error will be raised.
If/when DEFAULT clause is supported, this can be enhanced to set default value
rather than NULL.
> allow column list specification in INSERT statement
> ---------------------------------------------------
>
> Key: HIVE-9481
> URL: https://issues.apache.org/jira/browse/HIVE-9481
> Project: Hive
> Issue Type: Bug
> Components: Parser, Query Processor, SQL
> Affects Versions: 0.14.0
> Reporter: Eugene Koifman
> Assignee: Eugene Koifman
>
> Given a table FOO(a int, b int, c int), ANSI SQL supports insert into
> FOO(c,b) select x,y from T. The expectation is that 'x' is written to column
> 'c' and 'y' is written column 'b' and 'a' is set to NULL, assuming column 'a'
> is NULLABLE.
> Hive does not support this. In Hive one has to ensure that the data
> producing statement has a schema that matches target table schema.
> Since Hive doesn't support DEFAULT value for columns in CREATE TABLE, when
> target schema is explicitly provided, missing columns will be set to NULL if
> they are NULLABLE, otherwise an error will be raised.
> If/when DEFAULT clause is supported, this can be enhanced to set default
> value rather than NULL.
> Thus, given {noformat}
> create table source (a int, b int);
> create table target (x int, y int, z int);
> {noformat}
> {noformat}insert into target(y,z) select * from source;{noformat}
> will mean
> {noformat}insert into target select null as x, a, b from source;{noformat}
> and
> {noformat}insert into target(z,y) select * from source;{noformat}
> will meant
> {noformat}insert into target select null as x, b, a from source;{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
