[MDEV-7316] Assertion `join->best_read < double(...) failed in bool greedy_search with optimizer_use_condition_selectivity>2, InnoDB Created: 2014-12-13  Updated: 2015-02-25  Resolved: 2015-02-25

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.15
Fix Version/s: 10.0.17

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: eits, optimizer, regression

Issue Links:
Relates
relates to MDEV-7413 optimizer_use_condition_selectivity >... Closed

 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



 Comments   
Comment by Sergei Petrunia [ 2014-12-13 ]

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.

Comment by Sergei Petrunia [ 2014-12-13 ]

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.

Comment by Sergei Petrunia [ 2014-12-15 ]

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.

Comment by Sergei Petrunia [ 2014-12-15 ]

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.

Comment by Sergei Petrunia [ 2015-02-20 ]

A possible fix: http://lists.askmonty.org/pipermail/commits/2015-February/007456.html . igor, please review.

Comment by Sergei Petrunia [ 2015-02-25 ]

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.

Comment by Sergei Petrunia [ 2015-02-25 ]

Actually it has been merged already. After 10.0.16

Generated at Thu Feb 08 07:18:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.