David Wayne Birdsall created TRAFODION-3131:
-----------------------------------------------
Summary: 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
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)
