[MDEV-32105] Unexpected Results by join_cache_hashed Created: 2023-09-06  Updated: 2023-09-12

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.31, 10.6.15, 10.11.5, 11.1.2, 11.2.2
Fix Version/s: 10.4, 10.6, 10.11

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

Attachments: File test.sql    

 Description   

The same query returns different results under different configurations of join_cache_hashed.

CREATE TABLE t0(c0 CHAR(100) PRIMARY KEY);
 
CREATE TABLE t1(c0 CHAR(100) PRIMARY KEY);
 
 
 
INSERT INTO t1 VALUES ('');
 
SET SESSION join_cache_level = 5;
 
INSERT INTO t0 VALUES ('');
 
SET SESSION sql_log_off = ON;
 
SET SESSION autocommit = 1;
 
INSERT INTO t1 VALUES ('\nd');
 
 
 
 
 
SET SESSION optimizer_switch = 'join_cache_hashed=off';
 
SELECT t1.c0 FROM t0 NATURAL JOIN t1 WHERE ('\n' RLIKE t1.c0); -- {NULL}
 
SET SESSION optimizer_switch = 'join_cache_hashed=on';
 
SELECT t1.c0 FROM t0 NATURAL JOIN t1 WHERE ('\n' RLIKE t1.c0); -- {}



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

Are you sure, I'm getting empty string (not NULL) on 10.4, 11.1 and 11.2.

I am using the latest version from https://quay.io/repository/mariadb-foundation/mariadb-devel?tab=tags.

quay.io/mariadb-foundation/mariadb-devel:11.1

 
MariaDB [test]>  SET STATEMENT optimizer_switch = 'join_cache_hashed=on' FOR SELECT t1.c0 IS NULL, HEX(t1.c0) FROM t0 NATURAL JOIN t1 WHERE ('\n' RLIKE t1.c0);
 
+---------------+------------+
 
| t1.c0 IS NULL | HEX(t1.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.010 sec)
 
 
 
MariaDB [test]>  SET STATEMENT optimizer_switch = 'join_cache_hashed=off' FOR SELECT t1.c0 IS NULL, HEX(t1.c0) FROM t0 NATURAL JOIN t1 WHERE ('\n' RLIKE t1.c0);
 
+---------------+------------+
 
| t1.c0 IS NULL | HEX(t1.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select version(),  @@version_source_revision;
 
+---------------------------------------+------------------------------------------+
 
| version()                             | @@version_source_revision                |
 
+---------------------------------------+------------------------------------------+
 
| 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 | 9bc25d98209df6810f7a7d5e7dd3ae677a313ab5 |
 
+---------------------------------------+------------------------------------------+
 
1 row in set (0.000 sec)

Comment by Jinsheng Ba [ 2023-09-11 ]

Sorry, maybe I missed some statements. Let me check.

Comment by Jinsheng Ba [ 2023-09-11 ] 

CREATE TABLE t0(c0 VARCHAR(100), PRIMARY KEY(c0));
 
CREATE TABLE t1(c0 VARCHAR(100));
 
 
 
INSERT INTO t0 VALUES ('');
 
INSERT INTO t0 VALUES ('\ta');
 
INSERT INTO t1 VALUES ('');
 
 
 
 
 
mysql> SELECT * FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
Empty set (0.00 sec)
 
 
 
mysql> SET STATEMENT optimizer_switch = 'join_cache_hashed=off' FOR SELECT * FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+----+
 
| c0 |
 
+----+
 
|    |
 
+----+
 
1 row in set (0.00 sec)
 
 
 
mysql> SELECT version();
 
+---------------------------------------+
 
| version()                             |
 
+---------------------------------------+
 
| 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 |
 
+---------------------------------------+
 
1 row in set (0.00 sec)

Comment by Daniel Black [ 2023-09-11 ]

Sorry, still not seeing it:

lots of versions

 
$ podman pull quay.io/mariadb-foundation/mariadb-devel:verylatest
 
Trying to pull quay.io/mariadb-foundation/mariadb-devel:verylatest...
 
Getting image source signatures
 
Copying blob 5c90741f7100 done  
Copying blob 0a9d6efb0863 done  
Copying blob 9733fa59b777 done  
Copying blob a86c53e5e1ab done  
Copying blob 2c0f97d421a7 done  
Copying blob 3e47abd04232 done  
Copying blob 70ff22900875 done  
Copying blob ffac7bd85949 done  
Copying blob 1030e2d71ad8 done  
Copying config 402e8d5792 done  
Writing manifest to image destination
 
402e8d579278b64fe662cdc76d3766f52f96c2d36b872bc6ca438c600e83b989
 
 
 
~/repos/build-mariadb-server-11.1 
$ podman run --rm --env MARIADB_DATABASE=test --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 --name mverylatest -d quay.io/mariadb-foundation/mariadb-devel:verylatest
 
011bf37f6e73212efd2563508afda4c9da8cbeb9f6b88d1d5c0f4bc546be42b6
 
 
 
~/repos/build-mariadb-server-11.1 
$ podman exec -ti mverylatest mariadb test
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> CREATE TABLE t0(c0 VARCHAR(100), PRIMARY KEY(c0));
 
Query OK, 0 rows affected (0.007 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> CREATE TABLE t1(c0 VARCHAR(100));
 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]>  
MariaDB [test]> 
MariaDB [test]> INSERT INTO t0 VALUES ('');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> INSERT INTO t0 VALUES ('\ta');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> INSERT INTO t1 VALUES ('');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]>  SELECT * FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+----+
 
