From fe24add743df9dc2f8d72352e777c0f6f495d5fb Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 23 Aug 2005 18:08:04 +0300 Subject: 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. --- mysql-test/r/ps_3innodb.result | 24 ++++++++++++------------ 1 file changed, 12 insertions(+), 12 deletions(-) (limited to 'mysql-test/r/ps_3innodb.result') diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 237072d7218..9ab5a79f755 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -519,16 +519,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 @@ -549,7 +549,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 @@ -591,7 +591,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 @@ -612,7 +612,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 @@ -654,7 +654,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 @@ -675,7 +675,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 -- cgit v1.2.1