Yasuo Honda added a comment - 2019-04-06 03:16 The correct version number is 10.4.3 MariaDB [(none)]> select version(); +----------------+ | version() | +----------------+ | 10.4 . 3 -MariaDB | +----------------+ 1 row in set ( 0.000 sec)   MariaDB [(none)]>

Yasuo Honda added a comment - 2019-04-06 04:25 https://github.com/rails/rails/issues/35884 explains steps to reproduce using Active Record.

Alice Sherepa added a comment - 2019-04-08 12:39 - edited Thanks for the report! Reproducible on 10.4 with optimizer_switch="rowid_filter=on" set optimizer_switch= "rowid_filter=on" ; create table t1 (id int not null primary key ) engine=innodb; insert into t1 values (2),(1);   create table t2 (y int ,x int , index (x), index (y)) engine=innodb; insert into t2 values (4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1),(555,555),(666,1);   select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; 10.4 1e7ad5bb1c69dba8c7d MariaDB [test]> select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; Empty set (0.001 sec)   MariaDB [test]> analyze -> select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; +------+-------------+-------+------------+---------------+---------+---------+-------+---------+------------+----------+------------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------------+---------------+---------+---------+-------+---------+------------+----------+------------+---------------------------------+ | 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | Using index | | 1 | SIMPLE | t2 | ref|filter | x,y | x|y | 5|5 | const | 6 (20%) | 1.00 (16%) | 55.56 | 0.00 | Using where; Using rowid filter | +------+-------------+-------+------------+---------------+---------+---------+-------+---------+------------+----------+------------+---------------------------------+ 2 rows in set (0.000 sec) 10.3 d5a2bc6a0f24f2a9ec7 MariaDB [test]> select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; +----+------+------+ | id | y | x | +----+------+------+ | 1 | 2 | 1 | +----+------+------+ 1 row in set (0.001 sec)

Yasuo Honda added a comment - 2019-04-08 13:21 Thanks for the investigation. Setting `optimizer_switch=rowid_filter=off` addresses this wrong result. /etc/my.cnf.d/server.cnf [mysqld] optimizer_switch=rowid_filter=off MariaDB [(none)]> SELECT @ @optimizer_switch \G *************************** 1 . row *************************** @ @optimizer_switch : index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on 1 row in set ( 0.001 sec)   MariaDB [(none)]> quit

Igor Babaev (Inactive) added a comment - 2019-04-18 17:42 A fix for this bug was pushed into 10.4