[TRAFODION-3131] Use of reserved words as names fails in many places

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3
• Fix Version/s: 2.3
• Component/s: sql-cmp
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 05/Jul/18 18:48
• Updated: 13/Aug/18 22:36
• Resolved: 11/Jul/18 20:12

Description

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:

>>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

Issue Links

• links to: GitHub Pull Request #1637