Description:
Executing a SQL statement returns less rows than expected.
Here is the SQL which can be used to show the problem:
SQL_Test1:
select
td.*
from
test_data td
-- ignore index(idx_c7, idx_c8, idx_c15)
where
td.c1='XXXXXXXX463'
and td.c8 in ('XXXXXdXd-bXXX-XXXX-XXbX-fXXXXXXXdXXX')
and td.bet_timestamp between '2022-07-01T21:00Z' and '2022-11-02T21:00Z'
limit 100
;
In addition I will attach an "Dump_and_Variables.zip" file which will contain:
1. Dump_test_data_20221103_2.sql - dump file to create the table and load data.
2. MySQL_variables.csv - result of "show variables;" statement.
How to repeat:
Steps to reproduce:
1. Import the Dump_test_data_20221103_2.sql file, as result a "test_data" partitioned table with 2526 records should be loaded
2. Execute the SQL_Test1 statement:
Expected result: 10 records must be returned.
Actual result: no records are returned.
3. If you run the SQL_Test1 with using the hint "ignore index(idx_c7, idx_c8, idx_c15)" then it will return 10 records as expected.
Additional info:
* Running "explain SQL_Test1" on my machine shows:
select_type: 'SIMPLE',
table: 'td',
partitions: first,p_from_2022_7_30_to_2022_8_30_1659139200053,p_from_2022_8_29_to_2022_9_29_1661731200058,p_from_2022_9_29_to_2022_10_28_1664323200076,p_from_2022_10_29_to_2022_11_27_1666915200080
type: 'index_merge',
possible_keys: PRIMARY,idx_c1,idx_c8
keys: idx_c1,idx_c8
key_len: 88,152
ref: NULL,
rows: 1
filtered: 100.00
extra: Using intersect(idx_c1,idx_c8); Using where
* If you run "explain SQL_Test1" with the hint "ignore index(idx_c7, idx_c8, idx_c15)", on my machine shows:
select_type: SIMPLE
table: td
partitions: first,p_from_2022_7_30_to_2022_8_30_1659139200053,p_from_2022_8_29_to_2022_9_29_1661731200058,p_from_2022_9_29_to_2022_10_28_1664323200076,p_from_2022_10_29_to_2022_11_27_1666915200080
type: index_merge
possible_keys: PRIMARY,idx_c1
keys: idx_c1,PRIMARY
key_len: 88,5
ref: NULL
rows: 25
filtered: 1.28
extra: Using intersect(idx_c1,PRIMARY); Using where
I have done lots of experiments and noticed that always when the problem appears we have "intersect" with "idx_c8" index included.
Description: Executing a SQL statement returns less rows than expected. Here is the SQL which can be used to show the problem: SQL_Test1: select td.* from test_data td -- ignore index(idx_c7, idx_c8, idx_c15) where td.c1='XXXXXXXX463' and td.c8 in ('XXXXXdXd-bXXX-XXXX-XXbX-fXXXXXXXdXXX') and td.bet_timestamp between '2022-07-01T21:00Z' and '2022-11-02T21:00Z' limit 100 ; In addition I will attach an "Dump_and_Variables.zip" file which will contain: 1. Dump_test_data_20221103_2.sql - dump file to create the table and load data. 2. MySQL_variables.csv - result of "show variables;" statement. How to repeat: Steps to reproduce: 1. Import the Dump_test_data_20221103_2.sql file, as result a "test_data" partitioned table with 2526 records should be loaded 2. Execute the SQL_Test1 statement: Expected result: 10 records must be returned. Actual result: no records are returned. 3. If you run the SQL_Test1 with using the hint "ignore index(idx_c7, idx_c8, idx_c15)" then it will return 10 records as expected. Additional info: * Running "explain SQL_Test1" on my machine shows: select_type: 'SIMPLE', table: 'td', partitions: first,p_from_2022_7_30_to_2022_8_30_1659139200053,p_from_2022_8_29_to_2022_9_29_1661731200058,p_from_2022_9_29_to_2022_10_28_1664323200076,p_from_2022_10_29_to_2022_11_27_1666915200080 type: 'index_merge', possible_keys: PRIMARY,idx_c1,idx_c8 keys: idx_c1,idx_c8 key_len: 88,152 ref: NULL, rows: 1 filtered: 100.00 extra: Using intersect(idx_c1,idx_c8); Using where * If you run "explain SQL_Test1" with the hint "ignore index(idx_c7, idx_c8, idx_c15)", on my machine shows: select_type: SIMPLE table: td partitions: first,p_from_2022_7_30_to_2022_8_30_1659139200053,p_from_2022_8_29_to_2022_9_29_1661731200058,p_from_2022_9_29_to_2022_10_28_1664323200076,p_from_2022_10_29_to_2022_11_27_1666915200080 type: index_merge possible_keys: PRIMARY,idx_c1 keys: idx_c1,PRIMARY key_len: 88,5 ref: NULL rows: 25 filtered: 1.28 extra: Using intersect(idx_c1,PRIMARY); Using where I have done lots of experiments and noticed that always when the problem appears we have "intersect" with "idx_c8" index included.