diff options
Diffstat (limited to 'mysql-test/t/subselect_cache.test')
-rw-r--r-- | mysql-test/t/subselect_cache.test | 54 |
1 files changed, 54 insertions, 0 deletions
diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test index b83ab27e03e..47cf50e63d2 100644 --- a/mysql-test/t/subselect_cache.test +++ b/mysql-test/t/subselect_cache.test @@ -1566,6 +1566,7 @@ FROM t2 ) AND table1 .`col_varchar_key` OR table1 .`pk` ; drop table t1,t2; set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; # --echo # LP BUG#615378 (incorrect NULL result returning in Item_cache) # @@ -1597,6 +1598,8 @@ CREATE TABLE `t3` ( ) DEFAULT CHARSET=latin1; INSERT INTO `t3` VALUES (1,'w'); +# We may get warnings about 'h' not beeing a double here +--disable_warnings SELECT SUM( DISTINCT table2 . `pk` ) AS field2 , (SELECT SUM( SUBQUERY1_t2 . `pk` ) AS SUBQUERY1_field1 FROM t2 AS SUBQUERY1_t2 STRAIGHT_JOIN @@ -1609,5 +1612,56 @@ FROM ( t1 AS table1 LEFT JOIN WHERE ( table1 . `pk` < 5 ) OR ( table1 . `col_varchar_key` IS NOT NULL) GROUP BY field3 HAVING (field3 <= 'h' AND field2 != 4) ; +--enable_warnings +drop tables t1, t2, t3; + +--echo # +--echo # Test aggregate functions as parameters to subquery cache +--echo # + +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; + +DROP TABLE t1; + +--echo # +--echo # Test of LP BUG#800696 (deleting list of Items (OR arguments) +--echo # in optimization) +--echo # + +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 ); drop tables t1, t2, t3; +--echo # restore default +set @@optimizer_switch= default; |