diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 45 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 40 | ||||
| -rw-r--r-- | sql/sql_select.cc | 5 |
3 files changed, 90 insertions, 0 deletions
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; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8361986e020..a7158960ed0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1209,6 +1209,11 @@ int 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) |