Alice Sherepa added a comment - 2019-06-21 10:37 Thanks a lot for the report! As a workaround, you can try setting optimizer_switch='rowid_filter=off' --source include/have_sequence.inc --source include/have_innodb.inc   create table t1 ( a int , b int , key (b), key (a)) engine=innodb; insert into t1 select seq+10000, 2 from seq_1_to_1000; insert into t1 select 10100, 2 from seq_1_to_50;   select count (*) from t1 where a in (10100,1) and b = 2;   set optimizer_switch= 'rowid_filter=off' ; select count (*) from t1 where a in (10100,1) and b = 2;   drop table t1; MariaDB [test]> select count(*) from t1 where a in(10100,1) and b = 2; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.001 sec)   MariaDB [test]> set optimizer_switch='rowid_filter=off'; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> select count(*) from t1 where a in(10100,1) and b = 2; +----------+ | count(*) | +----------+ | 51 | +----------+ 1 row in set (0.001 sec)

Sylvain RUMEU added a comment - 2019-06-21 11:07 Thanks a lot, it works

Igor Babaev added a comment - 2019-06-21 16:26 If I add primary key to the table the query works fine: create table t1 (pk int not null auto_increment, a int , b int , primary key (pk), key (b), key (a)) engine=innodb; insert into t1(a,b) select seq+10000, 2 from seq_1_to_1000; insert into t1(a,b) select 10100, 2 from seq_1_to_50; MariaDB [test]> select count(*) from t1 where a in(10100,1) and b = 2; +----------+ | count(*) | +----------+ | 51 | +----------+ 1 row in set (0.007 sec) MariaDB [test]> explain extended select count(*) from t1 where a in(10100,1) and b = 2; +------+-------------+-------+------------+---------------+------+---------+-------+-----------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------------+---------------+------+---------+-------+-----------+----------+---------------------------------+ | 1 | SIMPLE | t1 | ref|filter | b,a | b|a | 5|5 | const | 1050 (5%) | 4.95 | Using where; Using rowid filter | +------+-------------+-------+------------+---------------+------+---------+-------+-----------+----------+---------------------------------+ 1 row in set, 1 warning (0.004 sec) MariaDB [test]> analyze format=json select count(*) from t1 where a in(10100,1) and b = 2; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 5.8473, "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["b", "a"], "key": "b", "key_length": "5", "used_key_parts": ["b"], "ref": ["const"], "rowid_filter": { "range": { "key": "a", "used_key_parts": ["a"] }, "rows": 52, "selectivity_pct": 4.9524, "r_rows": 51, "r_selectivity_pct": 4.8571, "r_buffer_size": 25, "r_filling_time_ms": 0.9365 }, "r_loops": 1, "rows": 1050, "r_rows": 51, "r_total_time_ms": 5.6406, "filtered": 4.9524, "r_filtered": 100, "attached_condition": "t1.a in (10100,1)" } } } |

Marko Mäkelä added a comment - 2019-06-25 05:57 I suggested a little shorter fix . In the case when the rowid filter does not match, it will do some extra work and convert all available fields of the index record, not only the rowid column.

Igor Babaev added a comment - 2019-06-26 05:22 A fix for this bug was pushed into 10.4.