From de35f1437ad43c45c3b62e8635fb488345ced516 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 19 Nov 2010 12:54:15 +0200 Subject: Fixed LP BUG#641203: Query returns rows where no result is expected (impossible WHERE) The cause for the bug was two-fold: 1. Incorrect detection of whether a table is the first one in a query plan - "used_table & 1" actually checks if used_table is table with number "1". 2. Missing logic to delay the evaluation of (expensive) constant conditions during the execution phase. The fix adds/changes: The patch: - removes incorrect treatment of expensive predicates from make_cond_for_table, and lets the caller decide when to evaluate expensive predicates. - saves expensive constant conditions in JOIN::exec_const_cond, which is evaluated once in the beginning of JOIN::exec. --- mysql-test/t/subselect4.test | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 35ea8388df3..3e3de4547ea 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -370,3 +370,29 @@ WHERE SET SESSION engine_condition_pushdown=@old_icp; DROP TABLE t1,t2; + +--echo # +--echo # LP BUG#641203 Query returns rows where no result is expected (impossible WHERE) +--echo # + +CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL); +CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL); +INSERT INTO t2 VALUES ('k'), ('d'); +CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL); +INSERT INTO t3 VALUES ('a'), ('b'), ('c'); +CREATE TABLE t4 (c1 varchar(1) primary key); +INSERT INTO t4 VALUES ('k'), ('d'); + +EXPLAIN +SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +EXPLAIN +SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +EXPLAIN +SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); +SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); +EXPLAIN +SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); +SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); +drop table t1, t2, t3, t4; -- cgit v1.2.1 From bd5c45dc7a7bda27a5e7a7f6961e79383b4e8a5b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 19 Nov 2010 17:01:48 +0200 Subject: Fix for LP BUG#676411 and MySQL BUG#52317 This is a backport of the fix for MySQL BUG#52317: Assertion failing in Field_varstring::store () at field.cc:6833 The orginal comment by Oystein is: In order for EXPLAIN to print const-refs, a Store_key_const_item object is created. This is different for normal execution of subqueries where a temporary store_key_item object is used instead. The problem is that EXPLAIN will execute subqueries. This leads to a scenario where a store_key_const_item object it told to write to its underlying field. This results in a failing assert since the write set of the underlying table does not reflect this. The resolution is to do the same trick as for store_key_item::copy_inner(). That is, temporarily change the write set to allow writes to all columns. This is only necessary in debug version since non-debug version does not contain asserts on write_set. sql/sql_select.h: Temporarily change write_set in store_key_const_item::copy_inner() to allow initialization of underlying field. This is necessary since subqueries are executed for EXPLAIN. (For normal execution, store_key_item::copy_inner is used.) --- mysql-test/t/subselect4.test | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 3e3de4547ea..4b77b790a5c 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -396,3 +396,19 @@ EXPLAIN SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); drop table t1, t2, t3, t4; + +--echo # +--echo # BUG#52317: Assertion failing in Field_varstring::store() +--echo # at field.cc:6833 +--echo # + +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (i INTEGER, KEY k(i)); +INSERT INTO t2 VALUES (1), (2); + +EXPLAIN +SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2); + +DROP TABLE t2; +DROP TABLE t1; -- cgit v1.2.1 From 0a31c4ffc3ce1577c4a76cbc6d755493a6c152ee Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 22 Nov 2010 11:07:45 +0200 Subject: Fixed LP BUG#675981 Cause: The optimize() phase for the subquery selected to use join buffering via setting JOIN_TAB::next_select= sub_select_cache in make_join_readinfo, however, the call to check_join_cache_usage() from make_join_readinfo didn't create the corresponding JOIN_CACHE_BNL object because of the condition: if ((options & SELECT_DESCRIBE) || (((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) && !tab->cache->init())) Since EXPLAIN for subqueries runs regular execution, the constant predicates that were delayed to be evaluated at the exec() phase, were evaluated during EXPLAIN. As a result the outer JOIN::exec called JOIN::exec for the subquery, while the subquery execution plan was no properly created, which resulted in an failed ASSERT. Fix: The patch blocks evaluation of constant expensive conditions during EXPLAIN. Notice that these conditions are "constant" with respect to the outer query, thus in general they could be arbitrarily expensive, which may result in very slow EXPLAINs. --- mysql-test/t/subselect4.test | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 4b77b790a5c..cc58b14db36 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -397,6 +397,27 @@ SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); drop table t1, t2, t3, t4; +--echo # +--echo # LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache() +--echo # on EXPLAIN +--echo # + +CREATE TABLE t1 (f1 int,f2 int) ; +INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL); + +CREATE TABLE t2 (f1 int, f5 int) ; +INSERT IGNORE INTO t2 VALUES (1,0); + +CREATE TABLE t3 (f4 int) ; +INSERT IGNORE INTO t3 VALUES (0),(0); + +set @@optimizer_switch='in_to_exists=on,materialization=off,semijoin=off'; +EXPLAIN +SELECT * FROM t2 +WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4); + +drop table t1, t2, t3; + --echo # --echo # BUG#52317: Assertion failing in Field_varstring::store() --echo # at field.cc:6833 -- cgit v1.2.1 From fb215f76bbcbb11177adfb04978b66b3151e7f4d Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 22 Nov 2010 16:32:36 +0200 Subject: Fix LP BUG#680005 Analysis: This another instance of the problem fixed in LP BUG#675981 - evaluation of subqueries during EXPLAIN when the subquery plan is incomplete because JOIN::optimize() generally doesn't create complete execution plans for EXPLAIN statements. In this case the call path is: mysql_explain_union -> outer_join.exec -> outer_join.init_execution -> create_sort_index -> filesort -> find_all_keys -> SQL_SELECT::skip_record -> outer_where_clause.val_int -> ... -> subselect_join.exec -> ... -> sub_select_cache When calling sub_select_cache JOIN_TAB::cache is NULL because the cache objects are not created for EXPLAIN statements. Solution: Delay the call to init_execution() after all EXPLAIN related processing is completed. Thus init_execution() is not called at all during EXPLAIN. --- mysql-test/t/subselect4.test | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index cc58b14db36..286fd321afe 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -418,6 +418,38 @@ WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4); drop table t1, t2, t3; +--echo # +--echo # LP BUG#680005 Second assertion `cache != __null' failed in +--echo # sub_select_cache() on EXPLAIN +--echo # + +CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ; +INSERT IGNORE INTO t1 VALUES +('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'), +('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'), +('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0'); + +CREATE TABLE t2 (f3 int) ; +INSERT IGNORE INTO t2 VALUES ('7'); + +CREATE TABLE t3 (f3 int) ; +INSERT IGNORE INTO t3 VALUES ('2'); + +EXPLAIN +SELECT t1.f4 +FROM t2 JOIN t1 ON t1.f6 +WHERE +( t1.f2 ) IN (SELECT SUBQUERY2_t1.f3 + FROM t3 AS SUBQUERY2_t1 + JOIN + (t1 AS SUBQUERY2_t2 + JOIN + t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1) + ON SUBQUERY2_t3.f2) +GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10; + +drop table t1, t2, t3; + --echo # --echo # BUG#52317: Assertion failing in Field_varstring::store() --echo # at field.cc:6833 -- cgit v1.2.1 From 2fa5df5f4eea94bdf8b25406c1e9534535b519c7 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 23 Nov 2010 00:01:24 +0200 Subject: Fix LP BUG#680038 Analysis: Single-row subqueries are not considered expensive and are evaluated both during EXPLAIN in to detect errors like "Subquery returns more than 1 row", and during optimization to perform constant optimization. The cause for the failed ASSERT is in JOIN::join_free, where we set bool full= (!select_lex->uncacheable && !thd->lex->describe); Thus for EXPLAIN statements full == FALSE, and as a result the call to JOIN::cleanup doesn't call JOIN_TAB::cleanup which should have called table->disable_keyread(). Solution: Consider all kinds of subquery predicates as expensive. --- mysql-test/t/subselect4.test | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 286fd321afe..786a20d6b30 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -450,6 +450,32 @@ GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10; drop table t1, t2, t3; +--echo # +--echo # LP BUG#680038 bool close_thread_table(THD*, TABLE**): +--echo # Assertion `table->key_read == 0' failed in EXPLAIN +--echo # + +CREATE TABLE t1 (f1 int,f3 int,f4 int) ; +INSERT IGNORE INTO t1 VALUES (NULL,1,0); + +CREATE TABLE t2 (f2 int,f4 int,f5 int) ; +INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0); + +CREATE TABLE t3 (f4 int,KEY (f4)) ; +INSERT IGNORE INTO t3 VALUES (0),(0); + +set @@optimizer_switch='semijoin=off'; + +EXPLAIN +SELECT * FROM t1 WHERE +(SELECT f2 FROM t2 + WHERE f4 <= ALL + (SELECT SQ1_t1.f4 + FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 + GROUP BY SQ1_t1.f4)); + +drop table t1, t2, t3; + --echo # --echo # BUG#52317: Assertion failing in Field_varstring::store() --echo # at field.cc:6833 -- cgit v1.2.1 From 6dfca7d346d484c1e9114de76a205b694a1891a9 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 26 Nov 2010 17:40:20 +0200 Subject: Fix LP BUG#680846 Analysis: JOIN::optimize performs constant optimization of GROUP by clauses by calling remove_const(): group_list= remove_const(this, (old_group_list= group_list), conds, rollup.state == ROLLUP::STATE_NONE, &simple_group); If it turns out that a GROUP clause references a field that is computed by a single-row subquery, then the said optimization performs premature execution of the subquery referenced by the group clause. Solution: Block the evaluation of subqueries similarly to the approach for the WHERE and JOIN..ON clauses. --- mysql-test/t/subselect4.test | 66 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 66 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 786a20d6b30..05c9e6da95d 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -491,3 +491,69 @@ SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2); DROP TABLE t2; DROP TABLE t1; + +--echo # +--echo # BUG#680846: Crash in clear_tables() with subqueries +--echo # + +CREATE TABLE t1 (f3 int) ; +INSERT IGNORE INTO t1 VALUES (0),(0); + +CREATE TABLE t2 (f1 int,f3 int,f4 varchar(32)) ; +INSERT IGNORE INTO t2 VALUES (1,0,'f'); + +EXPLAIN +SELECT COUNT(t2.f3), + (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; + +SELECT COUNT(t2.f3), + (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; + +EXPLAIN +SELECT COUNT(t2.f3), + (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; + +SELECT COUNT(t2.f3), + (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; + +# these queries are like the ones above, but without the ON clause, +# resulting in a different crash (failed assert) +EXPLAIN +SELECT COUNT(t2.f3), + (SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; + +SELECT COUNT(t2.f3), + (SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; + +EXPLAIN +SELECT COUNT(t2.f3), + (SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; + +SELECT COUNT(t2.f3), + (SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; + +drop table t1,t2; -- cgit v1.2.1 From 1b3336dc30cf97283cca6071f089df992c425eb2 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 2 Dec 2010 14:39:37 +0200 Subject: Fix LP BUG#680943 Analysis: The problem lies in filesort.cc:find_all_keys(). When find_all_keys() is called for the outer query, it resets all of the tree sets of fields - [read,write,vcol]_set and recomputes them with respect to sorting. However, in the loop for each current record the procedure calls select->skip_record(thd), which evaluates the where clause, which in turns evaluates the subquery. The JOIN evaluation of the subquery eventually calls Field_long::val_int to evaluate the field alias1.f1. The assertion condition "bitmap_is_set(table->read_set, field_index)" fails, because the outer query changed the read_set of table "alias1". Solution: Restore the original read_set of the table before calling SQL_SELECT::skip_record, then revert back to the read_set used in find_all_keys. --- mysql-test/t/subselect4.test | 22 +++++++++++++++++++++- 1 file changed, 21 insertions(+), 1 deletion(-) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 05c9e6da95d..fea5d8a2106 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -493,7 +493,7 @@ DROP TABLE t2; DROP TABLE t1; --echo # ---echo # BUG#680846: Crash in clear_tables() with subqueries +--echo # LP BUG#680846: Crash in clear_tables() with subqueries --echo # CREATE TABLE t1 (f3 int) ; @@ -557,3 +557,23 @@ WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; drop table t1,t2; + +--echo # +--echo # LP BUG#680943 Assertion `!table || (!table->read_set || +--echo # bitmap_is_set(table->read_set, field_index))' failed with subquery +--echo # + +CREATE TABLE t1 (f1 int,f3 int) ; +INSERT IGNORE INTO t1 VALUES ('6','0'),('4','0'); + +CREATE TABLE t2 (f1 int,f2 int,f3 int) ; +INSERT IGNORE INTO t2 VALUES ('6','0','0'),('2','0','0'); + +SELECT f2 +FROM (SELECT * FROM t2) AS alias1 +WHERE (SELECT SQ2_t2.f1 + FROM t1 JOIN t1 AS SQ2_t2 ON SQ2_t2.f3 + WHERE SQ2_t2.f3 AND alias1.f1) +ORDER BY f3 ; + +drop table t1,t2; -- cgit v1.2.1 From 620aea4fde7d40f3870bebdcfd66d2b0b556db2f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 2 Dec 2010 21:54:40 +0200 Subject: Fix LP BUG#682683 Analysis: The fix for LP BUG#680846 avoids evaluation of constant expressions with subqueries in the GROUP/ORDER clauses in the procedure remove_const(). The purpose of remove_const is to remove constant expressions in the GROUP/ORDER clauses. In order delay until execution the evaluation of such subqueries, they were not removed in the GROUP/ORDER clause. As a result temp table creation during execution attempted to create a column in the temp table for each constant GROUP/ORDER expression. However, the logic in create_tmp_table is to not create temp table columns for constant items. The crash was due to a group Item without a corresponding column in the temp table for GROUP BY. Solution: The patch adds back removal of constant expressions with subqueries. In order for such expressions to be evaluated, so that the server can ensure that such subquries return 1 row, the evaluation of these expressions is delayed until execution. --- mysql-test/t/subselect4.test | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index fea5d8a2106..cc183db8d87 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -558,6 +558,36 @@ ORDER BY f9; drop table t1,t2; +--echo # +--echo # LP BUG#682683 Crash in create_tmp_table called from +--echo # JOIN::init_execution +--echo # + +CREATE TABLE t2 (f1 int) ; +INSERT INTO t2 VALUES (1),(2); + +CREATE TABLE t1 (f1 int) ; + +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; + +INSERT INTO t1 VALUES (1),(2); + +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; + +drop table t1,t2; + --echo # --echo # LP BUG#680943 Assertion `!table || (!table->read_set || --echo # bitmap_is_set(table->read_set, field_index))' failed with subquery -- cgit v1.2.1