diff options
Diffstat (limited to 'mysql-test/r/func_group.result')
| -rw-r--r-- | mysql-test/r/func_group.result | 92 |
1 files changed, 92 insertions, 0 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 74247ae1b8e..dbeb458345e 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -2278,6 +2278,45 @@ EXECUTE stmt; t2_id GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1) DROP TABLE t1,t2,t3,t4,t5,t6; # +# MDEV-10500 CASE/IF Statement returns multiple values and shifts further result values to the next column +# +CREATE TABLE t1 ( +id int not null AUTO_INCREMENT, +active bool not null, +data1 bigint, +data2 bigint, +data3 bigint, +primary key (id) +); +INSERT INTO t1 (active,data1,data2,data3) VALUES (1,null,100,200); +SELECT +CASE WHEN active THEN SUM(data1) END AS C_1, +SUM(data2) AS C_2, +SUM(data3) AS C_3 +FROM t1; +C_1 C_2 C_3 +NULL 100 200 +SELECT +IF(active, SUM(data1), 5) AS C_1, +SUM(data2) AS C_2, +SUM(data3) AS C_3 +FROM t1; +C_1 C_2 C_3 +NULL 100 200 +DROP TABLE t1; +# +# MDEV-10468 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() +# +SELECT STDDEV_POP(f) FROM (SELECT "1e+309" AS f UNION SELECT "-1e+309" AS f) tbl; +STDDEV_POP(f) +1.7976931348623157e308 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+309' +Warning 1292 Truncated incorrect DOUBLE value: '-1e+309' +SELECT STDDEV(f) FROM (SELECT 1.7976931348623157e+308 AS f UNION SELECT -1.7976931348623157e+308 AS f) tbl; +STDDEV(f) +1.7976931348623157e308 +# # MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL does not produce warnings # SELECT MAX('x') << 1, CAST(MAX('x') AS DOUBLE), CAST(MAX('x') AS DECIMAL); @@ -2344,5 +2383,58 @@ companynr AVG(fld1) avg1 avg2 37 9223372036854775805.0000 9223372036854775805 9223372036854775805 DROP TABLE t1; # +# case where aggregate resolved in the local SELECT +# but outer ones are checked +# +create table t10 (a int , b int, c int); +insert into t10 values (0,0,0),(1,1,1); +create table t11 as select * from t10; +create table t12 as select * from t10; +explain extended select a from t10 where c<3 or a in (select c from t12 union select max(t10.b) from t11 group by t11.c); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t10 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t12 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT UNION t11 ALL NULL NULL NULL NULL 2 100.00 Using temporary +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1276 Field or reference 'test.t10.b' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `test`.`t10`.`a` AS `a` from `test`.`t10` where ((`test`.`t10`.`c` < 3) or <expr_cache><`test`.`t10`.`a`,`test`.`t10`.`b`>(<in_optimizer>(`test`.`t10`.`a`,<exists>(select `test`.`t12`.`c` from `test`.`t12` where (<cache>(`test`.`t10`.`a`) = `test`.`t12`.`c`) union select max(`test`.`t10`.`b`) from `test`.`t11` group by `test`.`t11`.`c` having (<cache>(`test`.`t10`.`a`) = <ref_null_helper>(max(`test`.`t10`.`b`))))))) +drop table t10,t11,t12; +# +# MDEV-10017: Get unexpected `Empty Set` for correlated subquery +# with aggregate functions +# +create table t1(c1 int, c2 int, c3 int); +insert into t1 values(1,1,1),(2,2,2),(3,3,3); +select * from t1; +c1 c2 c3 +1 1 1 +2 2 2 +3 3 3 +create table t2(c1 int, c2 int); +insert into t2 values(2,2); +select * from t2; +c1 c2 +2 2 +explain extended +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt)); +ERROR HY000: Invalid use of group function +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt)); +ERROR HY000: Invalid use of group function +explain extended +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+tt.c1) from t2 tt)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t system NULL NULL NULL NULL 1 100.00 +3 DEPENDENT SUBQUERY tt system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.c1' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` having (`test`.`t1`.`c1` >= <expr_cache><`test`.`t1`.`c1`>((select 2 AS `c` from dual order by (select min((`test`.`t1`.`c1` + 2)) from dual)))) +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+tt.c1) from t2 tt)); +c1 +2 +3 +drop table t1,t2; +# # End of 10.1 tests # |