FWD » Core Development » Database

In org.h2.util.ParserUtil, there are lots of constants defining various tokens. Not all can be used in a column name. I've collected all those appearing in ParserUtil.getTokenType and the results are these.

The following can be used as a column name:

[AND, AS, ASYMMETRIC, BETWEEN, BOTH, CAST, CURRENT_PATH, CURRENT_ROLE, DAY, ELSE, END, FILTER, GROUPS, HOUR, ILIKE, IN , KEY, LEADING, MINUTE, MONTH, OR, OVER, PARTITION, RANGE, REGEXP, ROWS, SECOND, SESSION_USER, SET, SYMMETRIC, SYSDATE, SYSTEM_USER, SYSTIME, SYSTIMESTAMP, TO, TODAY, TOP, TRAILING, USER, VALUE, WHEN, YEAR]

The following can not be used as a column name:

[ALL, ARRAY, CASE, CHECK, CONSTRAINT, CROSS, CURRENT_CATALOG, CURRENT_DATE, CURRENT_SCHEMA, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, DISTINCT, EXCEPT, EXISTS, FALSE, FETCH, FOR, FOREIGN, FROM, FULL, GROUP, HAVING, IF, INNER, INTERSECT, INTERSECTS, INTERVAL, IS, JOIN, LEFT, LIKE, LIMIT, LOCALTIME, LOCALTIMESTAMP, MINUS, NATURAL, NOT, NULL, OFFSET, ON, ORDER, PRIMARY, QUALIFY, RIGHT, ROW, ROWNUM, SELECT, TABLE, TRUE, UNION, UNIQUE, UNKNOWN, USING, VALUES, WHERE, WINDOW, WITH, _ROWID_]

In P2JH2Dialect.getReservedKeywords we currently have these:

      String[] keywords = {
         "cross",
         "current_date",
         "current_time",
         "current_timestamp",
         "distinct",
         "except",
         "exists",
         "false",
         "fetch",
         "for",
         "from",
         "full",
         "group",
         "having",
         "inner",
         "intersect",
         "interval",
         "is",
         "join",
         "like",
         "limit",
         "minus",
         "natural",
         "not",
         "null",
         "offset",
         "on",
         "order",
         "primary",
         "rownum",
         "select",
         "sysdate",
         "systime",
         "systimestamp",
         "today",
         "true",
         "union",
         "unique",
         "where",
      };

In NameConverter.reservedSql we have the same:

      String[] ansiKeywords = {
         "count",
         "cross",
         "current_date",
         "current_time",
         "current_timestamp",
         "distinct",
         "except",
         "exists",
         "false",
         "fetch",
         "for",
         "from",
         "full",
         "group",
         "having",
         "inner",
         "intersect",
         "is",
         "join",
         "like",
         "limit",
         "minus",
         "natural",
         "not",
         "null",
         "offset",
         "on",
         "order",
         "primary",
         "rownum",
         "select",
         "sysdate",
         "systime",
         "systimestamp",
         "today",
         "true",
         "union",
         "unique",
         "where",
      };

I'm inclined to add all tokens from ParserUtil as reserved keywords for H2.

Constantin,

I am not sure, but that these special non-reserved keywords which can be used a field/table names might need to be back-quoted in order to be correctly parsed.
This needs testing. Might depend on the dialect.

Ovidiu Maxiniuc wrote:

Constantin,

I am not sure, but that these special non-reserved keywords which can be used a field/table names might need to be back-quoted in order to be correctly parsed.
This needs testing. Might depend on the dialect.

Yes, for H2 this works:

create table tt1
(
`cross` integer
);

but this would require to back-quote the fields in any other statements (SELECT, INSERT, UPDATE, etc).

Just looked over the changes and they seem fine. Is _rowid_ really a reserved keyword for H2? Seems an odd one.

Is there anything left to do here, or can this issue be closed?

Eric Faulhaber wrote:

Just looked over the changes and they seem fine. Is _rowid_ really a reserved keyword for H2? Seems an odd one.

_rowid_ is defined in ParserUtil.getTokenType. What is used for - I haven't checked. But I did check that it can't be used as a column name.

... can this issue be closed?

Yes, it can be closed.