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

Wrong Results with Dependent Subquery

    Details

      Description

      Data Set

      create table t0 (a int);
      insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t1 (a int, b int);
      insert into t1 select a,a from t0 where a <5; # 5 rows
      create table t2 as select * from t1 ; # 5 rows
      create table t3(a int, b int, c int);
      insert into t3 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; # 100 rows
      create table t4(a int, b int, c int);
      insert into t4 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; # 100 rows
      

      The query is:

      SELECT * FROM t1,t2,t3
        WHERE t1.b=t2.b and
         EXISTS (select 1 from t4 where t4.b=t1.b group by t4.c having t3.b=max(t4.a))
      ORDER BY t2.a desc,t1.a desc;
      

      When I run the query agains MariaDB I get an empty result

      MariaDB [test]> SELECT * FROM t1,t2,t3
          -> WHERE t1.b=t2.b and
          -> EXISTS (select 1 from t4 where t4.b=t1.b group by t4.c having t3.b=max(t4.a))
          -> ORDER BY t2.a desc,t1.a desc;
      Empty set (0.009 sec)
      

      With MYSQL we get the correct results

      mysql> SELECT * FROM t1,t2,t3
          -> WHERE t1.b=t2.b and
          -> EXISTS (select 1 from t4 where t4.b=t1.b group by t4.c having t3.b=max(t4.a))
          -> ORDER BY t2.a desc,t1.a desc;
      +------+------+------+------+------+------+------+
      | a    | b    | a    | b    | a    | b    | c    |
      +------+------+------+------+------+------+------+
      |    4 |    4 |    4 |    4 |    4 |    4 |    4 |
      |    3 |    3 |    3 |    3 |    3 |    3 |    3 |
      |    2 |    2 |    2 |    2 |    2 |    2 |    2 |
      |    1 |    1 |    1 |    1 |    1 |    1 |    1 |
      |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
      +------+------+------+------+------+------+------+
      5 rows in set (0.08 sec)
      

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: