summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_min_max.result231
-rw-r--r--mysql-test/t/group_min_max.test23
-rw-r--r--sql/opt_range.cc2
3 files changed, 255 insertions, 1 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 5b6ae943529..62c29d8fd01 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2790,11 +2790,240 @@ INSERT INTO t2 VALUES (1),(1),(1),(4),(4),(5),(5),(8),(8),(9);
explain select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t2 range a a 5 NULL 6 Using where; Using index for group-by
+2 DEPENDENT SUBQUERY t2 index a a 5 NULL 10 Using where; Using index
select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
(select t2.a from t2 where t2.a >= t1.a group by t2.a)
NULL
NULL
9
drop table t1, t2;
+#
+# LP BUG#900375 Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS
+#
+CREATE TABLE t1 ( a INT, b INT, KEY (b) );
+INSERT INTO t1 VALUES
+(100,10),(101,11),(102,12),(103,13),(104,14),
+(105,15),(106,16),(107,17),(108,18),(109,19);
+EXPLAIN
+SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL b 5 NULL 10 Using index
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer
+3 DEPENDENT SUBQUERY t1 index b b 5 NULL 10 Using where; Using index; Using temporary
+2 DERIVED t1 ALL NULL NULL NULL NULL 10
+SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+a b
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+EXPLAIN
+SELECT alias1.* FROM t1, t1 AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL b 5 NULL 10 Using index
+1 PRIMARY alias1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer
+2 DEPENDENT SUBQUERY t1 index b b 5 NULL 10 Using where; Using index; Using temporary
+SELECT alias1.* FROM t1, t1 AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+a b
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+100 10
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+101 11
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+102 12
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+103 13
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+104 14
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+105 15
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+106 16
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+107 17
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+108 18
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+109 19
+drop table t1;
End of 5.1 tests
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index bd2cbd8a9f0..7c9c2b05eda 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -1114,4 +1114,27 @@ select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
drop table t1, t2;
+--echo #
+--echo # LP BUG#900375 Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS
+--echo #
+
+CREATE TABLE t1 ( a INT, b INT, KEY (b) );
+INSERT INTO t1 VALUES
+(100,10),(101,11),(102,12),(103,13),(104,14),
+(105,15),(106,16),(107,17),(108,18),(109,19);
+
+EXPLAIN
+SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+
+EXPLAIN
+SELECT alias1.* FROM t1, t1 AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+SELECT alias1.* FROM t1, t1 AS alias1
+WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
+
+drop table t1;
+
--echo End of 5.1 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 889af68f77a..bc434e2edc1 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9337,6 +9337,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree)
DBUG_RETURN(NULL);
if (table->s->keys == 0) /* There are no indexes to use. */
DBUG_RETURN(NULL);
+ if (join->conds && join->conds->used_tables() & OUTER_REF_TABLE_BIT)
+ DBUG_RETURN(NULL); /* Cannot execute with correlated conditions. */
/* Analyze the query in more detail. */
List_iterator<Item> select_items_it(join->fields_list);
View Lorry Controller Status