[MDEV-28217] Incorrect Join Execution When Controlling Join Buffer Size Created: 2022-04-02  Updated: 2023-06-07  Resolved: 2023-05-05

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.8.2, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5

Type: Bug Priority: Critical
Reporter: Xiu Tang Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: 11.0-sel


 Description    

CREATE TABLE `t1` (
 
  `c0` int(10) unsigned NOT NULL,
 
  PRIMARY KEY (`c0`),
 
  KEY `ic3` (`c0`) USING BTREE
 
);
 
INSERT INTO `t1` VALUES (1332945389);
 
 
 
CREATE TABLE `t2` (
 
  `c0` int(10) unsigned NOT NULL,
 
  PRIMARY KEY (`c0`)
 
);
 
INSERT INTO `t2` VALUES (1180244875), (1951338178);
 
 
 
mysql> SET SESSION join_buffer_size = 5250229460064350213;
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
mysql> SET SESSION join_cache_level = 4;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> SELECT t2.c0 FROM t2  LEFT OUTER JOIN t1 ON t1.c0 = t2.c0  WHERE t1.c0;
 
Empty set (0.00 sec)
 
 
 
mysql> SET optimizer_switch='optimize_join_buffer_size=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> SELECT t2.c0 FROM t2  LEFT OUTER JOIN t1 ON t1.c0 = t2.c0  WHERE t1.c0;
 
+------------+
 
| c0         |
 
+------------+
 
| 1180244875 |
 
| 1951338178 |
 
+------------+
 
2 rows in set (0.00 sec)



 Comments   
Comment by Alice Sherepa [ 2022-04-07 ]

Thanks! I repeated on 10.2 53b580a91c12e92726-10.8 as described:

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb;
 
INSERT INTO t1 VALUES (1332945389);
 
 
CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb;
 
INSERT INTO t2 VALUES (1180244875), (1951338178);
 
 
SET SESSION join_buffer_size = 5250229460064350213;
 
SET SESSION join_cache_level = 4;
 
 
 
SET optimizer_switch='optimize_join_buffer_size=on';
 
SELECT t2.i FROM t2  LEFT  JOIN t1 ON t1.i = t2.i  WHERE t1.i;
 
 
 
SET optimizer_switch='optimize_join_buffer_size=off';
 
SELECT t2.i FROM t2  LEFT  JOIN t1 ON t1.i = t2.i  WHERE t1.i;

Comment by Weijun Huang [ 2022-12-16 ]

I checked the bug and found it still exists in 10.11.

Comment by Sergei Petrunia [ 2023-01-09 ]

Reproducible for me on latest 10.11:

commit 70be59913c90e93fe5136d6f6df03c4254aa515d (HEAD -> 10.11, origin/HEAD, origin/10.11)
 
Author: Otto Kekäläinen <otto@kekalainen.net>
 
Date:   Sat Nov 26 18:19:35 2022 -0800

and on latest 11.0:

commit b075191ba8598af6aff5549e6e19f6255aef258a
 
Author: Rex <rex.johnston@mariadb.com>
 
Date:   Fri Jan 6 15:03:54 2023 +1200
 
 
 
    MDEV-30353 Debian additions version fix

Comment by Michael Widenius [ 2023-05-03 ]

The problem was that join_buffer_size conflicted with
join_buffer_space_limit, which caused the query to be run without
join buffer. However this caused wrong results as the code assumed
that the hash+join buffer would eliminate all rows in the ON condition.

Fixed by not using join_buffer_space_limit when
optimize_join_buffer_size=off. This matches the documentation at
https://mariadb.com/kb/en/block-based-join-algorithms

Comment by Michael Widenius [ 2023-05-05 ]

Pushed to 10.4 main tree

Generated at Thu Feb 08 09:59:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.