| c0 |
 
+----+
 
|    |
 
+----+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> SET STATEMENT optimizer_switch = 'join_cache_hashed=off' FOR SELECT t0.c0 IS NULL, HEX(t0.c0) FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+---------------+------------+
 
| t0.c0 IS NULL | HEX(t0.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> SET STATEMENT optimizer_switch = 'join_cache_hashed=on' FOR SELECT t0.c0 IS NULL, HEX(t0.c0) FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+---------------+------------+
 
| t0.c0 IS NULL | HEX(t0.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select version(),  @@version_source_revision;
 
+---------------------------------------+------------------------------------------+
 
| version()                             | @@version_source_revision                |
 
+---------------------------------------+------------------------------------------+
 
| 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 | 9b9067fcc0d5f0e663fe5723209263e944553138 |
 
+---------------------------------------+------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> ^DBye
 
 
 
~/repos/build-mariadb-server-11.1 
$ podman run --rm --env MARIADB_DATABASE=test --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 --name m11.1 -d mariadb:11.1
 
Resolved "mariadb" as an alias (/home/dan/.cache/containers/short-name-aliases.conf)
 
Trying to pull docker.io/library/mariadb:11.1...
 
Getting image source signatures
 
Copying blob a3e4bee69a58 done  
Copying blob 44ba2882f8eb done  
Copying blob ef2696fb09d6 done  
Copying blob 08b8223d0cb6 done  
Copying blob 6ae32c298a0d done  
Copying blob 5dc97cb97b44 done  
Copying blob b29c582204c9 done  
Copying blob f05405b8aaed done  
Copying config 871a9153c1 done  
Writing manifest to image destination
 
b7c21ea16fb19aab4b0c03744bd290447b0a9ccca3a0978b5b4a1c01e409016c
 
 
 
~/repos/build-mariadb-server-11.1 
$ podman exec -ti m11.1 mariadb test
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> CREATE TABLE t0(c0 VARCHAR(100), PRIMARY KEY(c0));
 
Query OK, 0 rows affected (0.019 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> CREATE TABLE t1(c0 VARCHAR(100));
 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]>  
MariaDB [test]> 
MariaDB [test]> INSERT INTO t0 VALUES ('');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> INSERT INTO t0 VALUES ('\ta');
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> INSERT INTO t1 VALUES ('');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]>   SELECT * FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+----+
 
| c0 |
 
+----+
 
|    |
 
+----+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> SET STATEMENT optimizer_switch = 'join_cache_hashed=on' FOR SELECT t0.c0 IS NULL, HEX(t0.c0) FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+---------------+------------+
 
| t0.c0 IS NULL | HEX(t0.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]>  SET STATEMENT optimizer_switch = 'join_cache_hashed=on' FOR SELECT t0.c0 IS NULL, HEX(t0.c0) FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+---------------+------------+
 
| t0.c0 IS NULL | HEX(t0.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]>  SET STATEMENT optimizer_switch = 'join_cache_hashed=off' FOR SELECT t0.c0 IS NULL, HEX(t0.c0) FROM t0 NATURAL JOIN t1
 
WHERE ('a' RLIKE t0.c0);
 
+---------------+------------+
 
| t0.c0 IS NULL | HEX(t0.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select version(),  @@version_source_revision;
 
+---------------------------------------+------------------------------------------+
 
| version()                             | @@version_source_revision                |
 
+---------------------------------------+------------------------------------------+
 
| 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 | 9bc25d98209df6810f7a7d5e7dd3ae677a313ab5 |
 
+---------------------------------------+------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]>  Ctrl-C -- exit!
 
Aborted
 
 
 
~/repos/build-mariadb-server-11.1 
$ podman run --rm --env MARIADB_DATABASE=test --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 --name mq11.1 -d quay.io/mariadb-foundation/mariadb-devel:11.1
 
522f4a40a1bd200639c08226b080f0eeb90363c9308dd3138d2e57ac254dcc34
 
 
 
~/repos/build-mariadb-server-11.1 
$ podman exec -ti mq11.1 mariadb test
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> CREATE TABLE t0(c0 VARCHAR(100), PRIMARY KEY(c0));
 
Query OK, 0 rows affected (0.016 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> CREATE TABLE t1(c0 VARCHAR(100));
 
Query OK, 0 rows affected (0.006 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]>  
MariaDB [test]> 
MariaDB [test]> INSERT INTO t0 VALUES ('');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> INSERT INTO t0 VALUES ('\ta');
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> INSERT INTO t1 VALUES ('');
 
Query OK, 1 row affected (0.009 sec)
 
 
 
MariaDB [test]> SELECT * FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+----+
 
| c0 |
 
+----+
 
|    |
 
+----+
 
1 row in set (0.009 sec)
 
 
 
MariaDB [test]> SET STATEMENT optimizer_switch = 'join_cache_hashed=on' FOR SELECT t0.c0 IS NULL, HEX(t0.c0) FROM t0 NATURAL JOIN t1 WHERE ('a' RLIKE t0.c0);
 
+---------------+------------+
 
| t0.c0 IS NULL | HEX(t0.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]>  SET STATEMENT optimizer_switch = 'join_cache_hashed=off' FOR SELECT t0.c0 IS NULL, HEX(t0.c0) FROM t0 NATURAL JOIN t1
 
    -> WHERE ('a' RLIKE t0.c0);
 
+---------------+------------+
 
| t0.c0 IS NULL | HEX(t0.c0) |
 
+---------------+------------+
 
|             0 |            |
 
+---------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]>   select version(),  @@version_source_revision;
 
+---------------------------------------+------------------------------------------+
 
| version()                             | @@version_source_revision                |
 
+---------------------------------------+------------------------------------------+
 
| 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 | 9bc25d98209df6810f7a7d5e7dd3ae677a313ab5 |
 
+---------------------------------------+------------------------------------------+
 
1 row in set (0.000 sec)

Comment by Jinsheng Ba [ 2023-09-11 ]

It is strange. You can try this way so that we can exclude any environmental issues:

docker pull mariadb:latest
 
docker run -it -p 3307:3306 -e MARIADB_ROOT_PASSWORD=root mariadb:latest
 
wget https://gist.githubusercontent.com/bajinsheng/d1a45080aa1be2b33ddb38d1a6312171/raw/26196c46aeffe3d6bb5fdd1818f19969d9506df8/gistfile1.txt -O test.sql
 
mysql -h 127.0.0.1 -P 3307 -uroot -proot -f < test.sql

I can reproduce it in this way.

Comment by Daniel Black [ 2023-09-11 ]

Thank you. Yes. Thanks for your persistance. Small changes for display purposes:

$ client/mariadb -h 127.0.0.1 -P 3307 -uroot -proot -f < test.sql
 
version()	@@version_source_revision
 
10.4.31-MariaDB-1:10.4.31+maria~ubu2004	2aea9387497cecb5668ef605b8f80886f9de812c
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=off
 
---------------- join_cache_hashed=off
 
t3.c0 IS NULL	HEX(t3.c0)	t2.c0 IS NULL	HEX(t2.c0)
 
0		0	
0	6B6D57494C35	0	
----------------
 
----------------
 
 
 
$ client/mariadb -h 127.0.0.1 -P 3307 -uroot -proot -f < test.sql
 
version()	@@version_source_revision
 
10.6.15-MariaDB-1:10.6.15+maria~ubu2004	0d16eb35bc981023ce2f4912e8ecde68ca381f4e
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=off
 
---------------- join_cache_hashed=off
 
t3.c0 IS NULL	HEX(t3.c0)	t2.c0 IS NULL	HEX(t2.c0)
 
0		0	
0	6B6D57494C35	0	
----------------
 
----------------
 
 
 
$ client/mariadb -h 127.0.0.1 -P 3307 -uroot -proot -f < test.sql
 
version()	@@version_source_revision
 
10.11.5-MariaDB-1:10.11.5+maria~ubu2204	7875294b6b74b53dd3aaa723e6cc103d2bb47b2c
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=off
 
---------------- join_cache_hashed=off
 
t3.c0 IS NULL	HEX(t3.c0)	t2.c0 IS NULL	HEX(t2.c0)
 
0		0	
0	6B6D57494C35	0	
----------------
 
----------------
 
 
 
$ client/mariadb -h 127.0.0.1 -P 3307 -uroot -proot -f < test.sql
 
version()	@@version_source_revision
 
11.2.2-MariaDB-1:11.2.2+maria~ubu2204	9b9067fcc0d5f0e663fe5723209263e944553138
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=on
 
---------------- join_cache_hashed=off
 
---------------- join_cache_hashed=off
 
t3.c0 IS NULL	HEX(t3.c0)	t2.c0 IS NULL	HEX(t2.c0)
 
0		0	
0	6B6D57494C35	0	
----------------
 
----------------

Comment by Jinsheng Ba [ 2023-09-12 ]

Thanks for your prompt reply!

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