List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:November 29 2010 1:04pm
Subject:bzr commit into mysql-trunk-bugfixing branch (guilhem:3289) WL#5694
View as plain text  
#At file:///home/mysql_src/bzrrepos_new/jcl4/ based on revid:jorgen.loland@stripped

 3289 Guilhem Bichot	2010-11-29
      WL#5694: "change default of optimizer_join_cache_level from 1 to 4"
      in this revision, only tests are modified:
      - sort results of queries whose result will have different order with jcl=4
      - don't print the value of jcl in the test
      This way, the next revision which changes the default to 4
      should be smaller and faster to review.
     @ mysql-test/include/subquery_mat.inc
        sort resumlts

    modified:
      mysql-test/include/subquery_mat.inc
      mysql-test/r/func_group.result
      mysql-test/r/join.result
      mysql-test/r/join_nested.result
      mysql-test/r/join_nested_jcl6.result
      mysql-test/r/join_outer_jcl6.result
      mysql-test/r/select_all_jcl6.result
      mysql-test/r/select_icp_mrr_jcl6.result
      mysql-test/r/select_none_jcl6.result
      mysql-test/r/subquery_all_jcl6.result
      mysql-test/r/subquery_nomat_nosj_jcl6.result
      mysql-test/r/subquery_none_jcl6.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_innodb_all_jcl6.result
      mysql-test/r/subquery_sj_innodb_all_jcl7.result
      mysql-test/r/subquery_sj_innodb_none_jcl6.result
      mysql-test/r/subquery_sj_innodb_none_jcl7.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      mysql-test/r/union.result
      mysql-test/r/view.result
      mysql-test/suite/funcs_1/views/views_master.inc
      mysql-test/t/func_group.test
      mysql-test/t/func_op.test
      mysql-test/t/group_by.test
      mysql-test/t/join.test
      mysql-test/t/join_nested.test
      mysql-test/t/join_nested_jcl6.test
      mysql-test/t/join_outer.test
      mysql-test/t/join_outer_jcl6.test
      mysql-test/t/select_all_jcl6.test
      mysql-test/t/select_icp_mrr_jcl6.test
      mysql-test/t/select_none_jcl6.test
      mysql-test/t/subquery_all_jcl6.test
      mysql-test/t/subquery_nomat_nosj_jcl6.test
      mysql-test/t/subquery_none_jcl6.test
      mysql-test/t/subquery_sj_all_jcl6.test
      mysql-test/t/subquery_sj_all_jcl7.test
      mysql-test/t/subquery_sj_dupsweed_jcl6.test
      mysql-test/t/subquery_sj_dupsweed_jcl7.test
      mysql-test/t/subquery_sj_firstmatch_jcl6.test
      mysql-test/t/subquery_sj_firstmatch_jcl7.test
      mysql-test/t/subquery_sj_innodb_all_jcl6.test
      mysql-test/t/subquery_sj_innodb_all_jcl7.test
      mysql-test/t/subquery_sj_innodb_none_jcl6.test
      mysql-test/t/subquery_sj_innodb_none_jcl7.test
      mysql-test/t/subquery_sj_loosescan_jcl6.test
      mysql-test/t/subquery_sj_loosescan_jcl7.test
      mysql-test/t/subquery_sj_mat_jcl6.test
      mysql-test/t/subquery_sj_mat_jcl7.test
      mysql-test/t/subquery_sj_none_jcl6.test
      mysql-test/t/subquery_sj_none_jcl7.test
      mysql-test/t/union.test
      mysql-test/t/view.test
=== modified file 'mysql-test/include/subquery_mat.inc'
--- a/mysql-test/include/subquery_mat.inc	2010-09-08 14:39:38 +0000
+++ b/mysql-test/include/subquery_mat.inc	2010-11-29 13:04:34 +0000
@@ -356,6 +356,7 @@ create table t1 (s1 int);
 create table t2 (s2 int);
 insert into t1 values (5),(1),(0);
 insert into t2 values (0), (1);
+--sorted_result
 select s2 from t2 where s2 in (select s1 from t1);
 drop table t1, t2;
 
@@ -421,6 +422,7 @@ having a in (select c from t2 where d >=
 explain extended
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
+--sorted_result
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 

=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2010-09-28 15:17:29 +0000
+++ b/mysql-test/r/func_group.result	2010-11-29 13:04:34 +0000
@@ -223,10 +223,10 @@ max(t2.a1)	max(t1.a1)
 NULL	NULL
 select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
 a1	a2	a1	a2
-10	aaa	AAA	10
-10	aaa	BBB	20
 10	NULL	AAA	10
 10	NULL	BBB	20
+10	aaa	AAA	10
+10	aaa	BBB	20
 10	bbb	AAA	10
 10	bbb	BBB	20
 20	zzz	NULL	NULL

=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result	2010-10-05 09:15:51 +0000
+++ b/mysql-test/r/join.result	2010-11-29 13:04:34 +0000
@@ -340,8 +340,8 @@ i
 2
 select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
 i	i	i
-NULL	NULL	1
 2	2	2
+NULL	NULL	1
 select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
 i	i
 1	2

=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result	2010-11-16 16:17:25 +0000
+++ b/mysql-test/r/join_nested.result	2010-11-29 13:04:34 +0000
@@ -127,17 +127,17 @@ LEFT JOIN              
 ON t2.b=t4.b;
 a	b	a	b	a	b	a	b
 3	3	NULL	NULL	NULL	NULL	NULL	NULL
-4	2	1	2	3	2	3	1
 4	2	1	2	3	2	2	2
+4	2	1	2	3	2	3	1
 4	2	1	2	3	2	3	3
-4	2	1	2	4	2	3	1
 4	2	1	2	4	2	2	2
+4	2	1	2	4	2	3	1
 4	2	1	2	4	2	3	3
-4	2	2	2	3	2	3	1
 4	2	2	2	3	2	2	2
+4	2	2	2	3	2	3	1
 4	2	2	2	3	2	3	3
-4	2	2	2	4	2	3	1
 4	2	2	2	4	2	2	2
+4	2	2	2	4	2	3	1
 4	2	2	2	4	2	3	3
 5	3	NULL	NULL	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
@@ -162,11 +162,11 @@ ON t2.b=t4.b
 WHERE t3.a>1 OR t3.c IS NULL;
 a	b	a	b	a	b	a	b
 3	3	NULL	NULL	NULL	NULL	NULL	NULL
-4	2	2	2	3	2	3	1
 4	2	2	2	3	2	2	2
+4	2	2	2	3	2	3	1
 4	2	2	2	3	2	3	3
-4	2	2	2	4	2	3	1
 4	2	2	2	4	2	2	2
+4	2	2	2	4	2	3	1
 4	2	2	2	4	2	3	3
 5	3	NULL	NULL	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
@@ -242,12 +242,12 @@ a	b	a	b	a	b
 3	2	1	1	NULL	NULL
 3	2	2	2	0	2
 3	2	2	2	1	2
-6	2	1	1	NULL	NULL
-6	2	2	2	0	2
-6	2	2	2	1	2
 6	1	1	1	NULL	NULL
 6	1	2	2	0	2
 6	1	2	2	1	2
+6	2	1	1	NULL	NULL
+6	2	2	2	0	2
+6	2	2	2	1	2
 SELECT t5.a,t5.b
 FROM t5;
 a	b
@@ -265,12 +265,12 @@ ON t7.b=t8.b AND t6.b < 10
 )
 ON t6.b >= 2 AND t5.b=t7.b;
 a	b	a	b	a	b	a	b
