maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04231
Fwd: [Commits] Rev 3071: Fix LP bug lp:802979 in file:///home/tsk/mprog/src/5.3-mwl89/
Sergey
Could you please review my fix for bug lp:802979.
I need you specifically because I needed to touch
the range optimzizer to disable evaluation of
single-row subqueries.
Please consider if I chose the best place in the
range optimizer to disable this evaluation of
single-row subqueries.
I will be back from vacation on Tuesday, so if there
is need for discussion, we can talk on Tuesday evening
or Wednesday any time.
Timour
-------- Original Message --------
Return-Path: <commits-bounces@xxxxxxxxxxx>
X-Original-To: timour@xxxxxxxxxxxx
Delivered-To: timour@xxxxxxxxxxxx
Received: from localhost (localhost.localdomain [127.0.0.1]) by hasky.askmonty.org (Postfix) with ESMTP id 2FB01D0A86; Mon, 4 Jul 2011 14:51:35 +0300 (EEST)
X-Virus-Scanned: Debian amavisd-new at mail.askmonty.org
Received: from hasky.askmonty.org ([127.0.0.1]) by localhost (mail.askmonty.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id VhBdpL-sj25D; Mon, 4 Jul 2011 14:51:34 +0300 (EEST)
Received: from hasky.askmonty.org (localhost.localdomain [127.0.0.1]) by hasky.askmonty.org (Postfix) with ESMTP id D6837D0A72; Mon, 4 Jul 2011 14:51:34 +0300 (EEST)
Received: by hasky.askmonty.org (Postfix) id 13265D0A72; Mon, 4 Jul 2011 14:51:33 +0300 (EEST)
Delivered-To: commits@xxxxxxxxxxx
Received: from localhost (localhost.localdomain [127.0.0.1]) by hasky.askmonty.org (Postfix) with ESMTP id 02961D0A84 for <commits@xxxxxxxxxxx>; Mon, 4 Jul 2011 14:51:33 +0300 (EEST)
X-Virus-Scanned: Debian amavisd-new at mail.askmonty.org
Received: from hasky.askmonty.org ([127.0.0.1]) by localhost (mail.askmonty.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id xbI1dw-8fTxt for <commits@xxxxxxxxxxx>; Mon, 4 Jul 2011 14:51:29
+0300 (EEST)
Received: from localhost6.localdomain6 (unknown [213.226.63.169]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by hasky.askmonty.org (Postfix) with
ESMTPSA id DC670D0A72 for <commits@xxxxxxxxxxx>; Mon, 4 Jul 2011 14:51:28 +0300 (EEST)
Content-Type: multipart/mixed; boundary="===============4096211003537732309=="
MIME-Version: 1.0
From: <timour@xxxxxxxxxxxx>
User-Agent: bzr/2.3.1
To: <commits@xxxxxxxxxxx>
Message-Id: <20110704115128.DC670D0A72@xxxxxxxxxxxxxxxxxx>
Date: Mon, 4 Jul 2011 14:51:28 +0300 (EEST)
Subject: [Commits] Rev 3071: Fix LP bug lp:802979 in file:///home/tsk/mprog/src/5.3-mwl89/
X-BeenThere: commits@xxxxxxxxxxx
X-Mailman-Version: 2.1.9
Precedence: list
Reply-To: maria-developers@xxxxxxxxxxxxxxxxxxx
List-Id: MariaDB Commits List <commits.mariadb.org>
List-Unsubscribe: <https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits>, <mailto:commits-request@xxxxxxxxxxx?subject=unsubscribe>
List-Archive: <http://lists.askmonty.org/pipermail/commits>
List-Post: <mailto:commits@xxxxxxxxxxx>
List-Help: <mailto:commits-request@xxxxxxxxxxx?subject=help>
List-Subscribe: <https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits>, <mailto:commits-request@xxxxxxxxxxx?subject=subscribe>
Sender: commits-bounces@xxxxxxxxxxx
Errors-To: commits-bounces@xxxxxxxxxxx
At file:///home/tsk/mprog/src/5.3-mwl89/
------------------------------------------------------------
revno: 3071
revision-id: timour@xxxxxxxxxxxx-20110704115116-cx7ibyty8uiwrpg7
parent: igor@xxxxxxxxxxxx-20110630030724-l43c25gdezawuum3
fixes bug(s): https://launchpad.net/bugs/802979
committer: timour@xxxxxxxxxxxx
branch nick: 5.3-mwl89
timestamp: Mon 2011-07-04 14:51:16 +0300
message:
Fix LP bug lp:802979
Analysis:
This bug consists of two related problems that are
result of too early evaluation of single-row subqueries
during the optimization phase of the outer query.
Several optimizer code paths try to evaluate single-row
subqueries in order to produce a constant and use that
constant for further optimzation.
When the execution of the subquery peforms destructive
changes to the representation of the subquery, and these
changes are not anticipated by the subsequent optimization
phases of the outer query, we tipically get a crash or
failed assert.
Specifically, in this bug the inner-most suqbuery with
DISTINCT triggers a substitution of the original JOIN
object by a single-table JOIN object with a temp table
needed to perform the DISTINCT operation (created by
JOIN::make_simple_join).
This substitution breaks EXPLAIN because:
a) in the first example JOIN::cleanup no longer can
reach the original table of the innermost subquery, and
close all indexes, and
b) in this second test query, EXPLAIN attempts to print
the name of the internal temp table, and crashes because
the temp table has no name (NULL pointer instead).
Solution:
a) fully disable subquery evaluation during optimization
in all cases - both for constant propagation and range
optimization, and
b) change JOIN::join_free() to perform cleanup irrespective
of EXPLAIN or not.
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2011-05-29 08:58:44 +0000
+++ b/mysql-test/r/group_min_max.result 2011-07-04 11:51:16 +0000
@@ -2412,7 +2412,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JO
ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2)
AND t1_outer1.b = t1_outer2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer1 ref a a 5 const 1 Using index
+1 PRIMARY t1_outer1 ref a a 5 const 2 Using where; Using index
1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join)
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
@@ -2749,7 +2749,7 @@ NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
id select_type table type possible_keys key key_len ref rows Extra
-x x x x x x x x x Impossible WHERE noticed after reading const tables
+x x x x x x x x x Using where; Using index
x x x x x x x x x Using where; Using index
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
MIN( a )
@@ -2821,7 +2821,7 @@ NULL
EXPLAIN
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
id select_type table type possible_keys key key_len ref rows Extra
-x x x x x x x x x Impossible WHERE noticed after reading const tables
+x x x x x x x x x Using where; Using index
x x x x x x x x x Using where; Using index
SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
MIN( a )
=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result 2011-06-03 17:45:24 +0000
+++ b/mysql-test/r/sp.result 2011-07-04 11:51:16 +0000
@@ -6420,16 +6420,16 @@ id select_type table type possible_keys
1 SIMPLE t1 ref c1 c1 5 const 1 Using index
EXPLAIN SELECT * FROM t1 WHERE c1=f1();
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref c1 c1 5 const 1 Using index
+1 SIMPLE t1 ref c1 c1 5 const 0 Using index
EXPLAIN SELECT * FROM v1 WHERE c1=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c1 c1 5 const 1 Using index
EXPLAIN SELECT * FROM v1 WHERE c1=f1();
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref c1 c1 5 const 1 Using index
+1 SIMPLE t1 ref c1 c1 5 const 0 Using index
EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref c1 c1 5 const 1 Using index
+1 SIMPLE t1 ref c1 c1 5 const 0 Using index
EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2011-06-06 19:19:35 +0000
+++ b/mysql-test/r/subselect.result 2011-07-04 11:51:16 +0000
@@ -551,7 +551,7 @@ id select_type table type possible_keys
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`))))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1236,7 +1236,7 @@ create table t1 (id int not null auto_in
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition
+1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -4030,7 +4030,7 @@ CREATE TABLE t1 (a int, b int, KEY (a));
INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
+1 PRIMARY t1 ref a a 5 const 0 Using where; Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result 2011-06-05 02:56:06 +0000
+++ b/mysql-test/r/subselect4.result 2011-07-04 11:51:16 +0000
@@ -731,7 +731,7 @@ WHERE f3 = (
SELECT t1.f3 FROM t1
WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY t1 ref f3 f3 5 const 0 Using index condition
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 test.t1.f10 1
3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary
@@ -746,7 +746,7 @@ WHERE f3 = (
SELECT f3 FROM t1
WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY t1 ref f3 f3 5 const 0 Using index condition
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 10 test.t1.f10,test.t1.f10 1
3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary
@@ -1736,3 +1736,49 @@ id select_type table type possible_keys
2 SUBQUERY t3 ALL NULL NULL NULL NULL 2
3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary
drop table t1, t2, t3;
+#
+# LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table
+#
+CREATE TABLE t1 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t1 VALUES (1,0),(5,0);
+CREATE TABLE t2 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t2 VALUES (1,0),(5,0);
+CREATE TABLE t3 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (1,0),(5,0);
+CREATE TABLE t4 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t4 VALUES (1,0),(5,0);
+EXPLAIN
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+WHERE EXISTS (SELECT DISTINCT f1 FROM t4))
+AND t2.f2 = t1.f1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 2
+3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+WHERE EXISTS (SELECT DISTINCT f1 FROM t4))
+AND t2.f2 = t1.f1;
+ERROR 21000: Subquery returns more than 1 row
+EXPLAIN
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1)
+AND t2.f2 = t1.f1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 2
+3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1)
+AND t2.f2 = t1.f1;
+f1 f2 f1 f2
+drop table t1,t2,t3,t4;
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2011-06-06 19:19:35 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2011-07-04 11:51:16 +0000
@@ -555,7 +555,7 @@ id select_type table type possible_keys
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`))))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1240,7 +1240,7 @@ create table t1 (id int not null auto_in
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition
+1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -4034,7 +4034,7 @@ CREATE TABLE t1 (a int, b int, KEY (a));
INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
+1 PRIMARY t1 ref a a 5 const 0 Using where; Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2011-06-06 19:19:35 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2011-07-04 11:51:16 +0000
@@ -552,7 +552,7 @@ id select_type table type possible_keys
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`))))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1237,7 +1237,7 @@ create table t1 (id int not null auto_in
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition
+1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -4031,7 +4031,7 @@ CREATE TABLE t1 (a int, b int, KEY (a));
INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
+1 PRIMARY t1 ref a a 5 const 0 Using where; Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2011-06-06 19:19:35 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2011-07-04 11:51:16 +0000
@@ -552,7 +552,7 @@ id select_type table type possible_keys
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`))))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1237,7 +1237,7 @@ create table t1 (id int not null auto_in
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition
+1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -4031,7 +4031,7 @@ CREATE TABLE t1 (a int, b int, KEY (a));
INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
+1 PRIMARY t1 ref a a 5 const 0 Using where; Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test 2011-06-02 20:25:58 +0000
+++ b/mysql-test/t/subselect4.test 2011-07-04 11:51:16 +0000
@@ -1415,3 +1415,45 @@ WHERE t2.f1 = (
FROM t1));
drop table t1, t2, t3;
+
+--echo #
+--echo # LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table
+--echo #
+
+CREATE TABLE t1 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t1 VALUES (1,0),(5,0);
+CREATE TABLE t2 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t2 VALUES (1,0),(5,0);
+CREATE TABLE t3 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (1,0),(5,0);
+CREATE TABLE t4 ( f1 int, f2 int , KEY (f1)) ;
+INSERT IGNORE INTO t4 VALUES (1,0),(5,0);
+
+EXPLAIN
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+ WHERE EXISTS (SELECT DISTINCT f1 FROM t4))
+ AND t2.f2 = t1.f1;
+
+-- error ER_SUBQUERY_NO_1_ROW
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+ WHERE EXISTS (SELECT DISTINCT f1 FROM t4))
+ AND t2.f2 = t1.f1;
+
+EXPLAIN
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+ WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1)
+ AND t2.f2 = t1.f1;
+
+SELECT *
+FROM t1, t2
+WHERE t2.f2 = (SELECT f2 FROM t3
+ WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1)
+ AND t2.f2 = t1.f1;
+
+drop table t1,t2,t3,t4;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-06-27 16:07:24 +0000
+++ b/sql/opt_range.cc 2011-07-04 11:51:16 +0000
@@ -7292,7 +7292,7 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_P
case Item_func::MULT_EQUAL_FUNC:
{
Item_equal *item_equal= (Item_equal *) cond;
- if (!(value= item_equal->get_const()))
+ if (!(value= item_equal->get_const()) || value->is_expensive())
DBUG_RETURN(0);
Item_equal_fields_iterator it(*item_equal);
ref_tables= value->used_tables();
@@ -7325,6 +7325,9 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_P
}
else
DBUG_RETURN(0);
+ if (value && value->is_expensive())
+ DBUG_RETURN(0);
+
ftree= get_full_func_mm_tree(param, cond_func, field_item, value, inv);
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-06-30 03:07:24 +0000
+++ b/sql/sql_select.cc 2011-07-04 11:51:16 +0000
@@ -9620,8 +9620,7 @@ void JOIN::join_free()
Optimization: if not EXPLAIN and we are done with the JOIN,
free all tables.
*/
- bool full= (!(select_lex->uncacheable) &&
- !thd->lex->describe);
+ bool full= !(select_lex->uncacheable);
bool can_unlock= full;
DBUG_ENTER("JOIN::join_free");
@@ -11472,8 +11471,8 @@ propagate_cond_constants(THD *thd, I_Lis
{
Item_func_eq *func=(Item_func_eq*) cond;
Item **args= func->arguments();
- bool left_const= args[0]->const_item();
- bool right_const= args[1]->const_item();
+ bool left_const= args[0]->const_item() && !args[0]->is_expensive();
+ bool right_const= args[1]->const_item() && !args[1]->is_expensive();
if (!(left_const && right_const) &&
args[0]->result_type() == args[1]->result_type())
{
_______________________________________________
commits mailing list
commits@xxxxxxxxxxx
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
Follow ups
