Debian Codegraph @

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

raw
1 create table t1 (a decimal(10,4) not null, primary key(a)) engine='InnoDB'
2 partition by key (a) (
3 partition pa1 max_rows=20 min_rows=2,
4 partition pa2 max_rows=30 min_rows=3,
5 partition pa3 max_rows=30 min_rows=4,
6 partition pa4 max_rows=40 min_rows=2);
7 show create table t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `a` decimal(10,4) NOT NULL,
11 PRIMARY KEY (`a`)
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
13 /*!50100 PARTITION BY KEY (a)
14 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
15 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
16 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
17 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
18 insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567);
19 select * from t1;
20 a
21 -1234.5670
22 123456.7899
23 -123456.7899
24 0.0000
25 -999999.9999
26 -1.5000
27 -1.0000
28 1.0000
29 1234.5670
30 999999.9999
31 1.5000
32 select * from t1 where a=1234.567;
33 a
34 1234.5670
35 delete from t1 where a=1234.567;
36 select * from t1;
37 a
38 -1234.5670
39 123456.7899
40 -123456.7899
41 0.0000
42 -999999.9999
43 -1.5000
44 -1.0000
45 1.0000
46 999999.9999
47 1.5000
48 drop table t1;
49 create table t2 (a decimal(18,9) not null, primary key(a)) engine='InnoDB'
50 partition by key (a) partitions 10;
51 show create table t2;
52 Table Create Table
53 t2 CREATE TABLE `t2` (
54 `a` decimal(18,9) NOT NULL,
55 PRIMARY KEY (`a`)
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
57 /*!50100 PARTITION BY KEY (a)
58 PARTITIONS 10 */
59 insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567);
60 select * from t2;
61 a
62 -1.000000000
63 1.500000000
64 -999999999.999999999
65 -1.500000000
66 1234.567000000
67 999999999.999999999
68 -1234.567000000
69 0.000000000
70 select * from t2 where a=1234.567;
71 a
72 1234.567000000
73 delete from t2 where a=1234.567;
74 select * from t2;
75 a
76 -1.000000000
77 1.500000000
78 -999999999.999999999
79 -1.500000000
80 999999999.999999999
81 -1234.567000000
82 0.000000000
83 delete from t2;
84 1024*3 inserts;
85 select count(*) from t2;
86 count(*)
87 3072
88 drop table t2;
89 create table t3 (a decimal(18,9) not null, primary key(a)) engine='InnoDB'
90 partition by range (floor(a)) subpartition by key (a) subpartitions 2 (
91 partition pa2 values less than (2),
92 partition pa4 values less than (4),
93 partition pa6 values less than (6),
94 partition pa8 values less than (8),
95 partition pa10 values less than (10)
96 );
97 show create table t3;
98 Table Create Table
99 t3 CREATE TABLE `t3` (
100 `a` decimal(18,9) NOT NULL,
101 PRIMARY KEY (`a`)
102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
103 /*!50100 PARTITION BY RANGE (floor(`a`))
104 SUBPARTITION BY KEY (a)
105 SUBPARTITIONS 2
106 (PARTITION pa2 VALUES LESS THAN (2) ENGINE = InnoDB,
107 PARTITION pa4 VALUES LESS THAN (4) ENGINE = InnoDB,
108 PARTITION pa6 VALUES LESS THAN (6) ENGINE = InnoDB,
109 PARTITION pa8 VALUES LESS THAN (8) ENGINE = InnoDB,
110 PARTITION pa10 VALUES LESS THAN (10) ENGINE = InnoDB) */
111 9*3 inserts;
112 insert into t3 values (9);
113 insert into t3 values (9+0.333333333);
114 insert into t3 values (9+0.755555555);
115 insert into t3 values (8);
116 insert into t3 values (8+0.333333333);
117 insert into t3 values (8+0.755555555);
118 insert into t3 values (7);
119 insert into t3 values (7+0.333333333);
120 insert into t3 values (7+0.755555555);
121 insert into t3 values (6);
122 insert into t3 values (6+0.333333333);
123 insert into t3 values (6+0.755555555);
124 insert into t3 values (5);
125 insert into t3 values (5+0.333333333);
126 insert into t3 values (5+0.755555555);
127 insert into t3 values (4);
128 insert into t3 values (4+0.333333333);
129 insert into t3 values (4+0.755555555);
130 insert into t3 values (3);
131 insert into t3 values (3+0.333333333);
132 insert into t3 values (3+0.755555555);
133 insert into t3 values (2);
134 insert into t3 values (2+0.333333333);
135 insert into t3 values (2+0.755555555);
136 insert into t3 values (1);
137 insert into t3 values (1+0.333333333);
138 insert into t3 values (1+0.755555555);
139 select count(*) from t3;
140 count(*)
141 27
142 drop table t3;
143 create table t4 (a decimal(18,9) not null, primary key(a)) engine='InnoDB'
144 partition by list (ceiling(a)) subpartition by key (a) subpartitions 2 (
145 partition pa2 values in (1,2),
146 partition pa4 values in (3,4),
147 partition pa6 values in (5,6),
148 partition pa8 values in (7,8),
149 partition pa10 values in (9,10)
150 );
151 show create table t4;
152 Table Create Table
153 t4 CREATE TABLE `t4` (
154 `a` decimal(18,9) NOT NULL,
155 PRIMARY KEY (`a`)
156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
157 /*!50100 PARTITION BY LIST (ceiling(`a`))
158 SUBPARTITION BY KEY (a)
159 SUBPARTITIONS 2
160 (PARTITION pa2 VALUES IN (1,2) ENGINE = InnoDB,
161 PARTITION pa4 VALUES IN (3,4) ENGINE = InnoDB,
162 PARTITION pa6 VALUES IN (5,6) ENGINE = InnoDB,
163 PARTITION pa8 VALUES IN (7,8) ENGINE = InnoDB,
164 PARTITION pa10 VALUES IN (9,10) ENGINE = InnoDB) */
165 9*3 inserts;
166 insert into t4 values (9);
167 insert into t4 values (9+0.333333333);
168 insert into t4 values (9+0.755555555);
169 insert into t4 values (8);
170 insert into t4 values (8+0.333333333);
171 insert into t4 values (8+0.755555555);
172 insert into t4 values (7);
173 insert into t4 values (7+0.333333333);
174 insert into t4 values (7+0.755555555);
175 insert into t4 values (6);
176 insert into t4 values (6+0.333333333);
177 insert into t4 values (6+0.755555555);
178 insert into t4 values (5);
179 insert into t4 values (5+0.333333333);
180 insert into t4 values (5+0.755555555);
181 insert into t4 values (4);
182 insert into t4 values (4+0.333333333);
183 insert into t4 values (4+0.755555555);
184 insert into t4 values (3);
185 insert into t4 values (3+0.333333333);
186 insert into t4 values (3+0.755555555);
187 insert into t4 values (2);
188 insert into t4 values (2+0.333333333);
189 insert into t4 values (2+0.755555555);
190 insert into t4 values (1);
191 insert into t4 values (1+0.333333333);
192 insert into t4 values (1+0.755555555);
193 select count(*) from t4;
194 count(*)
195 27
196 drop table t4;