1for master_1 2for child2 3child2_1 4child2_2 5child2_3 6for child3 7child3_1 8child3_2 9child3_3 10 11drop and create databases 12DROP DATABASE IF EXISTS auto_test_local; 13CREATE DATABASE auto_test_local; 14USE auto_test_local; 15DROP DATABASE IF EXISTS auto_test_remote; 16CREATE DATABASE auto_test_remote; 17USE auto_test_remote; 18DROP DATABASE IF EXISTS auto_test_remote2; 19CREATE DATABASE auto_test_remote2; 20USE auto_test_remote2; 21 22test select 1 23SELECT 1; 241 251 26DROP TABLE IF EXISTS tb_l; 27CREATE TABLE tb_l ( 28a INT, 29b CHAR(1), 30c DATETIME, 31PRIMARY KEY(a) 32) MASTER_1_ENGINE2 MASTER_1_CHARSET2 33INSERT INTO tb_l (a, b, c) VALUES 34(1, 'f', '2008-07-01 10:21:39'), 35(2, 'g', '2000-02-01 00:00:00'), 36(3, 'j', '2007-05-04 20:03:11'), 37(4, 'i', '2003-10-30 05:01:03'), 38(5, 'h', '2001-10-31 23:59:59'); 39 40create table with partition and select test 41CREATE TABLE ta_l2 ( 42PRIMARY KEY(a) 43) MASTER_1_ENGINE MASTER_1_COMMENT_P_2_1 44SELECT a, b, c FROM tb_l 45SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; 46a b date_format(c, '%Y-%m-%d %H:%i:%s') 471 f 2008-07-01 10:21:39 482 g 2000-02-01 00:00:00 493 j 2007-05-04 20:03:11 504 i 2003-10-30 05:01:03 515 h 2001-10-31 23:59:59 52 53select partition using pushdown 54SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 a WHERE 55a.b = 'g' ORDER BY a.a; 56a b date_format(a.c, '%Y-%m-%d %H:%i:%s') 572 g 2000-02-01 00:00:00 58 59select partition using index pushdown 60SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 a WHERE 61a.a > 0 AND a.b = 'g' ORDER BY a.a; 62a b date_format(a.c, '%Y-%m-%d %H:%i:%s') 632 g 2000-02-01 00:00:00 64 65update partition pushdown 66UPDATE ta_l2 SET b = 'e', c = '2009-03-03 03:03:03' WHERE b = 'j'; 67SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; 68a b date_format(c, '%Y-%m-%d %H:%i:%s') 691 f 2008-07-01 10:21:39 702 g 2000-02-01 00:00:00 713 e 2009-03-03 03:03:03 724 i 2003-10-30 05:01:03 735 h 2001-10-31 23:59:59 74 75update partition index pushdown 76UPDATE ta_l2 SET b = 'j', c = '2009-03-03 03:03:03' WHERE a > 0 AND b = 'e'; 77SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; 78a b date_format(c, '%Y-%m-%d %H:%i:%s') 791 f 2008-07-01 10:21:39 802 g 2000-02-01 00:00:00 813 j 2009-03-03 03:03:03 824 i 2003-10-30 05:01:03 835 h 2001-10-31 23:59:59 84 85delete partition pushdown 86TRUNCATE TABLE ta_l2; 87INSERT INTO ta_l2 SELECT a, b, c FROM tb_l; 88DELETE FROM ta_l2 WHERE b = 'g'; 89SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; 90a b date_format(c, '%Y-%m-%d %H:%i:%s') 911 f 2008-07-01 10:21:39 923 j 2007-05-04 20:03:11 934 i 2003-10-30 05:01:03 945 h 2001-10-31 23:59:59 95 96delete partition index pushdown 97TRUNCATE TABLE ta_l2; 98INSERT INTO ta_l2 SELECT a, b, c FROM tb_l; 99DELETE FROM ta_l2 WHERE a > 0 AND b = 'g'; 100SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; 101a b date_format(c, '%Y-%m-%d %H:%i:%s') 1021 f 2008-07-01 10:21:39 1033 j 2007-05-04 20:03:11 1044 i 2003-10-30 05:01:03 1055 h 2001-10-31 23:59:59 106 107deinit 108DROP DATABASE IF EXISTS auto_test_local; 109DROP DATABASE IF EXISTS auto_test_remote; 110DROP DATABASE IF EXISTS auto_test_remote2; 111for master_1 112for child2 113child2_1 114child2_2 115child2_3 116for child3 117child3_1 118child3_2 119child3_3 120 121end of test 122