Derby
  1. Derby
  2. DERBY-2245

DatabaseMetaData.getSQLKeywords() contains words that are not keywords in Derby.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0
    • Fix Version/s: None
    • Component/s: JDBC
    • Urgency:
      Normal

      Description

      Such as REFRESH and PUBLICATION.
      Not sure what the exact contents should be.

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2. Unassigned due to lack of activity.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2. Unassigned due to lack of activity.
          Hide
          Bryan Pendleton added a comment -

          Myrna suggests: "- make getSQLKeywords return all non SQL keywords (i.e. both those in the reserved, and non-reserved area). "

          This seems very reasonable to me. My expectation of this call is that it returns
          information about words that this database might treat specially, so that I might
          need to put them in quotation marks if I used them as the names of columns,
          tables, views, etc. I could see this sort of information being useful if I was trying
          to write a generic tool, like a DBA tool for creating tables, etc. – I might want my
          tool to warn my user that their chosen column name is a keyword in this database,
          and did they want to choose a different name. So your proposed
          implementation seems good to me.

          Show
          Bryan Pendleton added a comment - Myrna suggests: "- make getSQLKeywords return all non SQL keywords (i.e. both those in the reserved, and non-reserved area). " This seems very reasonable to me. My expectation of this call is that it returns information about words that this database might treat specially, so that I might need to put them in quotation marks if I used them as the names of columns, tables, views, etc. I could see this sort of information being useful if I was trying to write a generic tool, like a DBA tool for creating tables, etc. – I might want my tool to warn my user that their chosen column name is a keyword in this database, and did they want to choose a different name. So your proposed implementation seems good to me.
          Hide
          Myrna van Lunteren added a comment -

          After lack of response on the list (except from Dan, thx) and some more thought:

          • there is no mention of 'reserved' in the javadoc, and it's not clear what the behavior is supposed to be if you use a reserved keyword. So it's hard to verify what are
            really the reserved keywords. So we'll just take the current split between reserved and non-reserved in sqlgrammar.jj as truth.
          • Wikipedia says: 'A keyword is a word or concept with special significance' and 'an identifier in a computer language that indicates a specific command '. So again, this suggests all keywords, not just reserved.
          • the spirit of the call is to return non-sql keywords, not specific to which SQL spec. So, let's not make a difference between jdk16 and jdk14/15 (jdk16 says non-SQL2003, earlier says non-SQL92).

          So, I propose to:

          • move the XML related (and any other non SQL99, but SQL2003 - which would they be?) keywords into the SQL reserved keywords area
          • modify the javadoc to say, SQL2003, rather than SQL92.
          • update any relevant docs
          • make getSQLKeywords return all non SQL keywords (i.e. both those in the reserved, and non-reserved area).
          Show
          Myrna van Lunteren added a comment - After lack of response on the list (except from Dan, thx) and some more thought: there is no mention of 'reserved' in the javadoc, and it's not clear what the behavior is supposed to be if you use a reserved keyword. So it's hard to verify what are really the reserved keywords. So we'll just take the current split between reserved and non-reserved in sqlgrammar.jj as truth. Wikipedia says: 'A keyword is a word or concept with special significance' and 'an identifier in a computer language that indicates a specific command '. So again, this suggests all keywords, not just reserved. the spirit of the call is to return non-sql keywords, not specific to which SQL spec. So, let's not make a difference between jdk16 and jdk14/15 (jdk16 says non-SQL2003, earlier says non-SQL92). So, I propose to: move the XML related (and any other non SQL99, but SQL2003 - which would they be?) keywords into the SQL reserved keywords area modify the javadoc to say, SQL2003, rather than SQL92. update any relevant docs make getSQLKeywords return all non SQL keywords (i.e. both those in the reserved, and non-reserved area).
          Hide
          Daniel John Debrunner added a comment -

          Does a keyword being reserved has anything to do with this method, the JDBC javadoc as Knut Anders points out just says non-SQL 2003 keywords?

          In the list Myrna provides at least these are SQL 2003 keywords:

          BIGINT,BOOLEAN,CALL,CURRENT_ROLE,ROLE,NONE

          The XML words are part of standard SQL, but maybe not SQL2003.

          I'm not sure though what the correct list should be, I can't figure out a use for this method so it's hard to tell what it should do.

          Show
          Daniel John Debrunner added a comment - Does a keyword being reserved has anything to do with this method, the JDBC javadoc as Knut Anders points out just says non-SQL 2003 keywords? In the list Myrna provides at least these are SQL 2003 keywords: BIGINT,BOOLEAN,CALL,CURRENT_ROLE,ROLE,NONE The XML words are part of standard SQL, but maybe not SQL2003. I'm not sure though what the correct list should be, I can't figure out a use for this method so it's hard to tell what it should do.
          Hide
          Myrna van Lunteren added a comment -

          Is this issue stalled? Revision 504234 only removed REFRESH and PUBLICATION.

          I had a quick look, and it seems to me that the reason why a user would possibly want to return this is if it's assumed these keywords have a special effect, i.e. are reserved keywords.

          Maybe someone can check on the JDBC spec and say whether this is true, but even if it is not explicit about this, I would suggest we return the list of reserved non-SQL 92 keywords.

          So, instead of the current list: ALIAS,BIGINT,BOOLEAN,CALL,CLASS,COPY,DB2J_DEBUG,EXECUTE,EXPLAIN,FILE,FILTER,GETCURRENTCONNECTION,INDEX,INSTANCEOF,METHOD,NEW,OFF,PROPERTIES,RECOMPILE,RENAME,RUNTIMESTATISTICS,STATEMENT,STATISTICS,TIMING,WAIT
          we make this method return the contents of the sqlgrammar.jj section titled '/* Additional JSQL reserved keywords – non-SQL92 reserved Keywords */'

          However, I - possibly erroneously - assumed that reserved meant that you'd get a syntax error if you tried to use them in a create table statement; but this is not the case, for instance, I can create a table with the name 'curdate' for instance. So, are the items listed here 'really' reserved (non-sql 92) keywords? How can I find out?

          Assuming the ones on this list really are reserved keywords, I'd suggest toreturn:
          'BIGINT,BOOLEAN,CALL,CURDATE,CURRENT_ROLE,CURTIME,DATABASE,GETCURRENTCONNECTION,EXPLAIN,BIGINT,LONG,LTRIM,NONE,ROLE,RTRIM,SUBSTR,XML,XMLEXISTS,XMLPARSE,XMLQUERY,XMLSERIALIZE'

          Show
          Myrna van Lunteren added a comment - Is this issue stalled? Revision 504234 only removed REFRESH and PUBLICATION. I had a quick look, and it seems to me that the reason why a user would possibly want to return this is if it's assumed these keywords have a special effect, i.e. are reserved keywords. Maybe someone can check on the JDBC spec and say whether this is true, but even if it is not explicit about this, I would suggest we return the list of reserved non-SQL 92 keywords. So, instead of the current list: ALIAS,BIGINT,BOOLEAN,CALL,CLASS,COPY,DB2J_DEBUG,EXECUTE,EXPLAIN,FILE,FILTER,GETCURRENTCONNECTION,INDEX,INSTANCEOF,METHOD,NEW,OFF,PROPERTIES,RECOMPILE,RENAME,RUNTIMESTATISTICS,STATEMENT,STATISTICS,TIMING,WAIT we make this method return the contents of the sqlgrammar.jj section titled '/* Additional JSQL reserved keywords – non-SQL92 reserved Keywords */' However, I - possibly erroneously - assumed that reserved meant that you'd get a syntax error if you tried to use them in a create table statement; but this is not the case, for instance, I can create a table with the name 'curdate' for instance. So, are the items listed here 'really' reserved (non-sql 92) keywords? How can I find out? Assuming the ones on this list really are reserved keywords, I'd suggest toreturn: 'BIGINT,BOOLEAN,CALL,CURDATE,CURRENT_ROLE,CURTIME,DATABASE,GETCURRENTCONNECTION,EXPLAIN,BIGINT,LONG,LTRIM,NONE,ROLE,RTRIM,SUBSTR,XML,XMLEXISTS,XMLPARSE,XMLQUERY,XMLSERIALIZE'
          Hide
          Knut Anders Hatlen added a comment -

          The first approach seems fine. I guess sqlgrammar.jj is the best place to find Derby's keywords. You'd probably be most interested in the two sections labelled /* Additional JSQL reserved keywords – non-SQL92 reserved Keywords / and / Additional JSQL keywords – non-SQL92 non-reserved Keywords */.

          Note that the javadoc at http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getSQLKeywords() says that only keywords that are not SQL:2003 keywords should be returned.

          Show
          Knut Anders Hatlen added a comment - The first approach seems fine. I guess sqlgrammar.jj is the best place to find Derby's keywords. You'd probably be most interested in the two sections labelled /* Additional JSQL reserved keywords – non-SQL92 reserved Keywords / and / Additional JSQL keywords – non-SQL92 non-reserved Keywords */. Note that the javadoc at http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getSQLKeywords( ) says that only keywords that are not SQL:2003 keywords should be returned.
          Hide
          Saurabh Vyas added a comment -

          I am interested in working on this. EmbedDatabaseMetaData implements getSQLKeywords() method & it returns some key words which are not supported by Derby (like REFRESH and PUBLICATION, I am also not sure about other keywords ).

          Now we can have two approaches

          • to remove them from the return statement (i.e. only those KeyWords should be returned which are supported by derby)
          • to implement these functionality.

          First approach is simple & straight forward and I had modified the the getSQLKeywords() accordingly. Can someone please guide me to derby docs to find out what all keywords are supported by Derby so that I can modify the method for all the keywords.

          Comments or suggestions ?

          Show
          Saurabh Vyas added a comment - I am interested in working on this. EmbedDatabaseMetaData implements getSQLKeywords() method & it returns some key words which are not supported by Derby (like REFRESH and PUBLICATION, I am also not sure about other keywords ). Now we can have two approaches to remove them from the return statement (i.e. only those KeyWords should be returned which are supported by derby) to implement these functionality. First approach is simple & straight forward and I had modified the the getSQLKeywords() accordingly. Can someone please guide me to derby docs to find out what all keywords are supported by Derby so that I can modify the method for all the keywords. Comments or suggestions ?

            People

            • Assignee:
              Unassigned
              Reporter:
              Daniel John Debrunner
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development