diff options
Diffstat (limited to 'mysql-test/r/subselect2.result')
| -rw-r--r-- | mysql-test/r/subselect2.result | 99 |
1 files changed, 98 insertions, 1 deletions
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 7eff7f949a8..4fd303dfd44 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -180,6 +180,32 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP VIEW v1; DROP TABLE t1,t2,t3; # +# MDEV-536: LP:1050806 - different result for a query using subquery +# +DROP TABLE IF EXISTS `t1`; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE `t1` ( +`node_uid` bigint(20) unsigned DEFAULT NULL, +`date` datetime DEFAULT NULL, +`mirror_date` datetime DEFAULT NULL, +KEY `date` (`date`) +) ENGINE=MyISAM; +INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00'); +INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00'); +INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00'); +SELECT * FROM ( +SELECT node_uid, date, mirror_date, @result := 0 AS result +FROM t1 +WHERE date < '2012-12-12 12:12:12' + AND node_uid in (2085, 2084) +ORDER BY mirror_date ASC +) AS calculated_result; +node_uid date mirror_date result +2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0 +2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0 +DROP TABLE t1; +# # MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed # CREATE TABLE t1 (a int, b int, INDEX idx(a)); @@ -197,5 +223,76 @@ a b 1 0 1 1 1 3 -DROP TABLE t1, t2, t3; +set @tmp_mdev567=@@optimizer_switch; +set optimizer_switch='mrr=off'; +SELECT * FROM t3 +WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2 +WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9 +AND t3.b = t1.b +GROUP BY t1.b); +a b +1 0 +1 1 +1 3 +DROP TABLE t1,t2,t3; +set optimizer_switch=@tmp_mdev567; +# +# MDEV-614, also MDEV-536, also LP:1050806: +# different result for a query using subquery between 5.5.25 and 5.5.27 +# +CREATE TABLE `t1` ( +`node_uid` bigint(20) unsigned DEFAULT NULL, +`date` datetime DEFAULT NULL, +`mirror_date` datetime DEFAULT NULL, +KEY `date` (`date`) +) ENGINE=MyISAM; +INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00'); +INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00'); +INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00'); +explain +SELECT * FROM ( +SELECT node_uid, date, mirror_date, @result := 0 AS result +FROM t1 +WHERE date < '2012-12-12 12:12:12' + AND node_uid in (2085, 2084) +ORDER BY mirror_date ASC +) AS calculated_result; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort +SELECT * FROM ( +SELECT node_uid, date, mirror_date, @result := 0 AS result +FROM t1 +WHERE date < '2012-12-12 12:12:12' + AND node_uid in (2085, 2084) +ORDER BY mirror_date ASC +) AS calculated_result; +node_uid date mirror_date result +2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0 +2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0 +set @tmp_mdev614=@@optimizer_switch; +set optimizer_switch='mrr=off'; +explain +SELECT * FROM ( +SELECT node_uid, date, mirror_date, @result := 0 AS result +FROM t1 +WHERE date < '2012-12-12 12:12:12' + AND node_uid in (2085, 2084) +ORDER BY mirror_date ASC +) AS calculated_result; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Using filesort +SELECT * FROM ( +SELECT node_uid, date, mirror_date, @result := 0 AS result +FROM t1 +WHERE date < '2012-12-12 12:12:12' + AND node_uid in (2085, 2084) +ORDER BY mirror_date ASC +) AS calculated_result; +node_uid date mirror_date result +2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0 +2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0 +set optimizer_switch=@tmp_mdev614; +DROP TABLE t1; set optimizer_switch=@subselect2_test_tmp; |