[ 
https://issues.apache.org/jira/browse/TRAFODION-3131?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16540601#comment-16540601
 ] 

ASF GitHub Bot commented on TRAFODION-3131:
-------------------------------------------

Github user asfgit closed the pull request at:

    https://github.com/apache/trafodion/pull/1637


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

Reply via email to