-- release/opt BugNumber=118810 rm -rf $BugNumber/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/$BugNumber/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.43 [umshastr@bug118411:/bv/ushastry/Work/Binaries/mysql-8.0.43]$ bin/mysql -uroot -S/tmp/mysql.sock --ssl-mode=disabled Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.43 MySQL Community Server - GPL Copyright (c) 2000, 2025, 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.01 sec) mysql> use test Database changed mysql> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_typeindex_desc.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 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, 20 warnings (0.08 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, 56 rows affected, 45 warnings (0.01 sec) Records: 56 Duplicates: 0 Warnings: 45 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +-------------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_typeindex_desc | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +-------------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index.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, 28 warnings (0.17 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, 55 rows affected, 44 warnings (0.01 sec) Records: 55 Duplicates: 0 Warnings: 44 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +---------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_type2index | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +---------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.01 sec) +---------------------------+-----------+----------+-----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+-----------------------------------------------------+ | test.tbl_1_all_type2index | histogram | status | Histogram statistics created for column 'time_col'. | +---------------------------+-----------+----------+-----------------------------------------------------+ 1 row in set (0.00 sec) +---------------------------+-----------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+--------------------------------------------------------+ | test.tbl_1_all_type2index | histogram | status | Histogram statistics created for column 'varchar_col'. | +---------------------------+-----------+----------+--------------------------------------------------------+ 1 row in set (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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_typeindex.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.01 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, 22 warnings (0.07 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, 57 rows affected, 46 warnings (0.00 sec) Records: 57 Duplicates: 0 Warnings: 46 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) +--------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_typeindex | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +--------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index_desc.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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 30 warnings (0.16 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, 58 rows affected, 47 warnings (0.01 sec) Records: 58 Duplicates: 0 Warnings: 47 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +--------------------------------+-----------+----------+------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'float_col'. | +--------------------------------+-----------+----------+------------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +--------------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------+-----------+----------+----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'int_col'. | +--------------------------------+-----------+----------+----------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------+-----------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'mediumint_col'. | +--------------------------------+-----------+----------+----------------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------+-----------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'timestamp_col'. | +--------------------------------+-----------+----------+----------------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------+-----------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+--------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'varchar_col'. | +--------------------------------+-----------+----------+--------------------------------------------------------+ 1 row in set (0.01 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> explain SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C force index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D force index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----+-------------+-------+------------+-------+----------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | 1 | PRIMARY | A | NULL | ALL | NULL | NULL | NULL | NULL | 56 | 66.67 | Using where | | 1 | PRIMARY | B | NULL | range | ndx_int_float_col,ndx_time_varchar_col | ndx_int_float_col | 2 | NULL | 54 | 100.00 | Using where; Using index; Using join buffer (hash join) | | 1 | PRIMARY | C | NULL | range | ndx_bit_col | ndx_bit_col | 7 | NULL | 32 | 11.11 | Using where; Using join buffer (hash join) | | 2 | SUBQUERY | D | NULL | range | ndx_date_col | ndx_date_col | 7 | NULL | 41 | 100.00 | Using index condition | | 2 | SUBQUERY | E | NULL | index | NULL | ndx_bit_col | 2 | NULL | 57 | 100.00 | Using index; Using join buffer (hash join) | | 2 | SUBQUERY | F | NULL | ALL | NULL | NULL | NULL | NULL | 58 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+-------+----------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ 6 rows in set, 1 warning (0.01 sec) mysql> SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C force index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D force index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set, 107 warnings (0.00 sec) mysql> explain SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C ignore index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D ignore index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----+-------------+-------+------------+-------+----------------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | 1 | PRIMARY | A | NULL | ALL | NULL | NULL | NULL | NULL | 56 | 66.67 | Using where | | 1 | PRIMARY | B | NULL | range | ndx_int_float_col,ndx_time_varchar_col | ndx_int_float_col | 2 | NULL | 54 | 100.00 | Using where; Using index; Using join buffer (hash join) | | 1 | PRIMARY | C | NULL | ALL | ndx_varchar_col,ndx_smallint_col,ndx_int_col | NULL | NULL | NULL | 56 | 11.11 | Using where; Using join buffer (hash join) | | 2 | SUBQUERY | D | NULL | ALL | ndx_mediumint_col | NULL | NULL | NULL | 56 | 73.21 | Using where | | 2 | SUBQUERY | E | NULL | index | NULL | ndx_bit_col | 2 | NULL | 57 | 100.00 | Using index; Using join buffer (hash join) | | 2 | SUBQUERY | F | NULL | ALL | NULL | NULL | NULL | NULL | 58 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+-------+----------------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec) mysql> mysql> SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C ignore index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D ignore index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----------+ | count(*) | +----------+ | 27216 | +----------+ 1 row in set, 125 warnings (0.00 sec) mysql> -- 8.4.6 [umshastr@bug118411:/bv/ushastry/Work/Binaries/mysql-8.4.6]$ 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.4.6 MySQL Community Server - GPL Copyright (c) 2000, 2025, 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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_typeindex_desc.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.01 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, 20 warnings (0.06 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, 56 rows affected, 45 warnings (0.01 sec) Records: 56 Duplicates: 0 Warnings: 45 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +-------------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_typeindex_desc | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +-------------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index.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 (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, 28 warnings (0.14 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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 55 rows affected, 44 warnings (0.01 sec) Records: 55 Duplicates: 0 Warnings: 44 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +---------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_type2index | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +---------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) +---------------------------+-----------+----------+-----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+-----------------------------------------------------+ | test.tbl_1_all_type2index | histogram | status | Histogram statistics created for column 'time_col'. | +---------------------------+-----------+----------+-----------------------------------------------------+ 1 row in set (0.01 sec) +---------------------------+-----------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+--------------------------------------------------------+ | test.tbl_1_all_type2index | histogram | status | Histogram statistics created for column 'varchar_col'. | +---------------------------+-----------+----------+--------------------------------------------------------+ 1 row in set (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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_typeindex.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 (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, 22 warnings (0.06 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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 57 rows affected, 46 warnings (0.00 sec) Records: 57 Duplicates: 0 Warnings: 46 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) +--------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_typeindex | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +--------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index_desc.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 (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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 30 warnings (0.13 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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 58 rows affected, 47 warnings (0.01 sec) Records: 58 Duplicates: 0 Warnings: 47 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +--------------------------------+-----------+----------+------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'float_col'. | +--------------------------------+-----------+----------+------------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +--------------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------+-----------+----------+----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'int_col'. | +--------------------------------+-----------+----------+----------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------+-----------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'mediumint_col'. | +--------------------------------+-----------+----------+----------------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------+-----------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'timestamp_col'. | +--------------------------------+-----------+----------+----------------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------+-----------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+--------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'varchar_col'. | +--------------------------------+-----------+----------+--------------------------------------------------------+ 1 row in set (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.01 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> explain SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C force index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D force index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----+-------------+-------+------------+-------+----------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | 1 | PRIMARY | A | NULL | ALL | NULL | NULL | NULL | NULL | 56 | 66.67 | Using where | | 1 | PRIMARY | B | NULL | range | ndx_int_float_col,ndx_time_varchar_col | ndx_int_float_col | 2 | NULL | 54 | 100.00 | Using where; Using index; Using join buffer (hash join) | | 1 | PRIMARY | C | NULL | range | ndx_bit_col | ndx_bit_col | 7 | NULL | 32 | 11.11 | Using where; Using join buffer (hash join) | | 2 | SUBQUERY | D | NULL | range | ndx_date_col | ndx_date_col | 7 | NULL | 41 | 100.00 | Using index condition | | 2 | SUBQUERY | E | NULL | index | NULL | ndx_bit_col | 2 | NULL | 57 | 100.00 | Using index; Using join buffer (hash join) | | 2 | SUBQUERY | F | NULL | ALL | NULL | NULL | NULL | NULL | 58 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+-------+----------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C force index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D force index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set, 107 warnings (0.00 sec) mysql> explain SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C ignore index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D ignore index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----+-------------+-------+------------+-------+----------------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ | 1 | PRIMARY | A | NULL | ALL | NULL | NULL | NULL | NULL | 56 | 66.67 | Using where | | 1 | PRIMARY | B | NULL | range | ndx_int_float_col,ndx_time_varchar_col | ndx_int_float_col | 2 | NULL | 54 | 100.00 | Using where; Using index; Using join buffer (hash join) | | 1 | PRIMARY | C | NULL | ALL | ndx_varchar_col,ndx_smallint_col,ndx_int_col | NULL | NULL | NULL | 56 | 11.11 | Using where; Using join buffer (hash join) | | 2 | SUBQUERY | D | NULL | ALL | ndx_mediumint_col | NULL | NULL | NULL | 56 | 73.21 | Using where | | 2 | SUBQUERY | E | NULL | index | NULL | ndx_bit_col | 2 | NULL | 57 | 100.00 | Using index; Using join buffer (hash join) | | 2 | SUBQUERY | F | NULL | ALL | NULL | NULL | NULL | NULL | 58 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+-------+----------------------------------------------+-------------------+---------+------+------+----------+---------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec) mysql> SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C ignore index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D ignore index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----------+ | count(*) | +----------+ | 27216 | +----------+ 1 row in set, 125 warnings (0.01 sec) mysql> ################# hypergraph_optimizer=on - not affected BugNumber=118810 rm -rf $BugNumber/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='debug' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & [umshastr@bug118411:/bv/ushastry/Work/Binaries/mysql-8.0.43]$ 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.43-debug MySQL Community Server - GPL - Debug Copyright (c) 2000, 2025, 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.01 sec) mysql> SET SESSION optimizer_switch='hypergraph_optimizer=on'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> use test Database changed mysql> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_typeindex_desc.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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 20 warnings (0.22 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, 56 rows affected, 45 warnings (0.09 sec) Records: 56 Duplicates: 0 Warnings: 45 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) +-------------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_typeindex_desc | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +-------------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.01 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> source source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index.sql; ERROR: Failed to open file 'source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index.sql', error: 2 mysql> mysql> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index.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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 28 warnings (0.40 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 55 rows affected, 44 warnings (0.19 sec) Records: 55 Duplicates: 0 Warnings: 44 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) +---------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_type2index | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +---------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.01 sec) +---------------------------+-----------+----------+-----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+-----------------------------------------------------+ | test.tbl_1_all_type2index | histogram | status | Histogram statistics created for column 'time_col'. | +---------------------------+-----------+----------+-----------------------------------------------------+ 1 row in set (0.01 sec) +---------------------------+-----------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+-----------+----------+--------------------------------------------------------+ | test.tbl_1_all_type2index | histogram | status | Histogram statistics created for column 'varchar_col'. | +---------------------------+-----------+----------+--------------------------------------------------------+ 1 row in set (0.02 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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_typeindex.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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 22 warnings (0.20 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 57 rows affected, 46 warnings (0.11 sec) Records: 57 Duplicates: 0 Warnings: 46 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) +--------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_typeindex | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +--------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.01 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> source /bv/ushastry/Work/Binaries/mysql-8.0.43/tbl_1_all_type2index_desc.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.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 30 warnings (0.42 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 58 rows affected, 47 warnings (0.23 sec) Records: 58 Duplicates: 0 Warnings: 47 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) +--------------------------------+-----------+----------+------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'float_col'. | +--------------------------------+-----------+----------+------------------------------------------------------+ 1 row in set (0.02 sec) +--------------------------------+-----------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+---------------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | Error | The column 'id_col' is covered by a single-part unique index. | +--------------------------------+-----------+----------+---------------------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------+-----------+----------+----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'int_col'. | +--------------------------------+-----------+----------+----------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------+-----------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'mediumint_col'. | +--------------------------------+-----------+----------+----------------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------+-----------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+----------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'timestamp_col'. | +--------------------------------+-----------+----------+----------------------------------------------------------+ 1 row in set (0.02 sec) +--------------------------------+-----------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-----------+----------+--------------------------------------------------------+ | test.tbl_1_all_type2index_desc | histogram | status | Histogram statistics created for column 'varchar_col'. | +--------------------------------+-----------+----------+--------------------------------------------------------+ 1 row in set (0.01 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> explain SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C force index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D force index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Aggregate: count(0) (cost=11225..11225 rows=1) -> Left hash join (no condition) (cost=7956..9343 rows=18814) -> Inner hash join (no condition) (cost=7945..7980 rows=348) -> Filter: ((A.bigint_col >= (select #2))) (cost=7938..7944 rows=56) -> Table scan on A (cost=0.00446..0.25 rows=56) -> Select #2 (subquery in condition; run only once) -> Aggregate: min(D.bigint_col) (cost=7938..7938 rows=1) -> Left hash join (no condition) (cost=8.06..3420 rows=45177) -> Nested loop inner join (cost=0.00689..16.1 rows=2337) -> Filter: (D.time_col is not null) (cost=0.143..5.85 rows=41) -> Table scan on D (cost=0.00446..0.25 rows=56) -> Table scan on E (cost=0.00439..0.25 rows=57) -> Hash -> Filter: (F.float_col >= cast(F.varbinary_col as double)) (cost=0.313..6.05 rows=19.3) -> Table scan on F (cost=0.00431..0.25 rows=58) -> Hash -> Filter: (C.varbinary_col between 'dddddddddd1' and C.decimal_col) (cost=0.94..5.85 rows=6.22) -> Table scan on C (cost=0.00446..0.25 rows=56) -> Hash -> Index range scan on B using ndx_int_float_col over (bool_col = 2) OR (bool_col = 1) OR (bool_col = 0) (cost=0.105..5.67 rows=54) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec) mysql> SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C force index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D force index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----------+ | count(*) | +----------+ | 27216 | +----------+ 1 row in set, 125 warnings (0.02 sec) mysql> explain SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C ignore index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D ignore index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Aggregate: count(0) (cost=11225..11225 rows=1) -> Left hash join (no condition) (cost=7956..9343 rows=18814) -> Inner hash join (no condition) (cost=7945..7980 rows=348) -> Filter: ((A.bigint_col >= (select #2))) (cost=7938..7944 rows=56) -> Table scan on A (cost=0.00446..0.25 rows=56) -> Select #2 (subquery in condition; run only once) -> Aggregate: min(D.bigint_col) (cost=7938..7938 rows=1) -> Left hash join (no condition) (cost=8.06..3420 rows=45177) -> Nested loop inner join (cost=0.00689..16.1 rows=2337) -> Filter: (D.time_col is not null) (cost=0.143..5.85 rows=41) -> Table scan on D (cost=0.00446..0.25 rows=56) -> Table scan on E (cost=0.00439..0.25 rows=57) -> Hash -> Filter: (F.float_col >= cast(F.varbinary_col as double)) (cost=0.313..6.05 rows=19.3) -> Table scan on F (cost=0.00431..0.25 rows=58) -> Hash -> Filter: (C.varbinary_col between 'dddddddddd1' and C.decimal_col) (cost=0.94..5.85 rows=6.22) -> Table scan on C (cost=0.00446..0.25 rows=56) -> Hash -> Index range scan on B using ndx_int_float_col over (bool_col = 2) OR (bool_col = 1) OR (bool_col = 0) (cost=0.105..5.67 rows=54) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT count(*) FROM tbl_1_all_typeindex_desc A LEFT JOIN tbl_1_all_type2index B ON B.bool_col IN ('2',NULL,'0','1') RIGHT JOIN tbl_1_all_typeindex_desc C ignore index(ndx_bit_col) ON C.varbinary_col BETWEEN 'dddddddddd1' AND C.decimal_col WHERE A.bigint_col >= ANY (SELECT D.bigint_col FROM tbl_1_all_typeindex_desc D ignore index(ndx_date_col) JOIN tbl_1_all_typeindex E ON D.time_col IS NOT NULL LEFT JOIN tbl_1_all_type2index_desc F ON F.float_col >= F.varbinary_col group by 1); +----------+ | count(*) | +----------+ | 27216 | +----------+ 1 row in set, 125 warnings (0.03 sec) mysql>