WL#2486 - natural and using join according to SQL:2003

* Provide backwards compatibility extension to name resolution of
  coalesced columns. The patch allows such columns to be qualified
  with a table (and db) name, as it is in 4.1.
  Based on a patch from Monty.

* Adjusted tests accordingly to test both backwards compatible name
  resolution of qualified columns, and ANSI-style resolution of
  non-qualified columns.
  For this, each affected test has two versions - one with qualified
  columns, and one without. 


mysql-test/include/ps_query.inc:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/bdb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/innodb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/join.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/join_nested.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/join_outer.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/null_key.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/order_by.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_2myisam.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_3innodb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_4heap.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_5merge.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_6bdb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_7ndb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/select.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/subselect.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/type_ranges.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/bdb.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/innodb.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/join.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/join_nested.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/join_outer.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/null_key.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/order_by.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/select.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/subselect.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/type_ranges.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
sql/sql_base.cc:
  * Applied Monty's patch for backwards compatible name resolution
    of qualified columns. The idea is:
    - When a column is qualified, search for the column in all
      tables/views underlying each natural join. In this case
      natural joins are *not* considered leaves.
    - If a column is not qualified, then consider natural joins
      as leaves, thus directly search the result columns of
      natural joins.
  * Simplified 'find_field_in_tables()' - unified two similar
    loops into one.
sql/table.cc:
  - Removed method & members not needed after Monty's patch.
sql/table.h:
  - Removed method & members not needed after Monty's patch.
tests/mysql_client_test.c:
  Put back old tests to test that coalesced columns of natural joins can be qualified.

1 files changed, 24 insertions, 24 deletions

diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index adb300fdcf1..f341247a417 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -562,16 +562,16 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by a 
+SELECT * FROM t2 right join t1 using(a) order by t2.a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
 a	b	b
@@ -592,7 +592,7 @@ a	b	b
 3	three	three
 4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by a 
+SELECT * FROM t2 natural right join t1 order by t2.a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
 a	b
@@ -634,7 +634,7 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by a 
+SELECT * FROM t2 left join t1 using(a) order by t2.a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
 a	b	b
@@ -655,7 +655,7 @@ a	b	b
 3	three	three
 4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by a 
+SELECT * FROM t2 natural left join t1 order by t2.a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
 a	b
@@ -697,7 +697,7 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by a 
+SELECT * FROM t2 join t1 using(a) order by t2.a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
 a	b	b
@@ -718,7 +718,7 @@ a	b	b
 3	three	three
 4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by a 
+SELECT * FROM t2 natural join t1 order by t2.a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
@@ -3574,16 +3574,16 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by a 
+SELECT * FROM t2 right join t1 using(a) order by t2.a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
 a	b	b
@@ -3604,7 +3604,7 @@ a	b	b
 3	three	three
 4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by a 
+SELECT * FROM t2 natural right join t1 order by t2.a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
 a	b
@@ -3646,7 +3646,7 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by a 
+SELECT * FROM t2 left join t1 using(a) order by t2.a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
 a	b	b
@@ -3667,7 +3667,7 @@ a	b	b
 3	three	three
 4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by a 
+SELECT * FROM t2 natural left join t1 order by t2.a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
 a	b
@@ -3709,7 +3709,7 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by a 
+SELECT * FROM t2 join t1 using(a) order by t2.a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
 a	b	b
@@ -3730,7 +3730,7 @@ a	b	b
 3	three	three
 4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by a 
+SELECT * FROM t2 natural join t1 order by t2.a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b