bk commit into 5.0 tree (gkodinov:1.2235) BUG#21302

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-08-14 15:45:48+03:00, gkodinov@stripped +4 -0
  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@stripped, 2006-08-14 15:45:40+03:00, gkodinov@stripped +1
-1
    Bug #21302: Result not properly sorted when using an ORDER BY on a second 
                table in a join
     - don't remove columns of const tables in ORDER BY if the const table 
       is an outer table.

  mysql-test/r/order_by.result@stripped, 2006-08-14 15:45:41+03:00, gkodinov@stripped +37
-0
    Bug #21302: Result not properly sorted when using an ORDER BY on a second 
                table in a join
     - test case

  mysql-test/t/order_by.test@stripped, 2006-08-14 15:45:42+03:00, gkodinov@stripped +32 -0
    Bug #21302: Result not properly sorted when using an ORDER BY on a second 
                table in a join
     - test case

  sql/sql_select.cc@stripped, 2006-08-14 15:45:43+03:00, gkodinov@stripped +2 -1
    Bug #21302: Result not properly sorted when using an ORDER BY on a second 
                table in a join
     - don't remove columns of const tables in ORDER BY if the const table 
       is an outer table.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B21302-5.0-opt

--- 1.439/sql/sql_select.cc	2006-08-14 15:46:04 +03:00
+++ 1.440/sql/sql_select.cc	2006-08-14 15:46:04 +03:00
@@ -5976,7 +5976,8 @@ eq_ref_table(JOIN *join, ORDER *start_or
   if (tab->cached_eq_ref_table)			// If cached
     return tab->eq_ref_table;
   tab->cached_eq_ref_table=1;
-  if (tab->type == JT_CONST)			// We can skip const tables
+  /* We can skip const tables only if not an outer table */
+  if (tab->type == JT_CONST && !tab->first_inner)
     return (tab->eq_ref_table=1);		/* purecov: inspected */
   if (tab->type != JT_EQ_REF || tab->table->maybe_null)
     return (tab->eq_ref_table=0);		// We must use this

--- 1.48/mysql-test/r/join_outer.result	2006-08-14 15:46:04 +03:00
+++ 1.49/mysql-test/r/join_outer.result	2006-08-14 15:46:04 +03:00
@@ -735,7 +735,7 @@ explain select s.*, '*', m.*, (s.match_1
 (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) 

--- 1.51/mysql-test/r/order_by.result	2006-08-14 15:46:05 +03:00
+++ 1.52/mysql-test/r/order_by.result	2006-08-14 15:46:05 +03:00
@@ -852,3 +852,40 @@ b	a
 20	1
 10	2
 DROP TABLE t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+explain SELECT t1.b as a, t2.b as c FROM 
+t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+SELECT t1.b as a, t2.b as c FROM 
+t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+a	c
+1	NULL
+3	NULL
+2	2
+explain SELECT t1.b as a, t2.b as c FROM 
+t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)  
+ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 SELECT * from t1;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 SELECT * from t1;
+CREATE TABLE t4 LIKE t1;
+INSERT INTO t4 SELECT * from t1;
+INSERT INTO t1 values (0,0),(4,4);
+SELECT t1.*,t2.* FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
+ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
+a	b	a	b
+0	0	NULL	NULL
+4	4	NULL	NULL
+1	1	1	1
+2	2	2	2
+3	3	3	3
+DROP TABLE t1,t2,t3,t4;

--- 1.36/mysql-test/t/order_by.test	2006-08-14 15:46:05 +03:00
+++ 1.37/mysql-test/t/order_by.test	2006-08-14 15:46:05 +03:00
@@ -578,3 +578,35 @@ INSERT INTO t1 VALUES (1,30), (2,20), (1
 DROP TABLE t1;
 
 # End of 4.1 tests
+
+#
+# Bug#21302: Result not properly sorted when using an ORDER BY on a second 
+#             table in a join
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+
+explain SELECT t1.b as a, t2.b as c FROM 
+ t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+SELECT t1.b as a, t2.b as c FROM 
+ t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
+ORDER BY c;
+
+# check that it still removes sort of const table
+explain SELECT t1.b as a, t2.b as c FROM 
+ t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)  
+ORDER BY c;
+
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 SELECT * from t1;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 SELECT * from t1;
+CREATE TABLE t4 LIKE t1;
+INSERT INTO t4 SELECT * from t1;
+INSERT INTO t1 values (0,0),(4,4);
+
+SELECT t1.*,t2.* FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
+ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
+
+DROP TABLE t1,t2,t3,t4;