Merge spetrunia@bk-internal.mysql.com:/home/bk/mysql-5.0

into mysql.com:/dbdata/psergey/mysql-5.0-root

8 files changed, 154 insertions, 20 deletions

diff --git a/mysql-test/create-test-result b/mysql-test/create-test-result
index b9be2300976..ad19cdf08a1 100755
--- a/mysql-test/create-test-result
+++ b/mysql-test/create-test-result
@@ -6,7 +6,7 @@
 # to start mysqld yourself and run mysqltest -r
 
 RESULT_DIR=r
-if [ -z $EDITOR] ; then
+if [ -z "$EDITOR" ] ; then
  EDITOR=vi
 fi
 
@@ -24,7 +24,7 @@ function usage()
 
 test_name=$1
 
-[ -z $test_name ] && usage
+[ -z "$test_name" ] && usage
 
 result_file=$RESULT_DIR/$test_name.result
 reject_file=$RESULT_DIR/$test_name.reject
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index d33205c418b..db9b051a58f 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -59,9 +59,11 @@ id	count(t2.id)
 107	1
 select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
 id	id
+NULL	75
 explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	SIMPLE	t1	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 81ca53cb727..09c3e67ae70 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1218,12 +1218,106 @@ INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
 INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);
 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	index	c	c	5	NULL	6	Using where; Using index
-1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using where; Using index
 1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
+1	SIMPLE	t3	index	c	c	5	NULL	6	Using index
+1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	b	b	5	NULL	3	Using where; Using index
-1	SIMPLE	t3	ref	c	c	5	test.t2.b	2	Using where; Using index
 1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
+1	SIMPLE	t3	index	c	c	5	NULL	6	Using index
+1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+a	b	c
+NULL	0	0
+NULL	1	1
+NULL	2	2
+0	0	0
+0	1	1
+0	2	2
+1	0	0
+1	1	1
+1	2	2
+2	0	0
+2	1	1
+2	2	2
+3	0	0
+3	1	1
+3	2	2
+4	0	0
+4	1	1
+4	2	2
+5	0	0
+5	1	1
+5	2	2
+6	0	0
+6	1	1
+6	2	2
+7	0	0
+7	1	1
+7	2	2
+8	0	0
+8	1	1
+8	2	2
+9	0	0
+9	1	1
+9	2	2
+10	0	0
+10	1	1
+10	2	2
+11	0	0
+11	1	1
+11	2	2
+12	0	0
+12	1	1
+12	2	2
+13	0	0
+13	1	1
+13	2	2
+14	0	0
+14	1	1
+14	2	2
+15	0	0
+15	1	1
+15	2	2
+16	0	0
+16	1	1
+16	2	2
+17	0	0
+17	1	1
+17	2	2
+18	0	0
+18	1	1
+18	2	2
+19	0	0
+19	1	1
+19	2	2
+DELETE FROM t3;
+EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
+1	SIMPLE	t3	index	c	c	5	NULL	0	Using index
+1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+a	b	c
+NULL	NULL	NULL
+0	NULL	NULL
+1	NULL	NULL
+2	NULL	NULL
+3	NULL	NULL
+4	NULL	NULL
+5	NULL	NULL
+6	NULL	NULL
+7	NULL	NULL
+8	NULL	NULL
+9	NULL	NULL
+10	NULL	NULL
+11	NULL	NULL
+12	NULL	NULL
+13	NULL	NULL
+14	NULL	NULL
+15	NULL	NULL
+16	NULL	NULL
+17	NULL	NULL
+18	NULL	NULL
+19	NULL	NULL
 DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 3f4cfa815d3..16b12a436d3 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2181,10 +2181,10 @@ a	a	a
 select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
 a	a	a
 1	1	2
-2	2	2
-3	3	2
 1	1	3
+2	2	2
 2	2	3
+3	3	2
 3	3	3
 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
 a	a	a
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 4a00610f242..a7f6d2a1b2f 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1,3 +1,4 @@
+reset master;
 use test;
 drop table if exists t1;
 drop table if exists t2;
@@ -1457,7 +1458,6 @@ show keys from t1;
 show master status;
 show open tables like 'foo';
 show privileges;
