[MDEV-28214] Incorrect Join Execution When Controlling BKA and BKAH Join Algorithms Created: 2022-04-02  Updated: 2023-04-27

Status: Confirmed
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
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Xiu Tang Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description   

CREATE TABLE `t1` (
`c0` bigint(20) DEFAULT NULL
);
INSERT INTO `t1` VALUES (1777060672), (-1998952811), (-1723924625), (-191675722), (-998119991), (2062393966);

CREATE TABLE `t2` (
`c0` double NOT NULL,
`c1` varchar(100) NOT NULL,
PRIMARY KEY (`c1`)
);
INSERT INTO `t2` VALUES (-926353679,'');

CREATE TABLE `t3` (
`c0` mediumint(9) NOT NULL,
`c1` tinyint(1) NOT NULL
);

CREATE TABLE `t4` (
`c0` double NOT NULL,
`c1` varchar(100) NOT NULL,
PRIMARY KEY (`c1`)
);
INSERT INTO `t4` VALUES (507649242,'{P6\'Kd'), (1245950477,' '), (-2053299370,'5fL');

mysql> SELECT t3.c0 FROM t3 RIGHT OUTER JOIN t4 ON t3.c1 = t4.c1 JOIN t2 ON t2.c1 = t4.c1 CROSS JOIN t1;
------

c0

------

NULL
NULL
NULL
NULL
NULL
NULL

------
6 rows in set, 1 warning (0.00 sec)

mysql> SET optimizer_switch='join_cache_bka=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t3.c0 FROM t3 RIGHT OUTER JOIN t4 ON t3.c1 = t4.c1 JOIN t2 ON t2.c1 = t4.c1 CROSS JOIN t1;
Empty set (0.00 sec)



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

Thank you for the report!
I repeated on 10.2 ( 53b580a91c12e927) -10.8 - when setting join_cache_level > 2;

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