MDEV-16327: Server doesn't account for engines that supports OFFSET on their own.bb-10.5-MDEV-16327

Engine get LIMIT/OFFSET info an can it use/reset.

7 files changed, 114 insertions, 51 deletions

diff --git a/sql/group_by_handler.h b/sql/group_by_handler.h
index 97ee44d73d3..ff3b204fa56 100644
--- a/sql/group_by_handler.h
+++ b/sql/group_by_handler.h
@@ -14,6 +14,10 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */
 
+#ifndef GROUP_BY_HANDLER_INCLUDED
+#define GROUP_BY_HANDLER_INCLUDED
+
+class Select_limit_counters;
 /*
   This file implements the group_by_handler interface. This interface
   can be used by storage handlers that can intercept summary or GROUP
@@ -56,7 +60,7 @@ struct Query
   ORDER      *order_by;
   Item       *having;
   // LIMIT
-  //ha_rows select_limit_cnt, offset_limit_cnt;
+  Select_limit_counters *limit;
 };
 
 class group_by_handler
@@ -101,3 +105,4 @@ public:
   virtual void print_error(int error, myf errflag);
 };
 
+#endif //GROUP_BY_HANDLER_INCLUDED
diff --git a/sql/item.h b/sql/item.h
index f26ca6493d0..c4c8d6da792 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -33,6 +33,56 @@
 C_MODE_START
 #include <ma_dyncol.h>
 
+class Select_limit_counters
+{
+  ha_rows select_limit_cnt, offset_limit_cnt;
+
+  public:
+    Select_limit_counters():
+       select_limit_cnt(0), offset_limit_cnt(0)
+       {};
+    Select_limit_counters(Select_limit_counters &orig):
+       select_limit_cnt(orig.select_limit_cnt),
+       offset_limit_cnt(orig.offset_limit_cnt)
+       {};
+
+   void set_limit(ha_rows limit, ha_rows offset)
+   {
+      offset_limit_cnt= offset;
+      select_limit_cnt= limit;
+      if (select_limit_cnt + offset_limit_cnt >=
+          select_limit_cnt)
+        select_limit_cnt+= offset_limit_cnt;
+      else
+        select_limit_cnt= HA_POS_ERROR;
+   }
+
+   void set_single_row()
+   {
+     offset_limit_cnt= 0;
+     select_limit_cnt= 1;
+   }
+
+   bool is_unlimited()
+   { return select_limit_cnt == HA_POS_ERROR; }
+   bool is_unrestricted()
+   { return select_limit_cnt == HA_POS_ERROR && offset_limit_cnt == 0; }
+   void set_unlimited()
+   { select_limit_cnt= HA_POS_ERROR; offset_limit_cnt= 0; }
+
+   bool check_offset(ha_rows sent)
+   {
+     return sent < offset_limit_cnt;
+   }
+   void remove_offset() { offset_limit_cnt= 0; }
+
+   ha_rows get_select_limit()
+   { return select_limit_cnt; }
+   ha_rows get_offset_limit()
+   { return offset_limit_cnt; }
+};
+
+
 /*
   A prototype for a C-compatible structure to store a value of any data type.
   Currently it has to stay in /sql, as it depends on String and my_decimal.
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index d9fca27c354..6fbb8d836f2 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -829,50 +829,6 @@ void create_explain_query(LEX *lex, MEM_ROOT *mem_root);
 void create_explain_query_if_not_exists(LEX *lex, MEM_ROOT *mem_root);
 bool print_explain_for_slow_log(LEX *lex, THD *thd, String *str);
 
-class Select_limit_counters
-{
-  ha_rows select_limit_cnt, offset_limit_cnt;
-
-  public:
-    Select_limit_counters():
-       select_limit_cnt(0), offset_limit_cnt(0)
-       {};
-
-   void set_limit(ha_rows limit, ha_rows offset)
-   {
-      offset_limit_cnt= offset;
-      select_limit_cnt= limit;
-      if (select_limit_cnt + offset_limit_cnt >=
-          select_limit_cnt)
-        select_limit_cnt+= offset_limit_cnt;
-      else
-        select_limit_cnt= HA_POS_ERROR;
-   }
-
-   void set_single_row()
-   {
-     offset_limit_cnt= 0;
-     select_limit_cnt= 1;
-   }
-
-   bool is_unlimited()
-   { return select_limit_cnt == HA_POS_ERROR; }
-   void set_unlimited()
-   { select_limit_cnt= HA_POS_ERROR; offset_limit_cnt= 0; }
-
-   bool check_offset(ha_rows sent)
-   {
-     return sent < offset_limit_cnt;
-   }
-   void remove_offset() { offset_limit_cnt= 0; }
-
-   ha_rows get_select_limit()
-   { return select_limit_cnt; }
-   ha_rows get_offset_limit()
-   { return offset_limit_cnt; }
-};
-
-
 class st_select_lex_unit: public st_select_lex_node {
 protected:
   TABLE_LIST result_table_list;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 40d6e688ba9..b5144eee386 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3146,7 +3146,8 @@ bool JOIN::make_aggr_tables_info()
     {
       /* Check if the storage engine can intercept the query */
       Query query= {&all_fields, select_distinct, tables_list, conds,
-                    group_list, order ? order : group_list, having};
+                    group_list, order ? order : group_list, having,
+                    &select_lex->master_unit()->lim};
       group_by_handler *gbh= ht->create_group_by(thd, &query);
 
       if (gbh)
diff --git a/storage/sequence/mysql-test/sequence/group_by.result b/storage/sequence/mysql-test/sequence/group_by.result
index 2941e85c1f4..a90411f5fef 100644
--- a/storage/sequence/mysql-test/sequence/group_by.result
+++ b/storage/sequence/mysql-test/sequence/group_by.result
@@ -97,3 +97,33 @@ count(NULL)
 select count(NULL) from seq_1_to_3;
 count(NULL)
 0
+#
+# MDEV-16327: Server doesn't account for engines that supports
+# OFFSET on their own.
+#
+select count(NULL) from seq_1_to_3 limit 1;
+count(NULL)
+0
+explain format=json select count(NULL) from seq_1_to_3 limit 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "message": "Storage engine handles GROUP BY"
+    }
+  }
+}
+select count(NULL) from seq_1_to_3 limit 1 offset 1;
+count(NULL)
+explain format=json select count(NULL) from seq_1_to_3 limit 1 offset 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "message": "Storage engine handles GROUP BY"
+    }
+  }
+}
+# End of 10.5 tests
diff --git a/storage/sequence/mysql-test/sequence/group_by.test b/storage/sequence/mysql-test/sequence/group_by.test
index b3f4ab58771..dbdd0586615 100644
--- a/storage/sequence/mysql-test/sequence/group_by.test
+++ b/storage/sequence/mysql-test/sequence/group_by.test
@@ -49,3 +49,15 @@ explain select count(*) from seq_1_to_15_step_2 group by mod(seq,2);
 create temporary table t1 select * from seq_1_to_3;
 select count(NULL) from t1;
 select count(NULL) from seq_1_to_3;
+
+--echo #
+--echo # MDEV-16327: Server doesn't account for engines that supports
+--echo # OFFSET on their own.
+--echo #
+
+select count(NULL) from seq_1_to_3 limit 1;
+explain format=json select count(NULL) from seq_1_to_3 limit 1;
+select count(NULL) from seq_1_to_3 limit 1 offset 1;
+explain format=json select count(NULL) from seq_1_to_3 limit 1 offset 1;
+
+--echo # End of 10.5 tests
diff --git a/storage/sequence/sequence.cc b/storage/sequence/sequence.cc
index b9f5d02bd51..aa4c6252da8 100644
--- a/storage/sequence/sequence.cc
+++ b/storage/sequence/sequence.cc
@@ -361,15 +361,21 @@ static int dummy_savepoint(handlerton *, THD *, void *) { return 0; }
 
 class ha_seq_group_by_handler: public group_by_handler
 {
+  Select_limit_counters limit;
   List<Item> *fields;
   TABLE_LIST *table_list;
   bool first_row;
 
 public:
   ha_seq_group_by_handler(THD *thd_arg, List<Item> *fields_arg,
-                          TABLE_LIST *table_list_arg)
-    : group_by_handler(thd_arg, sequence_hton), fields(fields_arg),
-      table_list(table_list_arg) {}
+                          TABLE_LIST *table_list_arg,
+                          Select_limit_counters *orig_lim)
+    : group_by_handler(thd_arg, sequence_hton),  limit(orig_lim[0]),
+      fields(fields_arg), table_list(table_list_arg)
+    {
+      // Reset limit because we are handling it now
+      orig_lim->set_unlimited();
+    }
   ~ha_seq_group_by_handler() {}
   int init_scan() { first_row= 1 ; return 0; }
   int next_row();
@@ -425,7 +431,8 @@ create_group_by_handler(THD *thd, Query *query)
   }
 
   /* Create handler and return it */
-  handler= new ha_seq_group_by_handler(thd, query->select, query->from);
+  handler= new ha_seq_group_by_handler(thd, query->select, query->from,
+                                       query->limit);
   return handler;
 }
 
@@ -440,7 +447,9 @@ int ha_seq_group_by_handler::next_row()
     Check if this is the first call to the function. If not, we have already
     returned all data.
   */
-  if (!first_row)
+  if (!first_row ||
+      limit.get_offset_limit() > 0 ||
+      limit.get_select_limit() == 0)
     DBUG_RETURN(HA_ERR_END_OF_FILE);
   first_row= 0;