[MDEV-2920] LP:674423 - More rows returned with outer_join_with_cache=on and join_cache_level=6 in maria-5.3-mwl128 Created: 2010-11-12  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug674423.xml    

 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;



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 674423

Generated at Thu Feb 08 06:45:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.