[MDEV-22807] make NULLS LAST default when sql_mode=ORACLE Created: 2020-06-05  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Nilnandan Joshi Assignee: Ralf Gebhardt
Resolution: Unresolved Votes: 0
Labels: Compatibility, Oracle


 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)



 Comments   
Comment by Alexander Barkov [ 2020-06-05 ]

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

Comment by Julien Fritsch [ 2020-06-05 ]

niljoshi nice to have feature request or is there any customer issue request linked to it?

Comment by Sergei Golubchik [ 2020-07-08 ]

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

Generated at Thu Feb 08 09:17:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.