diff options
Diffstat (limited to 'mysql-test/main/subselect4.result')
| -rw-r--r-- | mysql-test/main/subselect4.result | 69 |
1 files changed, 63 insertions, 6 deletions
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index aa486fc0903..1d4a588977d 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -1677,7 +1677,7 @@ WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f f1 f2 8 8 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'u' +Warning 1292 Truncated incorrect DECIMAL value: 'u' EXPLAIN SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra @@ -1711,7 +1711,7 @@ WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f f1 f2 8 8 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'u' +Warning 1292 Truncated incorrect DECIMAL value: 'u' EXPLAIN SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra @@ -1903,8 +1903,8 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); col_int_key Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'r' set @@optimizer_switch='subquery_cache=off,materialization=off,in_to_exists=on,semijoin=off'; EXPLAIN SELECT col_int_key @@ -1928,8 +1928,8 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); col_int_key Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'r' drop table t1, t2; set @@optimizer_switch = @old_optimizer_switch; # @@ -2981,6 +2981,63 @@ ANALYZE } DROP TABLE t1; # End of 10.2 tests +# +# MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +# +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); +explain extended select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +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 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +a +3 +2 +prepare stmt from "select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1))"; +execute stmt; +a +3 +2 +execute stmt; +a +3 +2 +deallocate prepare stmt; +explain extended select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +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 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) < <cache>(`test`.`t1`.`a`))) +select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +a +1 +2 +explain extended select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +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 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +a +3 +2 +drop table t1,t2,t3; # End of 10.3 tests # # MDEV-19134: EXISTS() slower if ORDER BY is defined |