[ https://issues.apache.org/jira/browse/TRAFODION-3131?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wayne Birdsall resolved TRAFODION-3131. --------------------------------------------- Resolution: Fixed Fix Version/s: 2.3 > Use of reserved words as names fails in many places > --------------------------------------------------- > > Key: TRAFODION-3131 > URL: https://issues.apache.org/jira/browse/TRAFODION-3131 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Affects Versions: 2.3 > Reporter: David Wayne Birdsall > Assignee: David Wayne Birdsall > Priority: Major > Fix For: 2.3 > > Attachments: jira.sql.txt > > > It should be possible to use a reserved word (e.g., "YEAR") as an identifier > if it is made into a delimited identifier (that is, upper-cased and > surrounded with double-quotes). The sqlci session below demonstrates several > examples where this fails: > {quote}>>obey jira.sql; > >>-- script to reproduce various problems with reserved words > >> > >>?section tablename > >> > >>-- reserved word as a table name fails in various places > >> > >>create table "DELETE" (c1 int); > --- SQL operation complete. > >>invoke "DELETE"; > *** ERROR[15001] A syntax error occurred at or before: > TABLE TRAFODION.SCH.DELETE; > ^ (26 characters from start of SQL statement) > *** ERROR[15001] A syntax error occurred at or before: > TABLE ; > ^ (7 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > >>showddl "DELETE"; > CREATE TABLE TRAFODION.SCH.DELETE > ( > C1 INT DEFAULT NULL NOT SERIALIZED > ) > ATTRIBUTES ALIGNED FORMAT > ; > -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.DELETE > TO DB__ROOT WITH GRANT OPTION; > --- SQL operation complete. > >>insert into "DELETE" values (1); > *** ERROR[8448] Unable to access Hbase interface. Call to > ExpHbaseInterface::checkAndInsertRow returned error HBASE_ACCESS_ERROR(-706). > Cause: java.lang.IllegalArgumentException: Table qualifier must not be empty > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:179) > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:149) > org.apache.hadoop.hbase.TableName.<init>(TableName.java:322) > org.apache.hadoop.hbase.TableName.createTableNameIfNecessary(TableName.java:358) > org.apache.hadoop.hbase.TableName.valueOf(TableName.java:418) > org.apache.hadoop.hbase.client.HTable.<init>(HTable.java:274) > org.apache.hadoop.hbase.client.transactional.TransactionalTable.<init>(TransactionalTable.java:138) > org.apache.hadoop.hbase.client.transactional.RMInterface.<init>(RMInterface.java:159) > org.trafodion.sql.HTableClient.init(HTableClient.java:348) > org.trafodion.sql.HBaseClient.getHTableClient(HBaseClient.java:1008) > org.trafodion.sql.HBaseClient.insertRow(HBaseClient.java:1964). > --- 0 row(s) inserted. > >>delete from "DELETE" where c1=1; > *** ERROR[8448] Unable to access Hbase interface. Call to > ExpHbaseInterface::scanOpen returned error HBASE_OPEN_ERROR(-704). Cause: > java.lang.IllegalArgumentException: Table qualifier must not be empty > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:179) > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:149) > org.apache.hadoop.hbase.TableName.<init>(TableName.java:322) > org.apache.hadoop.hbase.TableName.createTableNameIfNecessary(TableName.java:358) > org.apache.hadoop.hbase.TableName.valueOf(TableName.java:418) > org.apache.hadoop.hbase.client.HTable.<init>(HTable.java:274) > org.apache.hadoop.hbase.client.transactional.TransactionalTable.<init>(TransactionalTable.java:138) > org.apache.hadoop.hbase.client.transactional.RMInterface.<init>(RMInterface.java:159) > org.trafodion.sql.HTableClient.init(HTableClient.java:348) > org.trafodion.sql.HBaseClient.getHTableClient(HBaseClient.java:1008). > --- 0 row(s) deleted. > >>update "DELETE" set c1=2 where c1=1; > *** ERROR[8448] Unable to access Hbase interface. Call to > ExpHbaseInterface::scanOpen returned error HBASE_OPEN_ERROR(-704). Cause: > java.lang.IllegalArgumentException: Table qualifier must not be empty > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:179) > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:149) > org.apache.hadoop.hbase.TableName.<init>(TableName.java:322) > org.apache.hadoop.hbase.TableName.createTableNameIfNecessary(TableName.java:358) > org.apache.hadoop.hbase.TableName.valueOf(TableName.java:418) > org.apache.hadoop.hbase.client.HTable.<init>(HTable.java:274) > org.apache.hadoop.hbase.client.transactional.TransactionalTable.<init>(TransactionalTable.java:138) > org.apache.hadoop.hbase.client.transactional.RMInterface.<init>(RMInterface.java:159) > org.trafodion.sql.HTableClient.init(HTableClient.java:348) > org.trafodion.sql.HBaseClient.getHTableClient(HBaseClient.java:1008). > --- 0 row(s) updated. > >>select * from "DELETE"; > *** ERROR[8448] Unable to access Hbase interface. Call to > ExpHbaseInterface::scanOpen returned error HBASE_OPEN_ERROR(-704). Cause: > java.lang.IllegalArgumentException: Table qualifier must not be empty > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:179) > org.apache.hadoop.hbase.TableName.isLegalTableQualifierName(TableName.java:149) > org.apache.hadoop.hbase.TableName.<init>(TableName.java:322) > org.apache.hadoop.hbase.TableName.createTableNameIfNecessary(TableName.java:358) > org.apache.hadoop.hbase.TableName.valueOf(TableName.java:418) > org.apache.hadoop.hbase.client.HTable.<init>(HTable.java:274) > org.apache.hadoop.hbase.client.transactional.TransactionalTable.<init>(TransactionalTable.java:138) > org.apache.hadoop.hbase.client.transactional.RMInterface.<init>(RMInterface.java:159) > org.trafodion.sql.HTableClient.init(HTableClient.java:348) > org.trafodion.sql.HBaseClient.getHTableClient(HBaseClient.java:1008). > --- 0 row(s) selected. > >>update statistics for table "DELETE" on every column; > *** ERROR[15001] A syntax error occurred at or before: > TABLE TRAFODION.SCH.DELETE; > ^ (26 characters from start of SQL statement) > *** ERROR[15001] A syntax error occurred at or before: > TABLE ; > ^ (7 characters from start of SQL statement) > --- SQL operation failed with errors. > >>drop table "DELETE"; > --- SQL operation complete. > >> > >>?section viewref > >> > >>-- referencing a column name using a reserved word doesn't work from a view > >> > >>drop table if exists mytable cascade; > --- SQL operation complete. > >>create table mytable (c1 int default 1, "YEAR" int); > --- SQL operation complete. > >>create index myidx on mytable ("YEAR"); > --- SQL operation complete. > >>insert into mytable ("YEAR") values (1); > --- 1 row(s) inserted. > >>select "YEAR" from mytable where "YEAR" > 0; > YEAR > ----------- > 1 > --- 1 row(s) selected. > >> > >>create view myview1 as select * from mytable; > --- SQL operation complete. > >>showddl myview1; > CREATE VIEW TRAFODION.SCH.MYVIEW1 AS > SELECT TRAFODION.SCH.MYTABLE.C1, TRAFODION.SCH.MYTABLE.YEAR FROM > TRAFODION.SCH.MYTABLE ; > -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.MYVIEW1 > TO DB__ROOT WITH GRANT OPTION; > --- SQL operation complete. > >>select * from myview1; > *** ERROR[15001] A syntax error occurred at or before: > CREATE VIEW TRAFODION.SCH.MYVIEW1 AS SELECT TRAFODION.SCH.MYTABLE.C1, > TRAFODION > .SCH.MYTABLE.YEAR FROM TRAFODION.SCH.MYTABLE; > ^ (96 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > >> > >>create view myview2 as select "YEAR" from mytable; > --- SQL operation complete. > >>showddl myview2; > CREATE VIEW TRAFODION.SCH.MYVIEW2 AS > SELECT TRAFODION.SCH.MYTABLE.YEAR FROM TRAFODION.SCH.MYTABLE ; > -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.MYVIEW2 > TO DB__ROOT WITH GRANT OPTION; > --- SQL operation complete. > >>select * from myview2; > *** ERROR[15001] A syntax error occurred at or before: > CREATE VIEW TRAFODION.SCH.MYVIEW2 AS SELECT TRAFODION.SCH.MYTABLE.YEAR FROM > TRA > ^ (70 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > >> > >>create view myview3 as select * from mytable where "YEAR" > 0; > --- SQL operation complete. > >>showddl myview3; > CREATE VIEW TRAFODION.SCH.MYVIEW3 AS > SELECT TRAFODION.SCH.MYTABLE.C1, TRAFODION.SCH.MYTABLE.YEAR FROM > TRAFODION.SCH.MYTABLE WHERE TRAFODION.SCH.MYTABLE.YEAR > 0 ; > -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.MYVIEW3 > TO DB__ROOT WITH GRANT OPTION; > --- SQL operation complete. > >>select * from myview3; > *** ERROR[15001] A syntax error occurred at or before: > CREATE VIEW TRAFODION.SCH.MYVIEW3 AS SELECT TRAFODION.SCH.MYTABLE.C1, > TRAFODION > .SCH.MYTABLE.YEAR FROM TRAFODION.SCH.MYTABLE WHERE TRAFODION.SCH.MYTABLE.YEAR > > > ^ (96 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > >> > >>create view myview4 as select * from myview1; > *** ERROR[15001] A syntax error occurred at or before: > CREATE VIEW TRAFODION.SCH.MYVIEW1 AS SELECT TRAFODION.SCH.MYTABLE.C1, > TRAFODION > .SCH.MYTABLE.YEAR FROM TRAFODION.SCH.MYTABLE; > ^ (96 characters from start of SQL statement) > --- SQL operation failed with errors. > >>showddl myview4; > *** ERROR[4082] Object TRAFODION.SCH.MYVIEW4 does not exist or is > inaccessible. > *** ERROR[8822] The statement was not prepared. > >>select * from myview4; > *** ERROR[4082] Object TRAFODION.SCH.MYVIEW4 does not exist or is > inaccessible. > *** ERROR[8822] The statement was not prepared. > >> > >>drop table mytable cascade; > --- SQL operation complete. > >> > >>?section ctas > >> > >>-- "create table as" fails when column name is a reserved word > >> > >>drop table if exists t1; > --- SQL operation complete. > >>drop table if exists t2; > --- SQL operation complete. > >>drop table if exists t3; > --- SQL operation complete. > >>drop table if exists t4; > --- SQL operation complete. > >> > >>create table t1 ("YEAR" int); > --- SQL operation complete. > >>showddl t1; > CREATE TABLE TRAFODION.SCH.T1 > ( > YEAR INT DEFAULT NULL NOT SERIALIZED > ) > ATTRIBUTES ALIGNED FORMAT > ; > -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.T1 TO > DB__ROOT WITH GRANT OPTION; > --- SQL operation complete. > >>create table t2 as select * from t1; > *** ERROR[15001] A syntax error occurred at or before: > CREATE TABLE TRAFODION.SCH.T2 ( YEAR INT ); > ^ (36 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > --- 0 row(s) inserted. > >>showddl t2; > *** ERROR[4082] Object TRAFODION.SCH.T2 does not exist or is inaccessible. > *** ERROR[8822] The statement was not prepared. > >>create table t3 ("YEAR" int not null primary key); > --- SQL operation complete. > >>showddl t3; > CREATE TABLE TRAFODION.SCH.T3 > ( > YEAR INT NO DEFAULT NOT NULL NOT DROPPABLE NOT > SERIALIZED > , PRIMARY KEY (YEAR ASC) > ) > ATTRIBUTES ALIGNED FORMAT > ; > -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.T3 TO > DB__ROOT WITH GRANT OPTION; > --- SQL operation complete. > >>create table t4 like t3; > *** ERROR[15001] A syntax error occurred at or before: > create table TRAFODION.SCH.T4 ( "YEAR" INT NO > DEFAULT NOT NULL NOT DROPPABLE NOT > SERIALIZED , PRIMARY KEY (YEAR ASC) ) ATTRIBUTES ALIGNED FORMAT; > ^ (151 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > --- SQL operation failed with errors. > >>showddl t4; > *** ERROR[4082] Object TRAFODION.SCH.T4 does not exist or is inaccessible. > *** ERROR[8822] The statement was not prepared. > >> > >>drop table if exists t1; > --- SQL operation complete. > >>drop table if exists t2; > --- SQL operation complete. > >>drop table if exists t3; > --- SQL operation complete. > >>drop table if exists t4; > --- SQL operation complete. > >> > >> > >>?section rangesplitby > >> > >>-- create table range split by fails when column name is a reserved word > >> > >>create table mytable (c1 int not null, "YEAR" int not null, primary key > >>(c1, "YEAR")) range split by (c1, "YEAR") (add first key (1, 1)); > *** ERROR[15001] A syntax error occurred at or before: > create table mytable (c1 int not null, "YEAR" int not null, primary key (c1, > "Y > EAR")) range split by (c1, "YEAR") (add first key (1, 1)); > ^ (97 characters from start of SQL statement) > *** ERROR[8822] The statement was not prepared. > >> > >> > >>?section constraintname > >> > >>-- using a reserved word as a constraint name fails > >> > >>drop table if exists mytable1; > --- SQL operation complete. > >>drop table if exists mytable2; > --- SQL operation complete. > >> > >>create table mytable1 (c1 int constraint "DATE" unique); > *** ERROR[4082] Object TRAFODION.SCH.MYTABLE1 does not exist or is > inaccessible. > *** ERROR[1029] Object TRAFODION.SCH.DATE could not be created. > *** ERROR[1029] Object TRAFODION.SCH.MYTABLE1 could not be created. > --- SQL operation failed with errors. > >>alter table mytable1 drop constraint "DATE"; > *** ERROR[1127] The specified table TRAFODION.SCH.MYTABLE1 does not exist, is > inaccessible or is not a base table. Please verify that the correct table was > specified. > --- SQL operation failed with errors. > >> > >>create table mytable2 (c1 int, constraint "TIME" unique(c1)); > *** ERROR[4082] Object TRAFODION.SCH.MYTABLE2 does not exist or is > inaccessible. > *** ERROR[1029] Object TRAFODION.SCH.TIME could not be created. > *** ERROR[1029] Object TRAFODION.SCH.MYTABLE2 could not be created. > --- SQL operation failed with errors. > >>alter table mytable2 drop constraint "TIME"; > *** ERROR[1127] The specified table TRAFODION.SCH.MYTABLE2 does not exist, is > inaccessible or is not a base table. Please verify that the correct table was > specified. > --- SQL operation failed with errors. > >> > >>drop table if exists mytable1; > --- SQL operation complete. > >>drop table if exists mytable2; > --- SQL operation complete. > >> > >> > >> > >> > >>exit; > End of MXCI Session > {quote} -- This message was sent by Atlassian JIRA (v7.6.3#76005)