-3	1	3	2	1	1	NULL	NULL
-3	1	6	2	1	1	NULL	NULL
 2	2	3	2	2	2	0	2
 2	2	3	2	2	2	1	2
 2	2	6	2	2	2	0	2
 2	2	6	2	2	2	1	2
+3	1	3	2	1	1	NULL	NULL
+3	1	6	2	1	1	NULL	NULL
 3	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t5 
@@ -284,10 +284,10 @@ ON t7.b=t8.b AND t6.b < 10
 ON t6.b >= 2 AND t5.b=t7.b AND
 (t8.a < 1 OR t8.c IS NULL);
 a	b	a	b	a	b	a	b
-3	1	3	2	1	1	NULL	NULL
-3	1	6	2	1	1	NULL	NULL
 2	2	3	2	2	2	0	2
 2	2	6	2	2	2	0	2
+3	1	3	2	1	1	NULL	NULL
+3	1	6	2	1	1	NULL	NULL
 3	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t2
@@ -315,33 +315,33 @@ ON t7.b=t8.b AND t6.b < 10
 )
 ON t6.b >= 2 AND t5.b=t7.b;
 a	b	a	b	a	b	a	b	a	b	a	b	a	b
-3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
-4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
-4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
-5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
+3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 4	2	1	2	3	2	2	2	3	2	2	2	0	2
 4	2	1	2	3	2	2	2	3	2	2	2	1	2
 4	2	1	2	3	2	2	2	6	2	2	2	0	2
 4	2	1	2	3	2	2	2	6	2	2	2	1	2
+4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
+4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
+4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 4	2	1	2	4	2	2	2	3	2	2	2	0	2
 4	2	1	2	4	2	2	2	3	2	2	2	1	2
 4	2	1	2	4	2	2	2	6	2	2	2	0	2
 4	2	1	2	4	2	2	2	6	2	2	2	1	2
+4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
+4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
-3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
@@ -361,17 +361,17 @@ ON t6.b >= 2 AND t5.b=t7.b
 WHERE t2.a > 3 AND
 (t6.a < 6 OR t6.c IS NULL);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b
-4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
 4	2	1	2	3	2	2	2	3	2	2	2	0	2
 4	2	1	2	3	2	2	2	3	2	2	2	1	2
+4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
+4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 4	2	1	2	4	2	2	2	3	2	2	2	0	2
 4	2	1	2	4	2	2	2	3	2	2	2	1	2
+4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
-4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b
 FROM t1;
@@ -402,12 +402,12 @@ ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 
 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
 (t1.a != 2);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
-1	3	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-1	3	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	3	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 1	3	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 1	3	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 1	3	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+1	3	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+1	3	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	3	3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
 1	3	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
@@ -415,41 +415,41 @@ a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
 1	3	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
 1	3	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 1	3	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	3	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-3	2	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 3	2	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 3	2	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 3	2	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	2	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+3	2	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	1	2
 3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	6	2	2	2	1	2
+3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
+3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
 3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
 3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
+3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
@@ -481,34 +481,34 @@ a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
 1	3	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
 1	3	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 1	3	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	3	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	1	2
 3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	6	2	2	2	1	2
+3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
+3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
 3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
 3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
+3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t0.a,t0.b
 FROM t0;
@@ -581,26 +581,26 @@ t0.b=t1.b AND          
 (t2.a >= 4 OR t2.c IS NULL);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
 1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
-1	2	3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	4	2	1	2	3	2	2	2	3	2	2	2	0	2
 1	2	3	2	4	2	1	2	3	2	2	2	3	2	2	2	1	2
 1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
 1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	1	2
+1	2	3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
+1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	2	3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
 1	2	3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
 1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
 1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
+1	2	3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
@@ -692,21 +692,21 @@ t0.b=t1.b AND          
 (t9.a=1);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
 1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	2
-1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	1
+1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	2
+1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
-1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
+1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	2
+1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	1
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	2
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 SELECT t1.a,t1.b
 FROM t1;
@@ -743,12 +743,12 @@ ON t2.b=t3.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b
 1	3	3	3	NULL	NULL
-2	2	3	3	NULL	NULL
 1	3	4	2	1	2
 1	3	4	2	2	2
+1	3	5	3	NULL	NULL
+2	2	3	3	NULL	NULL
 2	2	4	2	1	2
 2	2	4	2	2	2
-1	3	5	3	NULL	NULL
 2	2	5	3	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
 FROM t1, t3 
@@ -758,12 +758,12 @@ ON t2.b=t3.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b
 1	3	3	3	NULL	NULL
-2	2	3	3	NULL	NULL
 1	3	4	2	1	2
 1	3	4	2	2	2
+1	3	5	3	NULL	NULL
+2	2	3	3	NULL	NULL
 2	2	4	2	1	2
 2	2	4	2	2	2
-1	3	5	3	NULL	NULL
 2	2	5	3	NULL	NULL
 SELECT t3.a,t3.b,t4.a,t4.b
 FROM t3,t4;
@@ -790,12 +790,12 @@ ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b	a	b
 1	3	3	3	NULL	NULL	NULL	NULL
-2	2	3	3	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2
 1	3	4	2	1	2	4	2
+1	3	5	3	NULL	NULL	NULL	NULL
+2	2	3	3	NULL	NULL	NULL	NULL
 2	2	4	2	1	2	3	2
 2	2	4	2	1	2	4	2
-1	3	5	3	NULL	NULL	NULL	NULL
 2	2	5	3	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t1, (t3, t4) 
@@ -805,12 +805,12 @@ ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b	a	b
 1	3	3	3	NULL	NULL	NULL	NULL
-2	2	3	3	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2
 1	3	4	2	1	2	4	2
+1	3	5	3	NULL	NULL	NULL	NULL
+2	2	3	3	NULL	NULL	NULL	NULL
 2	2	4	2	1	2	3	2
 2	2	4	2	1	2	4	2
-1	3	5	3	NULL	NULL	NULL	NULL
 2	2	5	3	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t1, (t3, t4)
@@ -820,12 +820,12 @@ ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b	a	b
 1	3	3	3	NULL	NULL	NULL	NULL
-2	2	3	3	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2
 1	3	4	2	1	2	4	2
+1	3	5	3	NULL	NULL	NULL	NULL
+2	2	3	3	NULL	NULL	NULL	NULL
 2	2	4	2	1	2	3	2
 2	2	4	2	1	2	4	2
-1	3	5	3	NULL	NULL	NULL	NULL
 2	2	5	3	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b

=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result	2010-06-09 14:16:33 +0000
+++ b/mysql-test/r/join_nested_jcl6.result	2010-11-29 13:04:34 +0000
@@ -52,11 +52,11 @@ LEFT JOIN              
 (t3, t4)
 ON t2.b=t4.b;
 a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL
 4	2	1	2	3	2
-4	2	2	2	3	2
 4	2	1	2	4	2
