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

LP:674423 - More rows returned with outer_join_with_cache=on and join_cache_level=6 in maria-5.3-mwl128

    Details

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

      Description

      The following query

      SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;

      returns one extra NULL as compared to plan without a join buffer.

      explain:

      mysql> EXPLAIN SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9\G

                                                          • 1. row ***************************
                                                            id: 1
                                                            select_type: SIMPLE
                                                            table: t2
                                                            type: ALL
                                                            possible_keys: NULL
                                                            key: NULL
                                                            key_len: NULL
                                                            ref: NULL
                                                            rows: 2
                                                            Extra:
                                                          • 2. row ***************************
                                                            id: 1
                                                            select_type: SIMPLE
                                                            table: t1
                                                            type: ALL
                                                            possible_keys: NULL
                                                            key: NULL
                                                            key_len: NULL
                                                            ref: NULL
                                                            rows: 1
                                                            Extra: Using where; Using join buffer (flat, BNL join)
                                                            2 rows in set (0.00 sec)

      Test case:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
      f7 int) ;
      INSERT INTO t1 VALUES ('9');

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 (
      pk int,
      f7 int) ;
      INSERT INTO t2 VALUES ('9',NULL),('1',NULL);

      SET optimizer_switch='outer_join_with_cache=on';
      SET SESSION join_cache_level = 6;
      SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;
      EXPLAIN SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;

      SET SESSION join_cache_level = 0;
      SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;
      EXPLAIN SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            ratzpo Rasmus Johansson added a comment -

            Launchpad bug id: 674423

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

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: