[
https://issues.apache.org/jira/browse/DERBY-3256?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-3256:
---------------------------------
Attachment: keywords.tar
Attaching keywords.tar. This tarball contains files which list the reserved and
non-reserved keywords for SQL 92, SQL 2003, and Derby 10.4. The tarball also
contains a script, sqlKeywords.sql, which runs the LineListVTI against these
files in order to figure out discrepancies between Derby and the standard. Here
is the script output:
ij version 10.4
ij> connect 'jdbc:derby:keyworddb;create=true';
WARNING 01J01: Database 'keyworddb' not created, connection made to existing
database instead.
ij> --------------------------------------------------------
--
-- Declare a VTI to read the keywords.
--
--------------------------------------------------------
drop function sqlKeywordsVTI;
0 rows inserted/updated/deleted
ij> create function sqlKeywordsVTI( fileName varchar( 32672 ) )
returns TABLE
(
line varchar( 30 )
)
language java
parameter style DERBY_JDBC_RESULT_SET
no sql
external name 'org.apache.derbyDemo.vtis.example.LineListVTI.lineListVTI'
;
0 rows inserted/updated/deleted
ij> --------------------------------------------------------
--
-- Sanity check that for each rev of the SQL spec, the reserved
-- and non-reserved lists don't overlap.
--
--------------------------------------------------------
select reserved.line
from table( sqlKeywordsVTI( './sql92-reserved.txt' ) ) reserved,
table( sqlKeywordsVTI( './sql92-nonreserved.txt' ) ) nonreserved
where reserved.line = nonreserved.line;
LINE
------------------------------
0 rows selected
ij> select reserved.line
from table( sqlKeywordsVTI( './sql2003-reserved.txt' ) ) reserved,
table( sqlKeywordsVTI( './sql2003-nonreserved.txt' ) ) nonreserved
where reserved.line = nonreserved.line;
LINE
------------------------------
0 rows selected
ij> --------------------------------------------------------
--
-- List the SQL 92 reserved keywords which became non-reserved
-- in SQL 2003.
--
--------------------------------------------------------
select oldreserved.line
from table( sqlKeywordsVTI( './sql92-reserved.txt' ) ) oldreserved,
table( sqlKeywordsVTI( './sql2003-nonreserved.txt' ) ) newnonreserved
where oldreserved.line = newnonreserved.line;
LINE
------------------------------
ABSOLUTE
ACTION
ADD
ASC
ASSERTION
CASCADE
CATALOG
COLLATION
CONNECTION
CONSTRAINTS
CONTINUE
DEFERRABLE
DEFERRED
DESC
DESCRIPTOR
DIAGNOSTICS
DOMAIN
EXCEPTION
FIRST
FOUND
GO
GOTO
IMMEDIATE
INITIALLY
INPUT
ISOLATION
KEY
LAST
LEVEL
NAMES
NEXT
OPTION
OUTPUT
PAD
PARTIAL
PRESERVE
PRIOR
PRIVILEGES
PUBLIC
READ
RELATIVE
RESTRICT
SCHEMA
SECTION
SESSION
SIZE
SPACE
TEMPORARY
TRANSACTION
USAGE
VIEW
WORK
WRITE
ZONE
54 rows selected
ij> --------------------------------------------------------
--
-- No SQL 92 nonreserved keywords became reserved
-- in SQL 2003.
--
--------------------------------------------------------
select oldnonreserved.line
from table( sqlKeywordsVTI( './sql92-nonreserved.txt' ) ) oldnonreserved,
table( sqlKeywordsVTI( './sql2003-reserved.txt' ) ) newreserved
where oldnonreserved.line = newreserved.line;
LINE
------------------------------
0 rows selected
ij> --------------------------------------------------------
--
-- List the Derby non-reserved keywords which are NOT non-reserved
-- in the SQL 92 standard.
--
--------------------------------------------------------
select derbywords.line
from table( sqlKeywordsVTI( './derby10.4-nonreserved.txt' ) ) derbywords
where derbywords.line not in
(
select sqlwords.line
from table( sqlKeywordsVTI( './sql92-nonreserved.txt' ) ) sqlwords
);
LINE
------------------------------
ABS
ABSVAL
ACTION
ALWAYS
BLOB
CALLED
CLOB
CONCAT
CONTAINS
DATE
DAY
DEFINER
DYNAMIC
GENERATED
IDENTITY_VAL_LOCAL
INCREMENT
INITIAL
INOUT
INTERVAL
INVOKER
LANGUAGE
LARGE
LEVEL
LOCKS
LOCKSIZE
LOGGED
MOD
MODIFIES
MODIFY
MONTH
_MORE
NCLOB
NULLS
OBJECT
PRECISION
RELEASE
RESTART
RETURNS
ROW
SAVEPOINT
SECURITY
SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
START
STATEMENT
SYNONYM
THEN
TIME
TIMESTAMP
TIMESTAMPADD
TIMESTAMPDIFF
TRUNCATE
UNCOMMITTED
USAGE
WHEN
62 rows selected
ij> --------------------------------------------------------
--
-- List the Derby non-reserved keywords which are NOT non-reserved
-- in the SQL 2003 standard.
--
--------------------------------------------------------
select derbywords.line
from table( sqlKeywordsVTI( './derby10.4-nonreserved.txt' ) ) derbywords
where derbywords.line not in
(
select sqlwords.line
from table( sqlKeywordsVTI( './sql2003-nonreserved.txt' ) ) sqlwords
);
LINE
------------------------------
ABS
ABSVAL
BLOB
CALLED
CLOB
CONCAT
DATE
DAY
DYNAMIC
IDENTITY_VAL_LOCAL
INITIAL
INOUT
INTERVAL
LANGUAGE
LARGE
LOCKS
LOCKSIZE
LOGGED
MOD
MODIFIES
MODIFY
MONTH
_MORE
NCLOB
PRECISION
RELEASE
RETURNS
ROW
SAVEPOINT
SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
START
SYNONYM
THEN
TIME
TIMESTAMP
TIMESTAMPADD
TIMESTAMPDIFF
TRUNCATE
WHEN
47 rows selected
ij> --------------------------------------------------------
--
-- List the Derby reserved keywords which are NOT reserved
-- in the SQL 92 standard.
--
--------------------------------------------------------
select derbywords.line
from table( sqlKeywordsVTI( './derby10.4-reserved.txt' ) ) derbywords
where derbywords.line not in
(
select sqlwords.line
from table( sqlKeywordsVTI( './sql92-reserved.txt' ) ) sqlwords
);
LINE
------------------------------
BINARY
D
_DEFAULT
ENDEXEC
FUNCTION
T
TS
VARBINARY
8 rows selected
ij> --------------------------------------------------------
--
-- List the Derby reserved keywords which are NOT reserved
-- in the SQL 2003 standard.
--
--------------------------------------------------------
select derbywords.line
from table( sqlKeywordsVTI( './derby10.4-reserved.txt' ) ) derbywords
where derbywords.line not in
(
select sqlwords.line
from table( sqlKeywordsVTI( './sql2003-reserved.txt' ) ) sqlwords
);
LINE
------------------------------
ADD
ASC
ASSERTION
BIT
CASCADE
COLLATION
CONNECTION
CONSTRAINT
CONSTRAINTS
CONTINUE
CONVERT
CORRESPONDING
COUNT
D
_DEFAULT
DEFERRABLE
DEFERRED
DESC
DIAGNOSTICS
ENDEXEC
EXCEPTION
FIRST
FOUND
GO
GOTO
IMMEDIATE
INITIALLY
INPUT
ISOLATION
KEY
LAST
NEXT
OPTION
OUTPUT
PAD
PARTIAL
PRESERVE
PRIOR
PRIVILEGES
PUBLIC
READ
RELATIVE
RESTRICT
SCHEMA
SPACE
SQLCODE
SQLERROR
T
TEMPORARY
TRANSACTION
TS
VARBINARY
VIEW
WORK
WRITE
55 rows selected
ij> --------------------------------------------------------
--
-- List the Derby reserved keywords which are not part
-- of the SQL 2003 standard.
--
--------------------------------------------------------
select derbywords.line
from table( sqlKeywordsVTI( './derby10.4-reserved.txt' ) ) derbywords
where derbywords.line not in
(
select sqlwords.line
from table( sqlKeywordsVTI( './sql2003-reserved.txt' ) ) sqlwords
);
LINE
------------------------------
ADD
ASC
ASSERTION
BIT
CASCADE
COLLATION
CONNECTION
CONSTRAINT
CONSTRAINTS
CONTINUE
CONVERT
CORRESPONDING
COUNT
D
_DEFAULT
DEFERRABLE
DEFERRED
DESC
DIAGNOSTICS
ENDEXEC
EXCEPTION
FIRST
FOUND
GO
GOTO
IMMEDIATE
INITIALLY
INPUT
ISOLATION
KEY
LAST
NEXT
OPTION
OUTPUT
PAD
PARTIAL
PRESERVE
PRIOR
PRIVILEGES
PUBLIC
READ
RELATIVE
RESTRICT
SCHEMA
SPACE
SQLCODE
SQLERROR
T
TEMPORARY
TRANSACTION
TS
VARBINARY
VIEW
WORK
WRITE
55 rows selected
ij> --------------------------------------------------------
--
-- List the Derby reserved keywords which are non-reserved
-- in the SQL 2003 standard.
--
--------------------------------------------------------
select derbywords.line
from table( sqlKeywordsVTI( './derby10.4-reserved.txt' ) ) derbywords,
table( sqlKeywordsVTI( './sql2003-nonreserved.txt' ) ) sqlwords
where derbywords.line = sqlwords.line;
LINE
------------------------------
ADD
ASC
ASSERTION
CASCADE
COLLATION
CONNECTION
CONSTRAINTS
CONTINUE
DEFERRABLE
DEFERRED
DESC
DIAGNOSTICS
EXCEPTION
FIRST
FOUND
GO
GOTO
IMMEDIATE
INITIALLY
INPUT
ISOLATION
KEY
LAST
NEXT
OPTION
OUTPUT
PAD
PARTIAL
PRESERVE
PRIOR
PRIVILEGES
PUBLIC
READ
RELATIVE
RESTRICT
SCHEMA
SPACE
TEMPORARY
TRANSACTION
VIEW
WORK
WRITE
42 rows selected
> Derby's reserved keyword list does not agree with either the SQL 92 or SQL
> 2003 standards
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3256
> URL: https://issues.apache.org/jira/browse/DERBY-3256
> Project: Derby
> Issue Type: Improvement
> Affects Versions: 10.3.1.4
> Reporter: Rick Hillegas
> Attachments: keywords.tar
>
>
> According to the comments in sqlgrammar.jj, Derby's understanding of reserved
> vs. non-reserved keywords is supposed to be based on SQL 92. However, Derby
> has 8 reserved keywords which are not part of the SQL 92 list of reserved
> keywords. The SQL 2003 spec moved many of the SQL 92 reserved keywords to the
> non-reserved list. Derby has 55 reserved keywords which are not part of the
> SQL 2003 list. 42 of Derby's reserved keywords are in the SQL 2003 list of
> non-reserved keywords.
> The reserved keywords create migration problems when moving applications from
> other databases to Derby. We should consider whether there is any reason that
> Derby should have more reserved keywords than appear in the SQL 2003 standard.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
