Date: 2014-10-31 18:22:43 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.19.3 (Oct2014)
CC: @njnes

Last updated: 2015-05-07 12:37:50 +0200

Comment 20410

Date: 2014-10-31 18:22:43 +0100
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:33.0) Gecko/20100101 Firefox/33.0
Build Identifier:

The following keywords exist as column name in the sys.* (and t,p.*) tables/views:
action, default, schema, start, statement, user.
This causes an undesired error when using the column name in a query such as:
SELECT tbl.action FROM sys.keys tbl;
SELECT tbl.default FROM sys.columns tbl;
SELECT tbl.schema FROM sys.statistics tbl;
SELECT tbl.schema FROM sys.storage tbl;
SELECT tbl.start FROM sys.querylog_calls tbl;
SELECT tbl.start FROM sys.querylog_history tbl;
SELECT tbl.start FROM sys.sequences tbl;
SELECT tbl.statement FROM sys.triggers tbl;
SELECT tbl.user FROM sys.connections tbl;
SELECT tbl.user FROM sys.queue tbl;
SELECT tbl.user FROM sys.sessions tbl;
SELECT tbl.user FROM sys.tracelog tbl;

SQuirreL generates such SQL queries when you view the meta data objects. Due to the error returned by MonetDB, no data is returned and thus no meta data in visible in SQuirreL, which is confusing (as the count(*) does show a positive number). As these SQL query syntax is generated, it is not possible to correct it in SQuirreL.

A solution is
a) to avoid these names (which are reserved keywords) in the data dictionary, this implies however that the current names are changed
b) enhance the SQL parser to not give an error as it found a reserved keyword but first try match a column with that name and if successful use it

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (MonetDB 5 server v11.19.3 "Oct2014")
2. start SQuirreL
3. run the SQL queries:
   SELECT tbl.action FROM sys.keys tbl;
   SELECT tbl.action FROM tmp.keys tbl;

SELECT tbl.default FROM sys._columns tbl;
SELECT tbl.default FROM sys.columns tbl;
SELECT tbl.default FROM tmp._columns tbl;

SELECT tbl.schema FROM sys.statistics tbl;
SELECT tbl.schema FROM sys.storage tbl;
SELECT tbl.schema FROM sys.storagemodel tbl;
SELECT tbl.schema FROM sys.storagemodelinput tbl;
SELECT tbl.schema FROM sys.tablestoragemodel tbl;

SELECT tbl.start FROM sys.querylog_calls tbl;
SELECT tbl.start FROM sys.querylog_history tbl;
SELECT tbl.start FROM sys.sequences tbl;

SELECT tbl.statement FROM sys.triggers tbl;
SELECT tbl.statement FROM tmp.triggers tbl;

SELECT tbl.user FROM sys.connections tbl;
SELECT tbl.user FROM sys.queue tbl;
SELECT tbl.user FROM sys.sessions tbl;
SELECT tbl.user FROM sys.tracelog tbl;

SELECT action FROM sys.keys;
SELECT action FROM tmp.keys;

SELECT default FROM sys._columns;
SELECT default FROM sys.columns;
SELECT default FROM tmp._columns;

SELECT schema FROM sys.statistics;
SELECT schema FROM sys.storage;
SELECT schema FROM sys.storagemodel;
SELECT schema FROM sys.storagemodelinput;
SELECT schema FROM sys.tablestoragemodel;

SELECT start FROM sys.querylog_calls;
SELECT start FROM sys.querylog_history;
SELECT start FROM sys.sequences;

SELECT statement FROM sys.triggers;
SELECT statement FROM tmp.triggers;

4. in SQuirreL, select objects tab. Open the tree on sys, next SYSTEM TABLE. select the table "_columns" or any of the other above identified tables. select in the right view the tab Content. It should now show the records of the table sys._columns but no records are shown.

Actual Results:

SELECT default FROM sys._columns
-- Error: syntax error, unexpected DEFAULT in: "select default"

SELECT default FROM sys.columns
-- Error: syntax error, unexpected DEFAULT in: "select default"

SELECT default FROM tmp._columns
-- Error: syntax error, unexpected DEFAULT in: "select default"

SELECT schema FROM sys.statistics
-- Error: syntax error, unexpected SCHEMA in: "select schema"

SELECT schema FROM sys.storage
-- Error: syntax error, unexpected SCHEMA in: "select schema"

SELECT schema FROM sys.storagemodel
-- Error: syntax error, unexpected SCHEMA in: "select schema"

SELECT schema FROM sys.storagemodelinput
-- Error: syntax error, unexpected SCHEMA in: "select schema"

SELECT schema FROM sys.tablestoragemodel
-- Error: syntax error, unexpected SCHEMA in: "select schema"

SELECT start FROM sys.querylog_calls
-- Error: syntax error, unexpected START in: "select start"

SELECT start FROM sys.querylog_history
-- Error: syntax error, unexpected START in: "select start"

SELECT start FROM sys.sequences
-- Error: syntax error, unexpected START in: "select start"

etc.

Expected Results:

No syntax error for these queries

Comment 20653

Date: 2015-02-11 20:45:02 +0100
From: MonetDB Mercurial Repository <>

Changeset 914313189a7e made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=914313189a7e

Changeset description:

added some more (non)-reserved keywords in the exception list. This
solves bug #3613.

Comment 20657

Date: 2015-02-12 18:20:23 +0100
From: Martin van Dinther <<martin.van.dinther>>

