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.

10 files changed, 67 insertions, 25 deletions

diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test
index 5e487bd8036..3167682f816 100644
--- a/mysql-test/t/bdb.test
+++ b/mysql-test/t/bdb.test
@@ -400,6 +400,7 @@ drop table t1;
 set @a:=now();
 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=bdb;
 insert into t1 (a) values(1),(2),(3);
+select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
 select a from t1 natural join t1 as t2 where b >= @a order by a;
 update t1 set a=5 where a=1;
 select a from t1;
@@ -571,7 +572,7 @@ create temporary table tmp1
         select branch_id, target_id, platform_id, product_id
         from t1, t2, t3, t4 ;
 create temporary table tmp2 
-        select branch_id, target_id, platform_id, product_id 
+        select tmp1.branch_id, tmp1.target_id, tmp1.platform_id, tmp1.product_id 
         from tmp1 left join t8 
         using (branch_id,target_id,platform_id,product_id) 
         where t8.archive_id is null ;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 3eaf408af0f..bf094dd0e5d 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -440,6 +440,7 @@ drop table t1;
 set @a:=now();
 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
 insert into t1 (a) values(1),(2),(3);
+select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
 select a from t1 natural join t1 as t2 where b >= @a order by a;
 update t1 set a=5 where a=1;
 select a from t1;
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 2e82bad8abb..bb82a93c6c4 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -111,6 +111,10 @@ drop table t1, t2;
 
 create table t1 (a int primary key);
 insert into t1 values(1),(2);
+select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
+--replace_result "31 tables" "XX tables" "61 tables" "XX tables"
+--error 1116
+select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
 select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
 --replace_result "31 tables" "XX tables" "61 tables" "XX tables"
 --error 1116
@@ -144,6 +148,7 @@ DROP TABLE t1, t2;
 CREATE TABLE t1 (d DATE NOT NULL);
 CREATE TABLE t2 (d DATE NOT NULL);
 INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');
+SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
 SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
 SELECT * from t1 WHERE t1.d IS NULL;
 SELECT * FROM t1 WHERE 1/0 IS NULL;
@@ -266,6 +271,8 @@ CREATE TABLE t2 (
   FULLTEXT KEY rate_code (rate_code)
 ) ENGINE=MyISAM;
 INSERT INTO t2 VALUES ('rivercats','cust',20);
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
+SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
 SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
 SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
 drop table t1,t2;
@@ -315,14 +322,14 @@ select * from t3 right join t2 on (t3.i=t2.i);
 select * from t3 natural right join t2 natural right join t1;
 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 1,2;
+select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
 select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
-select * from t2 natural left join t3,t1 order by t1.i;
+select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
 select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 
-select * from t1,t2 natural right join t3 order by 1,2;
+select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
 select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
-select * from t2 natural right join t3,t1 order by t1.i;
+select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
 select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 drop table t1,t2,t3;
 
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index f6b74237123..482c7f9f8b9 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -678,7 +678,7 @@ SELECT t2.a,t2.b,t3.a1,t3.b
   FROM t2 LEFT JOIN t3 ON t2.b=t3.b
     WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
 
-SELECT *
+SELECT t2.a,t2.b,t3.a1,t3.b
   FROM t2 NATURAL LEFT JOIN t3
     WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
 
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 6a3b79f8354..aabc32c009a 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -19,11 +19,11 @@ select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
 select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
 select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
-select * from t1 left join t2 using (a);
-select t1.*,t2.* from t1 left join t2 on t1.a=t2.a;
-select * from t1 left join t2 using (a,c);
-select * from t1 left join t2 using (c);
-select * from t1 natural left outer join t2;
+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);
+select t1.*,t2.* from t1 left join t2 using (c);
+select t1.*,t2.* from t1 natural left outer join t2;
 
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
@@ -44,9 +44,9 @@ select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on
 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);
 
 # Test of inner join
-select * from t1 inner join t2 using (a);
+select t1.*,t2.* from t1 inner join t2 using (a);
 select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
-select * from t1 natural join t2;
+select t1.*,t2.* from t1 natural join t2;
 
 drop table t1,t2;
 
@@ -325,7 +325,7 @@ INSERT INTO t2 VALUES (2,'y');
 INSERT INTO t2 VALUES (3,'z');
 
 SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
-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;
 
 drop table t1,t2;
 
@@ -430,6 +430,8 @@ insert into t1 values(1),(2);
 insert into t2 values(2),(3);
 insert into t3 values(2),(4);
 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;
+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;
 select * from t1 natural left join t2 where (i is not null) is not null;
 drop table t1,t2,t3;
@@ -656,6 +658,8 @@ 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;
+select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
 select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
 select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
 drop table t1, t2;
diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index 4ab99fc5d6d..e15aec01d2a 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -152,18 +152,12 @@ INSERT INTO t1 (order_id, product_id, product_type) VALUES
 INSERT INTO t2 (order_id, product_id, product_type) VALUES
 ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
 
-select t1.* from t1 left join t2
-       on (t1.order_id = t2.order_id and
-           t1.product_id = t2.product_id and
-           t1.product_type = t2.product_type)
-where t2.order_id = NULL;
-
-select t1.* from t1 left join t2
-       on (t1.order_id = t2.order_id and
-           t1.product_id = t2.product_id and
-           t1.product_type = t2.product_type)
+select t1.* from t1
+left join t2 using(order_id, product_id, product_type)
+where t2.order_id=NULL;
+select t1.* from t1
+left join t2 using(order_id, product_id, product_type)
 where t2.order_id is NULL;
-
 drop table t1,t2;
 
 #
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 615b317eb36..b1807579b20 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -153,11 +153,22 @@ INSERT INTO t3 VALUES (2,'453 Boardwalk');
 
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
+LEFT JOIN t2 USING(c)
+LEFT JOIN t3 ON t3.c = t1.c;
+
+SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
+FROM      t1
 LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c;
 
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
+LEFT JOIN t2 USING(c)
+LEFT JOIN t3 ON t3.c = t1.c
+ORDER BY a;
+
+SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
+FROM      t1
 LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c
 ORDER BY a;
@@ -339,7 +350,9 @@ CREATE TABLE t2 (
 
 INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
 INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
+SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
 SELECT titre,numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
 SELECT titre,numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
 drop table t1,t2;
 
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 8e74167852b..f580d3d1223 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1538,6 +1538,11 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25
 #
 insert into t2 (fld1, companynr) values (999999,99);
 
+select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
+explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
+
 select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
 select count(*) from t2 left join t4 using (companynr) where companynr is not null;
 explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
@@ -1547,10 +1552,18 @@ delete from t2 where fld1=999999;
 #
 # Test left join optimization
 
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
+
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 # Following can't be optimized
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
+explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
+
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 22c5471dee2..5020902009d 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1132,7 +1132,9 @@ CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
 INSERT INTO t1 VALUES (1), (2);
 INSERT INTO t2 VALUES (1);
+SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
 SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
+SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
 SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id;
 DROP TABLE t1,t2;
 
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index 85862821aab..03ee91f14d8 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -157,11 +157,18 @@ create table t1 ( id integer unsigned not null primary key );
 create table t2 ( id integer unsigned not null primary key );
 insert into t1 values (1), (2);
 insert into t2 values (1);
+select  t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id ); 
 select  t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id); 
 create table t3 (id_A integer unsigned not null, id_B integer unsigned null  );
+insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
+select * from t3;
+delete from t3;
 insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
 select * from t3;
 drop table t3;
+create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
+select * from t3;
+drop table t3;
 create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
 select * from t3;
 drop table t1,t2,t3;