[MDEV-32105] Unexpected Results by join_cache_hashed - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4.31, 10.6.15, 10.11.5, 11.1.2, 11.2.2
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Optimizer
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 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); -- {}

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

test.sql
1 kB
2023-09-11 21:38

Activity

Ascending order - Click to sort in descending order

Daniel Black added a comment - 2023-09-06 02:08

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)

Daniel Black added a comment - 2023-09-06 02:08 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)

Jinsheng Ba added a comment - 2023-09-11 00:55

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

Jinsheng Ba added a comment - 2023-09-11 00:55 Sorry, maybe I missed some statements. Let me check.

Jinsheng Ba added a comment - 2023-09-11 07:33

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)

Jinsheng Ba added a comment - 2023-09-11 07:33 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)

Daniel Black added a comment - 2023-09-11 08:08

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)

Daniel Black added a comment - 2023-09-11 08:08 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)

Jinsheng Ba added a comment - 2023-09-11 08:42 - edited

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.

Jinsheng Ba added a comment - 2023-09-11 08:42 - edited 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.

Daniel Black added a comment - 2023-09-11 21:21

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

----------------

----------------

Daniel Black added a comment - 2023-09-11 21:21 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 ---------------- ----------------

Jinsheng Ba added a comment - 2023-09-12 00:49

Thanks for your prompt reply!

Jinsheng Ba added a comment - 2023-09-12 00:49 Thanks for your prompt reply!

People

Assignee:: Sergei Petrunia

Reporter:: Jinsheng Ba

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-09-06 01:08

Updated:: 2024-09-10 15:05

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration