MDEV-27382: OFFSET is ignored when combined with DISTINCTbb-10.5-mdev27382

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.

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);