| Bug #112251 | Uncertain Result | ||
|---|---|---|---|
| Submitted: | 4 Sep 13:19 | Modified: | 5 Sep 12:11 |
| Reporter: | JINSHENG BA | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.1.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Sep 7:07]
MySQL Verification Team
Hello Jinsheng Ba,
Thank you for the report and test case.
I tried to reproduce on 8.0.34 and 8.0.11 but end results is always empty. Is there anything I'm missing here? Please let us know.
-- start up
rm -rf 112251/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/112251 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/112251 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/112251/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 &
- 8.0.34/8.1.0
bin/mysql -uroot -S /tmp/mysql.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.34-debug MySQL Community Server - GPL - Debug
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE t1(c0 CHAR);
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE t2(c0 INT ZEROFILL);
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> CREATE TABLE t3(c0 FLOAT ZEROFILL) ;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> CREATE TABLE t4(c0 CHAR);
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> INSERT INTO t2(c0) VALUES(1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t3(c0) VALUES(2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t4(c0) VALUES("a");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t4 RIGHT JOIN t1 ON TRUE RIGHT JOIN t3 ON FALSE CROSS JOIN t2 WHERE (t4.c0 IS NOT FALSE) IN (t2.c0); -- {NULL|NULL|000000000002|0000000001}
Empty set (0.01 sec)
mysql> SELECT * FROM t4 RIGHT JOIN t1 ON TRUE RIGHT JOIN t3 ON FALSE CROSS JOIN t2 WHERE (t4.c0 IS NOT FALSE) IN (t2.c0); -- {NULL|NULL|000000000002|0000000001}
Empty set (0.00 sec)
^^ 20+ times same result
-- 8.1.0
bin/mysql -uroot -S /tmp/mysql.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.1.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE t1(c0 CHAR);
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE t2(c0 INT ZEROFILL);
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> CREATE TABLE t3(c0 FLOAT ZEROFILL) ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> CREATE TABLE t4(c0 CHAR);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO t2(c0) VALUES(1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t3(c0) VALUES(2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t4(c0) VALUES("a");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT * FROM t4 RIGHT JOIN t1 ON TRUE RIGHT JOIN t3 ON FALSE CROSS JOIN t2 WHERE (t4.c0 IS NOT FALSE) IN (t2.c0); -- {NULL|NULL|000000000002|0000000001}
Empty set (0.00 sec)
mysql> SELECT * FROM t4 RIGHT JOIN t1 ON TRUE RIGHT JOIN t3 ON FALSE CROSS JOIN t2 WHERE (t4.c0 IS NOT FALSE) IN (t2.c0); -- {NULL|NULL|000000000002|0000000001}
Empty set (0.00 sec)
^^ 20+ times
regards,
Umesh
[5 Sep 11:50]
JINSHENG BA
Thanks for your prompt reply! Sorry for that. I analyzed the bug report again and found it should be due to my environment---some other sessions are changing the variable optimizer_switch which affects the query results. I refined the test case and resubmitted the test case here: https://bugs.mysql.com/bug.php?id=112264
[5 Sep 12:11]
MySQL Verification Team
Thank you for the feedback. Ideally, you should have added the details here. For now will close this as can't reproduce. regards, Umesh
Description: CREATE TABLE t1(c0 CHAR); CREATE TABLE t2(c0 INT ZEROFILL); CREATE TABLE t3(c0 FLOAT ZEROFILL) ; CREATE TABLE t4(c0 CHAR); INSERT INTO t2(c0) VALUES(1); INSERT INTO t3(c0) VALUES(2); INSERT INTO t4(c0) VALUES("a"); SELECT * FROM t4 RIGHT JOIN t1 ON TRUE RIGHT JOIN t3 ON FALSE CROSS JOIN t2 WHERE (t4.c0 IS NOT FALSE) IN (t2.c0); -- {NULL|NULL|000000000002|0000000001} Executing the query multiple times (at least 10 times), the result of the above result is either empty or the above data. It is pretty strange that the result is uncertain. How to repeat: docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.1.0 Then execute the above test case.