bk commit into 5.0 tree (igor:1.2240) BUG#20519

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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, 2006-07-14 19:28:58-07:00, igor@stripped +3 -0
  Fixed bug #20519.
  The bug was due to a loss happened during a refactoring made
  on May 30 2005 that modified the function JOIN::reinit.
  As a result of it for any subquery the value of offset_limit_cnt
  was not restored for the following executions. Yet the first 
  execution of the subquery made it equal to 0.
  The fix restores this value in the function JOIN::reinit.  

  mysql-test/r/subselect.result@stripped, 2006-07-14 19:28:56-07:00, igor@stripped +45 -0
    Added a test case fir bug #20519.

  mysql-test/t/subselect.test@stripped, 2006-07-14 19:28:56-07:00, igor@stripped +40 -0
    Added a test case fir bug #20519.

  sql/sql_select.cc@stripped, 2006-07-14 19:28:56-07:00, igor@stripped +5 -0
    Fixed bug #20519.
    The bug was due to a loss happened during a refactoring made
    on May 30 2005 that modified the function JOIN::reinit.
    As a result of it for any subquery the value of offset_limit_cnt
    was not restored for the following executions. Yet the first 
    execution of the subquery made it equal to 0.
    The fix restores this value in the function JOIN::reinit.  

# 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:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/mysql-5.0-opt

--- 1.433/sql/sql_select.cc	2006-07-14 19:29:03 -07:00
+++ 1.434/sql/sql_select.cc	2006-07-14 19:29:03 -07:00
@@ -1209,6 +1209,11 @@
 JOIN::reinit()
 {
   DBUG_ENTER("JOIN::reinit");
+
+  unit->offset_limit_cnt= (ha_rows)(select_lex->offset_limit ?
+                                    select_lex->offset_limit->val_uint() :
+                                    ULL(0));
+
   first_record= 0;
 
   if (exec_tmp_table1)

--- 1.147/mysql-test/r/subselect.result	2006-07-14 19:29:03 -07:00
+++ 1.148/mysql-test/r/subselect.result	2006-07-14 19:29:03 -07:00
@@ -3238,3 +3238,48 @@
 10000000000000000000
 DROP TABLE t1;
 DROP TABLE t2;
+CREATE TABLE t1 (
+id bigint(20) unsigned NOT NULL auto_increment,
+name varchar(255) NOT NULL,
+PRIMARY KEY  (id)
+);
+INSERT INTO t1 VALUES
+(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
+CREATE TABLE t2 (
+id bigint(20) unsigned NOT NULL auto_increment,
+mid bigint(20) unsigned NOT NULL,
+date date NOT NULL,
+PRIMARY KEY  (id)
+);
+INSERT INTO t2 VALUES 
+(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
+(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
+SELECT *,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 0, 1) AS date_last,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
+FROM t1;
+id	name	date_last	date_next_to_last
+1	Balazs	2006-05-01	NULL
+2	Joe	2006-04-20	NULL
+3	Frank	2006-04-13	NULL
+SELECT *,
+(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 1, 1) AS date_count
+FROM t1;
+id	name	date_count
+1	Balazs	NULL
+2	Joe	NULL
+3	Frank	NULL
+SELECT *,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 0, 1) AS date_last,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
+FROM t1;
+id	name	date_last	date_next_to_last
+1	Balazs	2006-05-01	2006-03-30
+2	Joe	2006-04-20	2006-04-06
+3	Frank	2006-04-13	NULL
+DROP TABLE t1,t2;

--- 1.120/mysql-test/t/subselect.test	2006-07-14 19:29:03 -07:00
+++ 1.121/mysql-test/t/subselect.test	2006-07-14 19:29:03 -07:00
@@ -2161,3 +2161,43 @@
 
 DROP TABLE t1;
 DROP TABLE t2;
+
+# 
+# Bug#20519: subselect with LIMIT M, N
+#
+
+CREATE TABLE t1 (
+  id bigint(20) unsigned NOT NULL auto_increment,
+  name varchar(255) NOT NULL,
+  PRIMARY KEY  (id)
+);
+INSERT INTO t1 VALUES
+  (1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
+
+CREATE TABLE t2 (
+  id bigint(20) unsigned NOT NULL auto_increment,
+  mid bigint(20) unsigned NOT NULL,
+  date date NOT NULL,
+  PRIMARY KEY  (id)
+);
+INSERT INTO t2 VALUES 
+  (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
+  (4, 2, '2006-04-20'), (5, 1, '2006-05-01');
+
+SELECT *,
+      (SELECT date FROM t2 WHERE mid = t1.id
+         ORDER BY date DESC LIMIT 0, 1) AS date_last,
+      (SELECT date FROM t2 WHERE mid = t1.id
+         ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
+  FROM t1;
+SELECT *,
+      (SELECT COUNT(*) FROM t2 WHERE mid = t1.id
+         ORDER BY date DESC LIMIT 1, 1) AS date_count
+  FROM t1;
+SELECT *,
+      (SELECT date FROM t2 WHERE mid = t1.id
+         ORDER BY date DESC LIMIT 0, 1) AS date_last,
+      (SELECT date FROM t2 WHERE mid = t1.id
+         ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
+  FROM t1;
+DROP TABLE t1,t2;