diff options
author | unknown <timour@mysql.com> | 2005-08-23 18:08:04 +0300 |
---|---|---|
committer | unknown <timour@mysql.com> | 2005-08-23 18:08:04 +0300 |
commit | fe24add743df9dc2f8d72352e777c0f6f495d5fb (patch) | |
tree | 4cde988a40d008a8de1756cb6ac8fa97ea3fe6e3 /mysql-test/t/join_outer.test | |
parent | e39656fb3b526faf10940339f0cc17452724f524 (diff) | |
download | mariadb-git-fe24add743df9dc2f8d72352e777c0f6f495d5fb.tar.gz |
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.
Diffstat (limited to 'mysql-test/t/join_outer.test')
-rw-r--r-- | mysql-test/t/join_outer.test | 20 |
1 files changed, 12 insertions, 8 deletions
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; |