revision-id: bbc410622680ac2dbb543104b67c2cecbc5782a1 (mariadb-10.5.13-47-gbbc41062268) parent(s): 6831b3f2a0fd656fb41dd9df5f141431988448f1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-13 15:53:44 +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 in two places: - Make JOIN::optimize_inner be able to infer "Zero limit" for "LIMIT 0 OFFSET some_non_zero_value" (in addition to just "LIMIT 0") - Make JOIN_TAB::remove_duplicates not apply its optimization for cases with non-zero OFFSET clause. --- mysql-test/main/distinct.result | 36 ++++++++++++++++++++++++++++++++++++ mysql-test/main/distinct.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 20 ++++++++++++++++---- 3 files changed, 90 insertions(+), 4 deletions(-) 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_select.cc b/sql/sql_select.cc index cb391314603..66879a39e95 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2149,11 +2149,14 @@ JOIN::optimize_inner() if (sel->having != having && having_value == Item::COND_OK) thd->change_item_tree(&sel->having, having); } + bool zero_limit= !unit->lim.get_select_limit() || + (unit->lim.get_select_limit() == + unit->lim.get_offset_limit()); + if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || - (!unit->lim.get_select_limit() && - !(select_options & OPTION_FOUND_ROWS))) + (zero_limit && !(select_options & OPTION_FOUND_ROWS))) { /* Impossible cond */ - if (unit->lim.get_select_limit()) + if (!zero_limit) { DBUG_PRINT("info", (having_value == Item::COND_FALSE ? "Impossible HAVING" : "Impossible WHERE")); @@ -24304,7 +24307,16 @@ JOIN_TAB::remove_duplicates() field_count++; } - if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having) + /* + If the select list does not have any non-constant items, then all rows + are identical. Adjust the LIMIT to just produce the first row. + Exceptions to this are: + - SQL_CALC_FOUND_ROWS + - when HAVING caluse is present (we'll need to check it) + - when there's a non-zero OFFSET clause + */ + if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having && + !join->unit->lim.get_offset_limit()) { // only const items with no OPTION_FOUND_ROWS join->unit->lim.set_single_row(); // Only send first row DBUG_RETURN(false);