[MDEV-18980] Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed in greedy_search Created: 2019-03-20  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-19132 Strange combination of JOIN with NULL... Confirmed

 Description   

Repeatable on 10.4 with MyIsam, not with InnoDB.

CREATE TABLE t1 (c2 varchar(10), i1 int, KEY (c2), KEY (i1)) ENGINE=MyISAM;
 
 
 
CREATE TABLE t2 (i1 int, c1 varchar(10), c2 varchar(10) , pk int, KEY (i1), KEY (c1(10)), KEY (c2)) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (-174260224,'ixqy','NPCSX',1);
 
 
 
SELECT 1
 
FROM t2 AS tt22
 
RIGHT JOIN t2
 
LEFT JOIN t1 AS tt1
 
LEFT JOIN t1
 
JOIN t2 AS tt2 ON t1.c2 = tt2.c2 ON tt1.i1 = t1.i1 ON t2.pk = tt1.i1 ON tt22.c1 = tt2.c1;
 
 
 
drop table t1,t2;

10.4 514b305dfb2839c619a

 
/10.4/sql/sql_select.cc:8555: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed.
 
 
 
#5  0x00007f878e55202a in __GI_abort () at abort.c:89
 
#6  0x00007f878e548bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x563754d11510 "join->best_read < double(1.79769313486231570815e+308L)", file=file@entry=0x563754d10398 "/10.4/sql/sql_select.cc", line=line@entry=8555, function=function@entry=0x563754d14840 <greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int)::__PRETTY_FUNCTION__> "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:92
 
#7  0x00007f878e548c82 in __GI___assert_fail (assertion=0x563754d11510 "join->best_read < double(1.79769313486231570815e+308L)", file=0x563754d10398 "/10.4/sql/sql_select.cc", line=8555, function=0x563754d14840 <greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int)::__PRETTY_FUNCTION__> "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:101
 
#8  0x00005637540d88ee in greedy_search (join=0x7f873006c440, remaining_tables=5, search_depth=62, prune_level=1, use_cond_selectivity=4) at /10.4/sql/sql_select.cc:8555
 
#9  0x00005637540d7c44 in choose_plan (join=0x7f873006c440, join_tables=5) at /10.4/sql/sql_select.cc:8110
 
#10 0x00005637540cfe47 in make_join_statistics (join=0x7f873006c440, tables_list=..., keyuse_array=0x7f873006c760) at /10.4/sql/sql_select.cc:5465
 
#11 0x00005637540c3eca in JOIN::optimize_inner (this=0x7f873006c440) at /10.4/sql/sql_select.cc:2174
 
#12 0x00005637540c19d6 in JOIN::optimize (this=0x7f873006c440) at /10.4/sql/sql_select.cc:1551
 
#13 0x00005637540cc9fe in mysql_select (thd=0x7f8730000a98, tables=0x7f8730039da0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f873006c3e0, unit=0x7f87300049d0, select_lex=0x7f873003af10) at /10.4/sql/sql_select.cc:4571
 
#14 0x00005637540bcede in handle_select (thd=0x7f8730000a98, lex=0x7f8730004908, result=0x7f873006c3e0, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:424
 
#15 0x00005637540856c4 in execute_sqlcom_select (thd=0x7f8730000a98, all_tables=0x7f8730039da0) at /10.4/sql/sql_parse.cc:6601
 
#16 0x000056375407a79e in mysql_execute_command (thd=0x7f8730000a98) at /10.4/sql/sql_parse.cc:3889
 
#17 0x000056375408961c in mysql_parse (thd=0x7f8730000a98, rawbuf=0x7f873008b580 "SELECT 1\nFROM t2 AS tt22\nRIGHT JOIN t2\nLEFT JOIN t1 AS tt1\nLEFT JOIN t1\nJOIN t2 AS tt2 ON t1.c2 = tt2.c2 ON tt1.i1 = t1.i1 ON t2.pk = tt1.i1 ON tt22.c1 = tt2.c1", length=160, parser_state=0x7f8788072ff0, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:8154
 
#18 0x00005637540748ec in dispatch_command (command=COM_QUERY, thd=0x7f8730000a98, packet=0x7f8730009da9 "SELECT 1\nFROM t2 AS tt22\nRIGHT JOIN t2\nLEFT JOIN t1 AS tt1\nLEFT JOIN t1\nJOIN t2 AS tt2 ON t1.c2 = tt2.c2 ON tt1.i1 = t1.i1 ON t2.pk = tt1.i1 ON tt22.c1 = tt2.c1", packet_length=160, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1832
 
#19 0x0000563754073080 in do_command (thd=0x7f8730000a98) at /10.4/sql/sql_parse.cc:1365
 
#20 0x00005637541f2f1b in do_handle_one_connection (connect=0x5637579b0938) at /10.4/sql/sql_connect.cc:1398
 
#21 0x00005637541f2c59 in handle_one_connection (arg=0x5637579b0938) at /10.4/sql/sql_connect.cc:1301
 
#22 0x0000563754b81227 in pfs_spawn_thread (arg=0x5637579651c8) at /10.4/storage/perfschema/pfs.cc:1862
 
#23 0x00007f878f3916ba in start_thread (arg=0x7f8788074700) at pthread_create.c:333
 
#24 0x00007f878e62241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Igor Babaev [ 2019-04-20 ]

Sergey,
After a thorough investigation I assign this bug to you, because it is caused by your code that introduced the function mark_join_nest_as_const(). Unfortunately this function ignores join->cur_embedding_map. As a result we have problems with check_interleaving_with_nj.
I replaced the query for:

SELECT 1 FROM t2 LEFT JOIN (t1 AS tt1 LEFT JOIN (t1 JOIN t2 AS tt2 ON t1.c2 = tt2.c2) ON tt1.i1 = t1.i1) ON t2.pk = tt1.i1 LEFT JOIN t2 AS tt22 ON tt22.c1 = tt2.c1;

This query is equivalent to the old one.
t2 is marked as system table;
then t1, tt2 are marked as const tables with all nulls produced by impossible ON;
then we go to tt1 and at this moment join->cur_embedding_map is empty.
Then it adds the map for tt1 to it. Simultaneously it should have added maps for t1, tt2, but it does not do it. As result next call of check_interleaving_with_nj() does not allow to add the only remaining table tt22.

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Generated at Thu Feb 08 08:48:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.