[MDEV-3657] LP:602574 - RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < 1.7976931348623157e+308' failed. Created: 2010-07-07  Updated: 2015-02-02  Resolved: 2012-10-04

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

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug602574.xml    

 Description   

When executing the following query:

SELECT SQL_SMALL_RESULT table1 . `col_date_key` AS field1 FROM ( CC AS table1 STRAIGHT_JOIN CC AS table2 ON (table2 . `col_varchar_key` = table1 . `col_varchar_nokey` ) ) WHERE ( ( SELECT COUNT( SUBQUERY1_t1 . `col_varchar_nokey` ) AS SUBQUERY1_field1 FROM C AS SUBQUERY1_t1 WHERE SUBQUERY1_t1 . `col_varchar_nokey` IN ( SELECT CHILD_SUBQUERY1_t2 . `col_varchar_key` AS CHILD_SUBQUERY1_field1 FROM ( C AS CHILD_SUBQUERY1_t1 STRAIGHT_JOIN ( ( B AS CHILD_SUBQUERY1_t2 STRAIGHT_JOIN C AS CHILD_SUBQUERY1_t3 ON (CHILD_SUBQUERY1_t3 . `col_int_nokey` = CHILD_SUBQUERY1_t2 . `col_int_key` ) ) ) ON (CHILD_SUBQUERY1_t3 . `col_varchar_key` = CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) WHERE CHILD_SUBQUERY1_t3 . `col_varchar_nokey` <> CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) IS NULL ) AND ( table1 . `col_varchar_key` >= 'p' OR table1 . `pk` IN (12, 21, 214) ) ORDER BY table1 . `col_time_key` , table1 .`pk` , CONCAT ( table2 . `col_varchar_nokey`, table2 . `col_varchar_key` ), field1 LIMIT 2

maria-5.3-mwl-66 asserted as follows:

mysqld: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < 1.7976931348623157e+308' failed.

  1. 2010-07-07T10:30:17 #6 0x000000307b62b945 in __assert_fail (
  2. 2010-07-07T10:30:17 assertion=0xd3ea18 "join->best_read < 1.7976931348623157e+308",
  3. 2010-07-07T10:30:17 file=<value optimized out>, line=5385, function=<value optimized out>)
  4. 2010-07-07T10:30:17 at assert.c:81
  5. 2010-07-07T10:30:17 #7 0x000000000071c2eb in greedy_search (join=0x7ffdc81a5f70,
  6. 2010-07-07T10:30:17 remaining_tables=15, search_depth=62, prune_level=1) at sql_select.cc:5385
  7. 2010-07-07T10:30:17 #8 0x000000000071bad6 in choose_plan (join=0x7ffdc81a5f70, join_tables=15)
  8. 2010-07-07T10:30:17 at sql_select.cc:5041
  9. 2010-07-07T10:30:17 #9 0x00000000007db8da in optimize_semijoin_nests (join=0x7ffdc81a5f70,
  10. 2010-07-07T10:30:17 all_table_map=15) at opt_subselect.cc:1155
  11. 2010-07-07T10:30:17 #10 0x00000000007165f4 in make_join_statistics (join=0x7ffdc81a5f70,
  12. 2010-07-07T10:30:17 tables_arg=0x7ffdc81816e0, conds=0x7ffdc8217bb8,
  13. 2010-07-07T10:30:17 keyuse_array=0x7ffdc81abb18) at sql_select.cc:3130
  14. 2010-07-07T10:30:17 #11 0x000000000070ea64 in JOIN::optimize (this=0x7ffdc81a5f70)
  15. 2010-07-07T10:30:17 at sql_select.cc:939
  16. 2010-07-07T10:30:17 #12 0x0000000000630328 in subselect_single_select_engine::exec (
  17. 2010-07-07T10:30:17 this=0x7ffdc8110908) at item_subselect.cc:2609
  18. 2010-07-07T10:30:17 #13 0x000000000062a1a5 in Item_subselect::exec (this=0x7ffdc81107d0)
  19. 2010-07-07T10:30:17 at item_subselect.cc:481
  20. 2010-07-07T10:30:17 #14 0x000000000062b2c0 in Item_singlerow_subselect::val_int (
  21. 2010-07-07T10:30:17 this=0x7ffdc81107d0) at item_subselect.cc:890
  22. 2010-07-07T10:30:17 #15 0x00000000005ba7c9 in Item::update_null_value (this=0x7ffdc81107d0)
  23. 2010-07-07T10:30:17 at item.h:857
  24. 2010-07-07T10:30:17 #16 0x000000000063638f in Item_subselect::is_null (this=0x7ffdc81107d0)
  25. 2010-07-07T10:30:17 at item_subselect.h:143
  26. 2010-07-07T10:30:17 #17 0x00000000005f606f in Item_func_isnull::val_int (this=0x7ffdc8110948)
  27. 2010-07-07T10:30:17 at item_cmpfunc.cc:4620
  28. 2010-07-07T10:30:17 #18 0x00000000005cf499 in eval_const_cond (cond=0x7ffdc8110948)
  29. 2010-07-07T10:30:17 at item_func.cc:63
  30. 2010-07-07T10:30:17 #19 0x0000000000728c73 in remove_eq_conds (thd=0x3935518, cond=0x7ffdc8110948,
  31. 2010-07-07T10:30:17 cond_value=0x7ffe340e6db0) at sql_select.cc:10571
  32. 2010-07-07T10:30:17 #20 0x000000000072876d in remove_eq_conds (thd=0x3935518, cond=0x7ffdc82169c0,
  33. 2010-07-07T10:30:17 cond_value=0x7ffdc81a5d50) at sql_select.cc:10458
  34. 2010-07-07T10:30:17 #21 0x0000000000728647 in optimize_cond (join=0x7ffdc81a0190,
  35. 2010-07-07T10:30:17 conds=0x7ffdc82169c0, join_list=0x3937ef8, cond_value=0x7ffdc81a5d50)
  36. 2010-07-07T10:30:17 at sql_select.cc:10425
  37. 2010-07-07T10:30:17 #22 0x000000000070e33e in JOIN::optimize (this=0x7ffdc81a0190)
  38. 2010-07-07T10:30:17 at sql_select.cc:802
  39. 2010-07-07T10:30:17 #23 0x00000000007143cf in mysql_select (thd=0x3935518,
  40. 2010-07-07T10:30:17 rref_pointer_array=0x3937f78, tables=0x7ffdc80b2020, wild_num=0,
  41. 2010-07-07T10:30:17 fields=..., conds=0x7ffdc8111250, og_num=4, order=0x7ffdc81114a0,
  42. 2010-07-07T10:30:17 group=0x0, having=0x0, proc_param=0x0, select_options=2147764744,
  43. 2010-07-07T10:30:17 result=0x7ffdc8111c08, unit=0x3937920, select_lex=0x3937d90)
  44. 2010-07-07T10:30:17 at sql_select.cc:2540
  45. 2010-07-07T10:30:17 #24 0x000000000070c966 in handle_select (thd=0x3935518, lex=0x3937880,
  46. 2010-07-07T10:30:17 result=0x7ffdc8111c08, setup_tables_done_option=0) at sql_select.cc:276
  47. 2010-07-07T10:30:17 #25 0x00000000006a048e in execute_sqlcom_select (thd=0x3935518,
  48. 2010-07-07T10:30:17 all_tables=0x7ffdc80b2020) at sql_parse.cc:5081
  49. 2010-07-07T10:30:17 #26 0x00000000006973bb in mysql_execute_command (thd=0x3935518)
  50. 2010-07-07T10:30:17 at sql_parse.cc:2265
  51. 2010-07-07T10:30:17 #27 0x00000000006a2b21 in mysql_parse (thd=0x3935518,
  52. 2010-07-07T10:30:17 inBuf=0x7ffdc80b1240 "SELECT SQL_SMALL_RESULT table1 . `col_date_key` AS field1 FROM ( CC AS table1 STRAIGHT_JOIN CC AS table2 ON (table2 . `col_varchar_key` = table1 . `col_varchar_nokey` ) ) WHERE ( ( SELECT COUNT( SUBQUERY1_t1 . `col_varchar_nokey` ) AS SUBQUERY1_field1 FROM C AS SUBQUERY1_t1 WHERE SUBQUERY1_t1 . `col_varchar_nokey` IN ( SELECT CHILD_SUBQUERY1_t2 . `col_varchar_key` AS CHILD_SUBQUERY1_field1 FROM ( C AS CHILD_SUBQUERY1_t1 STRAIGHT_JOIN ( ( B AS CHILD_SUBQUERY1_t2 STRAIGHT_JOIN C AS CHILD_SUBQUERY1_t3 ON (CHILD_SUBQUERY1_t3 . `col_int_nokey` = CHILD_SUBQUERY1_t2 . `col_int_key` ) ) ) ON (CHILD_SUBQUERY1_t3 . `col_varchar_key` = CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) WHERE CHILD_SUBQUERY1_t3 . `col_varchar_nokey` <> CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) IS NULL ) AND ( table1 . `col_varchar_key` >= 'p' OR table1 . `pk` IN (12, 21, 214) ) ORDER BY table1 . `col_time_key` , table1 .`pk` , CONCAT ( table2 . `col_varchar_nokey`, table2 . `col_varchar_key` ), field1 LIMIT 2",
  53. 2010-07-07T10:30:17 length=1009, found_semicolon=0x7ffe340e8c88) at sql_parse.cc:6027
  54. 2010-07-07T10:30:17 #28 0x0000000000694b8f in dispatch_command (command=COM_QUERY, thd=0x3935518,
  55. 2010-07-07T10:30:17 packet=0x3938899 " SELECT SQL_SMALL_RESULT table1 . `col_date_key` AS field1 FROM ( CC AS table1 STRAIGHT_JOIN CC AS table2 ON (table2 . `col_varchar_key` = table1 . `col_varchar_nokey` ) ) WHERE ( ( SELECT COUNT( SUBQUERY1_t1 . `col_varchar_nokey` ) AS SUBQUERY1_field1 FROM C AS SUBQUERY1_t1 WHERE SUBQUERY1_t1 . `col_varchar_nokey` IN ( SELECT CHILD_SUBQUERY1_t2 . `col_varchar_key` AS CHILD_SUBQUERY1_field1 FROM ( C AS CHILD_SUBQUERY1_t1 STRAIGHT_JOIN ( ( B AS CHILD_SUBQUERY1_t2 STRAIGHT_JOIN C AS CHILD_SUBQUERY1_t3 ON (CHILD_SUBQUERY1_t3 . `col_int_nokey` = CHILD_SUBQUERY1_t2 . `col_int_key` ) ) ) ON (CHILD_SUBQUERY1_t3 . `col_varchar_key` = CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) WHERE CHILD_SUBQUERY1_t3 . `col_varchar_nokey` <> CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) IS NULL ) AND ( table1 . `col_varchar_key` >= 'p' OR table1 . `pk` IN (12, 21, 214) ) ORDER BY table1 . `col_time_key` , table1 .`pk` , CONCAT ( table2 . `col_varchar_nokey`, table2 . `col_varchar_key` ), field1 LIMIT 2",
  56. 2010-07-07T10:30:17 packet_length=1011) at sql_parse.cc:1184
  57. 2010-07-07T10:30:17 #29 0x0000000000693fa9 in do_command (thd=0x3935518) at sql_parse.cc:890
  58. 2010-07-07T10:30:17 #30 0x000000000069101c in handle_one_connection (arg=0x3935518)
  59. 2010-07-07T10:30:17 at sql_connect.cc:1153
  60. 2010-07-07T10:30:17 #31 0x000000307ba07761 in start_thread (arg=0x7ffe340e9710)
  61. 2010-07-07T10:30:17 at pthread_create.c:301
  62. 2010-07-07T10:30:17 #32 0x000000307b6e150d in clone ()

A simplified test case will be uploaded shortly.

 Comments   
Comment by Philip Stoev (Inactive) [ 2010-07-07 ]

Re: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < 1.7976931348623157e+308' failed.
Simplifed test case

CREATE TABLE `CC` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `C` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `B` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (7,'1900-01-01','f','f');
SELECT `col_date_key`
FROM CC
WHERE (
SELECT `col_varchar_nokey`
FROM C
WHERE `col_varchar_nokey` IN (
SELECT CHILD_SUBQUERY1_t2 .`col_varchar_key`
FROM B CHILD_SUBQUERY1_t2 STRAIGHT_JOIN C ON CHILD_SUBQUERY1_t2 .`col_int_key` ) ) ;
DROP TABLE CC;
DROP TABLE C;
DROP TABLE B;

Comment by Philip Stoev (Inactive) [ 2010-10-09 ]

Re: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < 1.7976931348623157e+308' failed.
Still repeatable in maria-5.3 with the following revision:

revision-id: sanja@askmonty.org-20100914134341-voquimk50t20zuiy
date: 2010-09-14 16:43:41 +0300
build-date: 2010-10-09 11:38:25 +0300
revno: 2818
branch-nick: maria-5.3

Comment by Timour Katchaounov (Inactive) [ 2010-10-09 ]

