From 2d9aa1e61ec770392fe96e3d7a3e4e516e087091 Mon Sep 17 00:00:00 2001 From: "gkodinov/kgeorge@macbook.gmz" <> Date: Mon, 14 Aug 2006 15:45:48 +0300 Subject: Bug #21302: Result not properly sorted when using an ORDER BY on a second table in a join The optimizer removes redundant columns in ORDER BY. It is considering redundant every reference to const table column, e.g b in : create table t1 (a int, b int, primary key(a)); select 1 from t1 order by b where a = 1 But it must not remove references to const table columns if the const table is an outer table because there still can be 2 values : the const value and NULL. e.g.: create table t1 (a int, b int, primary key(a)); select t2.b c from t1 left join t1 t2 on (t1.a = t2.a and t2.a = 5) order by c; --- mysql-test/r/join_outer.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r/join_outer.result') diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index eae023813b5..7fc1f8b6489 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -735,7 +735,7 @@ explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from (t2 s left join t1 m on m.match_id = 1) order by m.match_id desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE s ALL NULL NULL NULL NULL 10 +1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort 1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from (t2 s left join t1 m on m.match_id = 1) -- cgit v1.2.1