diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 82 |
1 files changed, 81 insertions, 1 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 74e9cd3caaa..222977e5106 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2021,8 +2021,88 @@ zzz 3 4 8 -#TODO: in merge with 5.3 add original test suite +SET @save_optimizer_switch967242=@@optimizer_switch; +SET optimizer_switch = 'in_to_exists=on'; +SELECT t2_1.b +FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 +ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) +WHERE +( SELECT COUNT(*) FROM t2 ) IS NOT NULL +OR a = t2_1.c +GROUP BY t2_1.b; +b +0 +3 +4 +8 +SET optimizer_switch=@save_optimizer_switch967242; drop table t1, t2; +# +# Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY +# ROWS WHEN GROUP IS OPTIMIZED AWAY +# +CREATE TABLE t1 (col1 int, col2 int) ; +INSERT INTO t1 VALUES (10,1),(11,7); +CREATE TABLE t2 (col1 int, col2 int) ; +INSERT INTO t2 VALUES (10,8); + +EXPLAIN SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +col2 +8 + +EXPLAIN SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +col2 +8 + +DROP TABLE t1,t2; +# +# BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A +# DIFFERENT QUERY OUTPUT +# +CREATE TABLE t1 ( +a int, +b varchar(1), +KEY (b,a) +); +INSERT INTO t1 VALUES (1,NULL),(0,'a'); + +EXPLAIN SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b b 9 NULL 2 Using where; Using index for group-by; Using temporary + +SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +MIN(a) b +0 a + +EXPLAIN SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b b 9 NULL 2 Using where; Using index for group-by + +SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +MIN(a) b +0 a + +DROP TABLE t1; +# +# LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0); +SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0); +1 +1 +SELECT max(1) FROM t1 WHERE a!=0; +max(1) +NULL +drop table t1; # End of 5.2 tests # # lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK |