diff options
Diffstat (limited to 'mysql-test/r/func_group.result')
| -rw-r--r-- | mysql-test/r/func_group.result | 149 |
1 files changed, 149 insertions, 0 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 6ea28efd91c..df54e0151eb 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -812,6 +812,144 @@ SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; MAX(id) NULL DROP TABLE t1; +create table t1m (a int) engine=myisam; +create table t1i (a int) engine=innodb; +create table t2m (a int) engine=myisam; +create table t2i (a int) engine=innodb; +insert into t2m values (5); +insert into t2i values (5); +select min(a) from t1m; +min(a) +NULL +select min(7) from t1m; +min(7) +NULL +select min(7) from DUAL; +min(7) +NULL +explain select min(7) from t2m join t1m; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(7) from t2m join t1m; +min(7) +NULL +select max(a) from t1m; +max(a) +NULL +select max(7) from t1m; +max(7) +NULL +select max(7) from DUAL; +max(7) +NULL +explain select max(7) from t2m join t1m; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select max(7) from t2m join t1m; +max(7) +NULL +select 1, min(a) from t1m where a=99; +1 min(a) +1 NULL +select 1, min(a) from t1m where 1=99; +1 min(a) +1 NULL +select 1, min(1) from t1m where a=99; +1 min(1) +1 NULL +select 1, min(1) from t1m where 1=99; +1 min(1) +1 NULL +select 1, max(a) from t1m where a=99; +1 max(a) +1 NULL +select 1, max(a) from t1m where 1=99; +1 max(a) +1 NULL +select 1, max(1) from t1m where a=99; +1 max(1) +1 NULL +select 1, max(1) from t1m where 1=99; +1 max(1) +1 NULL +select min(a) from t1i; +min(a) +NULL +select min(7) from t1i; +min(7) +NULL +select min(7) from DUAL; +min(7) +NULL +explain select min(7) from t2i join t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select min(7) from t2i join t1i; +min(7) +NULL +select max(a) from t1i; +max(a) +NULL +select max(7) from t1i; +max(7) +NULL +select max(7) from DUAL; +max(7) +NULL +explain select max(7) from t2i join t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select max(7) from t2i join t1i; +max(7) +NULL +select 1, min(a) from t1i where a=99; +1 min(a) +1 NULL +select 1, min(a) from t1i where 1=99; +1 min(a) +1 NULL +select 1, min(1) from t1i where a=99; +1 min(1) +1 NULL +select 1, min(1) from t1i where 1=99; +1 min(1) +1 NULL +select 1, max(a) from t1i where a=99; +1 max(a) +1 NULL +select 1, max(a) from t1i where 1=99; +1 max(a) +1 NULL +select 1, max(1) from t1i where a=99; +1 max(1) +1 NULL +select 1, max(1) from t1i where 1=99; +1 max(1) +1 NULL +explain select count(*), min(7), max(7) from t1m, t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select count(*), min(7), max(7) from t1m, t1i; +count(*) min(7) max(7) +0 NULL NULL +explain select count(*), min(7), max(7) from t1m, t2i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +select count(*), min(7), max(7) from t1m, t2i; +count(*) min(7) max(7) +0 NULL NULL +explain select count(*), min(7), max(7) from t2m, t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2m system NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select count(*), min(7), max(7) from t2m, t1i; +count(*) min(7) max(7) +0 NULL NULL +drop table t1m, t1i, t2m, t2i; CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b)); INSERT INTO t1 VALUES (1,'xx'), (2,'aa'); SELECT * FROM t1; @@ -979,3 +1117,14 @@ SUM(a) 6 DROP TABLE t1; set div_precision_increment= @sav_dpi; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +CREATE TABLE t2 (a INT PRIMARY KEY, b INT); +INSERT INTO t2 VALUES (1,1), (3,3); +SELECT SQL_NO_CACHE +(SELECT SUM(c.a) FROM t1 ttt, t2 ccc +WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid +FROM t1 t, t2 c WHERE t.a = c.b; +minid +NULL +DROP TABLE t1,t2; |