Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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.

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          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.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                philipstoev Philip Stoev added a comment -

                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;

                Show
                philipstoev Philip Stoev added a comment - 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;
                Hide
                Permalink
                philipstoev Philip Stoev added a comment -

                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

                Show
                philipstoev Philip Stoev added a comment - 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
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                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.

                Show
                timour Timour Katchaounov added a comment - 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.
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                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.

                Show
                timour Timour Katchaounov added a comment - 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.
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 602574

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 602574

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    philipstoev Philip Stoev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: