summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_min_max.result6
-rw-r--r--mysql-test/r/sp.result6
-rw-r--r--mysql-test/r/subselect.result6
-rw-r--r--mysql-test/r/subselect4.result50
-rw-r--r--mysql-test/r/subselect_no_mat.result6
-rw-r--r--mysql-test/r/subselect_no_opts.result6
-rw-r--r--mysql-test/r/subselect_no_semijoin.result6
-rw-r--r--mysql-test/t/subselect4.test42
-rw-r--r--sql/opt_range.cc5
-rw-r--r--sql/sql_select.cc7
10 files changed, 115 insertions, 25 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index c33e632ffbb..7cd0011427e 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2412,7 +2412,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
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 )
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 50dd2ae7047..ffb2dd1819c 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -6420,16 +6420,16 @@ 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 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
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 56c2c915cb0..bf27b06c64a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -552,7 +552,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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_increment primary key, salary int, key(sal
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));
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index de4f386f831..8735a75b45d 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -733,7 +733,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
@@ -748,7 +748,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
@@ -1738,4 +1738,50 @@ id select_type table type possible_keys key key_len ref rows Extra
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;
set optimizer_switch=@subselect4_tmp;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index dabffd16d93..2976a5d851c 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -556,7 +556,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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);
@@ -1241,7 +1241,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
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`))
@@ -4035,7 +4035,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));
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 2e643027ce6..af016357aa8 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -553,7 +553,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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);
@@ -1238,7 +1238,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
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`))
@@ -4032,7 +4032,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));
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index ebe85ca4670..c247d9f4377 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -553,7 +553,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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);
@@ -1238,7 +1238,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
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`))
@@ -4032,7 +4032,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));
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index bed28400c2c..69ef5ffbd21 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1419,4 +1419,46 @@ WHERE t2.f1 = (
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;
+
set optimizer_switch=@subselect4_tmp;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 3b672d6b704..88cd2a5e4cd 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -7292,7 +7292,7 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,COND *cond)
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_PARAM *param,COND *cond)
}
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);
}
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 79ec2bd58d1..7c82b5c368b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9689,8 +9689,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");
@@ -11541,8 +11540,8 @@ propagate_cond_constants(THD *thd, I_List<COND_CMP> *save_list,
{
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())
{
View Lorry Controller Status