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

Wrong result set from query with in subquery that uses window function

    Details

      Description

      The following test case demonstrates the problem:

      create table t1 (a int, b int, c int, d int);
      create table t2 (e int, f int, g int);
      insert into t1 values
      (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
      (4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2),
      (5,5,65,1), (2,3,70,3),  (1,4,35,3), (2,3,25,3);
      insert into t2 values
      (1,2,38), (2,3,15), (1,3,40), (1,4,35),
      (2,2,70), (3,4,23), (5,5,12), (5,4,17),
      (3,3,17), (4,2,24), (2,5,25), (5,1,65);
       
      select * from t1 where (a>1) and
        (a) in
        (
          select sum(e) over (partition by f)
          from t2
          where e<5
        )
      ;
      

      The output for the above query is:

      MariaDB [test]> select * from t1 where (a>1) and
          ->   (a) in
          ->   (
          ->     select sum(e) over (partition by f)
          ->     from t2
          ->     where e<5
          ->   )
          -> ;
      Empty set (0.00 sec)
      

      This not correct as we have:

      ariaDB [test]>     select sum(e) over (partition by f)
          ->     from t2
          ->     where e<5
          -> ;
      +------------------------------+
      | sum(e) over (partition by f) |
      +------------------------------+
      | 7                            |
      | 6                            |
      | 6                            |
      | 4                            |
      | 7                            |
      | 4                            |
      | 6                            |
      | 7                            |
      | 2                            |
      +------------------------------+
       
      MariaDB [test]> select a from t1 where (a>1);
      +------+
      | a    |
      +------+
      |    2 |
      |    2 |
      |    4 |
      |    3 |
      |    3 |
      |    5 |
      |    2 |
      |    2 |
      +------+
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                igor Igor Babaev
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: