[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.
On Saturday 01 April 2023 at 22:57:46, Keith Edmunds wrote:
"row_number" is a pre-defined function name and therefore cannot be used unquoted as a column name.
It could in 10.5 when I believe row_number was also a function.
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.
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.
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.
I'm not impressed that a point release introduces a reserved word that will undoubtedly clash with existing apps, but hey, we are where we are.
Hi, Keith,
On Apr 02, Keith Edmunds wrote:
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.
I'm not impressed that a point release introduces a reserved word that will undoubtedly clash with existing apps, but hey, we are where we are.
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:
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.
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