Re: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < 1.7976931348623157e+308' failed.
Also repeatable with the latest 5.3-mwl89 tree, when IN-TO-EXISTS=ON, MATERIALIZATION=OFF, and run the test files:
subselect_sj, subselect_sj_jcl6.

Comment by Timour Katchaounov (Inactive) [ 2010-10-24 ]

Re: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < 1.7976931348623157e+308' failed.
The RQG test case above doesn't crash when SEMIJOIN is OFF.

Some additional info from my investigation of the bug:

In order to reproduce this bug, it is sufficient to run the run in 5.3 'subselect_sj' with
materialization=OFF as follows:
./mtr -mysqld=-optimizer_switch='materialization=off' subselect_sj

The test file crashes in the following test case:
--echo Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order
--echo with semijoin=on"
The only reason why this test doesn't crash in 5.3 is that the default strategy for
non-flattened subquery execution in 5.3 is materialization, the same as in MySQL 6.0.

Trying all feasible combinations of IN-TO-EXISTS, MATERIALIZATION, and SEMIJOIN, the only
combination that triggers the crash in subselect_sj is:
optimizer_switch='materialization=off,semijoin=on' subselect_sj

The crash doesn't happen neither in mysql-6.0, nor in mysql-next-mr.

The comments for http://bugs.mysql.com/bug.php?id=46797 say that the bug disappeared on its own.
Most likely the bug got fixed somehow in MySQL 6.0, but the patch was lost while backporting/merging
into MariaDB 5.3.

Most likely the bug is related to semijoin.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 602574

Generated at Thu Feb 08 06:50:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.