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, 64 insertions, 48 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 9bc85dfa987..d4a20209162 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -36,16 +36,7 @@ grp	a	c	id	a	c	d
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
 NULL	NULL	NULL	4	7	D	7
-select * from t1 left join t2 using (a);
-a	grp	c	id	c	d
-1	1	a	1	a	1
-2	2	b	NULL	NULL	NULL
-3	2	c	NULL	NULL	NULL
-4	3	E	3	A	4
-5	3	C	3	B	5
-6	3	D	3	C	6
-NULL	NULL		NULL	NULL	NULL
-select t1.*,t2.* from t1 left join t2 on t1.a=t2.a;
+select t1.*,t2.* from t1 left join t2 using (a);
 grp	a	c	id	a	c	d
 1	1	a	1	1	a	1
 2	2	b	NULL	NULL	NULL	NULL
@@ -54,34 +45,40 @@ grp	a	c	id	a	c	d
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
 NULL	NULL		NULL	NULL	NULL	NULL
-select * from t1 left join t2 using (a,c);
-a	c	grp	id	d
-1	a	1	1	1
-2	b	2	NULL	NULL
-3	c	2	NULL	NULL
-4	E	3	NULL	NULL
-5	C	3	NULL	NULL
-6	D	3	NULL	NULL
-NULL		NULL	NULL	NULL
-select * from t1 left join t2 using (c);
-c	grp	a	id	a	d
-a	1	1	1	1	1
-a	1	1	3	4	4
-b	2	2	3	5	5
-c	2	3	3	6	6
-E	3	4	NULL	NULL	NULL
-C	3	5	3	6	6
-D	3	6	4	7	7
-	NULL	NULL	NULL	NULL	NULL
-select * from t1 natural left outer join t2;
-a	c	grp	id	d
-1	a	1	1	1
-2	b	2	NULL	NULL
-3	c	2	NULL	NULL
-4	E	3	NULL	NULL
-5	C	3	NULL	NULL
-6	D	3	NULL	NULL
-NULL		NULL	NULL	NULL
+select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
+grp	a	c	id	a	c	d
+1	1	a	1	1	a	1
+3	4	E	3	4	A	4
+3	5	C	3	5	B	5
+3	6	D	3	6	C	6
+select t1.*,t2.* from t1 left join t2 using (a,c);
+grp	a	c	id	a	c	d
+1	1	a	1	1	a	1
+2	2	b	NULL	NULL	NULL	NULL
+2	3	c	NULL	NULL	NULL	NULL
+3	4	E	NULL	NULL	NULL	NULL
+3	5	C	NULL	NULL	NULL	NULL
+3	6	D	NULL	NULL	NULL	NULL
+NULL	NULL		NULL	NULL	NULL	NULL
+select t1.*,t2.* from t1 left join t2 using (c);
+grp	a	c	id	a	c	d
+1	1	a	1	1	a	1
+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
+NULL	NULL		NULL	NULL	NULL	NULL
+select t1.*,t2.* from t1 natural left outer join t2;
+grp	a	c	id	a	c	d
+1	1	a	1	1	a	1
+2	2	b	NULL	NULL	NULL	NULL
+2	3	c	NULL	NULL	NULL	NULL
+3	4	E	NULL	NULL	NULL	NULL
+3	5	C	NULL	NULL	NULL	NULL
+3	6	D	NULL	NULL	NULL	NULL
+NULL	NULL		NULL	NULL	NULL	NULL
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
 grp	a	c	id	a	c	d
 3	4	E	3	4	A	4
@@ -114,21 +111,21 @@ select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on
 ERROR 42S22: Unknown column 't3.a' in 'on clause'
 select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 ERROR 42S22: Unknown column 't3.a' in 'on clause'
-select * from t1 inner join t2 using (a);
-a	grp	c	id	c	d
-1	1	a	1	a	1
-4	3	E	3	A	4
-5	3	C	3	B	5
-6	3	D	3	C	6
+select t1.*,t2.* from t1 inner join t2 using (a);
+grp	a	c	id	a	c	d
+1	1	a	1	1	a	1
+3	4	E	3	4	A	4
+3	5	C	3	5	B	5
+3	6	D	3	6	C	6
 select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
 grp	a	c	id	a	c	d
 1	1	a	1	1	a	1
 3	4	E	3	4	A	4
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
-select * from t1 natural join t2;
-a	c	grp	id	d
-1	a	1	1	1
+select t1.*,t2.* from t1 natural join t2;
+grp	a	c	id	a	c	d
+1	1	a	1	1	a	1
 drop table t1,t2;
 CREATE TABLE t1 (
 usr_id INT unsigned NOT NULL,
@@ -435,7 +432,7 @@ INSERT INTO t2 VALUES (3,'z');
 SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
 id2
 3
-SELECT id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
+SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
 id2
 3
 drop table t1,t2;
@@ -653,6 +650,13 @@ select * from t1 natural left join t2 natural left join t3;
 i
 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
+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;
@@ -931,6 +935,18 @@ create table t1 (a int, b varchar(20));
 create table t2 (a int, c varchar(20));
 insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
 insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
+select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
+group_concat(t1.b,t2.c)
+aaaaa
+bbbbb
+Warnings:
+Warning	1260	2 line(s) were cut by GROUP_CONCAT()
+select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
+group_concat(t1.b,t2.c)
+aaaaa
+bbbbb
+Warnings:
+Warning	1260	2 line(s) were cut by GROUP_CONCAT()
 select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
 group_concat(t1.b,t2.c)
 aaaaa