+4	2	2	2	3	2
 4	2	2	2	4	2
-3	3	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t2
@@ -64,9 +64,9 @@ LEFT JOIN              
 (t3, t4)
 ON t3.a=1 AND t2.b=t4.b;
 a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL
 4	2	1	2	3	2
 4	2	1	2	4	2
-3	3	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
@@ -88,9 +88,9 @@ LEFT JOIN              
 ON t2.b=t4.b
 WHERE t3.a=1 OR t3.c IS NULL;
 a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL
 4	2	1	2	3	2
 4	2	1	2	4	2
-3	3	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t2
@@ -99,9 +99,9 @@ LEFT JOIN              
 ON t2.b=t4.b
 WHERE t3.a>1 OR t3.c IS NULL;
 a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL
 4	2	2	2	3	2
 4	2	2	2	4	2
-3	3	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL
 SELECT t5.a,t5.b
 FROM t5;
@@ -130,19 +130,19 @@ LEFT JOIN              
 (t3, t4, t5)
 ON t2.b=t4.b;
 a	b	a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL	NULL	NULL
+4	2	1	2	3	2	2	2
 4	2	1	2	3	2	3	1
-4	2	2	2	3	2	3	1
+4	2	1	2	3	2	3	3
+4	2	1	2	4	2	2	2
 4	2	1	2	4	2	3	1
-4	2	2	2	4	2	3	1
-4	2	1	2	3	2	2	2
+4	2	1	2	4	2	3	3
 4	2	2	2	3	2	2	2
-4	2	1	2	4	2	2	2
-4	2	2	2	4	2	2	2
-4	2	1	2	3	2	3	3
+4	2	2	2	3	2	3	1
 4	2	2	2	3	2	3	3
-4	2	1	2	4	2	3	3
+4	2	2	2	4	2	2	2
+4	2	2	2	4	2	3	1
 4	2	2	2	4	2	3	3
-3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
@@ -165,13 +165,13 @@ LEFT JOIN              
 ON t2.b=t4.b
 WHERE t3.a>1 OR t3.c IS NULL;
 a	b	a	b	a	b	a	b
-4	2	2	2	3	2	3	1
-4	2	2	2	4	2	3	1
+3	3	NULL	NULL	NULL	NULL	NULL	NULL
 4	2	2	2	3	2	2	2
-4	2	2	2	4	2	2	2
+4	2	2	2	3	2	3	1
 4	2	2	2	3	2	3	3
+4	2	2	2	4	2	2	2
+4	2	2	2	4	2	3	1
 4	2	2	2	4	2	3	3
-3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
@@ -196,9 +196,9 @@ ON t2.b=t4.b
 WHERE (t3.a>1 OR t3.c IS NULL) AND 
 (t5.a<3 OR t5.c IS NULL);
 a	b	a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL	NULL	NULL
 4	2	2	2	3	2	2	2
 4	2	2	2	4	2	2	2
-3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t6.a,t6.b
 FROM t6;
@@ -243,15 +243,15 @@ LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10;
 a	b	a	b	a	b
+3	2	1	1	NULL	NULL
 3	2	2	2	0	2
-6	2	2	2	0	2
-6	1	2	2	0	2
 3	2	2	2	1	2
-6	2	2	2	1	2
+6	1	1	1	NULL	NULL
+6	1	2	2	0	2
 6	1	2	2	1	2
-3	2	1	1	NULL	NULL
 6	2	1	1	NULL	NULL
-6	1	1	1	NULL	NULL
+6	2	2	2	0	2
+6	2	2	2	1	2
 SELECT t5.a,t5.b
 FROM t5;
 a	b
@@ -270,8 +270,8 @@ ON t7.b=t8.b AND t6.b < 10
 ON t6.b >= 2 AND t5.b=t7.b;
 a	b	a	b	a	b	a	b
 2	2	3	2	2	2	0	2
-2	2	6	2	2	2	0	2
 2	2	3	2	2	2	1	2
+2	2	6	2	2	2	0	2
 2	2	6	2	2	2	1	2
 3	1	3	2	1	1	NULL	NULL
 3	1	6	2	1	1	NULL	NULL
@@ -299,9 +299,9 @@ LEFT JOIN              
 (t3, t4)
 ON t3.a=1 AND t2.b=t4.b;
 a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL
 4	2	1	2	3	2
 4	2	1	2	4	2
-3	3	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
@@ -319,33 +319,33 @@ ON t7.b=t8.b AND t6.b < 10
 )
 ON t6.b >= 2 AND t5.b=t7.b;
 a	b	a	b	a	b	a	b	a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
+3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
+3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
+3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
+3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 4	2	1	2	3	2	2	2	3	2	2	2	0	2
-4	2	1	2	4	2	2	2	3	2	2	2	0	2
-4	2	1	2	3	2	2	2	6	2	2	2	0	2
-4	2	1	2	4	2	2	2	6	2	2	2	0	2
 4	2	1	2	3	2	2	2	3	2	2	2	1	2
-4	2	1	2	4	2	2	2	3	2	2	2	1	2
+4	2	1	2	3	2	2	2	6	2	2	2	0	2
 4	2	1	2	3	2	2	2	6	2	2	2	1	2
-4	2	1	2	4	2	2	2	6	2	2	2	1	2
 4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
 4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
-4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+4	2	1	2	4	2	2	2	3	2	2	2	0	2
+4	2	1	2	4	2	2	2	3	2	2	2	1	2
+4	2	1	2	4	2	2	2	6	2	2	2	0	2
+4	2	1	2	4	2	2	2	6	2	2	2	1	2
+4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
-3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
-3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
-3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
-3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
@@ -366,12 +366,12 @@ WHERE t2.a > 3 AND
 (t6.a < 6 OR t6.c IS NULL);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b
 4	2	1	2	3	2	2	2	3	2	2	2	0	2
-4	2	1	2	4	2	2	2	3	2	2	2	0	2
 4	2	1	2	3	2	2	2	3	2	2	2	1	2
-4	2	1	2	4	2	2	2	3	2	2	2	1	2
 4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
 4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+4	2	1	2	4	2	2	2	3	2	2	2	0	2
+4	2	1	2	4	2	2	2	3	2	2	2	1	2
+4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
 4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
@@ -406,55 +406,55 @@ ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 
 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
 (t1.a != 2);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
+1	3	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
+1	3	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
+1	3	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
+1	3	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+1	3	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+1	3	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
+1	3	3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+1	3	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
+1	3	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
+1	3	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+1	3	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+1	3	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
+1	3	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+3	2	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
+3	2	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
+3	2	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
+3	2	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	2	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+3	2	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
+3	2	3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	0	2
-3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
-3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
-3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	1	2
-3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
+3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	6	2	2	2	1	2
-3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
-1	3	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-1	3	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-1	3	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
-1	3	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
-1	3	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	3	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
+3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
+3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
+3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
+3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	3	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
-3	2	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
-1	3	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-3	2	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-1	3	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
-3	2	3	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
-1	3	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
-3	2	3	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
-1	3	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-3	2	3	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-1	3	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
-3	2	3	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
-1	3	3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-3	2	3	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t1
@@ -479,41 +479,41 @@ ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 
 (t1.a != 2)
 WHERE (t2.a >= 4 OR t2.c IS NULL);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
