|
Not reproducible with optimizer_switch='rowid_filter=off';
On debug version there is assertion `sel->quick'
#3 <signal handler called>
|
#4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
#5 0x00007ff5a3320535 in __GI_abort () at abort.c:79
|
#6 0x00007ff5a332040f in __assert_fail_base (fmt=0x7ff5a3482ee0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x56188f470379 "sel->quick", file=0x56188f46fe78 "/10.5/sql/sql_select.cc", line=1691, function=<optimized out>) at assert.c:92
|
#7 0x00007ff5a332e102 in __GI___assert_fail (assertion=0x56188f470379 "sel->quick", file=0x56188f46fe78 "/10.5/sql/sql_select.cc", line=1691, function=0x56188f4741a0 <JOIN::make_range_rowid_filters()::__PRETTY_FUNCTION__> "bool JOIN::make_range_rowid_filters()") at assert.c:101
|
#8 0x000056188e7206dc in JOIN::make_range_rowid_filters (this=0x7ff564015ca8) at /10.5/sql/sql_select.cc:1691
|
#9 0x000056188e722c10 in JOIN::optimize_stage2 (this=0x7ff564015ca8) at /10.5/sql/sql_select.cc:2310
|
#10 0x000056188e722a5b in JOIN::optimize_inner (this=0x7ff564015ca8) at /10.5/sql/sql_select.cc:2286
|
#11 0x000056188e720282 in JOIN::optimize (this=0x7ff564015ca8) at /10.5/sql/sql_select.cc:1606
|
#12 0x000056188e72b6cb in mysql_select (thd=0x7ff564000d78, tables=0x7ff564014288, fields=..., conds=0x7ff564015258, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff564015c80, unit=0x7ff564004d80, select_lex=0x7ff564013c90) at /10.5/sql/sql_select.cc:4654
|
#13 0x000056188e71b42a in handle_select (thd=0x7ff564000d78, lex=0x7ff564004cb8, result=0x7ff564015c80, setup_tables_done_option=0) at /10.5/sql/sql_select.cc:417
|
#14 0x000056188e6e0c83 in execute_sqlcom_select (thd=0x7ff564000d78, all_tables=0x7ff564014288) at /10.5/sql/sql_parse.cc:6168
|
#15 0x000056188e6d77c2 in mysql_execute_command (thd=0x7ff564000d78) at /10.5/sql/sql_parse.cc:3901
|
#16 0x000056188e6e5bb2 in mysql_parse (thd=0x7ff564000d78, rawbuf=0x7ff564013b90 "SELECT * FROM t WHERE a>='2000-01-01 00:00:00' AND b='2030-01-01 00:00:00'", length=74, parser_state=0x7ff59c6ca510, is_com_multi=false, is_next_command=false) at /10.5/sql/sql_parse.cc:7953
|
#17 0x000056188e6d1546 in dispatch_command (command=COM_QUERY, thd=0x7ff564000d78, packet=0x7ff564008d09 "SELECT * FROM t WHERE a>='2000-01-01 00:00:00' AND b='2030-01-01 00:00:00'", packet_length=74, is_com_multi=false, is_next_command=false) at /10.5/sql/sql_parse.cc:1839
|
#18 0x000056188e6cfc84 in do_command (thd=0x7ff564000d78) at /10.5/sql/sql_parse.cc:1358
|
#19 0x000056188e8714f2 in do_handle_one_connection (connect=0x56189252fd18, put_in_cache=true) at /10.5/sql/sql_connect.cc:1422
|
#20 0x000056188e871222 in handle_one_connection (arg=0x5618925d5988) at /10.5/sql/sql_connect.cc:1319
|
#21 0x000056188eda0181 in pfs_spawn_thread (arg=0x561892610318) at /10.5/storage/perfschema/pfs.cc:2201
|
#22 0x00007ff5a3dc6fa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
|
#23 0x00007ff5a33f74cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
|
|
Here is the issue that i found, this is related to the fix for MDEV-21932.
The change there disallows range access for trivial saragable conditions if the optimizer switch turns off index_merge_sort_union.
Example:
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
MariaDB [test]> SET OPTIMIZER_SWITCH="index_merge_sort_union=ON";
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> explain SELECT * FROM t1 WHERE a > 5;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 4 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> SET OPTIMIZER_SWITCH="index_merge_sort_union=OFF";
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> explain SELECT * FROM t1 WHERE a > 5;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | a | a | 5 | NULL | 10 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
So as you see above the plan changed from range scan to index scan when we turned off index_merge_sort_union, which is absolutely WRONG.
|
|
Also look at this
https://jira.mariadb.org/browse/MDEV-22191?focusedCommentId=149413&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-149413
|
|
Looking at the debug build, the server hits the assert
DBUG_ASSER(sel->quick);
inside the function make_range_rowid_filters
The stack trace is here
frame #2: 0x00007fff69fa6a1c libsystem_c.dylib`abort + 120
|
frame #3: 0x00007fff69fa5cd6 libsystem_c.dylib`__assert_rtn + 314
|
frame #4: 0x0000000100ea8c73 mysqld`JOIN::make_range_rowid_filters(this=0x000062b000087bd8) at sql_select.cc:1691:5
|
frame #5: 0x0000000100e91acc mysqld`JOIN::optimize_stage2(this=0x000062b000087bd8) at sql_select.cc:2310:7
|
frame #6: 0x0000000100ea7207 mysqld`JOIN::optimize_inner(this=0x000062b000087bd8) at sql_select.cc:2286:9
|
frame #7: 0x0000000100e91554 mysqld`JOIN::optimize(this=0x000062b000087bd8) at sql_select.cc:1606:10
|
frame #8: 0x0000000100e72424 mysqld`mysql_select(thd=0x000062b00007e288, tables=0x000062b0000859a8, fields=0x000062b0000854c8, conds=0x000062b000086950, og_num=0, order=0x0000000000000000, group=0x0000000000000000, having=0x0000000000000000, proc_param=0x0000000000000000, select_options=2147748612, result=0x000062b0000873c0, unit=0x000062b0000822c0, select_lex=0x000062b000085378) at sql_select.cc:4654:19
|
frame #9: 0x0000000100fac1d5 mysqld`mysql_explain_union(thd=0x000062b00007e288, unit=0x000062b0000822c0, result=0x000062b0000873c0) at sql_select.cc:27187:10
|
frame #10: 0x0000000100d3e80a mysqld`execute_sqlcom_select(thd=0x000062b00007e288, all_tables=0x000062b0000859a8) at sql_parse.cc:6107:12
|
frame #11: 0x0000000100d1d52e mysqld`mysql_execute_command(thd=0x000062b00007e288) at sql_parse.cc:3901:12
|
frame #12: 0x0000000100d014fb mysqld`mysql_parse(thd=0x000062b00007e288, rawbuf="explain\nSELECT * FROM t1 WHERE a > 0 AND b=0", length=44, parser_state=0x000070000b003ce0, is_com_multi=false, is_next_command=false) at sql_parse.cc:7953:18
|
|
|
|
A meaningful mtr test showing the same issue
--source include/have_innodb.inc
|
SET OPTIMIZER_SWITCH="index_merge_sort_union=OFF";
|
CREATE TABLE t1 (a INT, b INT, INDEX(a), INDEX(b)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (0,0),(1,0);
|
explain
|
SELECT * FROM t1 WHERE a > 0 AND b=0;
|
drop table t1;
|
|
|
Also this issue cannot be reproduced when rowid filter is turned off, so lets try to see what happens when rowid filter is not used.
Turn off rowid filter
set optimizer_switch='rowid_filter=off';
|
Looking at the first test_quick_select() call, where we try to find range access on table t1
"range_scan_alternatives": [
|
{
|
"index": "a"
|
},
|
{
|
"index": "b",
|
"ranges": ["(0) <= (b) <= (0)"],
|
"rowid_ordered": true,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 2,
|
"cost": 2.5453,
|
"chosen": true
|
}
|
"chosen_range_access_summary": {
|
"range_access_plan": {
|
"type": "range_scan",
|
"index": "b",
|
"rows": 2,
|
"ranges": ["(0) <= (b) <= (0)"]
|
},
|
"rows_for_plan": 2,
|
"cost_for_plan": 2.5453,
|
"chosen": true
|
}
|
The optimizer chooses a range access on index b [it doesn't consider index on a which is strange]
|
|
Now lets see what happens with rowid_filter turned on
set optimizer_switch='rowid_filter=on';
|
Debugging here
(lldb) bt
|
* thread #2, stop reason = breakpoint 1.1
|
* frame #0: 0x0000000100ea7643 mysqld`JOIN::make_range_rowid_filters(this=0x000062b000089678) at sql_select.cc:1636
|
frame #1: 0x0000000100e91acc mysqld`JOIN::optimize_stage2(this=0x000062b000087bd8) at sql_select.cc:2310:7
|
frame #2: 0x0000000100ea7207 mysqld`JOIN::optimize_inner(this=0x000062b000087bd8) at sql_select.cc:2286:9
|
frame #3: 0x0000000100e91554 mysqld`JOIN::optimize(this=0x000062b000087bd8) at sql_select.cc:1606:10
|
frame #4: 0x0000000100e72424 mysqld`mysql_select(thd=0x000062b00007e288, tables=0x000062b0000859a8, fields=0x000062b0000854c8, conds=0x000062b000086950, og_num=0, order=0x0000000000000000, group=0x0000000000000000, having=0x0000000000000000, proc_param=0x0000000000000000, select_options=2147748612, result=0x000062b0000873c0, unit=0x000062b0000822c0, select_lex=0x000062b000085378) at sql_select.cc:4654:19
|
frame #5: 0x0000000100fac1d5 mysqld`mysql_explain_union(thd=0x000062b00007e288, unit=0x000062b0000822c0, result=0x000062b0000873c0) at sql_select.cc:27187:10
|
frame #6: 0x0000000100d3e80a mysqld`execute_sqlcom_select(thd=0x000062b00007e288, all_tables=0x000062b0000859a8) at sql_parse.cc:6107:12
|
frame #7: 0x0000000100d1d52e mysqld`mysql_execute_command(thd=0x000062b00007e288) at sql_parse.cc:3901:12
|
frame #8: 0x0000000100d014fb mysqld`mysql_parse(thd=0x000062b00007e288, rawbuf="explain\nSELECT * FROM t1 WHERE a > 0 AND b=0", length=44, parser_state=0x000070000dd57ce0, is_com_multi=false, is_next_command=false) at sql_parse.cc:7953:18
|
Some investigation
(lldb) p tab->quick->index
|
(uint) $1 = 1
|
Index used here is index(b), that is the quick select is on the range condition 0<= b <= 0 [as shown above in the optimizer trace output]
The key that the filter can use
(lldb) p tab->range_rowid_filter_info->key_no
|
(uint) $6 = 0
|
Index that the filter can use is index(a), so we try to build a quick_select on the range condition a > 0.
|
|
Now moving inside the function test_quick_select and then seeing where range conditions are considered
Debugging here
(lldb) bt
|
* thread #2, stop reason = step over
|
* frame #0: 0x00000001007a1cfe mysqld`get_key_scans_params(param=0x000070000dd4b9e0, tree=0x00006210000b2e20, index_read_must_be_used=true, update_tbl_stats=true, read_time=1.7976931348623157E+308) at opt_range.cc:7403:12
|
frame #1: 0x000000010079d741 mysqld`SQL_SELECT::test_quick_select(this=0x000062b00008a938, thd=0x000062b00007e288, keys_to_use=key_map @ 0x000070000dd4b6a0, prev_tables=0, limit=18446744073709551615, force_quick_range=true, ordered_output=false, remove_false_parts_of_where=true, only_single_index_range_scan=true) at opt_range.cc:2897:23
|
frame #2: 0x0000000100ea88f7 mysqld`JOIN::make_range_rowid_filters(this=0x000062b000087bd8) at sql_select.cc:1675:18
|
frame #3: 0x0000000100e91acc mysqld`JOIN::optimize_stage2(this=0x000062b000087bd8) at sql_select.cc:2310:7
|
frame #4: 0x0000000100ea7207 mysqld`JOIN::optimize_inner(this=0x000062b000087bd8) at sql_select.cc:2286:9
|
frame #5: 0x0000000100e91554 mysqld`JOIN::optimize(this=0x000062b000087bd8) at sql_select.cc:1606:10
|
So we are currently inside the function get_key_scans_params where we see if we calculate the cost for the range scan on index a for the condition a > 0
Here we see this snipped in the code:
if (!is_ror_scan &&
|
!optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
|
continue;
|
Which states that ignore all range scans that are non-ror if index_merge_sort_union is turned off.
In the test case we explicitly turned off index_merge_sort_union;
and this leads to us not creating any quick_select (even when we forced to create a quick_select)
After exiting from the function test_quick_select we see the code snippet:
int rc= sel->test_quick_select(thd, filter_map, (table_map) 0,
|
(ha_rows) HA_POS_ERROR,
|
true, false, true, true);
|
DBUG_ASSERT(sel->quick);
|
(lldb) p rc
|
(int) $11 = 0
|
(lldb) p sel->quick
|
(QUICK_SELECT_I *) $12 = 0x0000000000000000
|
So the quick select was not created and we hit the assert here.
|
|
Due to a bug in the fix for MDEV-21932 the optimizer fails to build the quick select for the chosen range filter unless the filter uses ROR scan. This triggers an assert failure.
I would recommend to use a more natural population of table t1. I added more rows to the table with
INSERT INTO t1 VALUES (-1,1), (-2,1), (-2,3), (-3,4), (-2,4);
|
and still had the same assert failure.
|
|
The fix for MDEV-22191 fixes this issue and is pushed to 5.5.
After the patch is merged is merged to 10.5, I would add the test case in the description to the regression suite.
|
|
Added the test case to 10.4 with rowid filter
|