|
Reproducing SQL test:
CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;
|
USE test;
|
CREATE TABLE author (name VARCHAR(32) NOT NULL);
|
INSERT INTO author (name) VALUES ('Charles Dickens'), ('Roald Dahl');
|
SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC;
|
SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC LIMIT 1;
|
SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC LIMIT 1 OFFSET 1;
|
(Based on a failing test in Django-MySQL: https://github.com/adamchainz/django-mysql/blob/2e4bb08488f737306f22b29ff217e2a33823d276/tests/testapp/test_functions.py#L634 ).
Tested using latest Docker images for 10.4 and 10.5.
On MariaDB 10.4, the LIMIT/OFFSET don't chage the order:
$ mysql -h 127.0.0.1 -P 3306 -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 8
|
Server version: 10.4.12-MariaDB-1:10.4.12+maria~bionic mariadb.org binary distribution
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
root@127.0.0.1 [1]> CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;
|
Query OK, 1 row affected (0.002 sec)
|
|
root@127.0.0.1 [2]> USE test;
|
Database changed
|
root@127.0.0.1 [3]> CREATE TABLE author (name VARCHAR(32) NOT NULL);
|
Query OK, 0 rows affected (0.012 sec)
|
|
root@127.0.0.1 [4]> INSERT INTO author (name) VALUES ('Charles Dickens'), ('Roald Dahl');
|
Query OK, 2 rows affected (0.006 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
root@127.0.0.1 [5]> SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC;
|
+-----------------+------------------+
|
| name | surname_first |
|
+-----------------+------------------+
|
| Roald Dahl | Dahl, Roald |
|
| Charles Dickens | Dickens, Charles |
|
+-----------------+------------------+
|
2 rows in set (0.002 sec)
|
|
root@127.0.0.1 [6]> SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC LIMIT 1;
|
+------------+---------------+
|
| name | surname_first |
|
+------------+---------------+
|
| Roald Dahl | Dahl, Roald |
|
+------------+---------------+
|
1 row in set (0.002 sec)
|
|
root@127.0.0.1 [7]> SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC LIMIT 1 OFFSET 1;
|
+-----------------+------------------+
|
| name | surname_first |
|
+-----------------+------------------+
|
| Charles Dickens | Dickens, Charles |
|
+-----------------+------------------+
|
1 row in set (0.003 sec)
|
On MariaDB 10.5, using LIMIT/OFFSET reverses the order
$ mysql -h 127.0.0.1 -P 3306 -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 3
|
Server version: 10.5.2-MariaDB-1:10.5.2+maria~bionic mariadb.org binary distribution
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
root@127.0.0.1 [1]> CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;
|
Query OK, 1 row affected (0.002 sec)
|
|
root@127.0.0.1 [2]> USE test;
|
Database changed
|
root@127.0.0.1 [3]> CREATE TABLE author (name VARCHAR(32) NOT NULL);
|
Query OK, 0 rows affected (0.009 sec)
|
|
root@127.0.0.1 [4]> INSERT INTO author (name) VALUES ('Charles Dickens'), ('Roald Dahl');
|
Query OK, 2 rows affected (0.007 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
root@127.0.0.1 [5]> SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC;
|
+-----------------+------------------+
|
| name | surname_first |
|
+-----------------+------------------+
|
| Roald Dahl | Dahl, Roald |
|
| Charles Dickens | Dickens, Charles |
|
+-----------------+------------------+
|
2 rows in set (0.003 sec)
|
|
root@127.0.0.1 [6]> SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC LIMIT 1;
|
+-----------------+------------------+
|
| name | surname_first |
|
+-----------------+------------------+
|
| Charles Dickens | Dickens, Charles |
|
+-----------------+------------------+
|
1 row in set (0.002 sec)
|
|
root@127.0.0.1 [7]> SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM author ORDER BY surname_first ASC LIMIT 1 OFFSET 1;
|
+------------+---------------+
|
| name | surname_first |
|
+------------+---------------+
|
| Roald Dahl | Dahl, Roald |
|
+------------+---------------+
|
1 row in set (0.002 sec)
|
|