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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2.14, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.5, 10.6

    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

              psergei Sergei Petrunia
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.