Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.5
Description
USE test;
|
SET @@SESSION.OPTIMIZER_SWITCH="index_merge_sort_union=OFF";
|
CREATE TABLE t (a INT, b INT, INDEX(a), INDEX(b)) ENGINE=InnoDB;
|
INSERT INTO t VALUES (0,0),(0,0);
|
SELECT * FROM t WHERE a>='2000-01-01 00:00:00' AND b='2030-01-01 00:00:00';
|
Leads to:
|
10.5.3 e8351934b68d6d3ee273292eaa2ece203bb2b846 |
Core was generated by `/data/MD020420-mariadb-10.5.3-linux-x86_64-opt/bin/mysqld --no-defaults --core-'.
|
Program terminated with signal SIGSEGV, Segmentation fault.
|
#0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=11)
|
at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
|
[Current thread is 1 (Thread 0x7f834e992700 (LWP 31862))]
|
(gdb) bt
|
#0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
|
#1 0x0000562fed3dcd47 in my_write_core (sig=sig@entry=11) at /data/10.5_opt/mysys/stacktrace.c:518
|
#2 0x0000562fecd9e87a in handle_fatal_signal (sig=11) at /data/10.5_opt/sql/signal_handler.cc:325
|
#3 <signal handler called>
|
#4 0x0000562fecbfc2c7 in st_join_table::save_explain_data (this=this@entry=0x7f831ac4b318, eta=eta@entry=0x7f831ac4c340, prefix_tables=prefix_tables@entry=0, distinct_arg=distinct_arg@entry=false, first_top_tab=first_top_tab@entry=0x7f831ac4b318) at /data/10.5_opt/sql/sql_select.cc:26518
|
#5 0x0000562fecbfe065 in JOIN::save_explain_data_intern (this=this@entry=0x7f831ac490f8, output=0x7f831ac49bd0, need_tmp_table_arg=<optimized out>, need_order_arg=<optimized out>, distinct_arg=distinct_arg@entry=false, message=<optimized out>) at /data/10.5_opt/sql/sql_select.cc:27072
|
#6 0x0000562fecbfe35a in JOIN::save_explain_data (this=this@entry=0x7f831ac490f8, output=0x7f831ac49bd0, can_overwrite=can_overwrite@entry=false, need_tmp_table=<optimized out>, need_order=<optimized out>, distinct=<optimized out>) at /data/10.5_opt/sql/sql_select.cc:4203
|
#7 0x0000562fecbfe433 in JOIN::build_explain (this=this@entry=0x7f831ac490f8) at /data/10.5_opt/sql/sql_select.cc:1559
|
#8 0x0000562fecc063f2 in JOIN::optimize (this=this@entry=0x7f831ac490f8) at /data/10.5_opt/sql/sql_select.cc:1612
|
#9 0x0000562fecc06551 in mysql_select (thd=thd@entry=0x7f831ac12018, tables=0x7f831ac47728, fields=..., conds=0x7f831ac486a8, og_num=<optimized out>, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f831ac490d0, unit=0x7f831ac15e60, select_lex=0x7f831ac47130) at /data/10.5_opt/sql/sql_select.cc:4654
|
#10 0x0000562fecc06f61 in handle_select (thd=thd@entry=0x7f831ac12018, lex=lex@entry=0x7f831ac15d98, result=result@entry=0x7f831ac490d0, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/10.5_opt/sql/sql_select.cc:417
|
#11 0x0000562fecbad881 in execute_sqlcom_select (thd=thd@entry=0x7f831ac12018, all_tables=0x7f831ac47728) at /data/10.5_opt/sql/sql_parse.cc:6168
|
#12 0x0000562fecba8b23 in mysql_execute_command (thd=thd@entry=0x7f831ac12018) at /data/10.5_opt/sql/sql_parse.cc:3901
|
#13 0x0000562fecbb0a6c in mysql_parse (thd=thd@entry=0x7f831ac12018, rawbuf=<optimized out>, length=74, parser_state=parser_state@entry=0x7f834e9914d0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/10.5_opt/sql/sql_parse.cc:7953
|
#14 0x0000562fecba58e0 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f831ac12018, packet=packet@entry=0x7f831ac3a019 "SELECT * FROM t WHERE a>='2000-01-01 00:00:00' AND b='2030-01-01 00:00:00'", packet_length=packet_length@entry=74, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/10.5_opt/sql/sql_parse.cc:1839
|
#15 0x0000562fecba3bff in do_command (thd=0x7f831ac12018) at /data/10.5_opt/sql/sql_parse.cc:1358
|
#16 0x0000562fecc9892e in do_handle_one_connection (connect=<optimized out>, connect@entry=0x7f834c4329b8, put_in_cache=put_in_cache@entry=true) at /data/10.5_opt/sql/sql_connect.cc:1422
|
#17 0x0000562fecc98ad4 in handle_one_connection (arg=arg@entry=0x7f834c4329b8) at /data/10.5_opt/sql/sql_connect.cc:1319
|
#18 0x0000562fed0049da in pfs_spawn_thread (arg=0x7f834c44b018) at /data/10.5_opt/storage/perfschema/pfs.cc:2201
|
#19 0x00007f834ddb96db in start_thread (arg=0x7f834e992700) at pthread_create.c:463
|
#20 0x00007f834d1b788f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Bug confirmed present in:
MariaDB: 10.5.3 (dbg), 10.5.3 (opt)
Bug confirmed not present in:
MariaDB: 10.1.45 (dbg), 10.1.45 (opt), 10.2.32 (dbg), 10.2.32 (opt), 10.3.23 (dbg), 10.3.23 (opt), 10.4.13 (dbg), 10.4.13 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.47 (dbg), 5.6.47 (opt), 5.7.29 (dbg), 5.7.29 (opt), 8.0.19 (dbg), 8.0.19 (opt)
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
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.
Now lets see what happens with rowid_filter turned 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:1636frame #1: 0x0000000100e91acc mysqld`JOIN::optimize_stage2(this=0x000062b000087bd8) at sql_select.cc:2310:7frame #2: 0x0000000100ea7207 mysqld`JOIN::optimize_inner(this=0x000062b000087bd8) at sql_select.cc:2286:9frame #3: 0x0000000100e91554 mysqld`JOIN::optimize(this=0x000062b000087bd8) at sql_select.cc:1606:10frame #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:19frame #5: 0x0000000100fac1d5 mysqld`mysql_explain_union(thd=0x000062b00007e288, unit=0x000062b0000822c0, result=0x000062b0000873c0) at sql_select.cc:27187:10frame #6: 0x0000000100d3e80a mysqld`execute_sqlcom_select(thd=0x000062b00007e288, all_tables=0x000062b0000859a8) at sql_parse.cc:6107:12frame #7: 0x0000000100d1d52e mysqld`mysql_execute_command(thd=0x000062b00007e288) at sql_parse.cc:3901:12frame #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:18Some investigation
(lldb) p tab->quick->index(uint) $1 = 1Index 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 = 0Index that the filter can use is index(a), so we try to build a quick_select on the range condition a > 0.