Hi Sergey, When I merged MySQL 5.1.44, there was a new testcase introduced: CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(2); EXPLAIN EXTENDED SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY (SELECT a FROM t2 WHERE b = 12); DROP TABLE t1,t2; This crashes the server in MariaDB. This is Bug#49734, but this bug is marked private. After my merge, the server no longer crashes, but there is a Valgrind error about uninitialised value: ==14946== Thread 9: ==14946== Conditional jump or move depends on uninitialised value(s) ==14946== at 0x71E89D: select_describe(JOIN*, bool, bool, bool, char const*) (sql_select.cc:16883) ==14946== by 0x71F585: JOIN::exec() (sql_select.cc:1837) ==14946== by 0x8467D1: st_select_lex_unit::exec() (sql_union.cc:513) ==14946== by 0x71C016: mysql_explain_union(THD*, st_select_lex_unit*, select_result*) (sql_select.cc:16927) ==14946== by 0x6880BC: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5091) ==14946== by 0x68A260: mysql_execute_command(THD*) (sql_parse.cc:2299) ==14946== by 0x692EB3: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:6034) ==14946== by 0x693CC5: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1247) ==14946== by 0x6951C3: do_command(THD*) (sql_parse.cc:886) ==14946== by 0x6805A0: handle_one_connection (sql_connect.cc:1132) ==14946== by 0x50463F6: start_thread (pthread_create.c:297) ==14946== by 0x6026B4C: clone (in /usr/lib/debug/libc-2.7.so) The tree on Launchpad containing the merge and showing the error is lp:~maria-captains/maria/mariadb-5.1-knielsen Problem is seen in this code in sql/sql_select.cc, static void select_describe(): if (!(unit->item && unit->item->eliminated)) { if (mysql_explain_union(thd, unit, result)) DBUG_VOID_RETURN; } Here unit->item->eliminated is uninitialised. Checking the code in item_subselect.cc, I see that item->eliminated is not initialised in the constructor, only in fix_fields() and mark_as_eliminated_processor(). However, I do not know the code enough to know if the problem is missing initialisation in the constructor, or missing call of fix_fields() (or maybe something else). Can you help? Looks related to the table elimination patch. Since the bug report is privat, I have instead appended the patch in MySQL for the bug. - Kristian. ----------------------------------------------------------------------- revno: 2502.884.63 committer: Georgi Kodinov branch nick: B49734-5.1-bugteam timestamp: Tue 2009-12-22 17:52:15 +0200 message: Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY <any non-const-function> Several problems fixed : 1. Non constant expressions in UNION ... ORDER BY were not correctly cleaned up in st_select_lex_unit::cleanup() causing crashes in EXPLAIN EXTENDED because of fields quoted by these expressions pointing to the already freed temporary table used to calculate the UNION. Fixed by correctly cleaning up expressions of any depth. 2. Subqueries in the order by part of UNION ... ORDER BY ... caused a crash in EXPLAIN EXTENDED because of a transformation attempt made during EXPLAIN EXTENDED execution. Fixed by not doing the transformation when in EXPLAIN. 3. Fulltext functions caused crash when in the ORDER BY part of an un-parenthesized UNION that gets "promoted" to be valid for the whole union, e.g. SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY MATCHES (a) AGAINST ('abc' IN BOOLEAN MODE). This is a case that demonstrates a more general problem of parts of the query being moved to another level. When doing such transformation late in the optimization run when most of the flags about the contents of the query are already aggregated it's possible to "split" the flags so that they correctly reflect the new queries after the transformation. In specific the ST_SELECT_LEX::ftfunc_list is holding all the free text function for all the parts of the second SELECT in the UNION and we don't know what part of that is in the ORDER BY that we're to move to the UNION level and what part is about the other parts of the second SELECT. Fixed by throwing and error when such statements are about to be processed by adding a check for the presence of MATCH() inside the ORDER BY clause that's going to get promoted to UNION. To workaround this new limitation one must parenthesize the UNION SELECTs and provide a real global ORDER BY for the UNION outside of the parenthesis. diff: === modified file 'mysql-test/r/fulltext_order_by.result' --- mysql-test/r/fulltext_order_by.result 2005-08-12 16:27:54 +0000 +++ mysql-test/r/fulltext_order_by.result 2009-12-22 15:52:15 +0000 @@ -126,7 +126,7 @@ a.text, b.id, b.betreff order by match(b.betreff) against ('+abc' in boolean mode) desc; -ERROR 42S22: Unknown column 'b.betreff' in 'order clause' +ERROR 42000: Incorrect usage/placement of 'MATCH()' select a.text, b.id, b.betreff from t2 a inner join t3 b on a.id = b.forum inner join @@ -142,7 +142,7 @@ match(c.beitrag) against ('+abc' in boolean mode) order by match(b.betreff) against ('+abc' in boolean mode) desc; -ERROR 42S22: Unknown column 'b.betreff' in 'order clause' +ERROR 42000: Incorrect usage/placement of 'MATCH()' select a.text, b.id, b.betreff from t2 a inner join t3 b on a.id = b.forum inner join @@ -158,7 +158,7 @@ match(c.beitrag) against ('+abc' in boolean mode) order by match(betreff) against ('+abc' in boolean mode) desc; -text id betreff +ERROR 42000: Incorrect usage/placement of 'MATCH()' (select b.id, b.betreff from t3 b) union (select b.id, b.betreff from t3 b) order by match(betreff) against ('+abc' in boolean mode) desc; === modified file 'mysql-test/r/union.result' --- mysql-test/r/union.result 2009-05-15 07:11:07 +0000 +++ mysql-test/r/union.result 2009-12-22 15:52:15 +0000 @@ -1588,3 +1588,66 @@ Note 1003 select '0' AS `a` from `test`.`t1` union select '0' AS `a` from `test`.`t1` order by `a` DROP TABLE t1; End of 5.0 tests +# +# Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY +# <any non-const-function> +# +CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1),(2); +# Should not crash +EXPLAIN EXTENDED +SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 UNION t1 ALL NULL NULL NULL NULL 2 100.00 +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (`a` + 12) +# Should not crash +SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12; +a +1 +2 +# Should not crash +EXPLAIN EXTENDED +SELECT * FROM t1 UNION SELECT * FROM t1 +ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); +ERROR 42000: Incorrect usage/placement of 'MATCH()' +# Should not crash +SELECT * FROM t1 UNION SELECT * FROM t1 +ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); +ERROR 42000: Incorrect usage/placement of 'MATCH()' +# Should not crash +(SELECT * FROM t1) UNION (SELECT * FROM t1) +ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); +a +1 +2 +# Should not crash +EXPLAIN EXTENDED +SELECT * FROM t1 UNION SELECT * FROM t1 +ORDER BY (SELECT a FROM t2 WHERE b = 12); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 UNION t1 ALL NULL NULL NULL NULL 2 100.00 +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `test`.`t1`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`b` = 12)) +# Should not crash +SELECT * FROM t1 UNION SELECT * FROM t1 +ORDER BY (SELECT a FROM t2 WHERE b = 12); +a +1 +2 +# Should not crash +SELECT * FROM t2 UNION SELECT * FROM t2 +ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); +b +1 +2 +DROP TABLE t1,t2; +End of 5.1 tests === modified file 'mysql-test/t/fulltext_order_by.test' --- mysql-test/t/fulltext_order_by.test 2005-08-12 16:27:54 +0000 +++ mysql-test/t/fulltext_order_by.test 2009-12-22 15:52:15 +0000 @@ -80,7 +80,7 @@ FULLTEXT KEY betreff (betreff) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ; ---error 1054 +--error ER_CANT_USE_OPTION_HERE select a.text, b.id, b.betreff from t2 a inner join t3 b on a.id = b.forum inner join @@ -100,7 +100,7 @@ order by match(b.betreff) against ('+abc' in boolean mode) desc; ---error 1054 +--error ER_CANT_USE_OPTION_HERE select a.text, b.id, b.betreff from t2 a inner join t3 b on a.id = b.forum inner join @@ -117,6 +117,7 @@ order by match(b.betreff) against ('+abc' in boolean mode) desc; +--error ER_CANT_USE_OPTION_HERE select a.text, b.id, b.betreff from t2 a inner join t3 b on a.id = b.forum inner join === modified file 'mysql-test/t/union.test' --- mysql-test/t/union.test 2009-05-15 07:11:07 +0000 +++ mysql-test/t/union.test 2009-12-22 15:52:15 +0000 @@ -1102,3 +1102,56 @@ --echo End of 5.0 tests + + +--echo # +--echo # Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY +--echo # <any non-const-function> +--echo # + +CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1),(2); + +--echo # Should not crash +EXPLAIN EXTENDED +SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12; + +--echo # Should not crash +SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12; + + +--echo # Should not crash +--error ER_CANT_USE_OPTION_HERE +EXPLAIN EXTENDED +SELECT * FROM t1 UNION SELECT * FROM t1 + ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); + +--echo # Should not crash +--error ER_CANT_USE_OPTION_HERE +SELECT * FROM t1 UNION SELECT * FROM t1 + ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); + +--echo # Should not crash +(SELECT * FROM t1) UNION (SELECT * FROM t1) + ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); + + +--echo # Should not crash +EXPLAIN EXTENDED +SELECT * FROM t1 UNION SELECT * FROM t1 + ORDER BY (SELECT a FROM t2 WHERE b = 12); + +--echo # Should not crash +SELECT * FROM t1 UNION SELECT * FROM t1 + ORDER BY (SELECT a FROM t2 WHERE b = 12); + +--echo # Should not crash +SELECT * FROM t2 UNION SELECT * FROM t2 + ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); + +DROP TABLE t1,t2; + + +--echo End of 5.1 tests === modified file 'sql/item.h' --- sql/item.h 2009-12-13 20:57:57 +0000 +++ sql/item.h 2009-12-22 15:52:15 +0000 @@ -967,6 +967,23 @@ return FALSE; } + /** + Find a function of a given type + + @param arg the function type to search (enum Item_func::Functype) + @return + @retval TRUE the function type we're searching for is found + @retval FALSE the function type wasn't found + + @description + This function can be used (together with Item::walk()) to find functions + in an item tree fragment. + */ + virtual bool find_function_processor (uchar *arg) + { + return FALSE; + } + /* For SP local variable returns pointer to Item representing its current value and pointer to current Item otherwise. === modified file 'sql/item_func.h' --- sql/item_func.h 2009-12-13 20:57:57 +0000 +++ sql/item_func.h 2009-12-22 15:52:15 +0000 @@ -212,6 +212,11 @@ { return has_timestamp_args(); } + + virtual bool find_function_processor (uchar *arg) + { + return functype() == *(Functype *) arg; + } }; === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2009-12-17 09:55:18 +0000 +++ sql/sql_select.cc 2009-12-22 15:52:15 +0000 @@ -519,7 +519,7 @@ thd->lex->allow_sum_func= save_allow_sum_func; } - if (!thd->lex->view_prepare_mode) + if (!thd->lex->view_prepare_mode && !(select_options & SELECT_DESCRIBE)) { Item_subselect *subselect; /* Is it subselect? */ === modified file 'sql/sql_union.cc' --- sql/sql_union.cc 2009-05-15 07:11:07 +0000 +++ sql/sql_union.cc 2009-12-22 15:52:15 +0000 @@ -335,6 +335,35 @@ } } + /* + Disable the usage of fulltext searches in the last union branch. + This is a temporary 5.x limitation because of the way the fulltext + search functions are handled by the optimizer. + This is manifestation of the more general problems of "taking away" + parts of a SELECT statement post-fix_fields(). This is generally not + doable since various flags are collected in various places (e.g. + SELECT_LEX) that carry information about the presence of certain + expressions or constructs in the parts of the query. + When part of the query is taken away it's not clear how to "divide" + the meaning of these accumulated flags and what to carry over to the + recipient query (SELECT_LEX). + */ + if (global_parameters->ftfunc_list->elements && + global_parameters->order_list.elements && + global_parameters != fake_select_lex) + { + ORDER *ord; + Item_func::Functype ft= Item_func::FT_FUNC; + for (ord= (ORDER*)global_parameters->order_list.first; ord; ord= ord->next) + if ((*ord->item)->walk (&Item::find_function_processor, FALSE, + (uchar *) &ft)) + { + my_error (ER_CANT_USE_OPTION_HERE, MYF(0), "MATCH()"); + goto err; + } + } + + create_options= (first_sl->options | thd_arg->options | TMP_TABLE_ALL_COLUMNS); /* @@ -669,7 +698,7 @@ { ORDER *ord; for (ord= (ORDER*)global_parameters->order_list.first; ord; ord= ord->next) - (*ord->item)->cleanup(); + (*ord->item)->walk (&Item::cleanup_processor, 0, 0); } }