Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22807

make NULLS LAST default when sql_mode=ORACLE

Details

    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

          Task - A task that needs to be done. MDEV-33663 Different sort order regarding NULLs

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            People

              Unassigned Unassigned
              niljoshi Nilnandan Joshi
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.