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

Wrong query result when window function is used in a subquery with HAVING

    XMLWordPrintable

    Details

      Description

      The testcase comes from David Hall.

      Load the dataset: source mysql-test/include/dbt3_s001.inc

      Then run the two queries:

      select * 
      from region 
      where (r_regionkey+1) in (select min(n_regionkey) over() + 1 
                                from nation 
                                where r_regionkey=n_regionkey);
      

      select * 
      from region 
      where (r_regionkey+1) in (select min(n_regionkey) + 1 
                                from nation 
                                where r_regionkey=n_regionkey);
      

      and see that they produce different results:

      MariaDB [test]> select * 
          -> from region 
          -> where (r_regionkey+1) in (select min(n_regionkey) over() + 1 
          ->                           from nation 
          ->                           where r_regionkey=n_regionkey);
      +-------------+--------+-------------------------------------------------------------+
      | r_regionkey | r_name | r_comment                                                   |
      +-------------+--------+-------------------------------------------------------------+
      |           0 | AFRICA | special Tiresias about the furiously even dolphins are furi |
      +-------------+--------+-------------------------------------------------------------+
      

      MariaDB [test]> select * 
          -> from region 
          -> where (r_regionkey+1) in (select min(n_regionkey) + 1 
          ->                           from nation 
          ->                           where r_regionkey=n_regionkey);
      +-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
      | r_regionkey | r_name      | r_comment                                                                                                       |
      +-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
      |           0 | AFRICA      | special Tiresias about the furiously even dolphins are furi                                                     |
      |           1 | AMERICA     | even, ironic theodolites according to the bold platelets wa                                                     |
      |           2 | ASIA        | silent, bold requests sleep slyly across the quickly sly dependencies. furiously silent instructions alongside  |
      |           3 | EUROPE      | special, bold deposits haggle foxes. platelet                                                                   |
      |           4 | MIDDLE EAST | furiously unusual packages use carefully above the unusual, exp                                                 |
      +-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: