Debian Codegraph @

mysql-test/suite/parts/r/partition_exch_qa_6.result

raw
1 CREATE USER test2@localhost;
2 CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
3 CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
4 CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
5 CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
6 CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
7 CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM
8 PARTITION BY RANGE (a)
9 (PARTITION p0 VALUES LESS THAN (10) ,
10 PARTITION p1 VALUES LESS THAN (100) ,
11 PARTITION p2 VALUES LESS THAN (1000) );
12 CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM
13 PARTITION BY RANGE (a)
14 SUBPARTITION BY HASH(a)
15 (PARTITION p0 VALUES LESS THAN (10)
16 (SUBPARTITION sp00,
17 SUBPARTITION sp01,
18 SUBPARTITION sp02,
19 SUBPARTITION sp03,
20 SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100)
21 (SUBPARTITION sp10 ,
22 SUBPARTITION sp11 ,
23 SUBPARTITION sp12 ,
24 SUBPARTITION sp13 ,
25 SUBPARTITION sp14 ),
26 PARTITION p2 VALUES LESS THAN (1000)
27 (SUBPARTITION sp20,
28 SUBPARTITION sp21,
29 SUBPARTITION sp22,
30 SUBPARTITION sp23,
31 SUBPARTITION sp24));
32 INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine");
33 INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen");
34 INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine");
35 INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen");
36 INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine");
37 INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine");
38 INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine");
39 INSERT INTO t_null VALUES (1, "NULL");
40 INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
41 INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
42 INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
43 INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
44 INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
45 INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
46 INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
47 INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
48 INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
49 INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
50 INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
51 INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
52 CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
53 CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
54 CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
55 CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
56 CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
57 GRANT USAGE ON *.* TO test2@localhost;
58 GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost;
59 connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
60 SHOW GRANTS FOR CURRENT_USER;
61 Grants for test2@localhost
62 GRANT USAGE ON *.* TO `test2`@`localhost`
63 GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO `test2`@`localhost`
64 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
65 SELECT * FROM t_10;
66 a b
67 2 Two
68 4 Four
69 6 Six
70 8 Eight
71 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
72 a b
73 1 One
74 3 Three
75 5 Five
76 9 Nine
77 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
78 SELECT * FROM t_10;
79 a b
80 1 One
81 3 Three
82 5 Five
83 9 Nine
84 SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
85 a b
86 2 Two
87 4 Four
88 6 Six
89 8 Eight
90 ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
91 SELECT * FROM tsp_00;
92 a b
93 SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
94 a b
95 2 Two
96 4 Four
97 5 Five
98 6 Six
99 8 Eight
100 ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
101 SELECT * FROM tsp_00;
102 a b
103 5 Five
104 SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
105 a b
106 2 Two
107 4 Four
108 6 Six
109 8 Eight
110 disconnect test2;
111 connection default;
112 REVOKE INSERT ON test.* FROM test2@localhost;
113 connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
114 USE test;
115 SHOW GRANTS FOR CURRENT_USER;
116 Grants for test2@localhost
117 GRANT USAGE ON *.* TO `test2`@`localhost`
118 GRANT SELECT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO `test2`@`localhost`
119 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
120 ERROR 42000: INSERT command denied to user 'test2'@'localhost' for table `test`.`tp`
121 disconnect test2;
122 connection default;
123 GRANT INSERT ON test.* TO test2@localhost;
124 REVOKE CREATE ON test.* FROM test2@localhost;
125 connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
126 USE test;
127 SHOW GRANTS FOR CURRENT_USER;
128 Grants for test2@localhost
129 GRANT USAGE ON *.* TO `test2`@`localhost`
130 GRANT SELECT, INSERT, UPDATE, DROP, ALTER ON `test`.* TO `test2`@`localhost`
131 ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
132 ERROR 42000: CREATE command denied to user 'test2'@'localhost' for table `test`.`tsp`
133 disconnect test2;
134 connection default;
135 GRANT CREATE ON test.* TO test2@localhost;
136 REVOKE DROP ON test.* FROM test2@localhost;
137 connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
138 SHOW GRANTS FOR CURRENT_USER;
139 Grants for test2@localhost
140 GRANT USAGE ON *.* TO `test2`@`localhost`
141 GRANT SELECT, INSERT, UPDATE, CREATE, ALTER ON `test`.* TO `test2`@`localhost`
142 ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
143 ERROR 42000: DROP command denied to user 'test2'@'localhost' for table `test`.`tp`
144 disconnect test2;
145 connection default;
146 DROP TABLE IF EXISTS t_10;
147 DROP TABLE IF EXISTS t_100;
148 DROP TABLE IF EXISTS t_1000;
149 DROP TABLE IF EXISTS tp;
150 DROP TABLE IF EXISTS tsp;
151 DROP TABLE IF EXISTS tsp_00;
152 DROP TABLE IF EXISTS tsp_01;
153 DROP TABLE IF EXISTS tsp_02;
154 DROP TABLE IF EXISTS tsp_03;
155 DROP TABLE IF EXISTS tsp_04;
156 DROP TABLE IF EXISTS t_empty;
157 DROP TABLE IF EXISTS t_null;
158 DROP USER test2@localhost;