MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:May 31 2010 11:48am
Subject:bzr commit into mysql-trunk-bugfixing branch (gshchepa:3079) Bug#38745
View as plain text  
#At file:///mnt/sda7/work/38745-5.5/ based on revid:tor.didriksen@stripped

 3079 Gleb Shchepa	2010-05-31
      Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
                  when it should use index
      
      Sometimes the LEFT/RIGHT JOIN with an empty table caused an
      unnecessary filesort.
      
      Sample query, where t1.i1 is indexed and t3 is empty:
      
        SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
                             LEFT JOIN t3 ON t2.i2 = t3.i3
          ORDER BY t1.i1 LIMIT 5;
      
      The server erroneously used an item of empty outer-joined
      table as a common constant of a Item_equal (multi-equivalence
      expression).
      By the fix for the bug 16590 the constant status of such
      an item has been propagated to st_table::const_key_parts
      map bits related to other Item_equal argument-related
      key parts (those are obviously not constant in our case).
      As far as test_if_skip_sort_order function skips constant
      prefixes of testing keys, this caused an ignorance of
      available indices, since some prefixes were marked as
      constant by mistake.
     @ mysql-test/r/order_by.result
        Test case for bug #38745.
     @ mysql-test/t/order_by.test
        Test case for bug #38745.
     @ sql/item.h
        Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
                    when it should use index
        
        Item::is_outer_field() has been added and overloaded for
        Item_field and Item_ref classes.
     @ sql/item_cmpfunc.cc
        Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
                    when it should use index
        
        Item_equal::update_const() and Item_equal::update_used_tables()
        have been updated to not take into account the constantness
        of outer-joined table items.

    modified:
      mysql-test/r/order_by.result
      mysql-test/t/order_by.test
      sql/item.h
      sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result	2009-12-10 15:38:01 +0000
+++ b/mysql-test/r/order_by.result	2010-05-31 11:48:47 +0000
@@ -1618,3 +1618,32 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 DROP TABLE t1, t2;
 End of 5.1 tests
+#
+# Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
+#             when it should use index
+#
+CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY);
+CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY);
+CREATE TABLE t3 (i3 integer);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);
+INSERT INTO t2 SELECT * FROM t1;
+EXPLAIN EXTENDED
+SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 
+LEFT JOIN t3 ON t2.i2 = t3.i3
+ORDER BY t1.i1 LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	5	240.00	Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.i1	1	100.00	Using index
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`i2` = `test`.`t1`.`i1`) order by `test`.`t1`.`i1` limit 5
+SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 
+LEFT JOIN t3 ON t2.i2 = t3.i3
+ORDER BY t1.i1 LIMIT 5;
+i1	i2
+1	1
+2	2
+3	3
+4	4
+5	5
+DROP TABLE t1, t2, t3;

=== modified file 'mysql-test/t/order_by.test'
--- a/mysql-test/t/order_by.test	2009-12-10 15:38:01 +0000
+++ b/mysql-test/t/order_by.test	2010-05-31 11:48:47 +0000
@@ -1468,3 +1468,27 @@ SELECT * FROM t1 FORCE INDEX FOR JOIN (a
 DROP TABLE t1, t2;
 
 --echo End of 5.1 tests
+
+
+--echo #
+--echo # Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
+--echo #             when it should use index
+--echo #
+
+CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY);
+CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY);
+CREATE TABLE t3 (i3 integer);
+
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);
+INSERT INTO t2 SELECT * FROM t1;
+
+EXPLAIN EXTENDED
+SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 
+                     LEFT JOIN t3 ON t2.i2 = t3.i3
+  ORDER BY t1.i1 LIMIT 5;
+
+SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 
+                     LEFT JOIN t3 ON t2.i2 = t3.i3
+  ORDER BY t1.i1 LIMIT 5;
+
+DROP TABLE t1, t2, t3;

=== modified file 'sql/item.h'
--- a/sql/item.h	2010-05-31 10:59:58 +0000
+++ b/sql/item.h	2010-05-31 11:48:47 +0000
@@ -1181,6 +1181,10 @@ public:
     collation.set(&my_charset_numeric, DERIVATION_NUMERIC, MY_REPERTOIRE_ASCII);
     fix_char_length(max_char_length_arg);
   }
+  /*
+    Return TRUE if the item points to a column of an outer-joined table.
+  */
+  virtual bool is_outer_field() const { DBUG_ASSERT(fixed); return FALSE; }
 };
 
 
@@ -1694,6 +1698,11 @@ public:
   int fix_outer_field(THD *thd, Field **field, Item **reference);
   virtual Item *update_value_transformer(uchar *select_arg);
   virtual void print(String *str, enum_query_type query_type);
+  bool is_outer_field() const
+  {
+    DBUG_ASSERT(fixed);
+    return field->table->pos_in_table_list->outer_join;
+  }
   Field::geometry_type get_geometry_type() const
   {
     DBUG_ASSERT(field_type() == MYSQL_TYPE_GEOMETRY);
@@ -2507,6 +2516,12 @@ public:
     return (*ref)->get_time(ltime);
   }
   virtual bool basic_const_item() const { return (*ref)->basic_const_item(); }
+  bool is_outer_field() const
+  {
+    DBUG_ASSERT(fixed);
+    DBUG_ASSERT(ref);
+    return (*ref)->is_outer_field();
+  }
 
 };
 

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-03-31 14:05:33 +0000
+++ b/sql/item_cmpfunc.cc	2010-05-31 11:48:47 +0000
@@ -5466,7 +5466,21 @@ void Item_equal::update_const()
   Item *item;
   while ((item= it++))
   {
-    if (item->const_item())
+    if (item->const_item() &&
+        /*
+          Don't propagate constant status of outer-joined column.
+          Such a constant status here is a result of:
+            a) empty outer-joined table: in this case such a column has a
+               value of NULL; but at the same time other arguments of
+               Item_equal don't have to be NULLs and the value of the whole
+               multiple equivalence expression doesn't have to be NULL or FALSE
+               because of the outer join nature;
+          or
+            b) outer-joined table contains only 1 row: the result of
+               this column is equal to a row field value *or* NULL.
+          Both values are inacceptable as Item_equal constants.
+        */
+        !item->is_outer_field())
     {
       it.remove();
       add(item);
@@ -5505,7 +5519,8 @@ void Item_equal::update_used_tables()
   {
     item->update_used_tables();
     used_tables_cache|= item->used_tables();
-    const_item_cache&= item->const_item();
+    /* see commentary at Item_equal::update_const() */
+    const_item_cache&= item->const_item() && !item->is_outer_field();
   }
 }
 


Attachment: [text/bzr-bundle] bzr/gshchepa@mysql.com-20100531114847-e728zekxmkaborq3.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (gshchepa:3079) Bug#38745Gleb Shchepa31 May