[jira] [Comment Edited] (PHOENIX-2809) Alter table doesn't take into account current table definition
[
https://issues.apache.org/jira/browse/PHOENIX-2809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15230084#comment-15230084
]
Pierre Lacave edited comment on PHOENIX-2809 at 4/7/16 11:20 AM:
-
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP COLUMN IF EXISTS DUMMY;
Error: ERROR 506 (42817): Primary key column may not be dropped.
columnName=DUMMY (state=42817,code=506)
java.sql.SQLException: ERROR 506 (42817): Primary key column may not be
dropped. columnName=DUMMY
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:422)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at
org.apache.phoenix.schema.MetaDataClient.dropColumn(MetaDataClient.java:3016)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDropColumnStatement$1.execute(PhoenixStatement.java:1047)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:338)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:326)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:324)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1345)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.047 seconds)
{noformat}
Not being able to drop the column is a bigger consequence, as I don't see an
easy way to get back to a regular state, the duplicate column names cause
issues in spark-sql because of ambiguity.
there are a few differences between the 2 columns as stated in the describe
{noformat}
0: jdbc:phoenix:localhost> !describe TEST_DUP
'TABLE_CAT','TABLE_SCHEM','TABLE_NAME','COLUMN_NAME','DATA_TYPE','TYPE_NAME','COLUMN_SIZE','BUFFER_LENGTH','DECIMAL_DIGITS','NUM_PREC_RADIX','NULLABLE','REMARKS','COLUMN_DEF','SQL_DATA_TYPE','SQL_DATETIME_SUB','CHAR_OCTET_LENGTH','ORDINAL_POSITION','IS_NULLABLE','SCOPE_CATALOG','SCOPE_SCHEMA','SCOPE_TABLE','SOURCE_DATA_TYPE','IS_AUTOINCREMENT','ARRAY_SIZE','COLUMN_FAMILY','TYPE_ID','VIEW_CONSTANT','MULTI_TENANT','KEY_SEQ'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','0','','','null','null','null','1','false','','','','null','','null','','12','','','1'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','1','','','null','null','null','2','true','','','','null','','null','0','12','','','null'
{noformat}
was (Author: pierre.lacave):
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP COLUMN IF EXISTS DUMMY;
Error: ERROR 506 (42817): Primary key column may not be dropped.
columnName=DUMMY
[jira] [Comment Edited] (PHOENIX-2809) Alter table doesn't take into account current table definition
[
https://issues.apache.org/jira/browse/PHOENIX-2809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15230084#comment-15230084
]
Pierre Lacave edited comment on PHOENIX-2809 at 4/7/16 11:07 AM:
-
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP COLUMN IF EXISTS DUMMY;
Error: ERROR 506 (42817): Primary key column may not be dropped.
columnName=DUMMY (state=42817,code=506)
java.sql.SQLException: ERROR 506 (42817): Primary key column may not be
dropped. columnName=DUMMY
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:422)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at
org.apache.phoenix.schema.MetaDataClient.dropColumn(MetaDataClient.java:3016)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDropColumnStatement$1.execute(PhoenixStatement.java:1047)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:338)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:326)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:324)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1345)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.047 seconds)
{noformat}
Not being able to drop the column is a bigger issue, as I don't see an easy way
to get back to a regular state, the duplicate column names cause issues in
spark-sql because of ambiguity.
there are a few differences between the 2 columns as stated in the describe
{noformat}
0: jdbc:phoenix:localhost> !describe TEST_DUP
'TABLE_CAT','TABLE_SCHEM','TABLE_NAME','COLUMN_NAME','DATA_TYPE','TYPE_NAME','COLUMN_SIZE','BUFFER_LENGTH','DECIMAL_DIGITS','NUM_PREC_RADIX','NULLABLE','REMARKS','COLUMN_DEF','SQL_DATA_TYPE','SQL_DATETIME_SUB','CHAR_OCTET_LENGTH','ORDINAL_POSITION','IS_NULLABLE','SCOPE_CATALOG','SCOPE_SCHEMA','SCOPE_TABLE','SOURCE_DATA_TYPE','IS_AUTOINCREMENT','ARRAY_SIZE','COLUMN_FAMILY','TYPE_ID','VIEW_CONSTANT','MULTI_TENANT','KEY_SEQ'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','0','','','null','null','null','1','false','','','','null','','null','','12','','','1'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','1','','','null','null','null','2','true','','','','null','','null','0','12','','','null'
{noformat}
was (Author: pierre.lacave):
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP COLUMN IF EXISTS DUMMY;
Error: ERROR 506 (42817): Primary key column may not be dropped.
columnName=DUMMY
[jira] [Comment Edited] (PHOENIX-2809) Alter table doesn't take into account current table definition
[
https://issues.apache.org/jira/browse/PHOENIX-2809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15230084#comment-15230084
]
Pierre Lacave edited comment on PHOENIX-2809 at 4/7/16 11:07 AM:
-
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP COLUMN IF EXISTS DUMMY;
Error: ERROR 506 (42817): Primary key column may not be dropped.
columnName=DUMMY (state=42817,code=506)
java.sql.SQLException: ERROR 506 (42817): Primary key column may not be
dropped. columnName=DUMMY
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:422)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at
org.apache.phoenix.schema.MetaDataClient.dropColumn(MetaDataClient.java:3016)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDropColumnStatement$1.execute(PhoenixStatement.java:1047)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:338)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:326)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:324)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1345)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.047 seconds)
{noformat}
Not being able to drop the column is a bigger issue, as I don't see an easy way
to get back to a regular state, the duplicate column names cause issues in
spark-sql because of ambiguity.
looking at the describe output, there are a few differences between the 2
columns as stated in the describe
{noformat}
0: jdbc:phoenix:localhost> !describe TEST_DUP
'TABLE_CAT','TABLE_SCHEM','TABLE_NAME','COLUMN_NAME','DATA_TYPE','TYPE_NAME','COLUMN_SIZE','BUFFER_LENGTH','DECIMAL_DIGITS','NUM_PREC_RADIX','NULLABLE','REMARKS','COLUMN_DEF','SQL_DATA_TYPE','SQL_DATETIME_SUB','CHAR_OCTET_LENGTH','ORDINAL_POSITION','IS_NULLABLE','SCOPE_CATALOG','SCOPE_SCHEMA','SCOPE_TABLE','SOURCE_DATA_TYPE','IS_AUTOINCREMENT','ARRAY_SIZE','COLUMN_FAMILY','TYPE_ID','VIEW_CONSTANT','MULTI_TENANT','KEY_SEQ'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','0','','','null','null','null','1','false','','','','null','','null','','12','','','1'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','1','','','null','null','null','2','true','','','','null','','null','0','12','','','null'
{noformat}
was (Author: pierre.lacave):
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP COLUMN IF EXISTS DUMMY;
Error: ERROR 506 (42817): Primary key column may not be dropped.
[jira] [Comment Edited] (PHOENIX-2809) Alter table doesn't take into account current table definition
[
https://issues.apache.org/jira/browse/PHOENIX-2809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15230084#comment-15230084
]
Pierre Lacave edited comment on PHOENIX-2809 at 4/7/16 10:57 AM:
-
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP COLUMN IF EXISTS DUMMY;
Error: ERROR 506 (42817): Primary key column may not be dropped.
columnName=DUMMY (state=42817,code=506)
java.sql.SQLException: ERROR 506 (42817): Primary key column may not be
dropped. columnName=DUMMY
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:422)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at
org.apache.phoenix.schema.MetaDataClient.dropColumn(MetaDataClient.java:3016)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDropColumnStatement$1.execute(PhoenixStatement.java:1047)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:338)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:326)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:324)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1345)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.047 seconds)
{noformat}
Not being able to drop the column is a bigger issue, as I don't see an easy way
to get back to a regular state, the duplicate column names cause issues in
spark-sql because of ambiguity.
looking at the describe output, it appears the KEY_SEQ is the only difference
between the two columns
{noformat}
0: jdbc:phoenix:localhost> !describe TEST_DUP
'TABLE_CAT','TABLE_SCHEM','TABLE_NAME','COLUMN_NAME','DATA_TYPE','TYPE_NAME','COLUMN_SIZE','BUFFER_LENGTH','DECIMAL_DIGITS','NUM_PREC_RADIX','NULLABLE','REMARKS','COLUMN_DEF','SQL_DATA_TYPE','SQL_DATETIME_SUB','CHAR_OCTET_LENGTH','ORDINAL_POSITION','IS_NULLABLE','SCOPE_CATALOG','SCOPE_SCHEMA','SCOPE_TABLE','SOURCE_DATA_TYPE','IS_AUTOINCREMENT','ARRAY_SIZE','COLUMN_FAMILY','TYPE_ID','VIEW_CONSTANT','MULTI_TENANT','KEY_SEQ'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','0','','','null','null','null','1','false','','','','null','','null','','12','','','1'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','1','','','null','null','null','2','true','','','','null','','null','0','12','','','null'
{noformat}
was (Author: pierre.lacave):
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected
[jira] [Comment Edited] (PHOENIX-2809) Alter table doesn't take into account current table definition
[
https://issues.apache.org/jira/browse/PHOENIX-2809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15230084#comment-15230084
]
Pierre Lacave edited comment on PHOENIX-2809 at 4/7/16 10:56 AM:
-
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.047 seconds)
{noformat}
Not being able to drop the column is a bigger issue, as I don't see an easy way
to get back to a regular state, the duplicate column names cause issues in
spark-sql because of ambiguity.
looking at the describe output, it appears the KEY_SEQ is the only difference
between the two columns
{noformat}
0: jdbc:phoenix:localhost> !describe TEST_DUP
'TABLE_CAT','TABLE_SCHEM','TABLE_NAME','COLUMN_NAME','DATA_TYPE','TYPE_NAME','COLUMN_SIZE','BUFFER_LENGTH','DECIMAL_DIGITS','NUM_PREC_RADIX','NULLABLE','REMARKS','COLUMN_DEF','SQL_DATA_TYPE','SQL_DATETIME_SUB','CHAR_OCTET_LENGTH','ORDINAL_POSITION','IS_NULLABLE','SCOPE_CATALOG','SCOPE_SCHEMA','SCOPE_TABLE','SOURCE_DATA_TYPE','IS_AUTOINCREMENT','ARRAY_SIZE','COLUMN_FAMILY','TYPE_ID','VIEW_CONSTANT','MULTI_TENANT','KEY_SEQ'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','0','','','null','null','null','1','false','','','','null','','null','','12','','','1'
'','','TEST_DUP','DUMMY','12','VARCHAR','null','null','null','null','1','','','null','null','null','2','true','','','','null','','null','0','12','','','null'
{noformat}
was (Author: pierre.lacave):
This issue can be reproduced with a unique column name.
{noformat}
0: jdbc:phoenix:localhost> CREATE TABLE IF NOT EXISTS TEST_DUP (DUMMY VARCHAR
CONSTRAINT pk PRIMARY KEY (DUMMY));
No rows affected (1.318 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
++
| DUMMY |
++
++
No rows selected (0.174 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.215 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.169 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP ADD IF NOT EXISTS DUMMY VARCHAR;
No rows affected (6.457 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST_DUP;
+++
| DUMMY | DUMMY |
+++
+++
No rows selected (0.044 seconds)
0: jdbc:phoenix:localhost> ALTER TABLE TEST_DUP DROP IF EXISTS DUMMY;
Error: ERROR 602 (42P00): Syntax error. Missing "COLUMN" at line 1, column 27.
(state=42P00,code=602)
org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax
error. Missing "COLUMN" at line 1, column 27.
at
org.apache.phoenix.exception.PhoenixParserException.newException(PhoenixParserException.java:33)
at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:111)
at
org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementParser.parseStatement(PhoenixStatement.java:1185)
at
org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1268)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1339)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
Caused by: MissingTokenException(inserted [@-1,0:0='',<26>,1:26] at IF)
at
org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:350)
at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
at
org.apache.phoenix.parse.PhoenixSQLParser.alter_table_node(PhoenixSQLParser.java:)
at
org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:847)
at
[jira] [Comment Edited] (PHOENIX-2809) Alter table doesn't take into account current table definition
[
https://issues.apache.org/jira/browse/PHOENIX-2809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15223922#comment-15223922
]
Biju Nair edited comment on PHOENIX-2809 at 4/4/16 10:42 AM:
-
[~sergey.soldatov] agreed that the statement shouldn't fail, but at the
sametime it should not create a duplicate column. no? In the example provided
duplicate column {{T1}} got created.
was (Author: gsbiju):
[~sergey.soldatov] agreed that the statement shouldn't fail, but at the
sometime it should not create a duplicate column. no? In the example provided
duplicate column {{T1}} got created.
> Alter table doesn't take into account current table definition
> --
>
> Key: PHOENIX-2809
> URL: https://issues.apache.org/jira/browse/PHOENIX-2809
> Project: Phoenix
> Issue Type: Bug
>Reporter: Biju Nair
>
> {{Alter table}} to add a new column with the column definition as an existing
> column in the table succeeds while the expectation will be that the alter
> will fail. Following is an example.
> {noformat}
> 0: jdbc:phoenix:localhost:2181:/hbase> create table test_alter (TI tinyint
> not null primary key);
> No rows affected (1.299 seconds)
> 0: jdbc:phoenix:localhost:2181:/hbase> alter table test_alter add if not
> exists TI tinyint, col1 varchar;
> No rows affected (15.962 seconds)
> 0: jdbc:phoenix:localhost:2181:/hbase> upsert into test_alter values
> (1,2,'add');
> 1 row affected (0.008 seconds)
> 0: jdbc:phoenix:localhost:2181:/hbase> select * from test_alter;
> +-+-+---+
> | TI | TI | COL1 |
> +-+-+---+
> | 1 | 1 | add |
> +-+-+---+
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