+1	3	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
+1	3	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
+1	3	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+1	3	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+1	3	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
+1	3	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
+1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	0	2
-3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
-3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
-3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	3	2	2	2	1	2
-3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
+3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
 3	2	4	2	1	2	3	2	2	2	6	2	2	2	1	2
-3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
-1	3	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-1	3	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
-1	3	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
-1	3	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
-3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
-1	3	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	3	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
+3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
+3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
+3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
+3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
-1	3	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
+3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
-1	3	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 SELECT t0.a,t0.b
 FROM t0;
 a	b
@@ -584,28 +584,28 @@ WHERE t0.a=1 AND
 t0.b=t1.b AND          
 (t2.a >= 4 OR t2.c IS NULL);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
+1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 1	2	3	2	4	2	1	2	3	2	2	2	3	2	2	2	0	2
-1	2	3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
-1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
-1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
 1	2	3	2	4	2	1	2	3	2	2	2	3	2	2	2	1	2
-1	2	3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
+1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2
 1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	1	2
-1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
 1	2	3	2	4	2	1	2	3	2	3	1	3	2	1	1	NULL	NULL
-1	2	3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL
-1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
+1	2	3	2	4	2	1	2	4	2	2	2	3	2	2	2	0	2
+1	2	3	2	4	2	1	2	4	2	2	2	3	2	2	2	1	2
+1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2
+1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	1	2
+1	2	3	2	4	2	1	2	4	2	3	1	3	2	1	1	NULL	NULL
+1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	0	2
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	3	2	2	2	1	2
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	3	2	1	1	NULL	NULL
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL
-1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
@@ -695,23 +695,23 @@ t0.b=t1.b AND          
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
+1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1
+1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
-1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	2
-1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
+1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
+1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	2
+1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	1
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
-1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 SELECT t1.a,t1.b
 FROM t1;
 a	b
@@ -735,9 +735,9 @@ LEFT JOIN              
 t3
 ON t2.b=t3.b;
 a	b	a	b
+3	3	NULL	NULL
 4	2	1	2
 4	2	2	2
-3	3	NULL	NULL
 5	3	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
 FROM t1, t2 
@@ -746,13 +746,13 @@ t3
 ON t2.b=t3.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b
+1	3	3	3	NULL	NULL
 1	3	4	2	1	2
-2	2	4	2	1	2
 1	3	4	2	2	2
-2	2	4	2	2	2
-1	3	3	3	NULL	NULL
-2	2	3	3	NULL	NULL
 1	3	5	3	NULL	NULL
+2	2	3	3	NULL	NULL
+2	2	4	2	1	2
+2	2	4	2	2	2
 2	2	5	3	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
 FROM t1, t3 
@@ -761,13 +761,13 @@ t2
 ON t2.b=t3.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b
+1	3	3	3	NULL	NULL
 1	3	4	2	1	2
-2	2	4	2	1	2
 1	3	4	2	2	2
-2	2	4	2	2	2
-1	3	3	3	NULL	NULL
-2	2	3	3	NULL	NULL
 1	3	5	3	NULL	NULL
+2	2	3	3	NULL	NULL
+2	2	4	2	1	2
+2	2	4	2	2	2
 2	2	5	3	NULL	NULL
 SELECT t3.a,t3.b,t4.a,t4.b
 FROM t3,t4;
@@ -782,9 +782,9 @@ LEFT JOIN              
 (t3, t4)
 ON t3.a=1 AND t2.b=t4.b;
 a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL
 4	2	1	2	3	2
 4	2	1	2	4	2
-3	3	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t1, t2 
@@ -793,13 +793,13 @@ LEFT JOIN              
 ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b	a	b
+1	3	3	3	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2
-2	2	4	2	1	2	3	2
 1	3	4	2	1	2	4	2
-2	2	4	2	1	2	4	2
-1	3	3	3	NULL	NULL	NULL	NULL
-2	2	3	3	NULL	NULL	NULL	NULL
 1	3	5	3	NULL	NULL	NULL	NULL
+2	2	3	3	NULL	NULL	NULL	NULL
+2	2	4	2	1	2	3	2
+2	2	4	2	1	2	4	2
 2	2	5	3	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t1, (t3, t4) 
@@ -808,13 +808,13 @@ t2
 ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b	a	b
+1	3	3	3	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2
-2	2	4	2	1	2	3	2
 1	3	4	2	1	2	4	2
-2	2	4	2	1	2	4	2
-1	3	3	3	NULL	NULL	NULL	NULL
-2	2	3	3	NULL	NULL	NULL	NULL
 1	3	5	3	NULL	NULL	NULL	NULL
+2	2	3	3	NULL	NULL	NULL	NULL
+2	2	4	2	1	2	3	2
+2	2	4	2	1	2	4	2
 2	2	5	3	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t1, (t3, t4)
@@ -823,13 +823,13 @@ t2
 ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b	a	b
+1	3	3	3	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2
-2	2	4	2	1	2	3	2
 1	3	4	2	1	2	4	2
-2	2	4	2	1	2	4	2
-1	3	3	3	NULL	NULL	NULL	NULL
-2	2	3	3	NULL	NULL	NULL	NULL
 1	3	5	3	NULL	NULL	NULL	NULL
+2	2	3	3	NULL	NULL	NULL	NULL
+2	2	4	2	1	2	3	2
+2	2	4	2	1	2	4	2
 2	2	5	3	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
@@ -1174,12 +1174,12 @@ INSERT INTO t3 VALUES (2);
 INSERT INTO t1 VALUES (2);
 SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;
 a	a	a
-2	2	2
 1	NULL	NULL
+2	2	2
 SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
 a	a	a
-2	2	2
 1	NULL	NULL
+2	2	2
 DELETE FROM t1 WHERE a=2;
 SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
 a	a	a
@@ -1340,12 +1340,12 @@ LEFT JOIN
 INNER JOIN t1 gp ON p.goods = gp.goods)
 ON gl.groupid = g.groupid and p.shop = 'fr') t;
 groupid	price
-3	2340
-6	9900
 1	NULL
 2	NULL
+3	2340
 4	NULL
 5	NULL
+6	9900
 CREATE VIEW v1 AS
 SELECT g.groupid groupid, p.goods goods,  
 p.name name, p.shop shop, 
@@ -1359,24 +1359,24 @@ LEFT JOIN
 v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
 SELECT * FROM v2;
 groupid	price
-3	2340
-6	9900
 1	NULL
 2	NULL
+3	2340
 4	NULL
 5	NULL
+6	9900
 SELECT * FROM 
 (SELECT DISTINCT g.groupid, fr.price
 FROM t4 g
 LEFT JOIN
 v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
 groupid	price
-3	2340
-6	9900
 1	NULL
 2	NULL
+3	2340
 4	NULL
 5	NULL
+6	9900
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
 CREATE TABLE t1(a int);
@@ -1851,6 +1851,3 @@ a	b	a	b	a	b	a	b
 3	2	NULL	NULL	NULL	NULL	NULL	NULL
 DROP TABLE t5,t6,t7,t8;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2010-09-01 14:00:12 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2010-11-29 13:04:34 +0000
@@ -70,9 +70,9 @@ grp	a	c	id	a	c	d
 1	1	a	3	4	A	4
 2	2	b	3	5	B	5
 2	3	c	3	6	C	6
+3	4	E	NULL	NULL	NULL	NULL
 3	5	C	3	6	C	6
 3	6	D	4	7	D	7
-3	4	E	NULL	NULL	NULL	NULL
 NULL	NULL		NULL	NULL	NULL	NULL
 select t1.*,t2.* from t1 natural left outer join t2;
 grp	a	c	id	a	c	d
@@ -103,11 +103,11 @@ id	select_type	table	type	possible_keys	
 select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 grp	a	c	id	a	c	d	a
 1	1	a	1	1	a	1	1
+2	2	b	NULL	NULL	NULL	NULL	NULL
+2	3	c	NULL	NULL	NULL	NULL	NULL
 3	4	E	3	4	A	4	4
 3	5	C	3	5	B	5	5
 3	6	D	3	6	C	6	6
-2	2	b	NULL	NULL	NULL	NULL	NULL
-2	3	c	NULL	NULL	NULL	NULL	NULL
 NULL	NULL		NULL	NULL	NULL	NULL	NULL
 explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
 ERROR 42S22: Unknown column 't3.a' in 'on clause'
@@ -308,8 +308,8 @@ select t1.name, t2.name, t2.id from t1 l
 name	name	id
 Antonio Paz	El Gato	1
 Antonio Paz	Perrito	2
-Thimble Smith	Happy	3
 Lilliana Angelovska	NULL	NULL
+Thimble Smith	Happy	3
 select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
 name	name	id
 Lilliana Angelovska	NULL	NULL
@@ -328,8 +328,8 @@ select t1.name, t2.name, t2.id from t2 r
 name	name	id
 Antonio Paz	El Gato	1
 Antonio Paz	Perrito	2
-Thimble Smith	Happy	3
 Lilliana Angelovska	NULL	NULL
+Thimble Smith	Happy	3
 select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
 name	name	id
 Lilliana Angelovska	NULL	NULL
@@ -348,8 +348,8 @@ select t1.name, t2.name, t2.id,t3.id fro
 name	name	id	id
 Antonio Paz	El Gato	1	1
 Antonio Paz	Perrito	2	1
-Thimble Smith	Happy	3	3
 Lilliana Angelovska	NULL	NULL	NULL
+Thimble Smith	Happy	3	3
 select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
 name	name	id	id
 Antonio Paz	El Gato	1	1
@@ -642,9 +642,9 @@ fooID	barID	fooID
 30	3	30
 select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
 fooID	barID	fooID
-30	3	30
 10	1	NULL
 20	2	NULL
+30	3	30
 drop table t1,t2;
 create table t1 (i int);
 create table t2 (i int);
@@ -654,21 +654,21 @@ insert into t2 values(2),(3);
 insert into t3 values(2),(4);
 select * from t1 natural left join t2 natural left join t3;
 i
-2
 1
+2
 select * from t1 natural left join t2 where (t2.i is not null)=0;
 i
 1
 select * from t1 natural left join t2 where (t2.i is not null) is not null;
 i
-2
 1
+2
 select * from t1 natural left join t2 where (i is not null)=0;
 i
 select * from t1 natural left join t2 where (i is not null) is not null;
 i
-2
 1
+2
 drop table t1,t2,t3;
 create table t1 (f1 integer,f2 integer,f3 integer);
 create table t2 (f2 integer,f4 integer);
@@ -1173,33 +1173,33 @@ INSERT INTO t1 VALUES (1), (2), (3), (4)
 INSERT INTO t2 VALUES (2), (3);
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
 a	b
+1	NULL
 2	2
 3	3
-1	NULL
 4	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
 a	b
+1	NULL
 2	2
 3	3
-1	NULL
 4	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
 a	b
+1	NULL
 2	2
 3	3
-1	NULL
 4	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
 a	b
+1	NULL
 2	2
 3	3
-1	NULL
 4	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
 a	b
+1	NULL
 2	2
 3	3
-1	NULL
 4	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
@@ -1476,6 +1476,3 @@ WHERE   t1.col_int_key IS NULL OR t4.pk 
 col_int
 DROP TABLE t1,t2,t3,t4;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/select_all_jcl6.result'
--- a/mysql-test/r/select_all_jcl6.result	2010-11-05 08:30:31 +0000
+++ b/mysql-test/r/select_all_jcl6.result	2010-11-29 13:04:34 +0000
@@ -4950,6 +4950,3 @@ DROP TABLE t1;
 # End of test BUG#57203
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/select_icp_mrr_jcl6.result'
--- a/mysql-test/r/select_icp_mrr_jcl6.result	2010-11-05 08:30:31 +0000
+++ b/mysql-test/r/select_icp_mrr_jcl6.result	2010-11-29 13:04:34 +0000
@@ -4950,6 +4950,3 @@ DROP TABLE t1;
 # End of test BUG#57203
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/select_none_jcl6.result'
--- a/mysql-test/r/select_none_jcl6.result	2010-11-05 08:30:31 +0000
+++ b/mysql-test/r/select_none_jcl6.result	2010-11-29 13:04:34 +0000
@@ -4949,6 +4949,3 @@ DROP TABLE t1;
 # End of test BUG#57203
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result	2010-11-24 22:20:27 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result	2010-11-29 13:04:34 +0000
@@ -6306,6 +6306,3 @@ pk
 drop table t1;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result	2010-11-24 22:20:27 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result	2010-11-29 13:04:34 +0000
@@ -6306,6 +6306,3 @@ pk
 drop table t1;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result	2010-11-16 16:02:57 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result	2010-11-29 13:04:34 +0000
@@ -6305,6 +6305,3 @@ pk
 drop table t1;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-11-29 13:04:34 +0000
@@ -5336,6 +5336,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-11-29 13:04:34 +0000
@@ -5336,6 +5336,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-11-29 13:04:34 +0000
@@ -5335,6 +5335,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-11-29 13:04:34 +0000
@@ -5335,6 +5335,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-11-29 13:04:34 +0000
@@ -5419,6 +5419,3 @@ SET @@optimizer_join_cache_level=default
 # End of bug#51457
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-11-29 13:04:34 +0000
@@ -5419,6 +5419,3 @@ SET @@optimizer_join_cache_level=default
 # End of bug#51457
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_innodb_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_innodb_all_jcl6.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all_jcl6.result	2010-11-29 13:04:34 +0000
@@ -147,6 +147,3 @@ id	select_type	table	type	possible_keys	
 drop table t2, t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_innodb_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_innodb_all_jcl7.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all_jcl7.result	2010-11-29 13:04:34 +0000
@@ -147,6 +147,3 @@ id	select_type	table	type	possible_keys	
 drop table t2, t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_innodb_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_innodb_none_jcl6.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl6.result	2010-11-29 13:04:34 +0000
@@ -147,6 +147,3 @@ id	select_type	table	type	possible_keys	
 drop table t2, t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_innodb_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_innodb_none_jcl7.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl7.result	2010-11-29 13:04:34 +0000
@@ -147,6 +147,3 @@ id	select_type	table	type	possible_keys	
 drop table t2, t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-11-29 13:04:34 +0000
@@ -5336,6 +5336,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-11-29 13:04:34 +0000
@@ -5336,6 +5336,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-11-29 13:04:34 +0000
@@ -5336,6 +5336,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-11-29 13:04:34 +0000
@@ -5336,6 +5336,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-11-29 13:04:34 +0000
@@ -5484,6 +5484,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2010-11-24 14:06:22 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-11-29 13:04:34 +0000
@@ -5484,6 +5484,3 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 # End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';
-Variable_name	Value
-optimizer_join_cache_level	1

=== modified file 'mysql-test/r/union.result'
--- a/mysql-test/r/union.result	2010-08-18 10:18:27 +0000
+++ b/mysql-test/r/union.result	2010-11-29 13:04:34 +0000
@@ -878,11 +878,11 @@ create table t1 (   RID int(11) not null
 insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');
 select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
 NAME	PHONE	NAME	PHONE
+NULL	NULL	f	666
+NULL	NULL	g	777
 a	111	NULL	NULL
 b	222	NULL	NULL
 d	444	d	454
-NULL	NULL	f	666
-NULL	NULL	g	777
 drop  table t1;
 create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
 insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2010-08-30 06:38:09 +0000
+++ b/mysql-test/r/view.result	2010-11-29 13:04:34 +0000
@@ -1680,6 +1680,7 @@ WHERE b.col2 IS NULL OR
 b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
 col1	col2	col2	col3
 1	trudy	2	y
+10	matt	NULL	NULL
 2	peter	1	n
 3	sanja	1	n
 4	monty	3	n
@@ -1687,13 +1688,13 @@ col1	col2	col2	col3
 6	kent	1	n
 7	carsten	NULL	NULL
 8	ranger	1	y
-10	matt	NULL	NULL
 SELECT a.col1,a.col2,b.col2,b.col3
 FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1
 WHERE b.col2 IS NULL OR
 b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
 col1	col2	col2	col3
 1	trudy	2	y
+10	matt	NULL	NULL
 2	peter	1	n
 3	sanja	1	n
 4	monty	3	n
@@ -1701,7 +1702,6 @@ col1	col2	col2	col3
 6	kent	1	n
 7	carsten	NULL	NULL
 8	ranger	1	y
-10	matt	NULL	NULL
 CREATE VIEW v2 AS SELECT * FROM t2;
 SELECT a.col1,a.col2,b.col2,b.col3
 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
@@ -1709,6 +1709,7 @@ WHERE b.col2 IS NULL OR
 b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
 col1	col2	col2	col3
 1	trudy	2	y
+10	matt	NULL	NULL
 2	peter	1	n
 3	sanja	1	n
 4	monty	3	n
@@ -1716,7 +1717,6 @@ col1	col2	col2	col3
 6	kent	1	n
 7	carsten	NULL	NULL
 8	ranger	1	y
-10	matt	NULL	NULL
 SELECT a.col1,a.col2,b.col2,b.col3
 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
 WHERE a.col1 IN (1,5,9) AND

=== modified file 'mysql-test/suite/funcs_1/views/views_master.inc'
--- a/mysql-test/suite/funcs_1/views/views_master.inc	2010-05-30 07:48:22 +0000
+++ b/mysql-test/suite/funcs_1/views/views_master.inc	2010-11-29 13:04:34 +0000
@@ -2546,6 +2546,7 @@ Select t1.f59 t1_f59, t2.f59 t2_f59, t1.
        t1.f61 t1_f61, t2.f61 t2_f61
 FROM t2 right outer join t1 on t2.f59=t1.f59;
 Select * from v1 order by t1_f59;
+--sorted_result
 Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
        t1.f61 t1_f61, t2.f61 t2_f61
 FROM t2 right outer join t1 on t2.f59=t1.f59;
@@ -2555,6 +2556,7 @@ Create or replace view test.v1 as
 Select f59, f60, a, b
 FROM t2 natural right outer join t1;
 Select * from v1 order by f59 desc;
+--sorted_result
 Select f59, f60, a, b
 FROM t2 natural right outer join t1;
 

=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test	2010-09-28 15:17:29 +0000
+++ b/mysql-test/t/func_group.test	2010-11-29 13:04:34 +0000
@@ -140,6 +140,7 @@ select t1.a1, t1.a2, t2.a1, t2.a2 from t
 
 select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
 select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
+--sorted_result
 select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
 select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
 select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;

=== modified file 'mysql-test/t/func_op.test'
--- a/mysql-test/t/func_op.test	2006-04-11 10:26:18 +0000
+++ b/mysql-test/t/func_op.test	2010-11-29 13:04:34 +0000
@@ -28,6 +28,7 @@ create table t1(a int);
 create table t2(a int, b int);
 insert into t1 values (1), (2), (3);
 insert into t2 values (1, 7), (3, 7);
+--sorted_result
 select t1.a, t2.a, t2.b, bit_count(t2.b) from t1 left join t2 on t1.a=t2.a;
 drop table t1, t2;
 

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2010-10-25 09:20:53 +0000
+++ b/mysql-test/t/group_by.test	2010-11-29 13:04:34 +0000
@@ -339,6 +339,7 @@ drop table t1,t2;
 CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
 ));
 insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
