bzr push into mysql-trunk branch (ashish.y.agarwal:3846 to 3847) Bug#11748748

[Ashish Agarwal]

 3847 Ashish Agarwal	2012-02-07
      BUG#11748748: Failing testcase on mysql-trunk. Patch to correct it.

    modified:
      storage/archive/ha_archive.cc
 3846 Guilhem Bichot	2012-02-07
      Fix for Bug #13595212 	EXTRA ROWS RETURNED ON RIGHT JOIN WITH VIEW AND IN-SUBQUERY IN WHERE:
      LEFT JOIN was wrongly converted to inner join because table maps were
      used incorrectly.
     @ mysql-test/r/subquery_all.result
        without the code fix, the last two SELECTs would return only 'j'.
     @ sql/item.h
        Scenario of the bug follows.
        Query is:
        SELECT alias2.col_varchar_nokey
        FROM t1 AS alias2
          LEFT JOIN view_D AS alias1 ON 1
        WHERE alias2.col_varchar_key IN (
          SELECT SQ2_alias1.col_varchar_nokey
          FROM view_D AS SQ2_alias1
            LEFT JOIN t1 AS SQ2_alias2
            ON (SQ2_alias2.col_int_key = SQ2_alias1.pk)
          WHERE SQ2_alias1.pk != alias1.col_int_key
            AND SQ2_alias1.col_varchar_key > alias1.col_varchar_key
        ) /* LEFT JOIN variant */;
        We merge the view. Then the subquery's LEFT JOIN is wrongly
        converted to inner join, leading to a wrong result.
        
        Why this conversion?
        
        simplify_joins() analyzes this subquery's LEFT JOIN,
        looks at its WHERE, for example at this piece of WHERE:
        "SQ2_alias1.pk != alias1.col_int_key".
        not_null_tables() is called on the left and right
        members of the "!=" (Item_func_ne)
        (not_null_tables() is defined as a map: if a table is
        in this map and has a NULL row then the item is surely
        false/unknown and thus the WHERE won't be satisfied)
        
        The left member is Item_field for SQL2_alias1.pk, i.e.
        a column of a table in the subquery's FROM clause;
        if it's NULL, the WHERE will be false; so
        not_null_tables() should return the map of "SQ2_alias1";
        and that's what it does, in this code:
        table_map Item_field::used_tables() const
        {
          if (field->table->const_table)
            return 0;					// const item
          return (depended_from ? OUTER_REF_TABLE_BIT : field->table->map);
        }
        
        The right member is, due to view merging, an Item_direct_view_ref
        wrapping an Item_field for alias1.col_int_key; the Item_direct_view_ref
        has 'depended_from'!=NULL (it belongs to a table which is "external
        to the subquery": "alias1"). But the Item_field has depended_from==NULL.
        Item_direct_view_ref::not_null_tables() is
        Item_ref::not_null_tables(), and this function calls
        not_null_tables() on the wrapped (a.k.a. "referenced")
        Item_field; and Item_field::not_null_tables() (see code copied above)
        returns the map of alias1.col_int_key because Item_field::depended_from
        is NULL.
        Returning this map is wrong: "alias1" is outside of the subquery's
        JOIN; OUTER_REF_TABLE_BIT should have been returned.
        Returning the map of alias1 confuses the code in simplify_joins(),
        which, in this test:
            if (!table->outer_join || (used_tables & not_null_tables))
        ends up comparing subquery's maps (used_tables)
        with outer query's maps (not_null_tables)! As "alias1"
        (in the outer JOIN) and "SQ2_alias2" (in the subquery) happen
        to have the same map (maps are unique *only inside the same JOIN*),
        simplify_joins() works as if the subquery's WHERE had
        SQ2_alias1.pk != SQ2_alias2.col_int_key
        and, "logically", concludes that a NULL-complemented row
        of SQ2_alias2 cannot match this, so the LEFT JOIN can be changed to an
        inner join.
        The fix is to make Item_ref::not_null_tables() work like
        its twin Item_ref::used_tables() already does (since 2004): test
        the Item_ref's depended_from too.
        
        Why did this bug happen for LEFT JOIN, and not with RIGHT JOIN?
        Maps are assigned based on the order in TABLE_LIST, and this order
        is the "parse order". Thus, with RIGHT JOIN, alias1 didn't have the
        same map as SQ2_alias2, thus the outer join was not converted;
        but it was pure luck.
        Same when the SELECT RIGHT JOIN was wrapped into a view: during parsing
        we convert RIGHT JOIN to LEFT JOIN (in the JOIN's FROM clause,
        not in TABLE_LIST) and write LEFT JOIN to the view's definition
        file, hitting the "LEFT JOIN" case.

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_bka.result
      mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_bka.result
      mysql-test/r/subquery_none_bka_nixbnl.result
      sql/item.h
=== modified file 'storage/archive/ha_archive.cc'
--- a/storage/archive/ha_archive.cc	2012-02-03 14:37:46 +0000
+++ b/storage/archive/ha_archive.cc	2012-02-07 11:03:11 +0000
@@ -1634,15 +1634,14 @@ int ha_archive::optimize(THD* thd, HA_CH
 
   azclose(&writer);
   share->dirty= FALSE;
+  azclose(&archive);
+  archive_reader_open= FALSE;
 
   // make the file we just wrote be our data file
   rc= my_rename(writer_filename, share->data_file_name, MYF(0));
   share->in_optimize= false;
   mysql_mutex_unlock(&share->mutex);
 
-  azclose(&archive);
-  archive_reader_open= FALSE;
-
   DBUG_RETURN(rc);
 error:
   DBUG_PRINT("ha_archive", ("Failed to recover, error was %d", rc));

No bundle (reason: useless for push emails).