bk commit into 5.0 tree (gshchepa:1.2501) BUG#29095

[<gshchepa]

Below is the list of changes that have just been committed into a local
5.0 repository of uchum. When uchum 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-06-23 02:11:58+05:00, gshchepa@stripped +3 -0
  Fixed bug #29095.
  INSERT into table from SELECT from the same table
  with ORDER BY and LIMIT was inserting other data
  than sole SELECT ... ORDER BY ... LIMIT returns.
  
  When INSERT from SELECT from the same table was made
  via temporary table, the LIMIT value was pushed to
  TMP_TABLE_PARAM::end_write_records ignoring the fact
  that resulting data set have to be ordered.
  
  The `test(select_options & OPTION_BUFFER_RESULT)'
  condition has been removed from the calculation of
  pushed LIMIT value:
  
    /*                         
      Pushing LIMIT to the temporary table creation is not applicable
      when there is ORDER BY or GROUP BY or there is no GROUP BY, but
      there are aggregate functions, because in all these cases we need
      all result rows.
    */           
    ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order ||
                              test(select_options & OPTION_BUFFER_RESULT))...
  
  The OPTION_BUFFER_RESULT flag in this context usually identify,
  that temporary table mast be created, because the result of SELECT
  have to be inserted into the same table:
  
      /* Using same table for INSERT and SELECT */
      lex->current_select->options|= OPTION_BUFFER_RESULT;
      lex->current_select->join->select_options|= OPTION_BUFFER_RESULT;
  
  NOTE: Removal of the `test(select_options & OPTION_BUFFER_RESULT)'
  condition doesn't affect the case when SQL_BUFFER_RESULT is forced by
  client, because we don't have to push LIMIT value too when SELECT have
  ORDER BY clause (`order == 0' is false), but skip_sort_order is false.

  mysql-test/r/limit.result@stripped, 2007-06-23 00:58:40+05:00, gshchepa@stripped +22 -0
    Updated test case for bug #29095.

  mysql-test/t/limit.test@stripped, 2007-06-23 00:58:23+05:00, gshchepa@stripped +23 -0
    Updated test case for bug #29095.

  sql/sql_select.cc@stripped, 2007-06-23 02:04:17+05:00, gshchepa@stripped +1 -2
    Fixed bug #29095.
    When INSERT from SELECT from the same table was made
    via temporary table, the LIMIT value was pushed to
    TMP_TABLE_PARAM::end_write_records ignoring the fact
    that resulting data set have to be ordered.
    The `test(select_options & OPTION_BUFFER_RESULT)'
    condition has been removed from the calculation of
    pushed LIMIT value.

# 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:	gshchepa
# Host:	gleb.loc
# Root:	/home/uchum/work/bk/5.0-opt-29095

--- 1.533/sql/sql_select.cc	2007-06-20 13:06:21 +05:00
+++ 1.534/sql/sql_select.cc	2007-06-23 02:04:17 +05:00
@@ -1347,8 +1347,7 @@ JOIN::optimize()
       there are aggregate functions, because in all these cases we need
       all result rows.
     */
-    ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order ||
-                              test(select_options & OPTION_BUFFER_RESULT)) &&
+    ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order) &&
                              !tmp_group &&
                              !thd->lex->current_select->with_sum_func) ?
                             select_limit : HA_POS_ERROR;

--- 1.12/mysql-test/r/limit.result	2007-05-18 12:08:05 +05:00
+++ 1.13/mysql-test/r/limit.result	2007-06-23 00:58:40 +05:00
@@ -101,4 +101,26 @@ ERROR HY000: Incorrect arguments to EXEC
 prepare s from "select 1 limit ?, ?";
 execute s using @a, @a;
 ERROR HY000: Incorrect arguments to EXECUTE
+CREATE TABLE t1 (
+id INT AUTO_INCREMENT PRIMARY KEY,
+prev_id INT,
+join_id INT DEFAULT 0);
+INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);
+SELECT * FROM t1;
+id	prev_id	join_id
+1	NULL	0
+2	1	0
+3	2	0
+CREATE TABLE t2 (join_id INT);
+INSERT INTO t2 (join_id) VALUES (0);
+INSERT INTO t1 (prev_id) SELECT id
+FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id
+ORDER BY id DESC LIMIT 1;
+SELECT * FROM t1;
+id	prev_id	join_id
+1	NULL	0
+2	1	0
+3	2	0
+4	3	0
+DROP TABLE t1,t2;
 End of 5.0 tests

--- 1.14/mysql-test/t/limit.test	2007-05-18 12:08:05 +05:00
+++ 1.15/mysql-test/t/limit.test	2007-06-23 00:58:23 +05:00
@@ -87,4 +87,27 @@ prepare s from "select 1 limit ?, ?";
 --error 1210
 execute s using @a, @a;
 
+#
+# Bug #29095: incorrect pushing of LIMIT into the temporary
+# table ignoring ORDER BY clause
+#
+
+CREATE TABLE t1 (
+  id INT AUTO_INCREMENT PRIMARY KEY,
+  prev_id INT,
+  join_id INT DEFAULT 0);
+
+INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);
+SELECT * FROM t1;
+
+CREATE TABLE t2 (join_id INT);
+INSERT INTO t2 (join_id) VALUES (0);
+
+INSERT INTO t1 (prev_id) SELECT id
+  FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id
+  ORDER BY id DESC LIMIT 1;
+SELECT * FROM t1;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests