You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2018/07/05 20:18:00 UTC
[jira] [Commented] (TRAFODION-3131) Use of reserved words as names
fails in many places
[ https://issues.apache.org/jira/browse/TRAFODION-3131?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16534094#comment-16534094 ]
David Wayne Birdsall commented on TRAFODION-3131:
-------------------------------------------------
Note: The CREATE TABLE example with RANGE SPLIT BY syntax is supported only in a derived product at the moment and not in Trafodion itself; I should not have included it as a test example in this JIRA.
> 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)