Details
Description
The problem appeared on 10.0 tree with the following revision:
revno: 4465 [merge]
revision-id: igor@askmonty.org-20141028233126-zt6nb214qnyqfuy8
parent: jplindst@mariadb.org-20141027145816-cuwsbq6d5yber97u
parent: igor@askmonty.org-20141028213331-qrdbjknh6i7nzsuf
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-10.0-trunk
timestamp: Tue 2014-10-28 16:31:26 -0700
message:
Merge
------------------------------------------------------------
revno: 4446.1.1
revision-id: igor@askmonty.org-20141028213331-qrdbjknh6i7nzsuf
parent: psergey@askmonty.org-20141016185808-n8cw47wxrn3a9yxm
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-10.0
timestamp: Tue 2014-10-28 14:33:31 -0700
message:
Fixed bug mdev-6325.
Field::selectivity should be set for all fields used in range conditions.
--source include/have_innodb.inc CREATE TABLE t1 (a VARCHAR(16), b INT, c INT, PRIMARY KEY(a), KEY(c)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('USAChinese',1,10),('USAEnglish',2,20),('USAFrench',3,30); CREATE TABLE t2 (i int) ENGINE=InnoDB; INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4); SET use_stat_tables=PREFERABLY, optimizer_use_condition_selectivity=3; ANALYZE TABLE t1, t2; SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL;
10.0/sql/sql_select.cc:6950: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed. 141213 22:37:26 [ERROR] mysqld got signal 6 ;
Stack trace from 10.0 revno 4543
#6 0x00007fbaa3e056f1 in *__GI___assert_fail (assertion=0xf11688 "join->best_read < double(1.79769313486231570815e+308L)", file=<optimized out>, line=6950, function=0xf14680 "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:81 #7 0x00000000006c17e5 in greedy_search (join=0x7fba91229bd0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=3) at 10.0/sql/sql_select.cc:6950 #8 0x00000000006c0d6f in choose_plan (join=0x7fba91229bd0, join_tables=3) at 10.0/sql/sql_select.cc:6519 #9 0x00000000006ba73d in make_join_statistics (join=0x7fba91229bd0, tables_list=..., conds=0x7fba912297f0, keyuse_array=0x7fba91229ed8) at 10.0/sql/sql_select.cc:4038 #10 0x00000000006b13a3 in JOIN::optimize_inner (this=0x7fba91229bd0) at 10.0/sql/sql_select.cc:1339 #11 0x00000000006b033a in JOIN::optimize (this=0x7fba91229bd0) at 10.0/sql/sql_select.cc:1024 #12 0x00000000006b7ebf in mysql_select (thd=0x7fba9d9ee070, rref_pointer_array=0x7fba9d9f26e0, tables=0x7fba91341300, wild_num=1, fields=..., conds=0x7fba912297f0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fba91229bb0, unit=0x7fba9d9f1d80, select_lex=0x7fba9d9f2468) at 10.0/sql/sql_select.cc:3294 #13 0x00000000006ae549 in handle_select (thd=0x7fba9d9ee070, lex=0x7fba9d9f1cb8, result=0x7fba91229bb0, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:373 #14 0x0000000000683244 in execute_sqlcom_select (thd=0x7fba9d9ee070, all_tables=0x7fba91341300) at 10.0/sql/sql_parse.cc:5261 #15 0x000000000067b542 in mysql_execute_command (thd=0x7fba9d9ee070) at 10.0/sql/sql_parse.cc:2545 #16 0x00000000006859bd in mysql_parse (thd=0x7fba9d9ee070, rawbuf=0x7fba91341088 "SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL", length=95, parser_state=0x7fbaa5fc5610) at 10.0/sql/sql_parse.cc:6407 #17 0x00000000006787b2 in dispatch_command (command=COM_QUERY, thd=0x7fba9d9ee070, packet=0x7fba986b9071 "SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL", packet_length=95) at 10.0/sql/sql_parse.cc:1299 #18 0x0000000000677b57 in do_command (thd=0x7fba9d9ee070) at 10.0/sql/sql_parse.cc:996 #19 0x0000000000794877 in do_handle_one_connection (thd_arg=0x7fba9d9ee070) at 10.0/sql/sql_connect.cc:1375 #20 0x00000000007945ca in handle_one_connection (arg=0x7fba9d9ee070) at 10.0/sql/sql_connect.cc:1289 #21 0x0000000000ccda5e in pfs_spawn_thread (arg=0x7fba986b10f0) at 10.0/storage/perfschema/pfs.cc:1860 #22 0x00007fbaa5bffb50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #23 0x00007fbaa3eb620d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-7413 optimizer_use_condition_selectivity > 2 crashes 10.0.15+maria-1~wheezy
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The first call for table_cond_selectivity(s=t1, idx=0).
Selectivity becomes infinite here:
sel /= table->field[fldno]->cond_selectivity;
The field in question is t1.c.
The WHERE clause has:
c IS NULL
This equality is used to construct ref access. Then, table_cond_selectivity attempts to discount the selectivity of "c IS NULL". This is where it divides by zero.
The data in table t1:
INSERT INTO t1 VALUES
('USAChinese',1,10),
('USAEnglish',2,20),
('USAFrench', 3,30);
There are 3 rows in total, 0 rows that have t1.c=NULL.
get_column_range_cardinality(field="c") executes this code:
if (nulls_incl)
{
/* This is null single point range */
res= col_nulls;
col_nulls=0, which is true. But then, we attempt to divide by zero to discount the selectivity.
A possible fix: http://lists.askmonty.org/pipermail/commits/2015-February/007456.html . Igor Babaev, please review.
A different fix for this bug has actually been pushed into the 10.0 bzr tree:
revno: 4544.1.1
revision-id: igor@askmonty.org-20150206040908-23e1eht6sj59qiz3
parent: knielsen@knielsen-hq.org-20141218105908-7n3szfd3xb5bm8gs
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-10.0
timestamp: Thu 2015-02-05 20:09:08 -0800
message:
Fixed bug mdev-7316.
The function table_cond_selectivity() should take into account that condition selectivity
for some fields can be set to 0.
this fix hasn't been merged into the git tree, yet.
Actually it has been merged already. After 10.0.16
The crash doesn't repeat if one re-runs the query. However, running the whole example reliably produces the crash.
The crash is caused by the first call to table_cond_selectivity() returning inf.