[ https://issues.apache.org/jira/browse/PHOENIX-2809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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='<missing COLUMN>',<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:3333) at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:847) at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:500) at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108) ... 9 more 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} > 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)