From 203d46bb387b4cb178fe0e4cf9135daa440233e5 Mon Sep 17 00:00:00 2001
From: unknown <igor@olga.mysql.com>
Date: Fri, 14 Jul 2006 19:28:58 -0700
Subject: 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:
  Added a test case fir bug #20519.
mysql-test/t/subselect.test:
  Added a test case fir bug #20519.
---
 mysql-test/r/subselect.result | 45 +++++++++++++++++++++++++++++++++++++++++++
 mysql-test/t/subselect.test   | 40 ++++++++++++++++++++++++++++++++++++++
 2 files changed, 85 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 11dc68fe99b..13f653b2f07 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3238,3 +3238,48 @@ i
 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;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9fc4f8a8d4e..310adcc5323 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2161,3 +2161,43 @@ SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);
 
 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;
-- 
cgit v1.2.1