[MDEV-22160] SIGSEGV in st_join_table::save_explain_data on SELECT - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.5
Fix Version/s: 10.4.13, 10.5.3
Component/s: Optimizer
Labels:
- not-10.1
- not-10.2
- not-10.3
- not-10.4
- regression

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

MDEV-19255 Server crash in st_join_table::save_explain_data or assertion `sel->quick' failure in JOIN::make_range_rowid_filters upon query with rowid_filter=ON

Closed

MDEV-19720 Server crash in st_join_table::save_explain_data or Assertion `sel->quick' failed in JOIN::make_range_rowid_filters with rowid_filter=on

Closed

MDEV-21932 A fast plan with ROR index-merge is ignored when 'index_merge_sort_union=off'

Closed

MDEV-22191 Range access is not picked when index_merge_sort_union is turned off

Closed

MDEV-30651 Crash after killing query while it is processed by test_quick_select

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2020-04-07 16:23

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

Alice Sherepa added a comment - 2020-04-07 16:23 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

Varun Gupta (Inactive) added a comment - 2020-04-08 11:54

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.

Varun Gupta (Inactive) added a comment - 2020-04-08 11:54 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.

Varun Gupta (Inactive) added a comment - 2020-04-08 12:24

Also look at this

https://jira.mariadb.org/browse/MDEV-22191?focusedCommentId=149413&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-149413

Varun Gupta (Inactive) added a comment - 2020-04-08 12:24 Also look at this https://jira.mariadb.org/browse/MDEV-22191?focusedCommentId=149413&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-149413

Varun Gupta (Inactive) added a comment - 2020-04-08 19:28

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

Varun Gupta (Inactive) added a comment - 2020-04-08 19:28 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

Varun Gupta (Inactive) added a comment - 2020-04-08 19:30

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;

Varun Gupta (Inactive) added a comment - 2020-04-08 19:30 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;

Varun Gupta (Inactive) added a comment - 2020-04-08 19:34 - edited

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]

Varun Gupta (Inactive) added a comment - 2020-04-08 19:34 - edited 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]

Varun Gupta (Inactive) added a comment - 2020-04-08 19:42

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.

Varun Gupta (Inactive) added a comment - 2020-04-08 19:42 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.

Varun Gupta (Inactive) added a comment - 2020-04-08 19:49

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.

Varun Gupta (Inactive) added a comment - 2020-04-08 19:49 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.

Igor Babaev (Inactive) added a comment - 2020-04-09 00:32

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.

Igor Babaev (Inactive) added a comment - 2020-04-09 00:32 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.

Varun Gupta (Inactive) added a comment - 2020-04-13 15:01

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.

Varun Gupta (Inactive) added a comment - 2020-04-13 15:01 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.

Varun Gupta (Inactive) added a comment - 2020-04-29 14:55

Added the test case to 10.4 with rowid filter

Varun Gupta (Inactive) added a comment - 2020-04-29 14:55 Added the test case to 10.4 with rowid filter

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Roel Van de Paar

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2020-04-06 08:49

Updated:: 2024-05-27 22:11

Resolved:: 2020-04-29 14:55

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server