Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Cannot Reproduce
-
10.4.10
-
None
Description
The file test.sql is mysqldump of tables subscription,label,track,license_track
Load in the tables query from the mysql command line
The problem query:
MariaDB [test]> explain SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
-------------------------------------------------------------------------------------------------------------------------------------------------+
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------------------------------------------------+
1 | SIMPLE | subscription | ref | account_id,license_id,account_id_2 | account_id | 4 | const | 1 | Using index |
1 | SIMPLE | track | ref | PRIMARY,label_id,enabled_2,enabled | enabled_2 | 1 | const | 16754 | |
1 | SIMPLE | label | eq_ref | PRIMARY,enabled | PRIMARY | 4 | test.track.label_id | 1 | Using where |
1 | SIMPLE | license_track | eq_ref | license_id,track_id,license_id_2 | license_id | 8 | test.subscription.license_id,test.track.id | 1 | Using index |
-------------------------------------------------------------------------------------------------------------------------------------------------+
Execute:
optimize table track;
optimize table license_track;
After this the explain shows:
MariaDB [test]> explain SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 | SIMPLE | subscription | ref | account_id,license_id,account_id_2 | account_id | 4 | const | 1 | Using index | |||
1 | SIMPLE | license_track | ref | license_id,track_id,license_id_2 | license_id | 4 | test.subscription.license_id | 20055 | Using index | |||
1 | SIMPLE | track | ALL | filter | PRIMARY,label_id,enabled_2,enabled | enabled_2 | 1 | NULL | 39744 (50%) | Using where; Using join buffer (flat, BNL join); Using rowid filter | ||
1 | SIMPLE | label | eq_ref | PRIMARY,enabled | PRIMARY | 4 | test.track.label_id | 1 | Using where |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The bug is that track is using rowid filter instead of lookup of primary key, like in the first explain
In 10.5 we get the following "correct" explain for the exact same 10.4 tables:
-----------------------------------------------------------------------------------------------------------------------------------+
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------+
1 | SIMPLE | subscription | ref | account_id,license_id,account_id_2 | account_id | 4 | const | 1 | Using index |
1 | SIMPLE | license_track | ref | license_id,track_id,license_id_2 | license_id | 4 | test.subscription.license_id | 20055 | Using index |
1 | SIMPLE | track | eq_ref | PRIMARY,label_id,enabled_2,enabled | PRIMARY | 4 | test.license_track.track_id | 1 | Using where |
1 | SIMPLE | label | eq_ref | PRIMARY,enabled | PRIMARY | 4 | test.track.label_id | 1 | Using where |
-----------------------------------------------------------------------------------------------------------------------------------+
I noticed that when one gets the 'wrong' explain in 10.4, one will get a crash when executing the following query:
SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription LEFT JOIN license_track ON license_track.license_id=subscription.license_id LEFT JOIN track ON track.id=license_track.track_id LEFT JOIN label ON label.id=track.label_id WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7;
#4 0x00000000010ed6d5 in row_search_idx_cond_check (mysql_rec=0x7fff840bcd50 "ÿ\001", prebuilt=0x7fff840c0820, rec=0x7fffe13a007d "\200", offsets=0x7ffff4253e60) at /my/maria-10.4/storage/innobase/row/row0sel.cc:3942
#5 0x00000000010f13da in row_search_mvcc (buf=0x7fff840bcd50 "ÿ\001", mode=PAGE_CUR_G, prebuilt=0x7fff840c0820, match_mode=0, direction=0) at /my/maria-10.4/storage/innobase/row/row0sel.cc:5257
#6 0x0000000000f05342 in ha_innobase::index_read (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /my/maria-10.4/storage/innobase/handler/ha_innodb.cc:9305
#7 0x0000000000f062f4 in ha_innobase::index_first (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001") at /my/maria-10.4/storage/innobase/handler/ha_innodb.cc:9679
#8 0x0000000000f064cc in ha_innobase::rnd_next (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001") at /my/maria-10.4/storage/innobase/handler/ha_innodb.cc:9772
#9 0x0000000000bd5256 in handler::ha_rnd_next (this=0x7fff840bf410, buf=0x7fff840bcd50 "ÿ\001") at /my/maria-10.4/sql/handler.cc:2826
#10 0x0000000000d76cb1 in rr_sequential (info=0x7fff84147f98) at /my/maria-10.4/sql/records.cc:477
#11 0x00000000007cc56d in READ_RECORD::read_record (this=0x7fff84147f98) at /my/maria-10.4/sql/records.h:69
#12 0x00000000008fa3ed in join_init_read_record (tab=0x7fff84147ed0) at /my/maria-10.4/sql/sql_select.cc:21129
#13 0x0000000000a4701e in JOIN_TAB_SCAN::open (this=0x7fff84022e60) at /my/maria-10.4/sql/sql_join_cache.cc:3349
#14 0x0000000000a4543e in JOIN_CACHE::join_matching_records (this=0x7fff84167fd0, skip_last=false) at /my/maria-10.4/sql/sql_join_cache.cc:2252
#15 0x0000000000a44f38 in JOIN_CACHE::join_records (this=0x7fff84167fd0, skip_last=false) at /my/maria-10.4/sql/sql_join_cache.cc:2088
#16 0x00000000008f7b53 in sub_select_cache (join=0x7fff840226a0, join_tab=0x7fff84147ed0, end_of_records=false) at /my/maria-10.4/sql/sql_select.cc:19986
#17 0x00000000008f870c in evaluate_join_record (join=0x7fff840226a0, join_tab=0x7fff84147b28, error=0) at /my/maria-10.4/sql/sql_select.cc:20408
#18 0x00000000008f8134 in sub_select (join=0x7fff840226a0, join_tab=0x7fff84147b28, end_of_records=false) at /my/maria-10.4/sql/sql_select.cc:20227
#19 0x00000000008f870c in evaluate_join_record (join=0x7fff840226a0, join_tab=0x7fff84147780, error=0) at /my/maria-10.4/sql/sql_select.cc:20408
#20 0x00000000008f7f3f in sub_select (join=0x7fff840226a0, join_tab=0x7fff84147780, end_of_records=false) at /my/maria-10.4/sql/sql_select.cc:20188
This happens for table 'track'.