revision-id: 2fd6cfe120b9f09a31e06db27acae993b4c3e55c (mariadb-10.5.13-47-g2fd6cfe120b) parent(s): 6831b3f2a0fd656fb41dd9df5f141431988448f1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 10:47:48 +0300 message: MDEV-27382: OFFSET is ignored when combined with DISTINCT A query in form SELECT DISTINCT expr_that_is_inferred_to_be_const LIMIT 0 OFFSET n produces one row when it should produce none. The issue was in JOIN_TAB::remove_duplicates() in the piece of logic that tried to avoid duplicate removal for such cases but didn't account for possible "LIMIT 0". Fixed by making Select_limit_counters::set_limit() change OFFSET to 0 when LIMIT is 0. --- mysql-test/main/distinct.result | 36 ++++++++++++++++++++++++++++++++++++ mysql-test/main/distinct.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_limit.h | 2 ++ 3 files changed, 76 insertions(+) diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index 2062ff0091d..0e31a174be7 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -1070,3 +1070,39 @@ UNION 1 drop table t1; End of 5.5 tests +# +# MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN +# +CREATE TABLE t1 ( +id int(7) NOT NULL AUTO_INCREMENT, +name varchar(50) DEFAULT NULL, +primary key (id) +); +INSERT INTO t1 VALUES (1, 'Reed'), (10, 'no-child'); +CREATE TABLE t2 ( +id int(11) NOT NULL AUTO_INCREMENT, +parent_id int(7) NOT NULL, +name varchar(100) DEFAULT NULL, +primary key (id), +key(parent_id) +); +INSERT INTO t2 VALUES (1, 1,'John'), (2, 2,'no-parent'); +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0; +id +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0 offset 5; +id +# Test the second part of the fix: just check that "LIMIT 0 OFFSET n" is +# handled in the same way as "LIMIT 0" +explain select * from t1 limit 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +explain select * from t1 limit 0 offset 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +drop table t1, t2; diff --git a/mysql-test/main/distinct.test b/mysql-test/main/distinct.test index da12c7273b2..32e189da98a 100644 --- a/mysql-test/main/distinct.test +++ b/mysql-test/main/distinct.test @@ -818,3 +818,41 @@ UNION drop table t1; --echo End of 5.5 tests + +--echo # +--echo # MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN +--echo # +CREATE TABLE t1 ( + id int(7) NOT NULL AUTO_INCREMENT, + name varchar(50) DEFAULT NULL, + primary key (id) +); +INSERT INTO t1 VALUES (1, 'Reed'), (10, 'no-child'); + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + parent_id int(7) NOT NULL, + name varchar(100) DEFAULT NULL, + primary key (id), + key(parent_id) +); + +INSERT INTO t2 VALUES (1, 1,'John'), (2, 2,'no-parent'); + +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0; + +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0 offset 5; + +--echo # Test the second part of the fix: just check that "LIMIT 0 OFFSET n" is +--echo # handled in the same way as "LIMIT 0" + +explain select * from t1 limit 0; +explain select * from t1 limit 0 offset 10; + +drop table t1, t2; diff --git a/sql/sql_limit.h b/sql/sql_limit.h index a4fcedac14a..19c1ce57e99 100644 --- a/sql/sql_limit.h +++ b/sql/sql_limit.h @@ -35,6 +35,8 @@ class Select_limit_counters void set_limit(ha_rows limit, ha_rows offset) { + if (limit == 0) + offset= 0; offset_limit_cnt= offset; select_limit_cnt= limit; if (select_limit_cnt + offset_limit_cnt >=