Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13384

"window" seems like a reserved column name but it's not listed as one

Details

    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.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12809 Bad column type created for TEXT(1431655798) CHARACTER SET utf8

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            teuneboon Teun Beijers created issue -
            teuneboon Teun Beijers made changes -
            Field Original Value New Value
            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.             		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.
            alice Alice Sherepa made changes -
            Affects Version/s 10.2 [ 14601 ]
            alice Alice Sherepa added a comment -

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

            alice Alice Sherepa added a comment - thanks for the report! indeed, an error is shown, starting from 10.2.0
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.2.7 [ 22543 ]
            elenst Elena Stepanova added a comment -

            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.

            elenst Elena Stepanova added a comment - 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.
            elenst Elena Stepanova made changes -
            Fix Version/s 10.2 [ 14601 ]
            Assignee Vicentiu Ciorbaru [ cvicentiu ]
            serg Sergei Golubchik made changes -
            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.             		When I try to make a table with a column called "window" I get the following error:
            {code:sql}
            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
            {code}
            If I do the same with another column name it works though:
            {code:sql}
            mysql> CREATE TABLE door (id INT, door VARCHAR(10));
            Query OK, 0 rows affected (0.04 sec)
            {code}
            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.
            ErikCederstrand Erik Cederstrand added a comment -

            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.

            ErikCederstrand Erik Cederstrand added a comment - 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.
            cvicentiu Vicențiu Ciorbaru added a comment -

            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.

            cvicentiu Vicențiu Ciorbaru added a comment - 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.
            ErikCederstrand Erik Cederstrand added a comment -

            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.

            ErikCederstrand Erik Cederstrand added a comment - 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.
            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.
            cvicentiu Vicențiu Ciorbaru added a comment -

            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).

            cvicentiu Vicențiu Ciorbaru added a comment - 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).
            cvicentiu Vicențiu Ciorbaru made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            ErikCederstrand Erik Cederstrand added a comment -

            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.

            ErikCederstrand Erik Cederstrand added a comment - 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.
            cvicentiu Vicențiu Ciorbaru added a comment -

            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.

            cvicentiu Vicențiu Ciorbaru added a comment - 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.
            cvicentiu Vicențiu Ciorbaru added a comment -

            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

            cvicentiu Vicențiu Ciorbaru added a comment - 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
            cvicentiu Vicențiu Ciorbaru made changes -
            issue.field.resolutiondate 2017-11-29 07:45:09.0 2017-11-29 07:45:09.561
            cvicentiu Vicențiu Ciorbaru made changes -
            Component/s Optimizer - Window functions [ 13502 ]
            Fix Version/s 10.2.12 [ 22810 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 81796 ] MariaDB v4 [ 152529 ]

            People

              cvicentiu Vicențiu Ciorbaru
              teuneboon Teun Beijers
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.