Description:
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> 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.41 |
+-----------+
1 row in set (0.00 sec)
How to repeat:
1.create table and insert data
create database test;
use test
source tbl_1_all_typeindex_desc.sql
source tbl_1_all_type2index.sql
source tbl_1_all_typeindex.sql
source tbl_1_all_type2index_desc.sql
2.execute query
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);
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);
Description: 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> 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> select version(); +-----------+ | version() | +-----------+ | 8.0.41 | +-----------+ 1 row in set (0.00 sec) How to repeat: 1.create table and insert data create database test; use test source tbl_1_all_typeindex_desc.sql source tbl_1_all_type2index.sql source tbl_1_all_typeindex.sql source tbl_1_all_type2index_desc.sql 2.execute query 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); 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);