"t", "d" as column names need to be quoted in SQL

[arielCo]

Suppose I have Base document with table that has a column named “d”.

The query select d from mytable fails with:

SQL Status: HY000
Error code: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"

While select "d" from mytable succeeds. Same if the column is named t (haven’t bothered to try all one-letter names).

I can’t find a function named ‘t’ or ‘d’ in the HSQL docs at Chapter 4. Built In Functions, the literals appear to be written as DATE|TIME|TIMESTAMP|INTERVAL ‘…’ and another page has examples like SELECT a, b FROM atable WHERE d < 5 ....

What gives?

[oweng]

I cant find it right now (because I am mobile) but it does mention in the HSQLDB v1.8 User Guide that using double quotation marks allows reserved terms to be used in SQL. This seems to be the most likely reason why the quoted variant works. It is evidently good practice to quote all column and table name as I see Base experts doing this all the time over on the alternate forum.

[arielCo]

I thought the same (even though the error message is utterly uninformative), but I cant find what “t” and “d” mean. And the un-quoted example above came from the current HSQL User Guide.

[oweng]

I now see from the error code that you are likely using HSQL v2.3. It is possible I suppose that d and t are undocumented shorthand notation for DATE and TIME literals (as you suggest). I too can find nothing in the User Guide (other than the reference to “Reserved keywords”) that indicate these column names would require quotation marks.