summaryrefslogtreecommitdiff
path: root/storage/spider
diff options
context:
space:
mode:
authorJacob Mathew <jacob.mathew@mariadb.com>2018-09-13 13:27:03 -0700
committerJacob Mathew <jacob.mathew@mariadb.com>2018-09-13 13:27:03 -0700
commit6c47c1c45696affc5e4b1ecb1b8e941c70dfde8a (patch)
tree52109d3ee96769be3169d87da8a8d8179f06577e /storage/spider
parentc9d6728c36831ee4e1ba0b105652922faa4a3aee (diff)
downloadmariadb-git-6c47c1c45696affc5e4b1ecb1b8e941c70dfde8a.tar.gz
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. Author: Jacob Mathew. Reviewer: Kentoku Shiba. Cherry-Picked: Commit eb2ca3d on branch bb-10.2-MDEV-16912
Diffstat (limited to 'storage/spider')
-rw-r--r--storage/spider/mysql-test/spider/include/init_child2_1.inc14
-rw-r--r--storage/spider/mysql-test/spider/include/init_master_1.inc4
-rw-r--r--storage/spider/mysql-test/spider/r/spider_fixes_part.result48
-rw-r--r--storage/spider/mysql-test/spider/t/spider_fixes_part.test109
4 files changed, 175 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=
diff --git a/storage/spider/mysql-test/spider/r/spider_fixes_part.result b/storage/spider/mysql-test/spider/r/spider_fixes_part.result
index 104ba971df9..2b313e0e9dc 100644
--- a/storage/spider/mysql-test/spider/r/spider_fixes_part.result
+++ b/storage/spider/mysql-test/spider/r/spider_fixes_part.result
@@ -197,6 +197,54 @@ id
6216
10000
+Test ORDER BY with LIMIT and OFFSET
+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)
+) ENGINE=Spider 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"')
+INSERT INTO ta_ob VALUES ('0B95CD65DF994BC9A09A6AABE53A2733',
+'6CFED89FF6A84C7AA55C3C432663D094',
+'51041110620304', '2018-08-02 13:41:13',
+510411, 1);
+INSERT INTO ta_ob VALUES ('15E8D55EF099443BAEE639E60A4650BD',
+'879DC2A0B6AC46D9A62E8EA47E2970F2',
+'51041110620301', NULL,
+510411, 0);
+INSERT INTO ta_ob VALUES ('51ECF2C0CD3C48D99C91792E99D3C1A0',
+'017B8A460DBC444682B791305EF75356',
+'51041110620308', '2018-08-02 13:48:29',
+510411, 0);
+INSERT INTO ta_ob VALUES ('093B37A93A534DF883787AF5F6799674',
+'996C7F14989D480589A553717D735E3E',
+'51041110620302', '2018-08-02 13:48:30',
+510411, 0);
+INSERT INTO ta_ob VALUES ('53F5266FB069499AB6234755CACA2583',
+'017B8A460DBC444682B791305EF75356',
+'51041110620308', '2018-08-02 13:48:28',
+510411, 0);
+INSERT INTO ta_ob VALUES ('56E59BC4BDC143868D4A219C2D07A24B',
+'821E71E6ABB4404EBAA349BB681089F8',
+'51041110620310', '2018-08-02 13:48:27',
+510411, 0);
+INSERT INTO ta_ob VALUES ('56B68DA68D6D4A04A08B453D09AD7B70',
+'821E71E6ABB4404EBAA349BB681089F8',
+'51041110620310', '2018-08-02 13:48:28',
+510411, 0);
+SELECT * FROM ta_ob WHERE c LIKE "%510411106%" AND e = 510411 AND f != 1 ORDER BY d,c LIMIT 5 OFFSET 1;
+a b c d e f
+56E59BC4BDC143868D4A219C2D07A24B 821E71E6ABB4404EBAA349BB681089F8 51041110620310 2018-08-02 13:48:27 510411 0
+53F5266FB069499AB6234755CACA2583 017B8A460DBC444682B791305EF75356 51041110620308 2018-08-02 13:48:28 510411 0
+56B68DA68D6D4A04A08B453D09AD7B70 821E71E6ABB4404EBAA349BB681089F8 51041110620310 2018-08-02 13:48:28 510411 0
+51ECF2C0CD3C48D99C91792E99D3C1A0 017B8A460DBC444682B791305EF75356 51041110620308 2018-08-02 13:48:29 510411 0
+093B37A93A534DF883787AF5F6799674 996C7F14989D480589A553717D735E3E 51041110620302 2018-08-02 13:48:30 510411 0
+
deinit
DROP DATABASE IF EXISTS auto_test_local;
DROP DATABASE IF EXISTS auto_test_local;
diff --git a/storage/spider/mysql-test/spider/t/spider_fixes_part.test b/storage/spider/mysql-test/spider/t/spider_fixes_part.test
index ef5a8026c02..868e684f959 100644
--- a/storage/spider/mysql-test/spider/t/spider_fixes_part.test
+++ b/storage/spider/mysql-test/spider/t/spider_fixes_part.test
@@ -616,6 +616,115 @@ if ($HAVE_PARTITION)
}
}
+--echo
+--echo Test ORDER BY with LIMIT and OFFSET
+if ($HAVE_PARTITION)
+{
+ if ($USE_CHILD_GROUP2)
+ {
+ if (!$OUTPUT_CHILD_GROUP2)
+ {
+ --disable_query_log
+ --disable_result_log
+ }
+ --connection child2_1
+ if ($OUTPUT_CHILD_GROUP2)
+ {
+ --disable_query_log
+ echo CHILD2_1_DROP_TABLES7;
+ echo CHILD2_1_CREATE_TABLES7;
+ }
+ --disable_warnings
+ eval $CHILD2_1_DROP_TABLES7;
+ --enable_warnings
+ eval $CHILD2_1_CREATE_TABLES7;
+ if ($OUTPUT_CHILD_GROUP2)
+ {
+ --enable_query_log
+ }
+ if ($USE_GENERAL_LOG)
+ {
+ TRUNCATE TABLE mysql.general_log;
+ }
+ if (!$OUTPUT_CHILD_GROUP2)
+ {
+ --enable_query_log
+ --enable_result_log
+ }
+ }
+ --connection master_1
+ --disable_query_log
+ --disable_warnings
+ DROP TABLE IF EXISTS ta_ob;
+ --enable_warnings
+ echo 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)
+ ) $MASTER_1_ENGINE $MASTER_1_COMMENT6_P_1_1;
+ eval 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)
+ ) $MASTER_1_ENGINE $MASTER_1_COMMENT6_P_1_1;
+ --enable_query_log
+ INSERT INTO ta_ob VALUES ('0B95CD65DF994BC9A09A6AABE53A2733',
+ '6CFED89FF6A84C7AA55C3C432663D094',
+ '51041110620304', '2018-08-02 13:41:13',
+ 510411, 1);
+ INSERT INTO ta_ob VALUES ('15E8D55EF099443BAEE639E60A4650BD',
+ '879DC2A0B6AC46D9A62E8EA47E2970F2',
+ '51041110620301', NULL,
+ 510411, 0);
+ INSERT INTO ta_ob VALUES ('51ECF2C0CD3C48D99C91792E99D3C1A0',
+ '017B8A460DBC444682B791305EF75356',
+ '51041110620308', '2018-08-02 13:48:29',
+ 510411, 0);
+ INSERT INTO ta_ob VALUES ('093B37A93A534DF883787AF5F6799674',
+ '996C7F14989D480589A553717D735E3E',
+ '51041110620302', '2018-08-02 13:48:30',
+ 510411, 0);
+ INSERT INTO ta_ob VALUES ('53F5266FB069499AB6234755CACA2583',
+ '017B8A460DBC444682B791305EF75356',
+ '51041110620308', '2018-08-02 13:48:28',
+ 510411, 0);
+ INSERT INTO ta_ob VALUES ('56E59BC4BDC143868D4A219C2D07A24B',
+ '821E71E6ABB4404EBAA349BB681089F8',
+ '51041110620310', '2018-08-02 13:48:27',
+ 510411, 0);
+ INSERT INTO ta_ob VALUES ('56B68DA68D6D4A04A08B453D09AD7B70',
+ '821E71E6ABB4404EBAA349BB681089F8',
+ '51041110620310', '2018-08-02 13:48:28',
+ 510411, 0);
+ SELECT * FROM ta_ob WHERE c LIKE "%510411106%" AND e = 510411 AND f != 1 ORDER BY d,c LIMIT 5 OFFSET 1;
+ if ($USE_CHILD_GROUP2)
+ {
+ if (!$OUTPUT_CHILD_GROUP2)
+ {
+ --disable_query_log
+ --disable_result_log
+ }
+ --connection child2_1
+ if ($USE_GENERAL_LOG)
+ {
+ SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
+ }
+ eval $CHILD2_1_SELECT_TABLES7;
+ if (!$OUTPUT_CHILD_GROUP2)
+ {
+ --enable_query_log
+ --enable_result_log
+ }
+ }
+}
--echo
--echo deinit