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

Query returns wrong result while using big_tables=1;

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.5
    • Fix Version/s: 10.5.9
    • Component/s: Optimizer
    • Labels:
      None

      Description

      test case is a part of the set_operation.test:

      create table t1 (a int, b int) engine=MyISAM;
      create table t2 (c int, d int) engine=MyISAM;
      create table t3 (e int, f int) engine=MyISAM;
      create table t4 (g int, h int) engine=MyISAM;
      insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3);
      insert into t2 values (2,2),(3,3),(5,5),(2,2),(2,2),(3,3);
      insert into t3 values (4,4),(2,2),(2,2),(1,1),(3,3);
      insert into t4 values (3,3),(3,3);
       
      let $q=
          select * from t1
          INTERSECT ALL
          select * from t2
          UNION ALL
          select * from t3
          EXCEPT ALL
          select * from t1
          UNION
          select * from t4
          EXCEPT
          select * from t3
          UNION ALL
          select * from t1;
       
      eval $q;
       
      set big_tables=1;
       
      eval $q;
      

      MariaDB [test1]> select * from t1
          ->     INTERSECT ALL
          ->     select * from t2
          ->     UNION ALL
          ->     select * from t3
          ->     EXCEPT ALL
          ->     select * from t1
          ->     UNION
          ->     select * from t4
          ->     EXCEPT
          ->     select * from t3
          ->     UNION ALL
          ->     select * from t1;
      +------+------+
      | a    | b    |
      +------+------+
      |    2 |    2 |
      |    2 |    2 |
      |    1 |    1 |
      |    3 |    3 |
      |    3 |    3 |
      +------+------+
      5 rows in set (0.009 sec)
       
      MariaDB [test1]> set big_tables=1;
      Query OK, 0 rows affected, 1 warning (0.001 sec)
       
      MariaDB [test1]>     select * from t1
          ->     INTERSECT ALL
          ->     select * from t2
          ->     UNION ALL
          ->     select * from t3
          ->     EXCEPT ALL
          ->     select * from t1
          ->     UNION
          ->     select * from t4
          ->     EXCEPT
          ->     select * from t3
          ->     UNION ALL
          ->     select * from t1;
      Empty set (0.013 sec)
       
      MariaDB [test1]>  select * from t1     INTERSECT ALL     select * from t2     UNION ALL     select * from t3     EXCEPT ALL     select * from t1     UNION     select * from t4     EXCEPT     select * from t3     UNION    (select * from t1);
      +------+------+
      | a    | b    |
      +------+------+
      |    2 |    2 |
      |    1 |    1 |
      |    3 |    3 |
      +------+------+
      3 rows in set (0.013 sec)
       
      MariaDB [test1]>  select * from t1     INTERSECT ALL     select * from t2     UNION ALL     select * from t3     EXCEPT ALL     select * from t1     UNION     select * from t4     EXCEPT     select * from t3     UNION  all  (select * from t1);
      Empty set (0.007 sec)
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: