Below two sql was working fine with MariaDB 10.5.8 and MySQL 5.7
SET @curRow=0;
select @curRow := @curRow + 1 AS row_number, `users`.`name` from `users`
or
select ROW_NUMBER() OVER (ORDER BY users.id) AS row_number, `users`.`name` from `users`
But when I run in MariaDB 11.1.2
It throws error:
Query 1 ERROR: 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, users.name from users' at line 1
I checked with ChatGPT 3.5 It suggest me
SELECT row_number FROM (
SELECT @curRow := @curRow + 1 AS row_number, name
FROM users, (SELECT @curRow := 0) r
) AS subquery_alias
The error you're encountering is because you cannot use a user-defined variable to generate row numbers directly within the SELECT clause and then reference it within the same SELECT clause in MariaDB. Instead, you should use a subquery or a common table expression (CTE) to achieve this. Here's how you can rewrite your query using a subquery.
But it is not working. So what to do in MariaDB 11?
Thank for danblack answer it - https://dba.stackexchange.com/users/158002/danblack