| Bug #111453 | sql query with "where time_col not between val1 and null' has diffrent results | ||
|---|---|---|---|
| Submitted: | 16 Jun 2023 0:54 | Modified: | 19 Jun 2023 10:08 |
| Reporter: | yujie wang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.33 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 Jun 2023 0:54]
yujie wang
[16 Jun 2023 0:56]
yujie wang
table for sql query posted in bug
Attachment: tbl_1_all_type2index.sql (application/octet-stream, text), 46.68 KiB.
[19 Jun 2023 9:03]
MySQL Verification Team
Hello yujie wang, Thank you for the report and test case. I'm not seeing any discrepancies even after running 1000+ times on each 8.0.22 and 8.0.33 instances. Anything I'm missing here? Please let us know. Thank you. - 8.0.33 for((i=1;i<=1000;i++)); do bin/mysql -uroot -S /tmp/mysql.sock aaa -e "SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL;"; done +----------+ | count(*) | +----------+ | 20 | +----------+ . +----------+ | count(*) | +----------+ | 20 | +----------+ - 8.0.22 for((i=1;i<=1000;i++)); do bin/mysql -uroot -S /tmp/mysql.sock aaa -e "SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL;"; done +----------+ | count(*) | +----------+ | 20 | +----------+ . +----------+ | count(*) | +----------+ | 20 | +----------+ regards, Umesh
[19 Jun 2023 9:24]
yujie wang
you can try to repeat the query manually instead of code mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.33 | +-----------+ 1 row in set (0.00 sec)
[19 Jun 2023 9:35]
MySQL Verification Team
Thank you for the feedback. I ran it manually for 20+ times but no changes seen: bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.33 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> use aaa 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> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> \r Connection id: 8 Current database: aaa mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.01 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.01 sec . . Are you running server with non-default settings? Please share the conf file if it is not running on default. Thank you
[19 Jun 2023 9:41]
yujie wang
here is the my.cnf: [mysqld] # These are commonly set, remove the # and set as required. skip-name-resolve port = 3306 basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket= /usr/local/mysql/data/mysql.sock log-error = /usr/local/mysql/data/error.log pid-file = /usr/local/mysql/data/mysql.pid secure_file_priv = '' and the sql mode is like this: mysql> select @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+
[19 Jun 2023 9:43]
yujie wang
you can try to drop the table and then source again,the issue may will appear
[19 Jun 2023 9:44]
yujie wang
after the result become 20, the later results are always 20
[19 Jun 2023 10:08]
MySQL Verification Team
Thank you for the valuable input, that did the trick here: 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> show variables like 'sql_mode'; +---------------+-----------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +---------------+-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> drop database aaa; Query OK, 1 row affected (0.03 sec) mysql> create database aaa; Query OK, 1 row affected (0.01 sec) mysql> use aaa Database changed mysql> source 111454.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 17 warnings (0.22 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 113 rows affected (0.03 sec) Records: 113 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.01 sec) mysql> source 111454.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 17 warnings (0.21 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 113 rows affected (0.02 sec) Records: 113 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 83 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_type2index WHERE time_col not BETWEEN '04:59:59.000000' AND NULL; +----------+ | count(*) | +----------+ | 20 |
