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

ROW_NUMBER () OVER () with empty OVER () clause seems to perform a sort

Details

    Description

      Try this query:

      SELECT a, ROW_NUMBER() OVER () 
      FROM (
      		 
        SELECT 'a' a UNION ALL 
        SELECT 'c' a UNION ALL 
        SELECT 'b' a UNION ALL 
        SELECT 'b' a UNION ALL 
        SELECT 'c' a UNION ALL 
        SELECT 'b' a UNION ALL 
        SELECT 'b' a
      		 
      ) t;

      It seems to perform a sort, resulting in:

      a ROW_NUMBER() OVER ()
      a 1
      b 2
      b 3
      b 4
      b 5
      c 6
      c 7

      On the other hand, when adding a dummy ORDER BY clause, then the lexical order is preserved:

      SELECT a, ROW_NUMBER() OVER (ORDER BY NULL) 
      FROM (
      		 
        SELECT 'a' a UNION ALL 
        SELECT 'c' a UNION ALL 
        SELECT 'b' a UNION ALL 
        SELECT 'b' a UNION ALL 
        SELECT 'c' a UNION ALL 
        SELECT 'b' a UNION ALL 
        SELECT 'b' a
      		 
      ) t;

      Resulting in:

      a ROW_NUMBER() OVER (ORDER BY NULL)
      a 1
      c 2
      b 3
      b 4
      c 5
      b 6
      b 7

      I would expect both executions to not touch the ordering of the derived table contents, from a performance perspective. I'm aware that both results are technically correct. I just wanted to make sure there's no performance penalty.

      Attachments

        Activity

          People

            Unassigned Unassigned
            lukas.eder Lukas Eder
            Votes:
            1 Vote for this issue
            Watchers:
            2 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.