diff options
Diffstat (limited to 'mysql-test/r/subselect_cache.result')
| -rw-r--r-- | mysql-test/r/subselect_cache.result | 46 |
1 files changed, 46 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index f80b9882c60..c4826eeb50f 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -3272,6 +3272,7 @@ FROM t2 ) AND table1 .`col_varchar_key` OR table1 .`pk` ; col_varchar_nokey drop table t1,t2; set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; # LP BUG#615378 (incorrect NULL result returning in Item_cache) CREATE TABLE `t1` ( `pk` int(11) NOT NULL AUTO_INCREMENT, @@ -3310,3 +3311,48 @@ GROUP BY field3 HAVING (field3 <= 'h' AND field2 != 4) ; field2 field3 drop tables t1, t2, t3; +# +# Test aggregate functions as parameters to subquery cache +# +CREATE TABLE t1 ( a INT, b INT, c INT, KEY (a, b)); +INSERT INTO t1 VALUES +( 1, 1, 1 ), +( 1, 2, 2 ), +( 1, 3, 3 ), +( 1, 4, 6 ), +( 1, 5, 5 ), +( 1, 9, 13 ), +( 2, 1, 6 ), +( 2, 2, 7 ), +( 2, 3, 8 ); +SELECT a, AVG(t1.b), +(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c +FROM t1 GROUP BY a; +a AVG(t1.b) t11c +1 4.0000 6 +2 2.0000 7 +DROP TABLE t1; +# +# Test of LP BUG#800696 (deleting list of Items (OR arguments) +# in optimization) +# +set optimizer_switch='subquery_cache=on,in_to_exists=on'; +CREATE TABLE t1 ( f3 int) ; +INSERT INTO t1 VALUES (0),(0); +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (0),(0); +CREATE TABLE t2 ( f1 int, f2 int, f3 int) ; +INSERT INTO t2 VALUES (7,0,0); +SELECT * +FROM t2, t3 +WHERE t2.f2 OR t3.f3 IN +( +SELECT t2.f2 +FROM t1 +WHERE t2.f1 OR t2.f3 ); +f1 f2 f3 f3 +7 0 0 0 +7 0 0 0 +drop tables t1, t2, t3; +# restore default +set @@optimizer_switch= default; |