There appears to be even more columns in system tables with names which are keywords. They can be retrieved with following query:
SELECT 'SELECT distinct '||C.name||' FROM '||S.name||'.'||T.name||';' as SQL_query
FROM sys.columns C join sys.tables T on C.table_id = T.id join sys.schemas S on T.schema_id = S.id
WHERE lower(C.name) in ('as', 'action', 'default','schema', 'start', 'statement', 'user', 'authorization', 'column', 'maxvalue', 'minvalue', 'sample', 'table', 'null', 'unique', 'cycle', 'distinct', 'increment', 'plan')
ORDER BY C.name, S.name, T.name

It produces following 40 occurences:
SELECT distinct action FROM sys.keys;
SELECT distinct action FROM tmp.keys;
SELECT distinct as FROM bam.sq;
SELECT distinct authorization FROM sys.schemas;
SELECT distinct column FROM sys.statistics;
SELECT distinct column FROM sys.storage;
SELECT distinct column FROM sys.storagemodel;
SELECT distinct column FROM sys.storagemodelinput;
SELECT distinct cycle FROM sys.sequences;
SELECT distinct default FROM sys._columns;
SELECT distinct default FROM sys.columns;
SELECT distinct default FROM tmp._columns;
SELECT distinct distinct FROM sys.storagemodelinput;
SELECT distinct increment FROM sys.sequences;
SELECT distinct maxvalue FROM sys.sequences;
SELECT distinct minvalue FROM sys.sequences;
SELECT distinct null FROM sys._columns;
SELECT distinct null FROM sys.columns;
SELECT distinct null FROM tmp._columns;
SELECT distinct sample FROM sys.statistics;
SELECT distinct schema FROM sys.statistics;
SELECT distinct schema FROM sys.storage;
SELECT distinct schema FROM sys.storagemodel;
SELECT distinct schema FROM sys.storagemodelinput;
SELECT distinct schema FROM sys.tablestoragemodel;
SELECT distinct start FROM sys.querylog_calls;
SELECT distinct start FROM sys.querylog_history;
SELECT distinct start FROM sys.sequences;
SELECT distinct statement FROM sys.triggers;
SELECT distinct statement FROM tmp.triggers;
SELECT distinct table FROM sys.statistics;
SELECT distinct table FROM sys.storage;
SELECT distinct table FROM sys.storagemodel;
SELECT distinct table FROM sys.storagemodelinput;
SELECT distinct table FROM sys.tablestoragemodel;
SELECT distinct unique FROM sys.statistics;
SELECT distinct user FROM sys.connections;
SELECT distinct user FROM sys.queue;
SELECT distinct user FROM sys.sessions;
SELECT distinct user FROM sys.tracelog;

in default branch two new ones are introduced:
SELECT distinct plan FROM sys.querylog_catalog;
SELECT distinct plan FROM sys.querylog_history;

Following patch could be used for sql_parse.y in section non_reserved_word:

/* next reserved words are used as column names in system tables, see issue 3613. /
| ACTION { $$ = sa_strdup(SA, "action"); } / sloppy: officially reserved /
| AS { $$ = sa_strdup(SA, "as"); } / sloppy: officially reserved /
| AUTHORIZATION { $$ = sa_strdup(SA, "authorization"); }/ sloppy: officially reserved /
| COLUMN { $$ = sa_strdup(SA, "column"); } / sloppy: officially reserved /
| CYCLE { $$ = sa_strdup(SA, "cycle"); } / sloppy: officially reserved /
| DISTINCT { $$ = sa_strdup(SA, "distinct"); } / sloppy: officially reserved /
| INCREMENT { $$ = sa_strdup(SA, "increment"); } / sloppy: officially reserved /
| MAXVALUE { $$ = sa_strdup(SA, "maxvalue"); } / sloppy: officially reserved /
| MINVALUE { $$ = sa_strdup(SA, "minvalue"); } / sloppy: officially reserved /
| SQL_PLAN { $$ = sa_strdup(SA, "plan"); } / sloppy: officially reserved /
| SAMPLE { $$ = sa_strdup(SA, "sample"); } / sloppy: officially reserved /
| SCHEMA { $$ = sa_strdup(SA, "schema"); } / sloppy: officially reserved /
| START { $$ = sa_strdup(SA, "start"); } / sloppy: officially reserved /
| STATEMENT { $$ = sa_strdup(SA, "statement"); } / sloppy: officially reserved /
| TABLE { $$ = sa_strdup(SA, "table"); } / sloppy: officially reserved /
| USER { $$ = sa_strdup(SA, "user"); } / sloppy: officially reserved /
/ end of 3613 */

Comment 20666

Date: 2015-02-19 14:06:22 +0100
From: MonetDB Mercurial Repository <>

Changeset 19f0a1f83f67 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=19f0a1f83f67

Changeset description:

Removed sloppy parsing for default and user as it changes the way the statement is processed.
Added sloppy parsing for keywords: 'as', 'authorization', 'column', 'cycle', 'distinct', 'increment', 'maxvalue', 'minvalue', 'plan', 'sample' and 'table'.
Also updated and extended tests for the occurences of these keywords as column name.
Also included a query which synthesizes the test queries. Whenever the SQL data dictionary changes and new column are introduced which are reserved keywords, this query will detect it.
This fix is related to bug #3613

Comment 20676

Date: 2015-02-25 20:47:04 +0100
From: @njnes

you could also telll squirel to quote (any) identifier.