Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
In MySQL/MariaDB NULL values are considered lower than any non-NULL
value, therefore, NULL values appear first when the order is ASC
(ascending), and ordered last when the order is DESC (descending).
MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name;
|
+---------+
|
| name |
|
+---------+
|
| NULL |
|
| Berlin |
|
| Leipzig |
|
+---------+
|
3 rows in set (0.001 sec)
|
Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first?
Though for now there is some workaround like using order by with null.
MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc;
|
+---------+
|
| name |
|
+---------+
|
| Berlin |
|
| Leipzig |
|
| NULL |
|
+---------+
|
3 rows in set (0.001 sec)
|
|
Attachments
Issue Links
- is duplicated by
-
MDEV-33663 Different sort order regarding NULLs
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
In MySQL/MariaDB NULL values are considered lower than any non-NULL
value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +---------+ | name | +---------+ | NULL | | Berlin | | Leipzig | +---------+ 3 rows in set (0.001 sec) {code} Can we implement something like NULL LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +---------+ | name | +---------+ | Berlin | | Leipzig | | NULL | +---------+ 3 rows in set (0.001 sec) {code} |
In MySQL/MariaDB NULL values are considered lower than any non-NULL
value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +---------+ | name | +---------+ | NULL | | Berlin | | Leipzig | +---------+ 3 rows in set (0.001 sec) {code} Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +---------+ | name | +---------+ | Berlin | | Leipzig | | NULL | +---------+ 3 rows in set (0.001 sec) {code} |
| Key | MENT-822 | MDEV-22807 |
| Affects Version/s | 10.3 [ 23605 ] | |
| Issue Type | New Feature [ 2 ] | Task [ 3 ] |
| Project | MariaDB Enterprise [ 11500 ] | MariaDB Server [ 10000 ] |
| Labels | Compatibility |
| Assignee | Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] |
niljoshi nice to have feature request or is there any customer issue request linked to it?
This is just a change in filesort, with NULLS LAST the sorting won't match index order so the optimizer won't be able to use index to resolve the filesort. No changes in storage engines will be needed.
As an additional optimization it is possible to use indexes in this case. Instead of index_first and then many times index_next it should start from the first non-null value, iterate till the end, and then start from the beginning to iterate all NULLs. Still doesn't need any changes in engines, all indexes will always stay NULLS FIRST
| Labels | Compatibility | Compatibility Oracle |
| Workflow | MariaDB v3 [ 109614 ] | MariaDB v4 [ 131302 ] |
| Description |
In MySQL/MariaDB NULL values are considered lower than any non-NULL
value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +---------+ | name | +---------+ | NULL | | Berlin | | Leipzig | +---------+ 3 rows in set (0.001 sec) {code} Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +---------+ | name | +---------+ | Berlin | | Leipzig | | NULL | +---------+ 3 rows in set (0.001 sec) {code} |
In MySQL/MariaDB NULL values are considered lower than any non\-NULL value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). \{code\} MariaDB \[(none)\]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | NULL | | Berlin | | Leipzig | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) \{code\} Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. \{code\} MariaDB \[(none)\]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | Berlin | | Leipzig | | NULL | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) \{code\} |
| Description |
In MySQL/MariaDB NULL values are considered lower than any non\-NULL value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). \{code\} MariaDB \[(none)\]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | NULL | | Berlin | | Leipzig | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) \{code\} Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. \{code\} MariaDB \[(none)\]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | Berlin | | Leipzig | | NULL | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) \{code\} |
In MySQL/MariaDB NULL values are considered lower than any non\-NULL
value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | NULL | | Berlin | | Leipzig | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) {code} Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | Berlin | | Leipzig | | NULL | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) {code} |
| Description |
In MySQL/MariaDB NULL values are considered lower than any non\-NULL
value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | NULL | | Berlin | | Leipzig | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) {code} Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +\-\-\-\-\-\-\-\-\-+ | name | +\-\-\-\-\-\-\-\-\-+ | Berlin | | Leipzig | | NULL | +\-\-\-\-\-\-\-\-\-+ 3 rows in set (0.001 sec) {code} |
In MySQL/MariaDB NULL values are considered lower than any non\-NULL
value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending). {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name; +---------+ | name | +---------+ | NULL | | Berlin | | Leipzig | +---------+ 3 rows in set (0.001 sec) {code} Can we implement something like NULLS LAST so even if with order by esc, NULLs come last and not first? Though for now there is some workaround like using order by with null. {code} MariaDB [(none)]> SELECT name FROM (SELECT 'Leipzig' AS name FROM dual UNION ALL SELECT NULL FROM dual UNION ALL SELECT 'Berlin' FROM dual) t ORDER BY name is null, name asc; +---------+ | name | +---------+ | Berlin | | Leipzig | | NULL | +---------+ 3 rows in set (0.001 sec) {code} |
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |
| Link |
This issue is duplicated by |
| Assignee | Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] |
| Epic Link | MDEV-10872 [ 58182 ] |
| Zendesk Related Tickets | 175899 |
This is the relevant SQL standard grammar:
<sort specification list> ::= <sort specification> [ { <comma> <sort specification> }... ]<sort specification> ::= <sort key> [ <ordering specification> ] [ <null ordering> ]<sort key> ::= <value expression> <ordering specification> ::= ASC | DESC<null ordering> ::= NULLS FIRST | NULLS LAST