#13 0x000055fbbfc39d1e in get_best_group_min_max (param=param@entry=0x7ffb5d6360e0, tree=tree@entry=0x621000111d90, read_time=<optimized out>) at /data/src/bb-11.0/sql/opt_range.cc:14539
#14 0x000055fbbfc43600 in SQL_SELECT::test_quick_select (this=this@entry=0x6290000e9da8, thd=thd@entry=0x62b00007e218, keys_to_use=..., prev_tables=prev_tables@entry=0, limit=limit@entry=18446744073709551615, force_quick_range=force_quick_range@entry=false, ordered_output=<optimized out>, remove_false_parts_of_where=<optimized out>, only_single_index_range_scan=false) at /data/src/bb-11.0/sql/opt_range.cc:3072
#15 0x000055fbc01925f9 in get_quick_record_count (thd=<optimized out>, select=select@entry=0x6290000e9da8, table=0x61900008b698, keys=keys@entry=0x62900026c3b0, limit=<optimized out>) at /data/src/bb-11.0/sql/sql_select.cc:5172
#16 0x000055fbc02c2a1d in make_join_statistics (join=join@entry=0x6290000e8d10, tables_list=..., keyuse_array=keyuse_array@entry=0x6290000e9068) at /data/src/bb-11.0/sql/sql_select.cc:5929
#17 0x000055fbc02dc243 in JOIN::optimize_inner (this=this@entry=0x6290000e8d10) at /data/src/bb-11.0/sql/sql_select.cc:2571
#18 0x000055fbc02dcaa5 in JOIN::optimize (this=this@entry=0x6290000e8d10) at /data/src/bb-11.0/sql/sql_select.cc:1899
#20 0x000055fbc02df433 in handle_select (thd=thd@entry=0x62b00007e218, lex=lex@entry=0x62b000082588, result=result@entry=0x6290000e8ce0, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/bb-11.0/sql/sql_select.cc:609
#21 0x000055fbc0061d33 in execute_sqlcom_select (thd=thd@entry=0x62b00007e218, all_tables=<optimized out>) at /data/src/bb-11.0/sql/sql_parse.cc:6263
#22 0x000055fbc0094427 in mysql_execute_command (thd=thd@entry=0x62b00007e218, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /data/src/bb-11.0/sql/sql_parse.cc:3947
#23 0x000055fbc00a937b in mysql_parse (thd=thd@entry=0x62b00007e218, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7ffb5d638a60) at /data/src/bb-11.0/sql/sql_parse.cc:7998
#24 0x000055fbc00b1409 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x62b00007e218, packet=packet@entry=0x629000253219 "SELECT DISTINCT a FROM tm WHERE a > 50", packet_length=packet_length@entry=38, blocking=blocking@entry=true) at /data/src/bb-11.0/sql/sql_parse.cc:1894
#25 0x000055fbc00b9724 in do_command (thd=0x62b00007e218, blocking=blocking@entry=true) at /data/src/bb-11.0/sql/sql_parse.cc:1407
#26 0x000055fbc067eb87 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x6080000021b8, put_in_cache=put_in_cache@entry=true) at /data/src/bb-11.0/sql/sql_connect.cc:1415
#27 0x000055fbc067f49c in handle_one_connection (arg=arg@entry=0x6080000021b8) at /data/src/bb-11.0/sql/sql_connect.cc:1317
#28 0x000055fbc186a2ae in pfs_spawn_thread (arg=0x617000004698) at /data/src/bb-11.0/storage/perfschema/pfs.cc:2201
#29 0x00007ffb66c85ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
#30 0x00007ffb66872aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
Attachments
Issue Links
is caused by
MDEV-26974Improve selectivity and related costs in optimizer
Ok the problem here was that it was trying to construct loose scan access to the merge table.
The table reported table->stat_records()=0 and it has arrived at the conclusion that loose scan will read 0 groups.
cost_group_min_max() made this call with num_groups=0:
Sergei Petrunia
added a comment - - edited Ok the problem here was that it was trying to construct loose scan access to the merge table.
The table reported table->stat_records()=0 and it has arrived at the conclusion that loose scan will read 0 groups.
cost_group_min_max() made this call with num_groups=0:
*read_cost= file->cost(file->ha_keyread_and_compare_time(keyno,
(ulong) num_groups,
num_groups,
io_cost));
which triggered an assert.
#9 0x00007f272d653df2 in __GI___assert_fail (assertion=0x555d7bd3a1a7 "ranges > 0", file=0x555d7bd38bb8 "/data/src/bb-11.0/sql/handler.cc", line=3321, function=0x555d7bd3a1b8 "virtual IO_AND_CPU_COST handler::keyread_time(uint, ulong, ha_rows, ulonglong)") at ./assert/assert.c:101
#10 0x0000555d7a9621ff in handler::keyread_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/sql/handler.cc:3321
#11 0x0000555d7ba1d3ac in ha_myisammrg::keyread_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/storage/myisammrg/ha_myisammrg.cc:357
#12 0x0000555d7a962474 in handler::ha_keyread_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/sql/handler.cc:3366
#13 0x0000555d79f82d14 in handler::ha_keyread_and_compare_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/sql/handler.h:3785
#14 0x0000555d79f77263 in cost_group_min_max (table=0x7f2718102618, index_info=0x7f271812bf68, used_key_parts=1, group_key_parts=1, range_tree=0x7f2718078ed8, index_tree=0x7f2718078f58, quick_prefix_records=0, have_min=false, have_max=false, read_cost=0x7f27280f1b08, out_records=0x7f27280f1cf0) at /data/src/bb-11.0/sql/opt_range.cc:15289
#15 0x0000555d79f73978 in get_best_group_min_max (param=0x7f27280f3840, tree=0x7f2718078ed8, read_time=0.0067193540000000003) at /data/src/bb-11.0/sql/opt_range.cc:14541
#16 0x0000555d79f3aeed in SQL_SELECT::test_quick_select (this=0x7f2718075d50, thd=0x7f2718000dc8, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true, only_single_index_range_scan=false) at /data/src/bb-11.0/sql/opt_range.cc:3072
#17 0x0000555d7a2f5cfe in get_quick_record_count (thd=0x7f2718000dc8, select=0x7f2718075d50, table=0x7f2718102618, keys=0x7f27180749c0, limit=18446744073709551615) at /data/src/bb-11.0/sql/sql_select.cc:5166
#18 0x0000555d7a2fa615 in make_join_statistics (join=0x7f2718073650, tables_list=..., keyuse_array=0x7f27180739b0) at /data/src/bb-11.0/sql/sql_select.cc:5916
#19 0x0000555d7a2e408b in JOIN::optimize_inner (this=0x7f2718073650) at /data/src/bb-11.0/sql/sql_select.cc:2569
#20 0x0000555d7a2df65c in JOIN::optimize (this=0x7f2718073650) at /data/src/bb-11.0/sql/sql_select.cc:1897
#21 0x0000555d7a2f56f3 in mysql_select (thd=0x7f2718000dc8, tables=0x7f27180172d8, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525825, result=0x7f2718019210, unit=0x7f2718005210, select_lex=0x7f27180155a8) at /data/src/bb-11.0/sql/sql_select.cc:5108
#22 0x0000555d7a2d5192 in handle_select (thd=0x7f2718000dc8, lex=0x7f2718005138, result=0x7f2718019210, setup_tables_done_option=0) at /data/src/bb-11.0/sql/sql_select.cc:608
#23 0x0000555d7a227a59 in execute_sqlcom_select (thd=0x7f2718000dc8, all_tables=0x7f27180172d8) at /data/src/bb-11.0/sql/sql_parse.cc:6265
#24 0x0000555d7a214018 in mysql_execute_command (thd=0x7f2718000dc8, is_called_from_prepared_stmt=false) at /data/src/bb-11.0/sql/sql_parse.cc:3949
#25 0x0000555d7a230e1f in mysql_parse (thd=0x7f2718000dc8, rawbuf=0x7f27180154c0 "SELECT DISTINCT a FROM (SELECT * FROM tm WHERE a iS NOT NULL) AS sq", length=67, parser_state=0x7f27280f5370) at /data/src/bb-11.0/sql/sql_parse.cc:8000
#26 0x0000555d7a2073d3 in dispatch_command (command=COM_QUERY, thd=0x7f2718000dc8, packet=0x7f271800ba49 "SELECT DISTINCT a FROM (SELECT * FROM tm WHERE a iS NOT NULL) AS sq", packet_length=67, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1894
#27 0x0000555d7a204518 in do_command (thd=0x7f2718000dc8, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1407
#28 0x0000555d7a592887 in do_handle_one_connection (connect=0x555d806ff3c8, put_in_cache=true) at /data/src/bb-11.0/sql/sql_connect.cc:1416
#29 0x0000555d7a5922b2 in handle_one_connection (arg=0x555d8073f068) at /data/src/bb-11.0/sql/sql_connect.cc:1318
#30 0x0000555d7aee6c91 in pfs_spawn_thread (arg=0x555d806fef38) at /data/src/bb-11.0/storage/perfschema/pfs.cc:2201
#31 0x00007f272d6a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#32 0x00007f272d72866c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
Elena Stepanova
added a comment -
CREATE TABLE t1 (a INT , KEY (a)) ENGINE=MyISAM;
CREATE TABLE t2 (a INT , KEY (a)) ENGINE=MyISAM;
CREATE TABLE tm (a INT , KEY (a)) ENGINE=MERGE UNION = (t1, t2) INSERT_METHOD= FIRST ;
ANALYZE TABLE tm PERSISTENT FOR ALL ;
SELECT DISTINCT a FROM ( SELECT * FROM tm WHERE a iS NOT NULL ) AS sq;
# Cleanup
DROP TABLE tm, t1, t2;
bb-11.0 527cc3e2c5
mariadbd: /data/src/bb-11.0/sql/handler.cc:3321: virtual IO_AND_CPU_COST handler::keyread_time(uint, ulong, ha_rows, ulonglong): Assertion `ranges > 0' failed.
230208 16:17:08 [ERROR] mysqld got signal 6 ;
#9 0x00007f272d653df2 in __GI___assert_fail (assertion=0x555d7bd3a1a7 "ranges > 0", file=0x555d7bd38bb8 "/data/src/bb-11.0/sql/handler.cc", line=3321, function=0x555d7bd3a1b8 "virtual IO_AND_CPU_COST handler::keyread_time(uint, ulong, ha_rows, ulonglong)") at ./assert/assert.c:101
#10 0x0000555d7a9621ff in handler::keyread_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/sql/handler.cc:3321
#11 0x0000555d7ba1d3ac in ha_myisammrg::keyread_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/storage/myisammrg/ha_myisammrg.cc:357
#12 0x0000555d7a962474 in handler::ha_keyread_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/sql/handler.cc:3366
#13 0x0000555d79f82d14 in handler::ha_keyread_and_compare_time (this=0x7f271812c280, index=0, ranges=0, rows=0, blocks=0) at /data/src/bb-11.0/sql/handler.h:3785
#14 0x0000555d79f77263 in cost_group_min_max (table=0x7f2718102618, index_info=0x7f271812bf68, used_key_parts=1, group_key_parts=1, range_tree=0x7f2718078ed8, index_tree=0x7f2718078f58, quick_prefix_records=0, have_min=false, have_max=false, read_cost=0x7f27280f1b08, out_records=0x7f27280f1cf0) at /data/src/bb-11.0/sql/opt_range.cc:15289
#15 0x0000555d79f73978 in get_best_group_min_max (param=0x7f27280f3840, tree=0x7f2718078ed8, read_time=0.0067193540000000003) at /data/src/bb-11.0/sql/opt_range.cc:14541
#16 0x0000555d79f3aeed in SQL_SELECT::test_quick_select (this=0x7f2718075d50, thd=0x7f2718000dc8, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true, only_single_index_range_scan=false) at /data/src/bb-11.0/sql/opt_range.cc:3072
#17 0x0000555d7a2f5cfe in get_quick_record_count (thd=0x7f2718000dc8, select=0x7f2718075d50, table=0x7f2718102618, keys=0x7f27180749c0, limit=18446744073709551615) at /data/src/bb-11.0/sql/sql_select.cc:5166
#18 0x0000555d7a2fa615 in make_join_statistics (join=0x7f2718073650, tables_list=..., keyuse_array=0x7f27180739b0) at /data/src/bb-11.0/sql/sql_select.cc:5916
#19 0x0000555d7a2e408b in JOIN::optimize_inner (this=0x7f2718073650) at /data/src/bb-11.0/sql/sql_select.cc:2569
#20 0x0000555d7a2df65c in JOIN::optimize (this=0x7f2718073650) at /data/src/bb-11.0/sql/sql_select.cc:1897
#21 0x0000555d7a2f56f3 in mysql_select (thd=0x7f2718000dc8, tables=0x7f27180172d8, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525825, result=0x7f2718019210, unit=0x7f2718005210, select_lex=0x7f27180155a8) at /data/src/bb-11.0/sql/sql_select.cc:5108
#22 0x0000555d7a2d5192 in handle_select (thd=0x7f2718000dc8, lex=0x7f2718005138, result=0x7f2718019210, setup_tables_done_option=0) at /data/src/bb-11.0/sql/sql_select.cc:608
#23 0x0000555d7a227a59 in execute_sqlcom_select (thd=0x7f2718000dc8, all_tables=0x7f27180172d8) at /data/src/bb-11.0/sql/sql_parse.cc:6265
#24 0x0000555d7a214018 in mysql_execute_command (thd=0x7f2718000dc8, is_called_from_prepared_stmt=false) at /data/src/bb-11.0/sql/sql_parse.cc:3949
#25 0x0000555d7a230e1f in mysql_parse (thd=0x7f2718000dc8, rawbuf=0x7f27180154c0 "SELECT DISTINCT a FROM (SELECT * FROM tm WHERE a iS NOT NULL) AS sq", length=67, parser_state=0x7f27280f5370) at /data/src/bb-11.0/sql/sql_parse.cc:8000
#26 0x0000555d7a2073d3 in dispatch_command (command=COM_QUERY, thd=0x7f2718000dc8, packet=0x7f271800ba49 "SELECT DISTINCT a FROM (SELECT * FROM tm WHERE a iS NOT NULL) AS sq", packet_length=67, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1894
#27 0x0000555d7a204518 in do_command (thd=0x7f2718000dc8, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1407
#28 0x0000555d7a592887 in do_handle_one_connection (connect=0x555d806ff3c8, put_in_cache=true) at /data/src/bb-11.0/sql/sql_connect.cc:1416
#29 0x0000555d7a5922b2 in handle_one_connection (arg=0x555d8073f068) at /data/src/bb-11.0/sql/sql_connect.cc:1318
#30 0x0000555d7aee6c91 in pfs_spawn_thread (arg=0x555d806fef38) at /data/src/bb-11.0/storage/perfschema/pfs.cc:2201
#31 0x00007f272d6a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#32 0x00007f272d72866c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
MDEV-30525: Assertion `ranges > 0' fails in IO_AND_CPU_COST
Part #2: fix the case where table->stat_records()=1 (due to EITS
statistics), but the range returns rows=0.
Sergei Petrunia
added a comment - Pushed another commit to fix the second case:
commit 62531b0df82b98055d0c29b3a9bdb2154657c3b0 (HEAD -> bb-11.0, origin/bb-11.0)
Author: Sergei Petrunia <sergey@mariadb.com>
Date: Thu Feb 9 12:52:20 2023 +0300
MDEV-30525: Assertion `ranges > 0' fails in IO_AND_CPU_COST
Part #2: fix the case where table->stat_records()=1 (due to EITS
statistics), but the range returns rows=0.
Ok the problem here was that it was trying to construct loose scan access to the merge table.
The table reported table->stat_records()=0 and it has arrived at the conclusion that loose scan will read 0 groups.
cost_group_min_max() made this call with num_groups=0:
*read_cost= file->cost(file->ha_keyread_and_compare_time(keyno,
(ulong) num_groups,
num_groups,
io_cost));
which triggered an assert.