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

LP:889750 - Semi-join neglects the semijoin_with_cache flag when firstmatch=off

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      When the 'firstmatch' flag is off a semi-join may use join cache even in the cases with 'semijoin_with_cache is set to 'off'.
      This this reproducible in MariaDB-5.3 with the following test case:

      create table t1 (a int);
      insert into t1 values (7), (1), (5), (3);
      create table t2 (a int);
      insert into t2 values (4), (1), (8), (3), (9), (2);

      set @tmp_otimizer_swictch= @@optimizer_switch;
      set optimizer_switch='semijoin=on';
      set optimizer_switch='firstmatch=off';

      set optimizer_switch='semijoin_with_cache=on';
      explain
      select * from t1 where t1.a in (select t2.a from t2);
      select * from t1 where t1.a in (select t2.a from t2);

      set optimizer_switch='semijoin_with_cache=off';
      explain
      select * from t1 where t1.a in (select t2.a from t2);
      select * from t1 where t1.a in (select t2.a from t2);

      set optimizer_switch= @tmp_otimizer_swictch;

      drop table t1,t2;

      One can easily see this problem:

      MariaDB [test]> set optimizer_switch='firstmatch=off';
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]>
      MariaDB [test]> set optimizer_switch='semijoin_with_cache=on';
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> explain
      -> select * from t1 where t1.a in (select t2.a from t2);
      ----------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ----------------------------------------------------------------------------------------------------------------------+

      1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary
      1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join)

      ----------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)

      MariaDB [test]> set optimizer_switch='semijoin_with_cache=off';
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> explain
      -> select * from t1 where t1.a in (select t2.a from t2);
      ----------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ----------------------------------------------------------------------------------------------------------------------+

      1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary
      1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join)

      ----------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            ratzpo Rasmus Johansson added a comment -

            Launchpad bug id: 889750

            Show
            ratzpo Rasmus Johansson added a comment - Launchpad bug id: 889750

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: