From: Date: January 22 2007 5:16pm Subject: bk commit into 5.0 tree (gkodinov:1.2385) BUG#25575 List-Archive: http://lists.mysql.com/commits/18559 X-Bug: 25575 Message-Id: <20070122161616.3E276C001E2@macbook.gmz> Below is the list of changes that have just been committed into a local 5.0 repository of kgeorge. When kgeorge does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-01-22 18:15:57+02:00, gkodinov@stripped +6 -0 BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join Two problems here: Problem 1: While constructing the join columns list the optimizer does as follows: 1. Sets the join_using_fields/natural_join members of the rightmost table 2. Makes a "table" (TABLE_LIST) to wrap the two tables. 3. Assigns the join_using_fields/is_natural_join of the wrapper table using join_using_fields/natural_join of the rightmost table 4. Sets join_using_fields to NULL for the rightmost table 5. Passes the wrapping table up to the same procedure on the upper level. Step 1 overrides the the join_using_fields that are set for a nested join wrapping table in step 4. Fixed by making a designated variable join_using to pass the data from step 1 to step 4 without destroying the wrapping table data. Problem 2: The optimizer checks for ambiguous columns while transforming NATURAL JOIN/JOIN USING to JOIN ON. While doing that there was no distinction between columns that are used in the generated join condition (where ambiguity can be checked) and the other columns (where ambiguity can be checked only when resolving references coming from outside the JOIN construct itself). Fixed by allowing the non-USING columns to be present in multiple copies in both sides of the join and moving the ambiguity check to the place where unqualified references to the join columns are resolved. mysql-test/r/join_nested.result@stripped, 2007-01-22 18:15:48+02:00, gkodinov@stripped +13 -0 BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - test case mysql-test/t/join_nested.test@stripped, 2007-01-22 18:15:48+02:00, gkodinov@stripped +19 -0 BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - test case sql/sql_base.cc@stripped, 2007-01-22 18:15:49+02:00, gkodinov@stripped +33 -8 BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - proper check for duplicate cols - more detailed debug output sql/sql_parse.cc@stripped, 2007-01-22 18:15:50+02:00, gkodinov@stripped +3 -6 BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - proper check for duplicate cols sql/table.cc@stripped, 2007-01-22 18:15:51+02:00, gkodinov@stripped +1 -1 BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - return null if no table ref (as in nested join columns). sql/table.h@stripped, 2007-01-22 18:15:51+02:00, gkodinov@stripped +13 -0 BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - proper check for duplicate cols # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: gkodinov # Host: macbook.gmz # Root: /Users/kgeorge/mysql/work/B25575-5.0-opt --- 1.364/sql/sql_base.cc 2007-01-11 22:20:26 +02:00 +++ 1.365/sql/sql_base.cc 2007-01-22 18:15:49 +02:00 @@ -2945,7 +2945,7 @@ find_field_in_natural_join(THD *thd, TAB { List_iterator_fast field_it(*(table_ref->join_columns)); - Natural_join_column *nj_col; + Natural_join_column *nj_col, *curr_nj_col; Field *found_field; Query_arena *arena, backup; DBUG_ENTER("find_field_in_natural_join"); @@ -2956,14 +2956,21 @@ find_field_in_natural_join(THD *thd, TAB LINT_INIT(found_field); - for (;;) + for (nj_col= NULL, curr_nj_col= field_it++; curr_nj_col; + curr_nj_col= field_it++) { - if (!(nj_col= field_it++)) - DBUG_RETURN(NULL); - - if (!my_strcasecmp(system_charset_info, nj_col->name(), name)) - break; + if (!my_strcasecmp(system_charset_info, curr_nj_col->name(), name)) + { + if (nj_col) + { + my_error(ER_NON_UNIQ_ERROR, MYF(0), name, thd->where); + DBUG_RETURN(NULL); + } + nj_col= curr_nj_col; + } } + if (!nj_col) + DBUG_RETURN(NULL); if (nj_col->view_field) { @@ -3777,6 +3784,9 @@ mark_common_columns(THD *thd, TABLE_LIST if (!(nj_col_1= it_1.get_or_create_column_ref(leaf_1))) goto err; field_name_1= nj_col_1->name(); + DBUG_PRINT ("info", ("field_name_1=%s.%s", + nj_col_1->table_name() ? nj_col_1->table_name() : "", + field_name_1)); /* Find a field with the same name in table_ref_2. @@ -3793,6 +3803,10 @@ mark_common_columns(THD *thd, TABLE_LIST if (!(cur_nj_col_2= it_2.get_or_create_column_ref(leaf_2))) goto err; cur_field_name_2= cur_nj_col_2->name(); + DBUG_PRINT ("info", ("cur_field_name_2=%s.%s", + cur_nj_col_2->table_name() ? + cur_nj_col_2->table_name() : "", + cur_field_name_2)); /* Compare the two columns and check for duplicate common fields. @@ -3803,7 +3817,10 @@ mark_common_columns(THD *thd, TABLE_LIST */ if (!my_strcasecmp(system_charset_info, field_name_1, cur_field_name_2)) { - if (found || cur_nj_col_2->is_common) + DBUG_PRINT ("info", ("match c1.is_common=%d", nj_col_1->is_common)); + if (cur_nj_col_2->is_common || + (found && (!using_fields || + test_if_string_in_list(field_name_1, using_fields)))) { my_error(ER_NON_UNIQ_ERROR, MYF(0), field_name_1, thd->where); goto err; @@ -3886,6 +3903,13 @@ mark_common_columns(THD *thd, TABLE_LIST eq_cond); nj_col_1->is_common= nj_col_2->is_common= TRUE; + DBUG_PRINT ("info", ("%s.%s and %s.%s are common", + nj_col_1->table_name() ? + nj_col_1->table_name() : "", + nj_col_1->name(), + nj_col_2->table_name() ? + nj_col_2->table_name() : "", + nj_col_2->name())); if (field_1) { @@ -4192,6 +4216,7 @@ store_top_level_join_columns(THD *thd, T and the two tables may be reordered. */ table_ref_1->natural_join= table_ref_2->natural_join= NULL; + table_ref_1->join_using= table_ref_2->join_using= NULL; /* Add a TRUE condition to outer joins that have no common columns. */ if (table_ref_2->outer_join && --- 1.593/sql/sql_parse.cc 2007-01-09 12:12:22 +02:00 +++ 1.594/sql/sql_parse.cc 2007-01-22 18:15:50 +02:00 @@ -6365,11 +6365,8 @@ TABLE_LIST *st_select_lex::nest_last_joi If this is a JOIN ... USING, move the list of joined fields to the table reference that describes the join. */ - if (table->join_using_fields) - { - ptr->join_using_fields= table->join_using_fields; - table->join_using_fields= NULL; - } + if (table->join_using) + ptr->join_using_fields= table->join_using; } } join_list->push_front(ptr); @@ -6656,7 +6653,7 @@ void add_join_on(TABLE_LIST *b, Item *ex void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List *using_fields) { b->natural_join= a; - b->join_using_fields= using_fields; + b->join_using= using_fields; } --- 1.240/sql/table.cc 2007-01-09 12:12:23 +02:00 +++ 1.241/sql/table.cc 2007-01-22 18:15:51 +02:00 @@ -2628,7 +2628,7 @@ Field *Natural_join_column::field() const char *Natural_join_column::table_name() { - return table_ref->alias; + return table_ref ? table_ref->alias : NULL; } --- 1.137/sql/table.h 2006-12-30 22:02:07 +02:00 +++ 1.138/sql/table.h 2007-01-22 18:15:51 +02:00 @@ -492,6 +492,19 @@ typedef struct st_table_list to the other operand of 'this'. */ bool is_natural_join; + /* + This is a buffer for the original JOIN USING list that comes from + the parser. The parser : + 1. Sets the natural_join/join_using of the second TABLE_LIST in the join + 2. Makes a wrapper TABLE_LIST and sets it's is_natural_join/ + join_using_fields members. + 3. Uses the wrapper TABLE_LIST as a table in the upper level. + We cannot assign directly to join_using_fields in the parser because + at that stage (1.) the wrapping TABLE_LIST is not constructed yet and + the assignment will override the JOIN USING fields of the lower level + joins on the right. + */ + List *join_using; /* Field names in a USING clause for JOIN ... USING. */ List *join_using_fields; /* --- 1.24/mysql-test/r/join_nested.result 2007-01-03 22:16:01 +02:00 +++ 1.25/mysql-test/r/join_nested.result 2007-01-22 18:15:48 +02:00 @@ -1605,3 +1605,16 @@ WHERE t1.id='5'; id ct pc nm 5 NULL NULL NULL DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); +SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +b +SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +ERROR 23000: Column 'c' in field list is ambiguous +DROP TABLE t1,t2,t3,t4,t5; +End of 5.0 tests --- 1.20/mysql-test/t/join_nested.test 2007-01-03 22:16:01 +02:00 +++ 1.21/mysql-test/t/join_nested.test 2007-01-22 18:15:48 +02:00 @@ -1045,3 +1045,22 @@ SELECT t1.*, t4.nm WHERE t1.id='5'; DROP TABLE t1,t2,t3,t4; + +# +# BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join +# +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); + +SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); + +--error ER_NON_UNIQ_ERROR +SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); + +DROP TABLE t1,t2,t3,t4,t5; +--echo End of 5.0 tests