[Maria-discuss] Column named 'row_number' gives error in 10.11
I have a table that includes an int column named row_number. This worked fine until I upgraded Debian from 11 to 12 which upgraded MariaDB from 10.5 to 10.11. I now get errors when referencing that column for updates. If I put backticks around the column name the update runs without error. Is this an expected behaviour change from 10.5 → 10.11? Example: CREATE DATABASE test; USE test; CREATE TABLE `kae` ( `id` int(11) NOT NULL, `row_number` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO `kae` (`id`, `row_number`) VALUES (1, 0); UPDATE kae SET row_number=12 WHERE id=1; 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 'row_number=12 WHERE id=1' at line 1
On Saturday 01 April 2023 at 21:39:17, Keith Edmunds wrote:
I have a table that includes an int column named row_number.
"row_number" is a pre-defined function name and therefore cannot be used unquoted as a column name. https://mariadb.com/kb/en/row_number/ Antony. -- Ramdisk is not an installation procedure. Please reply to the list; please *don't* CC me.
On Saturday 01 April 2023 at 22:57:46, Keith Edmunds wrote:
I've confirmed on a 10.5.18 instance I have here that row_number *can* be used as a column name, however I don't believe it was an available window function.
This is a change 10.5 → 10.11
See https://mariadb.com/kb/en/window-functions/ and select the box down on the left-hand side "MariaDB starting with 10.5.0" - you'll find that row_number is no longer highlighted, indicating to me that it was not an available function in that version of MariaDB. Antony. -- What do you get when you cross a joke with a rhetorical question? Please reply to the list; please *don't* CC me.
SQLAlchemy workaround and patch here: https://github.com/sqlalchemy/sqlalchemy/discussions/9585
On Sunday 02 April 2023 at 10:54:25, Keith Edmunds wrote:
SQLAlchemy workaround and patch here: https://github.com/sqlalchemy/sqlalchemy/discussions/9585
Fair enough. I can't say I'm impressed with a "patch" which eliminates something like this from the list of reserved words. They are, after all, reserved for a reason. However, if this works for you, that's good. Personally I would have renamed the column to "row_num", "rownum", "rownumber" or similar... Antony. -- 90% of networking problems are routing problems. 9 of the remaining 10% are routing problems in the other direction. The remaining 1% might be something else, but check the routing anyway. Please reply to the list; please *don't* CC me.
Hi, Keith, On Apr 02, Keith Edmunds wrote:
It was done in 10.7.1, part of the feature https://mariadb.com/kb/en/get-diagnostics/#row_number Every minor MariaDB release comes with new features, patch releases don't. In this case, in particular, ROW_NUMBER is a reserved keyword according to the SQL Standard (see the <reserved word> rule in the part 2). Using reserved words as identifiers is generally discouraged, even if a particular implementation allows that in a particular release. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
On 2023/04/02 10:29, Antony Stone wrote:
This is not the correct interpretation. Checking "MariaDB starting with 10.5.0" will highlight all pages/sections that were introduced in 10.5.0. If version information is not shown, it means that the text applies to all maintained versions (currently 10.3+). The ROW_NUMBER function was actually introduced in 10.2.
participants (4)
-
Antony Stone -
Ian Gilfillan -
Keith Edmunds -
Sergei Golubchik