From 1be11803c762d0251d10ae423899f807de5c999a Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 14 Feb 2011 08:26:36 +0200 Subject: MWL#89 Adjusted test results. --- mysql-test/suite/pbxt/r/group_min_max.result | 8 ++--- mysql-test/suite/pbxt/r/join_nested.result | 4 +-- mysql-test/suite/pbxt/r/ps_11bugs.result | 6 ++-- mysql-test/suite/pbxt/r/subselect.result | 45 ++++++++++++++-------------- 4 files changed, 31 insertions(+), 32 deletions(-) (limited to 'mysql-test/suite/pbxt') diff --git a/mysql-test/suite/pbxt/r/group_min_max.result b/mysql-test/suite/pbxt/r/group_min_max.result index e4ba16ce527..04f14a9f5e7 100644 --- a/mysql-test/suite/pbxt/r/group_min_max.result +++ b/mysql-test/suite/pbxt/r/group_min_max.result @@ -1364,7 +1364,7 @@ explain select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c > 'b1' ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range NULL idx_t1_1 147 NULL 129 Using index for group-by +1 PRIMARY t1 range NULL idx_t1_1 147 NULL 129 Using where; Using index for group-by 2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra @@ -2247,17 +2247,17 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 index NULL a 10 NULL 1 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index 2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index d6c4b9a7322..b4edf18f2f5 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -854,7 +854,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on((((`test`.`t3`.`a` = 1) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`))) and (`test`.`t3`.`b` is not null))) where 1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN @@ -966,7 +966,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, diff --git a/mysql-test/suite/pbxt/r/ps_11bugs.result b/mysql-test/suite/pbxt/r/ps_11bugs.result index fad35b97b24..dd09e9d14f3 100644 --- a/mysql-test/suite/pbxt/r/ps_11bugs.result +++ b/mysql-test/suite/pbxt/r/ps_11bugs.result @@ -120,9 +120,9 @@ create table t1 (a int primary key); insert into t1 values (1); explain select * from t1 where 3 in (select (1+1) union select 1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING -3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +1 PRIMARY t1 index NULL PRIMARY 4 NULL 1 Using index +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL select * from t1 where 3 in (select (1+1) union select 1); a diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index efa7b548122..b73f55d2991 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -420,7 +420,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where 1 +Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1)) drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, @@ -908,7 +908,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<`test`.`t1`.`a`>((`test`.`t1`.`a`,(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<`test`.`t1`.`a`>((`test`.`t1`.`a`,(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -1179,9 +1179,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select (0,(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select (0,(select 1 from `test`.`t1` `a` where ((0) = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1189,9 +1189,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select (0,(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select (0,(select 1 from `test`.`t1` `a` where ((0) = 1))) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', @@ -1530,34 +1530,34 @@ select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((NULL >= (select `test`.`t2`.`b` from `test`.`t2` group by 1))) select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((NULL >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((NULL >= (select `test`.`t2`.`b` from `test`.`t2` group by 1))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1619,7 +1619,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION t1 ALL NULL NULL NULL NULL 1 100.00 NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1 +Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where (('f' > (select `test`.`t1`.`s1` from `test`.`t1` union select `test`.`t1`.`s1` from `test`.`t1`))) drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -2829,10 +2829,9 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`, 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a -2 -4 1 3 +2 +4 SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); @@ -3124,9 +3123,9 @@ SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a -2 1 3 +2 4 SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), @@ -3422,7 +3421,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); a b @@ -3433,7 +3432,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort +2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); -- cgit v1.2.1 From 96efe1cab3861bedc9d1f30812b1d6557d8f1585 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 15 Feb 2011 22:17:18 +0200 Subject: Fix for LP BUG#714808 and LP BUG#719280. The patch also adjusts several instable test results to order the result. Analysis: The function prev_record_reads() may skip (jump over) some query plan nodes where record_count < 1. At the same time, even though get_partial_join_cost() uses all first N plan nodes after the last constant table, it may produce a smaller record_count than prev_record_reads(), because the record count for some plan nodes may be < 1, and these nodes may not participate in prev_record_reads. Solution: The current solution is to treat the result of get_partial_join_cost() as the upper bound for the total number of unique lookup keys. --- mysql-test/suite/pbxt/r/subselect.result | 4 ++-- mysql-test/suite/pbxt/t/subselect.test | 8 ++++---- 2 files changed, 6 insertions(+), 6 deletions(-) (limited to 'mysql-test/suite/pbxt') diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index b73f55d2991..08110e90609 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -3112,8 +3112,8 @@ ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a 1 -3 2 +3 4 SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), @@ -3124,8 +3124,8 @@ ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a 1 -3 2 +3 4 SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test index 19658cb202e..3f4d6a9a870 100644 --- a/mysql-test/suite/pbxt/t/subselect.test +++ b/mysql-test/suite/pbxt/t/subselect.test @@ -2052,7 +2052,7 @@ SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); --error 1242 SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); - +--sorted_result SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; @@ -2060,7 +2060,7 @@ SELECT a FROM t1 GROUP BY a SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; - +--sorted_result SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; @@ -2068,7 +2068,7 @@ SELECT a FROM t1 GROUP BY a SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; - +--sorted_result SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); @@ -2076,7 +2076,7 @@ SELECT a FROM t1 SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); - +--sorted_result SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); -- cgit v1.2.1