EVOLUTION-MANAGER

Edit File: spider_fixes_part.result

for master_1
for child2
child2_1
child2_2
child2_3
for child3
child3_1
child3_2
child3_3
for slave1_1

drop and create databases
connection master_1;
DROP DATABASE IF EXISTS auto_test_local;
CREATE DATABASE auto_test_local;
USE auto_test_local;
connection slave1_1;
DROP DATABASE IF EXISTS auto_test_local;
CREATE DATABASE auto_test_local;
USE auto_test_local;
connection child2_1;
DROP DATABASE IF EXISTS auto_test_remote;
CREATE DATABASE auto_test_remote;
USE auto_test_remote;
connection child2_2;
DROP DATABASE IF EXISTS auto_test_remote2;
CREATE DATABASE auto_test_remote2;
USE auto_test_remote2;

test select 1
connection master_1;
SELECT 1;
1
1
connection master_1;
DROP TABLE IF EXISTS tb_l;
CREATE TABLE tb_l (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE2 MASTER_1_CHARSET2
INSERT INTO tb_l (a, b, c) VALUES
(1, 'a', '2008-08-01 10:21:39'),
(2, 'b', '2000-01-01 00:00:00'),
(3, 'e', '2007-06-04 20:03:11'),
(4, 'd', '2003-11-30 05:01:03'),
(5, 'c', '2001-12-31 23:59:59');

2.17
partition with sort
connection master_1;
CREATE TABLE ta_l2 (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1
INSERT INTO ta_l2 SELECT a, b, c FROM tb_l;
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 WHERE a > 1
ORDER BY a;
a	b	date_format(c, '%Y-%m-%d %H:%i:%s')
2	b	2000-01-01 00:00:00
3	e	2007-06-04 20:03:11
4	d	2003-11-30 05:01:03
5	c	2001-12-31 23:59:59

2.23
partition update with moving partition
connection master_1;
DROP TABLE IF EXISTS ta_l2;
connection master_1;
CREATE TABLE ta_l2 (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1
INSERT INTO ta_l2 (a, b, c) VALUES (3, 'B', '2010-09-26 00:00:00');
UPDATE ta_l2 SET a = 4 WHERE a = 3;
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2;
a	b	date_format(c, '%Y-%m-%d %H:%i:%s')
4	B	2010-09-26 00:00:00
index merge with partition
connection master_1;
DROP TABLE IF EXISTS ta_l_int;
connection master_1;
CREATE TABLE ta_l_int (
a INT AUTO_INCREMENT,
b INT DEFAULT 10,
c INT DEFAULT 11,
PRIMARY KEY(a),
KEY idx1(b),
KEY idx2(c)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT3_P_2_1
INSERT INTO ta_l_int (a, b, c) VALUES (1, 2, 3);
INSERT INTO ta_l_int (a, b, c) SELECT a + 1, b + 1, c + 1 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 2, b + 2, c + 2 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 4, b + 4, c + 4 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 8, b + 8, c + 8 FROM ta_l_int;
connection master_1;
SELECT a, b, c FROM ta_l_int force index(primary, idx1, idx2)
WHERE a = 5 OR b = 5 OR c = 5 ORDER BY a;
a	b	c
3	4	5
4	5	6
5	6	7

2.26
auto_increment with partition
connection master_1;
connection slave1_1;
connection master_1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_INCREMENT1_P_1
MASTER_1_AUTO_INCREMENT_INCREMENT2
MASTER_1_AUTO_INCREMENT_OFFSET2
spider_direct_sql('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '',
'srv "s_2_1"')
1
spider_direct_sql('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '',
'srv "s_2_2"')
1
spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 2', '',
'srv "s_2_1"')
1
spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 3', '',
'srv "s_2_2"')
1
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
777
SELECT MAX(id) FROM t1;
MAX(id)
777
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1554
SELECT MAX(id) FROM t1;
MAX(id)
1554
INSERT INTO t1 (id) VALUES (null);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
2331
SELECT MAX(id) FROM t1;
MAX(id)
2331
INSERT INTO t1 (id) VALUES (null);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
3108
SELECT MAX(id) FROM t1;
MAX(id)
3108
INSERT INTO t1 () VALUES (),(),(),();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
3885
SELECT id FROM t1 ORDER BY id;
id
777
1554
2331
3108
3885
4662
5439
6216
SET INSERT_ID=5000;
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5000
SELECT MAX(id) FROM t1;
MAX(id)
6216
INSERT INTO t1 (id) VALUES (10000);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5000
SELECT MAX(id) FROM t1;
MAX(id)
10000
INSERT INTO t1 (id) VALUES (1000);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5000
SELECT MAX(id) FROM t1;
MAX(id)
10000
connection slave1_1;
SELECT id FROM t1 ORDER BY id;
id
777
1000
1554
2331
3108
3885
4662
5000
5439
6216
10000
connection master_1;

Test ORDER BY with LIMIT and OFFSET
connection master_1;
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

Crash from b4a2baffa82e5c07b96a1c752228560dcac1359b (MDEV-11084)
Fixed with 4968049799193394d442f26b4e3a8d95b185be72
Spider crashed if the first partition was not used first
connection master_1;
CREATE TABLE ta_l2 (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1
INSERT INTO ta_l2 SELECT a, b, c FROM tb_l;
Query a Spider table only using the second partition
SELECT a,b,c FROM ta_l2 PARTITION (pt2);
a	b	c
4	d	2003-11-30 05:01:03
5	c	2001-12-31 23:59:59
Query a Spider table only using the first partition
SELECT a,b,c FROM ta_l2 PARTITION (pt1);
a	b	c
1	a	2008-08-01 10:21:39
2	b	2000-01-01 00:00:00
3	e	2007-06-04 20:03:11
Query a Spider table by all paritions, then the second partition
SELECT min(a), max(a), min(b), max(b) FROM ta_l2;
min(a)	max(a)	min(b)	max(b)
1	5	a	e
SELECT a,b,c FROM ta_l2 PARTITION (pt2);
a	b	c
4	d	2003-11-30 05:01:03
5	c	2001-12-31 23:59:59

deinit
connection master_1;
DROP DATABASE IF EXISTS auto_test_local;
connection slave1_1;
DROP DATABASE IF EXISTS auto_test_local;
connection child2_1;
DROP DATABASE IF EXISTS auto_test_remote;
connection child2_2;
DROP DATABASE IF EXISTS auto_test_remote2;
for slave1_1
for master_1
for child2
child2_1
child2_2
child2_3
for child3
child3_1
child3_2
child3_3

end of test