MDEV-16912: Spider Order By column[datatime] limit 5 returns 3 rows

The problem occurs in 10.2 and earlier releases of MariaDB Server because the
Partition Engine was not pushing the engine conditions to the underlying
storage engine of each partition.  This caused Spider to return the first 5
rows in the table with the data provided by the customer.  2 of the 5 rows
did not qualify the WHERE clause, so they were removed from the result set by
the server.

To fix the problem, I have back-ported support for engine condition pushdown
in the Partition Engine from MariaDB Server 10.3 to 10.2 and 10.1.  In 10.3
and 10.4 I have merged the comments and the test case.

Author:
  Jacob Mathew.

Reviewer:
  Kentoku Shiba.

Merged:
  Commit eb2ca3d on branch bb-10.2-MDEV-16912

2 files changed, 18 insertions, 0 deletions

diff --git a/storage/spider/mysql-test/spider/include/init_child2_1.inc b/storage/spider/mysql-test/spider/include/init_child2_1.inc
index 10793649f94..782f538eb43 100644
--- a/storage/spider/mysql-test/spider/include/init_child2_1.inc
+++ b/storage/spider/mysql-test/spider/include/init_child2_1.inc
@@ -69,6 +69,20 @@ let $CHILD2_1_CREATE_TABLES6=
   ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
 let $CHILD2_1_SELECT_TABLES6=
   SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r_3 ORDER BY a;
+let $CHILD2_1_DROP_TABLES7=
+  DROP TABLE IF EXISTS ta_ob;
+let $CHILD2_1_CREATE_TABLES7=
+  CREATE TABLE ta_ob (
+    a VARCHAR(50) NOT NULL,
+    b VARCHAR(50) NULL DEFAULT NULL,
+    c VARCHAR(100) NULL DEFAULT NULL,
+    d DATETIME(0) NULL DEFAULT NULL,
+    e INT(11) NOT NULL,
+    f INT(10) NULL DEFAULT NULL,
+    PRIMARY KEY (a, e)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+let $CHILD2_1_SELECT_TABLES7=
+  SELECT * FROM ta_ob WHERE c LIKE "%510411106%" AND e = 510411 AND f != 1 ORDER BY d,c LIMIT 6 OFFSET 0;
 let $CHILD2_1_DROP_FT_TABLES=
   DROP TABLE IF EXISTS ft_r;
 let $CHILD2_1_CREATE_FT_TABLES=
diff --git a/storage/spider/mysql-test/spider/include/init_master_1.inc b/storage/spider/mysql-test/spider/include/init_master_1.inc
index 93947a8d454..2c45d0bd65d 100644
--- a/storage/spider/mysql-test/spider/include/init_master_1.inc
+++ b/storage/spider/mysql-test/spider/include/init_master_1.inc
@@ -94,6 +94,10 @@ if (!$VERSION_COMPILE_OS_WIN)
     CONNECTION='host "localhost", socket "$CHILD2_1_MYSOCK", user "root",
     password ""';
 }
+let $MASTER_1_COMMENT6_P_1_1=
+  COMMENT='database "auto_test_remote", table "ta_ob"'
+  PARTITION BY LIST COLUMNS (e) PARTITIONS 1
+  (PARTITION pt1 values in (510411) COMMENT = 'srv "s_2_1"');
 if ($VERSION_COMPILE_OS_WIN)
 {
   let $MASTER_1_COMMENT_FT_2_1=