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

[Philip Stoev]

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;