[MDEV-13384] "window" seems like a reserved column name but it's not listed as one Created: 2017-07-26  Updated: 2018-06-21  Resolved: 2017-11-29

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Optimizer - Window functions
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.2.12

Type: Bug Priority: Minor
Reporter: Teun Beijers Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: None
Environment:

official MariaDB docker container on Debian 8 (jessie)

Issue Links:
Relates
relates to MDEV-12809 Bad column type created for TEXT(1431... Closed

 Description   

When I try to make a table with a column called "window" I get the following error:

mysql> CREATE TABLE door (id INT, window VARCHAR(10));
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'window VARCHAR(10))' at line 1

If I do the same with another column name it works though:

mysql> CREATE TABLE door (id INT, door VARCHAR(10));
 
Query OK, 0 rows affected (0.04 sec)

If I look at the reserved keywords: https://mariadb.com/kb/en/mariadb/reserved-words/ "window" doesn't appear to be listed there.

I use the official mariadb docker image without any custom config changes. Tested in an empty database.

 Comments   
Comment by Alice Sherepa [ 2017-07-26 ]

thanks for the report! indeed, an error is shown, starting from 10.2.0

Comment by Elena Stepanova [ 2017-07-26 ]

cvicentiu, could you please check whether there is a good reason why the word is reserved? If there is, then documentation needs to be amended, otherwise probably the code should be fixed.

Comment by Erik Cederstrand [ 2017-11-05 ]

Any updates on this? We just hit this with some proprietary software that uses MariaDB as a backend, and where we don't have access to change the query to properly quote the `window` column name.

Comment by Vicențiu Ciorbaru [ 2017-11-06 ]

Hi!

I will check the standard and come back to you today or tomorrow. If the standard marks the keyword as reserved, it's going to be a bit more difficult. I'll post an update soon.

Comment by Erik Cederstrand [ 2017-11-21 ]

I can't seem to find a link to the official ANSI standard, but in all the overviews I did find, including https://en.wikipedia.org/wiki/Category:SQL_keywords, 'window' is not a reserved word.

Comment by Sergei Golubchik [ 2017-11-21 ]

window is a reserved word in the latest SQL:2016 standard. And, looking at the parser, I don't see how we can "un-reserve" it, if we do, the grammar would become ambiguous.

Comment by Vicențiu Ciorbaru [ 2017-11-23 ]

I've tried a bit to make the grammar work with un-reserving the keyword. It doesn't seem to be possible (or I haven't found a good solution yet).

Comment by Erik Cederstrand [ 2017-11-23 ]

Thanks for the update. The ISO standard costs real money, unfortunately, but according to https://www.postgresql.org/docs/9.6/static/sql-keywords-appendix.html WINDOW has actually been reserved since SQL:2008.

Comment by Vicențiu Ciorbaru [ 2017-11-23 ]

ErikCederstrand I'm working on a fix that will allow usage of WINDOW as an identifier except in Table Aliases. We can't guarantee if this will remain permanent for all future versions (10.4, etc) but 10.2 and 10.3 should have it.

Comment by Vicențiu Ciorbaru [ 2017-11-29 ]

Fixed by allowing a workaround within the grammar. The fix was suggested by monty. Window can not be used as a table alias, as it would make the following statement ambiguous:

SELECT *
FROM t1
WINDOW ...

However, we are able to use WINDOW in all other contexts, so we allow it.
8cee2f136d7cccb4bf8d6ac4ecfc224a495b9cec

Generated at Thu Feb 08 08:05:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.