+--sorted_result
 select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2;
 select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS  on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1;
 drop table t1;

=== modified file 'mysql-test/t/join.test'
--- a/mysql-test/t/join.test	2010-09-07 13:43:00 +0000
+++ b/mysql-test/t/join.test	2010-11-29 13:04:34 +0000
@@ -312,14 +312,20 @@ insert into t1 values(1),(2);
 insert into t2 values(2),(3);
 insert into t3 values (2),(4);
 
+--sorted_result
 select * from t1 natural left join t2;
+--sorted_result
 select * from t1 left join t2 on (t1.i=t2.i);
+--sorted_result
 select * from t1 natural left join t2 natural left join t3;
+--sorted_result
 select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
 
 select * from t3 natural right join t2;
 select * from t3 right join t2 on (t3.i=t2.i);
+--sorted_result
 select * from t3 natural right join t2 natural right join t1;
+--sorted_result
 select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
 
 select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
@@ -419,9 +425,13 @@ select * from v2a natural join v3a;
 
 # Nested natural/using joins.
 select * from (t1 natural join t2) natural join (t3 natural join t4);
+--sorted_result
 select * from (t1 natural join t2) natural left join (t3 natural join t4);
+--sorted_result
 select * from (t3 natural join t4) natural right join (t1 natural join t2);
+--sorted_result
 select * from (t1 natural left join t2) natural left join (t3 natural left join t4);
+--sorted_result
 select * from (t4 natural right join t3) natural right join (t2 natural right join t1);
 select * from t1 natural join t2 natural join t3 natural join t4;
 select * from ((t1 natural join t2) natural join t3) natural join t4;
