[MDEV-32106] Unexpected Results by outer_join_with_cache Created: 2023-09-06  Updated: 2023-09-06

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.31, 11.1.2
Fix Version/s: 10.4, 11.1

Type: Bug Priority: Major
Reporter: Jinsheng Ba Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

docker run -it -p 3307:3306 -e MARIADB_ROOT_PASSWORD=root mariadb:latest


 Description   

The same query returns different results under different outer_join_with_cache:

CREATE TABLE t0(c0 CHAR);
 
CREATE TABLE t2(c0 CHAR);
 
INSERT INTO t0 VALUES ('a'), (''), ('');
 
INSERT INTO t2 VALUES ('b'), ('');
 
 
 
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
 
SELECT t2.c0 FROM t2 LEFT JOIN t0 ON (4 RLIKE t0.c0);  -- {b}, { }
 
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 
SELECT t2.c0 FROM t2 LEFT JOIN t0 ON (4 RLIKE t0.c0);  -- {b}, { }, { }



 Comments   
Comment by Daniel Black [ 2023-09-06 ]

10.4-922db0642b6321ece41adb3232c1616812143573

 
MariaDB [test]> SET STATEMENT optimizer_switch = 'outer_join_with_cache=off' FOR SELECT t2.c0 FROM t2 LEFT JOIN t0 ON (4 RLIKE t0.c0);
 
+------+
 
| c0   |
 
+------+
 
| b    |
 
|      |
 
|      |
 
+------+
 
3 rows in set (0.001 sec)
 
 
 
MariaDB [test]> SET STATEMENT optimizer_switch = 'outer_join_with_cache=on' FOR SELECT t2.c0 FROM t2 LEFT JOIN t0 ON (4 RLIKE t0.c0);
 
+------+
 
| c0   |
 
+------+
 
| b    |
 
|      |
 
+------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------------------------------+
 
| version()                               |
 
+-----------------------------------------+
 
| 10.4.31-MariaDB-1:10.4.31+maria~ubu2004 |
 
+-----------------------------------------+
 
1 row in set (0.000 sec)

Generated at Thu Feb 08 10:28:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.