Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
Description
Probably the setting set global InnoDB.optimizer_disk_read_ratio=0 is invalid, but the problem is that the test case below is derived directly from check_costs.pl script – when it's run on a debug build, it fails on the assertion.
So, something should change – either the script shouldn't be using it, or the assertion should be relaxed, or the invalid setting should be forbidden and/or automatically adjusted to the closest valid value.
--source include/have_innodb.inc
|
|
set global InnoDB.optimizer_disk_read_ratio=0; |
create table check_costs_InnoDB ( |
`l_orderkey` int(11) NOT NULL, |
`l_partkey` int(11) DEFAULT NULL, |
`l_suppkey` int(11) DEFAULT NULL, |
`l_linenumber` int(11) NOT NULL, |
`l_extra` int(11) NOT NULL, |
`l_quantity` double DEFAULT NULL, |
`l_extendedprice` double DEFAULT NULL, |
`l_discount` double DEFAULT NULL, |
`l_tax` double DEFAULT NULL, |
`l_returnflag` char(1) DEFAULT NULL, |
`l_linestatus` char(1) DEFAULT NULL, |
`l_shipDATE` date DEFAULT NULL, |
`l_commitDATE` date DEFAULT NULL, |
`l_receiptDATE` date DEFAULT NULL, |
`l_shipinstruct` char(25) DEFAULT NULL, |
`l_shipmode` char(10) DEFAULT NULL, |
`l_comment` varchar(44) DEFAULT NULL, |
PRIMARY KEY (`l_orderkey`), |
UNIQUE (`l_linenumber`), |
UNIQUE (`l_extra`) , |
KEY `l_suppkey` (l_suppkey, l_partkey), |
KEY `long_suppkey` (l_partkey, l_suppkey, l_linenumber, l_extra) ) |
ENGINE= InnoDB;
|
|
explain select count(*) from test.check_costs_InnoDB force index (l_suppkey) where l_suppkey >= 0 and l_partkey >=0; |
bb-11.0 dd26d4f43 |
mariadbd: /data/src/bb-11.0/sql/handler.h:2832: double Cost_estimate::total_cost() const: Assertion `avg_io_cost != 0.0 || index_cost.io + row_cost.io == 0' failed.
|
230102 23:23:47 [ERROR] mysqld got signal 6 ;
|
|
#7 0x00007f20f8d6d662 in __GI___assert_fail (assertion=0x560ce1624028 "avg_io_cost != 0.0 || index_cost.io + row_cost.io == 0", file=0x560ce1624060 "/data/src/bb-11.0/sql/handler.h", line=2832, function=0x560ce1624080 "double Cost_estimate::total_cost() const") at assert.c:101
|
#8 0x0000560cdfb2ed47 in Cost_estimate::total_cost (this=0x7f20ec2d55f0) at /data/src/bb-11.0/sql/handler.h:2832
|
#9 0x0000560cdfaffaac in get_key_scans_params (param=0x7f20ec2d5890, tree=0x7f20c0067778, index_read_must_be_used=false, for_range_access=true, read_time=0.0059734050000000002, limit=18446744073709551615, using_table_scan=true) at /data/src/bb-11.0/sql/opt_range.cc:7771
|
#10 0x0000560cdfae937c in SQL_SELECT::test_quick_select (this=0x7f20c0063d58, thd=0x7f20c0000db8, 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:2975
|
#11 0x0000560cdfe91865 in get_quick_record_count (thd=0x7f20c0000db8, select=0x7f20c0063d58, table=0x7f20c012b878, keys=0x7f20c00188b8, limit=18446744073709551615) at /data/src/bb-11.0/sql/sql_select.cc:5169
|
#12 0x0000560cdfe961d8 in make_join_statistics (join=0x7f20c0017d30, tables_list=..., keyuse_array=0x7f20c0018088) at /data/src/bb-11.0/sql/sql_select.cc:5926
|
#13 0x0000560cdfe80014 in JOIN::optimize_inner (this=0x7f20c0017d30) at /data/src/bb-11.0/sql/sql_select.cc:2568
|
#14 0x0000560cdfe7b67a in JOIN::optimize (this=0x7f20c0017d30) at /data/src/bb-11.0/sql/sql_select.cc:1899
|
#15 0x0000560cdfe91295 in mysql_select (thd=0x7f20c0000db8, tables=0x7f20c0015d08, fields=..., conds=0x7f20c0016bf0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525828, result=0x7f20c0017690, unit=0x7f20c0005210, select_lex=0x7f20c0015608) at /data/src/bb-11.0/sql/sql_select.cc:5111
|
#16 0x0000560cdff0be4f in mysql_explain_union (thd=0x7f20c0000db8, unit=0x7f20c0005210, result=0x7f20c0017690) at /data/src/bb-11.0/sql/sql_select.cc:29413
|
#17 0x0000560cdfdc6a0c in execute_sqlcom_select (thd=0x7f20c0000db8, all_tables=0x7f20c0015d08) at /data/src/bb-11.0/sql/sql_parse.cc:6201
|
#18 0x0000560cdfdb3b91 in mysql_execute_command (thd=0x7f20c0000db8, is_called_from_prepared_stmt=false) at /data/src/bb-11.0/sql/sql_parse.cc:3947
|
#19 0x0000560cdfdd02ae in mysql_parse (thd=0x7f20c0000db8, rawbuf=0x7f20c00154c0 "explain select count(*) from test.check_costs_InnoDB force index (l_suppkey) where l_suppkey >= 0 and l_partkey >=0", length=115, parser_state=0x7f20ec2d7390) at /data/src/bb-11.0/sql/sql_parse.cc:7998
|
#20 0x0000560cdfda7756 in dispatch_command (command=COM_QUERY, thd=0x7f20c0000db8, packet=0x7f20c000ba49 "", packet_length=115, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1894
|
#21 0x0000560cdfda4911 in do_command (thd=0x7f20c0000db8, blocking=true) at /data/src/bb-11.0/sql/sql_parse.cc:1407
|
#22 0x0000560ce011cd9a in do_handle_one_connection (connect=0x560ce56ee5e8, put_in_cache=true) at /data/src/bb-11.0/sql/sql_connect.cc:1415
|
#23 0x0000560ce011c790 in handle_one_connection (arg=0x560ce572b608) at /data/src/bb-11.0/sql/sql_connect.cc:1317
|
#24 0x0000560ce0a34e09 in pfs_spawn_thread (arg=0x560ce56ee158) at /data/src/bb-11.0/storage/perfschema/pfs.cc:2201
|
#25 0x00007f20f9249ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#26 0x00007f20f8e38aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Attachments
Issue Links
- is caused by
-
MDEV-26974 Improve selectivity and related costs in optimizer
- Closed