@@ -481,9 +491,11 @@ select t1.*, t2.*, t3.*, t4.* from (t1 n
 select * from (select * from t1 natural join t2) as t12
               natural join
               (select * from t3 natural join t4) as t34;
+--sorted_result
 select * from (select * from t1 natural join t2) as t12
               natural left join
               (select * from t3 natural join t4) as t34;
+--sorted_result
 select * from (select * from t3 natural join t4) as t34
               natural right join
               (select * from t1 natural join t2) as t12;

=== modified file 'mysql-test/t/join_nested.test'
--- a/mysql-test/t/join_nested.test	2007-07-12 01:45:35 +0000
+++ b/mysql-test/t/join_nested.test	2010-11-29 13:04:34 +0000
@@ -38,12 +38,14 @@ SELECT t4.a,t4.b
 SELECT t3.a,t3.b,t4.a,t4.b
   FROM t3,t4;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t2
        LEFT JOIN              
        (t3, t4)
        ON t2.b=t4.b;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t2
        LEFT JOIN              
@@ -58,6 +60,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
        ON t2.b=t4.b
     WHERE t3.a=1 OR t3.c IS NULL;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t2
        LEFT JOIN              
@@ -65,6 +68,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
        ON t2.b=t4.b
     WHERE t3.a=1 OR t3.c IS NULL;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t2
        LEFT JOIN              
@@ -78,6 +82,7 @@ SELECT t5.a,t5.b
 SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
   FROM t3,t4,t5;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
   FROM t2
        LEFT JOIN              
@@ -92,6 +97,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.
        ON t2.b=t4.b
     WHERE t3.a>1 OR t3.c IS NULL;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
   FROM t2
        LEFT JOIN              
@@ -108,6 +114,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.
     WHERE (t3.a>1 OR t3.c IS NULL) AND 
           (t5.a<3 OR t5.c IS NULL);
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
   FROM t2
        LEFT JOIN              
@@ -135,6 +142,7 @@ SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
        t8
        ON t7.b=t8.b AND t6.b < 10;
 
+--sorted_result
 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM (t6, t7)
        LEFT JOIN 
@@ -144,6 +152,7 @@ SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 SELECT t5.a,t5.b
   FROM t5;
 
+--sorted_result
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t5 
        LEFT JOIN 
@@ -155,6 +164,7 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.
        )
        ON t6.b >= 2 AND t5.b=t7.b;
 
+--sorted_result
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t5 
        LEFT JOIN 
@@ -167,12 +177,14 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.
        ON t6.b >= 2 AND t5.b=t7.b AND
           (t8.a < 1 OR t8.c IS NULL);
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t2
        LEFT JOIN              
        (t3, t4)
        ON t3.a=1 AND t2.b=t4.b;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t2
@@ -189,6 +201,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        )
        ON t6.b >= 2 AND t5.b=t7.b; 
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t2
@@ -210,6 +223,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 SELECT t1.a,t1.b
   FROM t1;
 
+--sorted_result
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t1
@@ -233,6 +247,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.
           (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
           (t1.a != 2);
 
+--sorted_result
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t1
@@ -287,6 +302,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL); 
 
+--sorted_result
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t0,t1
@@ -352,6 +368,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.
 SELECT t9.a,t9.b
   FROM t9;
 
+--sorted_result
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
   FROM t0,t1
@@ -396,12 +413,14 @@ SELECT t2.a,t2.b
 SELECT t3.a,t3.b
   FROM t3;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b
   FROM t2 
        LEFT JOIN              
        t3
        ON t2.b=t3.b;
 
+--sorted_result
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
   FROM t1, t2 
        LEFT JOIN              
@@ -409,6 +428,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
        ON t2.b=t3.b
     WHERE t1.a <= 2;
 
+--sorted_result
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
   FROM t1, t3 
        RIGHT JOIN              
@@ -419,12 +439,14 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
 SELECT t3.a,t3.b,t4.a,t4.b
   FROM t3,t4;
 
+--sorted_result
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t2 
        LEFT JOIN              
        (t3, t4)
        ON t3.a=1 AND t2.b=t4.b;
 
+--sorted_result
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t1, t2 
        LEFT JOIN              
@@ -432,6 +454,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.
        ON t3.a=1 AND t2.b=t4.b
     WHERE t1.a <= 2;
 
+--sorted_result
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t1, (t3, t4) 
        RIGHT JOIN              
@@ -439,6 +462,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.
        ON t3.a=1 AND t2.b=t4.b
     WHERE t1.a <= 2;
 
+--sorted_result
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
   FROM t1, (t3, t4)
        RIGHT JOIN              
@@ -695,10 +719,12 @@ INSERT INTO t1 VALUES (2);
 
 #check proper syntax for nested outer joins
 
+--sorted_result
 SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;
 
 #must be equivalent to:
 
+--sorted_result
 SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
 
 #check that everything is al right when all tables contain not more than 1 row
@@ -770,6 +796,7 @@ INSERT INTO t3 VALUES (3,23), (6,26);
 CREATE TABLE t4 (groupid int(12));
 INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
 
