Debian Codegraph @

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

raw
1 SET @max_row = 20;
2 SET @@session.storage_engine = 'InnoDB';
3
4 #------------------------------------------------------------------------
5 # There are several testcases disabled because of the open bugs
6 # #15890
7 # The expected results suffer from the following bugs
8 # harmless #17455, #19305
9 # which cannot be suppressed because of technical reasons.
10 #------------------------------------------------------------------------
11
12 #------------------------------------------------------------------------
13 # 0. Setting of auxiliary variables + Creation of an auxiliary tables
14 # needed in many testcases
15 #------------------------------------------------------------------------
16 SELECT @max_row DIV 2 INTO @max_row_div2;
17 SELECT @max_row DIV 3 INTO @max_row_div3;
18 SELECT @max_row DIV 4 INTO @max_row_div4;
19 SET @max_int_4 = 2147483647;
20 DROP TABLE IF EXISTS t0_template;
21 CREATE TABLE t0_template (
22 f_int1 INTEGER,
23 f_int2 INTEGER,
24 f_char1 CHAR(20),
25 f_char2 CHAR(20),
26 f_charbig VARCHAR(1000) ,
27 PRIMARY KEY(f_int1))
28 ENGINE = MEMORY;
29 # Logging of <max_row> INSERTs into t0_template suppressed
30 DROP TABLE IF EXISTS t0_definition;
31 CREATE TABLE t0_definition (
32 state CHAR(3),
33 create_command VARBINARY(5000),
34 file_list VARBINARY(5000),
35 PRIMARY KEY (state)
36 ) ENGINE = MEMORY;
37 DROP TABLE IF EXISTS t0_aux;
38 CREATE TABLE t0_aux ( f_int1 INTEGER,
39 f_int2 INTEGER,
40 f_char1 CHAR(20),
41 f_char2 CHAR(20),
42 f_charbig VARCHAR(1000) )
43 ENGINE = MEMORY;
44 SET AUTOCOMMIT= 1;
45 SET @@session.sql_mode= '';
46 # End of basic preparations needed for all tests
47 #-----------------------------------------------
48
49 #========================================================================
50 # Calculation of "exotic" results within the partition function
51 # outside of SIGNED BIGINT value range, 0, NULL
52 # column used in partitioning function has type CHAR
53 #========================================================================
54 # 1. HASH(<check value>)
55 DROP TABLE IF EXISTS t1;
56 CREATE TABLE t1 (
57 f_int1 INTEGER,
58 f_int2 INTEGER,
59 f_char1 CHAR(20),
60 f_char2 CHAR(20),
61 f_charbig VARCHAR(1000)
62 )
63 PARTITION BY HASH(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) PARTITIONS 8;
64 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
65 VALUES(2147483646,2147483646,'2147483646','2147483646','#2147483646#');
66 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '2147483646';
67 COUNT(*) = 1
68 1
69 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
70 VALUES(-2147483646,-2147483646,'-2147483646','-2147483646','#-2147483646#');
71 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '-2147483646';
72 COUNT(*) = 1
73 1
74 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
75 VALUES(0,0,'0','0','#0#');
76 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '0';
77 COUNT(*) = 1
78 1
79 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
80 VALUES(NULL,NULL,NULL,NULL,NULL);
81 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL;
82 COUNT(*) = 1
83 1
84 DROP TABLE t1;
85 # 2. RANGE(<check value>)
86 CREATE TABLE t1 (
87 f_int1 INTEGER,
88 f_int2 INTEGER,
89 f_char1 CHAR(20),
90 f_char2 CHAR(20),
91 f_charbig VARCHAR(1000)
92 )
93 PARTITION BY RANGE(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER))
94 (PARTITION p0 VALUES LESS THAN (0),
95 PARTITION p1 VALUES LESS THAN (1000000),
96 PARTITION p2 VALUES LESS THAN MAXVALUE);
97 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
98 VALUES(2147483646,2147483646,'2147483646','2147483646','#2147483646#');
99 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '2147483646';
100 COUNT(*) = 1
101 1
102 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
103 VALUES(-2147483646,-2147483646,'-2147483646','-2147483646','#-2147483646#');
104 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '-2147483646';
105 COUNT(*) = 1
106 1
107 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
108 VALUES(0,0,'0','0','#0#');
109 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '0';
110 COUNT(*) = 1
111 1
112 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
113 VALUES(NULL,NULL,NULL,NULL,NULL);
114 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL;
115 COUNT(*) = 1
116 1
117 DROP TABLE t1;
118 # 3. LIST(<check value>)
119 CREATE TABLE t1 (
120 f_int1 INTEGER,
121 f_int2 INTEGER,
122 f_char1 CHAR(20),
123 f_char2 CHAR(20),
124 f_charbig VARCHAR(1000)
125 )
126 PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER))
127 (PARTITION p0 VALUES IN (0),
128 PARTITION p1 VALUES IN (NULL),
129 PARTITION p2 VALUES IN (CAST( 2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)),
130 PARTITION p3 VALUES IN (CAST(-2147483646 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)));
131 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
132 VALUES(2147483646,2147483646,'2147483646','2147483646','#2147483646#');
133 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '2147483646';
134 COUNT(*) = 1
135 1
136 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
137 VALUES(-2147483646,-2147483646,'-2147483646','-2147483646','#-2147483646#');
138 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '-2147483646';
139 COUNT(*) = 1
140 1
141 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
142 VALUES(0,0,'0','0','#0#');
143 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 = '0';
144 COUNT(*) = 1
145 1
146 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
147 VALUES(NULL,NULL,NULL,NULL,NULL);
148 SELECT COUNT(*) = 1 FROM t1 WHERE f_char1 IS NULL;
149 COUNT(*) = 1
150 1
151 DROP TABLE t1;
152 # 4. Partition by RANGE(...) subpartition by HASH(<check value>)
153 CREATE TABLE t1 (
154 f_int1 INTEGER,
155 f_int2 INTEGER,
156 f_char1 CHAR(20),
157 f_char2 CHAR(20),
158 f_charbig VARCHAR(1000)
159 )
160 PARTITION BY RANGE(CAST(f_char1 AS SIGNED INTEGER))
161 SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4
162 (PARTITION p0 VALUES LESS THAN (0),
163 PARTITION p1 VALUES LESS THAN MAXVALUE);
164 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
165 VALUES(2147483646,2147483646,'1','2147483646','#2147483646#');
166 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '2147483646';
167 COUNT(*) = 1
168 1
169 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
170 VALUES(-2147483646,-2147483646,'-1','-2147483646','#-2147483646#');
171 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '-2147483646';
172 COUNT(*) = 1
173 1
174 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
175 VALUES(0,0,'0','0','#0#');
176 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '0';
177 COUNT(*) = 1
178 1
179 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
180 VALUES(NULL,NULL,NULL,NULL,NULL);
181 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL;
182 COUNT(*) = 1
183 1
184 DROP TABLE t1;
185 # 5. Partition by LIST(...) subpartition by HASH(<check value>)
186 CREATE TABLE t1 (
187 f_int1 INTEGER,
188 f_int2 INTEGER,
189 f_char1 CHAR(20),
190 f_char2 CHAR(20),
191 f_charbig VARCHAR(1000)
192 )
193 PARTITION BY LIST(CAST(f_char1 AS SIGNED INTEGER))
194 SUBPARTITION BY HASH(CAST(f_char2 AS SIGNED INTEGER) * CAST(5.0E+18 AS SIGNED INTEGER)) SUBPARTITIONS 4
195 (PARTITION p0 VALUES IN (NULL),
196 PARTITION p1 VALUES IN (1));
197 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
198 VALUES(2147483646,2147483646,'1','2147483646','#2147483646#');
199 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '2147483646';
200 COUNT(*) = 1
201 1
202 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
203 VALUES(-2147483646,-2147483646,'1','-2147483646','#-2147483646#');
204 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '-2147483646';
205 COUNT(*) = 1
206 1
207 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
208 VALUES(0,0,'1','0','#0#');
209 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 = '0';
210 COUNT(*) = 1
211 1
212 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
213 VALUES(NULL,NULL,NULL,NULL,NULL);
214 SELECT COUNT(*) = 1 FROM t1 WHERE f_char2 IS NULL;
215 COUNT(*) = 1
216 1
217 DROP TABLE t1;
218 DROP VIEW IF EXISTS v1;
219 DROP TABLE IF EXISTS t1;
220 DROP TABLE IF EXISTS t0_aux;
221 DROP TABLE IF EXISTS t0_definition;
222 DROP TABLE IF EXISTS t0_template;