-show processlist;
 show slave hosts;
 show slave status;
 show status like 'foo';
@@ -1513,8 +1513,6 @@ Shutdown	Server Admin	To shut down the server
 Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
 Update	Tables	To update existing rows
 Usage	Server Admin	No privileges - allow connect only
-Id	User	Host	db	Command	Time	State	Info
-1	root	localhost	test	Query	0	query end	call bug4902()
 Server_id	Host	Port	Rpl_recovery_rank	Master_id
 Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master
 Variable_name	Value
@@ -1569,8 +1567,6 @@ Shutdown	Server Admin	To shut down the server
 Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
 Update	Tables	To update existing rows
 Usage	Server Admin	No privileges - allow connect only
-Id	User	Host	db	Command	Time	State	Info
-1	root	localhost	test	Query	0	query end	call bug4902()
 Server_id	Host	Port	Rpl_recovery_rank	Master_id
 Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master
 Variable_name	Value
@@ -1578,6 +1574,17 @@ Tables_in_test (foo)	table_type
 Variable_name	Value
 Level	Code	Message
 drop procedure bug4902|
+create procedure bug4902_2()
+begin
+show processlist;
+end|
+call bug4902_2()|
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	test	Query	0	NULL	call bug4902_2()
+call bug4902_2()|
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	test	Query	0	NULL	call bug4902_2()
+drop procedure bug4902_2|
 drop table if exists t3|
 create procedure bug4904()
 begin
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index 63623cb5f54..9591f1fa7ed 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -745,5 +745,10 @@ INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);
  
 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+
+DELETE FROM t3;
+EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
 
 DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 9a641559959..3913afed735 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -3,6 +3,10 @@
 #
 #
 
+# Let us reset binary logs in order to make show master status 
+# in test for bug 4902 repeatable.
+reset master;
+
 use test;
 
 --disable_warnings
@@ -1701,7 +1705,6 @@ begin
   show master status;
   show open tables like 'foo';
   show privileges;
-  show processlist;
   show slave hosts;
   show slave status;
   show status like 'foo';
@@ -1717,6 +1720,17 @@ call bug4902()|
 
 drop procedure bug4902|
 
+# We need separate SP for SHOW PROCESSLIST  since we want use replace_column
+create procedure bug4902_2()
+begin
+  show processlist;
+end|
+--replace_column 1 #
+call bug4902_2()|
+--replace_column 1 #
+call bug4902_2()|
+drop procedure bug4902_2|
+
 #
 # BUG#4904
 #
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4eeef610b0c..1768f332fd3 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2304,7 +2304,8 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
     if (s->worst_seeks < 2.0)			// Fix for small tables
       s->worst_seeks=2.0;
 
-    if (! s->const_keys.is_clear_all())
+    if (!s->const_keys.is_clear_all() &&
+        !s->table->pos_in_table_list->embedding)
     {
       ha_rows records;
       SQL_SELECT *select;
@@ -6077,10 +6078,8 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
       table->embedding->nested_join->used_tables|= used_tables;
       table->embedding->nested_join->not_null_tables|= not_null_tables;
     }
-   
-    if (!table->outer_join || (used_tables & not_null_tables) ||
-        (table->outer_join &&
-         !(table->on_expr->used_tables() & ~used_tables)))
+
+    if (!table->outer_join || (used_tables & not_null_tables))
     {
       /* 
         For some of the inner tables there are conjunctive predicates
@@ -6130,7 +6129,20 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
       if (prev_table->straight)
         prev_table->dep_tables|= used_tables;
       if (prev_table->on_expr)
+      {
         prev_table->dep_tables|= table->on_expr_dep_tables;
+        table_map prev_used_tables= prev_table->nested_join ?
+	                            prev_table->nested_join->used_tables :
+	                            prev_table->table->map;
+        /* 
+          If on expression contains only references to inner tables
+          we still make the inner tables dependent on the outer tables.
+          It would be enough to set dependency only on one outer table
+          for them. Yet this is really a rare case.
+	*/  
+        if (!(prev_table->on_expr->used_tables() & ~prev_used_tables))
+          prev_table->dep_tables|= used_tables;
+      }
     }
     prev_table= table;
   }