+--sorted_result
 SELECT * FROM
 (SELECT DISTINCT gl.groupid, gp.price
   FROM t4 gl 
@@ -791,8 +818,10 @@ SELECT DISTINCT g.groupid, fr.price
        LEFT JOIN
        v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
 
+--sorted_result
 SELECT * FROM v2;
 
+--sorted_result
 SELECT * FROM 
 (SELECT DISTINCT g.groupid, fr.price
   FROM t4 g

=== modified file 'mysql-test/t/join_nested_jcl6.test'
--- a/mysql-test/t/join_nested_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/join_nested_jcl6.test	2010-11-29 13:04:34 +0000
@@ -94,4 +94,3 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.
 DROP TABLE t5,t6,t7,t8;
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2010-08-26 13:26:03 +0000
+++ b/mysql-test/t/join_outer.test	2010-11-29 13:04:34 +0000
@@ -22,6 +22,7 @@ select t1.*,t2.* from t1 as t0,{ oj t2 l
 select t1.*,t2.* from t1 left join t2 using (a);
 select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
 select t1.*,t2.* from t1 left join t2 using (a,c);
+--sorted_result
 select t1.*,t2.* from t1 left join t2 using (c);
 select t1.*,t2.* from t1 natural left outer join t2;
 
@@ -31,6 +32,7 @@ select t1.*,t2.* from t1 left join t2 on
 explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
 explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
 
+--sorted_result
 select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 
 # The next query should rearange the left joins to get this to work
@@ -233,18 +235,21 @@ INSERT INTO t2 VALUES (1,1,'El Gato');
 INSERT INTO t2 VALUES (2,1,'Perrito');
 INSERT INTO t2 VALUES (3,3,'Happy');
 
+--sorted_result
 select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);
 select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
 explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
 explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
 select count(*) from t1 left join t2 on (t1.id = t2.owner);
 
+--sorted_result
 select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
 select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
 explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
 explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
 select count(*) from t2 right join t1 on (t1.id = t2.owner);
 
+--sorted_result
 select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner;
 select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
 select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
@@ -420,6 +425,7 @@ insert into t1 (fooID) values (10),(20),
 insert into t2 values (10,1),(20,2),(30,3);
 explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
 select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
+--sorted_result
 select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
 drop table t1,t2;
 
@@ -429,10 +435,13 @@ create table t3 (i int);
 insert into t1 values(1),(2);
 insert into t2 values(2),(3);
 insert into t3 values(2),(4);
+--sorted_result
 select * from t1 natural left join t2 natural left join t3;
 select * from t1 natural left join t2 where (t2.i is not null)=0;
+--sorted_result
 select * from t1 natural left join t2 where (t2.i is not null) is not null;
 select * from t1 natural left join t2 where (i is not null)=0;
+--sorted_result
 select * from t1 natural left join t2 where (i is not null) is not null;
 drop table t1,t2,t3;
 
@@ -800,11 +809,16 @@ CREATE TABLE t2 (b int);
 INSERT INTO t1 VALUES (1), (2), (3), (4);
 INSERT INTO t2 VALUES (2), (3);
 
+--sorted_result
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
 
+--sorted_result
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
+--sorted_result
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
+--sorted_result
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
+--sorted_result
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
 
 DROP TABLE t1,t2;

=== modified file 'mysql-test/t/join_outer_jcl6.test'
--- a/mysql-test/t/join_outer_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/join_outer_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/join_outer.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/select_all_jcl6.test'
--- a/mysql-test/t/select_all_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/select_all_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/select_all.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/select_icp_mrr_jcl6.test'
--- a/mysql-test/t/select_icp_mrr_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/select_icp_mrr_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/select_icp_mrr.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/select_none_jcl6.test'
--- a/mysql-test/t/select_none_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/select_none_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/select_none.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_all_jcl6.test'
--- a/mysql-test/t/subquery_all_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_all_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_all.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_nomat_nosj_jcl6.test'
--- a/mysql-test/t/subquery_nomat_nosj_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_nomat_nosj_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_nomat_nosj.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_none_jcl6.test'
--- a/mysql-test/t/subquery_none_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_none_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_none.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_all_jcl6.test'
--- a/mysql-test/t/subquery_sj_all_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_all_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_all.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_all_jcl7.test'
--- a/mysql-test/t/subquery_sj_all_jcl7.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_all_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_all.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_dupsweed_jcl6.test'
--- a/mysql-test/t/subquery_sj_dupsweed_jcl6.test	2010-06-18 13:36:53 +0000
+++ b/mysql-test/t/subquery_sj_dupsweed_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_dupsweed.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_dupsweed_jcl7.test'
--- a/mysql-test/t/subquery_sj_dupsweed_jcl7.test	2010-06-18 13:36:53 +0000
+++ b/mysql-test/t/subquery_sj_dupsweed_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_dupsweed.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_firstmatch_jcl6.test'
--- a/mysql-test/t/subquery_sj_firstmatch_jcl6.test	2010-06-18 13:36:53 +0000
+++ b/mysql-test/t/subquery_sj_firstmatch_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_firstmatch.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_firstmatch_jcl7.test'
--- a/mysql-test/t/subquery_sj_firstmatch_jcl7.test	2010-06-18 14:28:24 +0000
+++ b/mysql-test/t/subquery_sj_firstmatch_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_firstmatch.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_innodb_all_jcl6.test'
--- a/mysql-test/t/subquery_sj_innodb_all_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_innodb_all_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_innodb_all.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_innodb_all_jcl7.test'
--- a/mysql-test/t/subquery_sj_innodb_all_jcl7.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_innodb_all_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_innodb_all.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_innodb_none_jcl6.test'
--- a/mysql-test/t/subquery_sj_innodb_none_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_innodb_none_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_innodb_none.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_innodb_none_jcl7.test'
--- a/mysql-test/t/subquery_sj_innodb_none_jcl7.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_innodb_none_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_innodb_none.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_loosescan_jcl6.test'
--- a/mysql-test/t/subquery_sj_loosescan_jcl6.test	2010-06-18 13:36:53 +0000
+++ b/mysql-test/t/subquery_sj_loosescan_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_loosescan.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_loosescan_jcl7.test'
--- a/mysql-test/t/subquery_sj_loosescan_jcl7.test	2010-06-18 13:36:53 +0000
+++ b/mysql-test/t/subquery_sj_loosescan_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_loosescan.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_mat_jcl6.test'
--- a/mysql-test/t/subquery_sj_mat_jcl6.test	2010-06-18 13:36:53 +0000
+++ b/mysql-test/t/subquery_sj_mat_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_mat.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_mat_jcl7.test'
--- a/mysql-test/t/subquery_sj_mat_jcl7.test	2010-06-18 13:36:53 +0000
+++ b/mysql-test/t/subquery_sj_mat_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_mat.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_none_jcl6.test'
--- a/mysql-test/t/subquery_sj_none_jcl6.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_none_jcl6.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_none.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/subquery_sj_none_jcl7.test'
--- a/mysql-test/t/subquery_sj_none_jcl7.test	2010-06-18 08:45:53 +0000
+++ b/mysql-test/t/subquery_sj_none_jcl7.test	2010-11-29 13:04:34 +0000
@@ -10,4 +10,3 @@ show variables like 'optimizer_join_cach
 --source t/subquery_sj_none.test
 
 set optimizer_join_cache_level=default;
-show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/union.test'
--- a/mysql-test/t/union.test	2010-11-06 18:21:12 +0000
+++ b/mysql-test/t/union.test	2010-11-29 13:04:34 +0000
@@ -481,6 +481,7 @@ drop table t1;
 #
 create table t1 (   RID int(11) not null default '0',   IID int(11) not null default '0',    nada varchar(50)  not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;
 insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');
+--sorted_result
 select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
 drop  table t1;
 

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2010-08-20 09:15:16 +0000
+++ b/mysql-test/t/view.test	2010-11-29 13:04:34 +0000
@@ -155,8 +155,11 @@ insert into t1 values (1), (2), (3);
 create view v1 (a) as select a+1 from t1;
 create view v2 (a) as select a-1 from t1;
 
+--sorted_result
 select * from t1 natural left join v1;
+--sorted_result
 select * from v2 natural left join t1;
+--sorted_result
 select * from v2 natural left join v1;
 
 drop view v1, v2;
@@ -983,7 +986,9 @@ insert into t1 values (0), (1), (2), (3)
 create table t2 (a int);
 insert into t2 select a from t1 where a > 1;
 create view v1 as select a from t1 where a > 1;
+--sorted_result
 select * from t1 left join (t2 as t, v1) on v1.a=t1.a;
+--sorted_result
 select * from t1 left join (t2 as t, t2) on t2.a=t1.a;
 drop view v1;
 drop table t1, t2;
@@ -1512,11 +1517,13 @@ INSERT INTO t2 VALUES (8,1,'y');
 
 CREATE VIEW v1 AS SELECT * FROM t1;
 
+--sorted_result
 SELECT a.col1,a.col2,b.col2,b.col3
   FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1
     WHERE b.col2 IS NULL OR
           b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
 
+--sorted_result
 SELECT a.col1,a.col2,b.col2,b.col3
   FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1
     WHERE b.col2 IS NULL OR
@@ -1524,6 +1531,7 @@ SELECT a.col1,a.col2,b.col2,b.col3
 
 CREATE VIEW v2 AS SELECT * FROM t2;
 
+--sorted_result
 SELECT a.col1,a.col2,b.col2,b.col3
   FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
     WHERE b.col2 IS NULL OR


Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20101129130434-ousg27hde0dr3k7a.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (guilhem:3289) WL#5694Guilhem Bichot29 Nov