Debian Codegraph @

mysql-test/suite/storage_engine/col_opt_unsigned.result

raw
1 #
2 # Fixed point columns (NUMERIC, DECIMAL)
3 #
4 DROP TABLE IF EXISTS t1;
5 CREATE TABLE t1 (d DECIMAL UNSIGNED <CUSTOM_COL_OPTIONS>,
6 d0 DECIMAL(0) UNSIGNED <CUSTOM_COL_OPTIONS>,
7 d1_1 DECIMAL(1,1) UNSIGNED <CUSTOM_COL_OPTIONS>,
8 d10_2 DECIMAL(10,2) UNSIGNED <CUSTOM_COL_OPTIONS>,
9 d60_10 DECIMAL(60,10) UNSIGNED <CUSTOM_COL_OPTIONS>,
10 n NUMERIC UNSIGNED <CUSTOM_COL_OPTIONS>,
11 n0_0 NUMERIC(0,0) UNSIGNED <CUSTOM_COL_OPTIONS>,
12 n1 NUMERIC(1) UNSIGNED <CUSTOM_COL_OPTIONS>,
13 n20_4 NUMERIC(20,4) UNSIGNED <CUSTOM_COL_OPTIONS>,
14 n65_4 NUMERIC(65,4) UNSIGNED <CUSTOM_COL_OPTIONS>
15 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
16 SHOW COLUMNS IN t1;
17 Field Type Null Key Default Extra
18 d decimal(10,0) unsigned # # #
19 d0 decimal(10,0) unsigned # # #
20 d1_1 decimal(1,1) unsigned # # #
21 d10_2 decimal(10,2) unsigned # # #
22 d60_10 decimal(60,10) unsigned # # #
23 n decimal(10,0) unsigned # # #
24 n0_0 decimal(10,0) unsigned # # #
25 n1 decimal(1,0) unsigned # # #
26 n20_4 decimal(20,4) unsigned # # #
27 n65_4 decimal(65,4) unsigned # # #
28 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (100,123456,0.3,40000.25,123456789123456789.10001,1024,7000.0,8.0,999999.9,9223372036854775807);
29 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (0,0,0,0,0,0,0,0,0,0);
30 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.0,9999999999.0,0.9,99999999.99,99999999999999999999999999999999999999999999999999.9999999999,9999999999.0,9999999999.0,9.0,9999999999999999.9999,9999999999999999999999999999999999999999999999999999999999999.9999);
31 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
32 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
33 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
34 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
35 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
36 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807);
37 Warnings:
38 Warning 1264 Out of range value for column 'd' at row 1
39 Warning 1264 Out of range value for column 'd0' at row 1
40 Warning 1264 Out of range value for column 'd1_1' at row 1
41 Warning 1264 Out of range value for column 'd10_2' at row 1
42 Warning 1264 Out of range value for column 'd60_10' at row 1
43 Warning 1264 Out of range value for column 'n' at row 1
44 Warning 1264 Out of range value for column 'n0_0' at row 1
45 Warning 1264 Out of range value for column 'n1' at row 1
46 Warning 1264 Out of range value for column 'n20_4' at row 1
47 Warning 1264 Out of range value for column 'n65_4' at row 1
48 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999);
49 Warnings:
50 Warning 1264 Out of range value for column 'd' at row 1
51 Warning 1264 Out of range value for column 'd0' at row 1
52 Warning 1264 Out of range value for column 'd1_1' at row 1
53 Warning 1264 Out of range value for column 'd10_2' at row 1
54 Warning 1264 Out of range value for column 'd60_10' at row 1
55 Warning 1264 Out of range value for column 'n' at row 1
56 Warning 1264 Out of range value for column 'n0_0' at row 1
57 Warning 1264 Out of range value for column 'n1' at row 1
58 Warning 1264 Out of range value for column 'n20_4' at row 1
59 Warning 1264 Out of range value for column 'n65_4' at row 1
60 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
61 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
62 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
63 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
64 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
65 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
66 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
67 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1 WHERE n20_4 = 9999999999999999.9999 OR d < 100;
68 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
69 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
70 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
71 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
72 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
73 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) SELECT n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4 FROM t1 WHERE n65_4 = ( SELECT MAX(n65_4) FROM t1 );
74 Warnings:
75 Warning 1264 Out of range value for column 'd' at row 1
76 Warning 1264 Out of range value for column 'd0' at row 1
77 Warning 1264 Out of range value for column 'd1_1' at row 1
78 Warning 1264 Out of range value for column 'd10_2' at row 1
79 Warning 1264 Out of range value for column 'd60_10' at row 1
80 Warning 1264 Out of range value for column 'n' at row 1
81 Warning 1264 Out of range value for column 'n0_0' at row 1
82 Warning 1264 Out of range value for column 'n1' at row 1
83 Warning 1264 Out of range value for column 'n20_4' at row 1
84 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
85 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
86 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
87 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
88 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
89 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
90 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
91 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
92 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (10000000000.0,10000000000.0,1.1,100000000.99,100000000000000000000000000000000000000000000000000.0,10000000000.0,10000000000.0,10.0,10000000000000000.9999,10000000000000000000000000000000000000000000000000000000000000.9999);
93 Warnings:
94 Warning 1264 Out of range value for column 'd' at row 1
95 Warning 1264 Out of range value for column 'd0' at row 1
96 Warning 1264 Out of range value for column 'd1_1' at row 1
97 Warning 1264 Out of range value for column 'd10_2' at row 1
98 Warning 1264 Out of range value for column 'd60_10' at row 1
99 Warning 1264 Out of range value for column 'n' at row 1
100 Warning 1264 Out of range value for column 'n0_0' at row 1
101 Warning 1264 Out of range value for column 'n1' at row 1
102 Warning 1264 Out of range value for column 'n20_4' at row 1
103 Warning 1264 Out of range value for column 'n65_4' at row 1
104 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
105 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
106 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
107 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
108 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
109 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
110 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
111 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
112 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
113 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.1,9999999999.1,1.9,99999999.001,99999999999999999999999999999999999999999999999999.99999999991,9999999999.1,9999999999.1,9.1,9999999999999999.00001,9999999999999999999999999999999999999999999999999999999999999.11111);
114 Warnings:
115 Note 1265 Data truncated for column 'd' at row 1
116 Note 1265 Data truncated for column 'd0' at row 1
117 Warning 1264 Out of range value for column 'd1_1' at row 1
118 Note 1265 Data truncated for column 'd10_2' at row 1
119 Note 1265 Data truncated for column 'd60_10' at row 1
120 Note 1265 Data truncated for column 'n' at row 1
121 Note 1265 Data truncated for column 'n0_0' at row 1
122 Note 1265 Data truncated for column 'n1' at row 1
123 Note 1265 Data truncated for column 'n20_4' at row 1
124 Note 1265 Data truncated for column 'n65_4' at row 1
125 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
126 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
127 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
128 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
129 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
130 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
131 9999999999 9999999999 0.9 99999999.00 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.0000 9999999999999999999999999999999999999999999999999999999999999.1111
132 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
133 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
134 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
135 ALTER TABLE t1 ADD COLUMN n66 NUMERIC(66);
136 ERROR 42000: Too big precision 66 specified for 'n66'. Maximum is 65.
137 ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(66,6);
138 ERROR 42000: Too big precision 66 specified for 'n66_6'. Maximum is 65.
139 ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(66,66);
140 ERROR 42000: Too big scale 66 specified for 'n66_66'. Maximum is 30.
141 DROP TABLE t1;
142 CREATE TABLE t1 (a DECIMAL UNSIGNED <CUSTOM_COL_OPTIONS>,
143 b NUMERIC UNSIGNED <CUSTOM_COL_OPTIONS>
144 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
145 SHOW COLUMNS IN t1;
146 Field Type Null Key Default Extra
147 a decimal(10,0) unsigned # # # #
148 b decimal(10,0) unsigned # # # #
149 INSERT INTO t1 (a,b) VALUES (1.0,-1.0);
150 Warnings:
151 Warning 1264 Out of range value for column 'b' at row 1
152 INSERT INTO t1 (a,b) VALUES (-100,100);
153 Warnings:
154 Warning 1264 Out of range value for column 'a' at row 1
155 SELECT a,b FROM t1;
156 a b
157 0 100
158 1 0
159 DROP TABLE t1;
160 #
161 # Floating point columns (FLOAT, DOUBLE)
162 #
163 DROP TABLE IF EXISTS t1;
164 CREATE TABLE t1 (f FLOAT UNSIGNED <CUSTOM_COL_OPTIONS>,
165 f0 FLOAT(0) UNSIGNED <CUSTOM_COL_OPTIONS>,
166 r1_1 REAL(1,1) UNSIGNED <CUSTOM_COL_OPTIONS>,
167 f23_0 FLOAT(23) UNSIGNED <CUSTOM_COL_OPTIONS>,
168 f20_3 FLOAT(20,3) UNSIGNED <CUSTOM_COL_OPTIONS>,
169 d DOUBLE UNSIGNED <CUSTOM_COL_OPTIONS>,
170 d1_0 DOUBLE(1,0) UNSIGNED <CUSTOM_COL_OPTIONS>,
171 d10_10 DOUBLE PRECISION (10,10) UNSIGNED <CUSTOM_COL_OPTIONS>,
172 d53 DOUBLE(53,0) UNSIGNED <CUSTOM_COL_OPTIONS>,
173 d53_10 DOUBLE(53,10) UNSIGNED <CUSTOM_COL_OPTIONS>
174 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
175 SHOW COLUMNS IN t1;
176 Field Type Null Key Default Extra
177 f float unsigned # # #
178 f0 float unsigned # # #
179 r1_1 double(1,1) unsigned # # #
180 f23_0 float unsigned # # #
181 f20_3 float(20,3) unsigned # # #
182 d double unsigned # # #
183 d1_0 double(1,0) unsigned # # #
184 d10_10 double(10,10) unsigned # # #
185 d53 double(53,0) unsigned # # #
186 d53_10 double(53,10) unsigned # # #
187 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (12345.12345,12345.12345,0.9,123456789.123,56789.987,11111111.111,8.0,0.0123456789,1234566789123456789,99999999999999999.99999999);
188 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
189 f 12345.1
190 d 11111111.111
191 d10_10 0.0123456789
192 d1_0 8
193 d53 1234566789123456800
194 d53_10 100000000000000000.0000000000
195 f0 12345.1
196 f20_3 56789.988
197 f23_0 123457000
198 r1_1 0.9
199 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (0,0,0,0,0,0,0,0,0,0);
200 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
201 99999999999999999999999999999999999999,
202 99999999999999999999999999999999999999.9999999999999999,
203 0.9,
204 99999999999999999999999999999999999999.9,
205 99999999999999999.999,
206 999999999999999999999999999999999999999999999999999999999999999999999999999999999,
207 9,
208 0.9999999999,
209 1999999999999999999999999999999999999999999999999999999,
210 19999999999999999999999999999999999999999999.9999999999
211 );
212 Warnings:
213 Warning 1264 Out of range value for column 'd53' at row 1
214 Warning 1264 Out of range value for column 'd53_10' at row 1
215 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
216 f 12345.1
217 d 0
218 d 11111111.111
219 d 1e81
220 d10_10 0.0000000000
221 d10_10 0.0123456789
222 d10_10 0.9999999999
223 d1_0 0
224 d1_0 8
225 d1_0 9
226 d53 0
227 d53 100000000000000000000000000000000000000000000000000000
228 d53 1234566789123456800
229 d53_10 0.0000000000
230 d53_10 100000000000000000.0000000000
231 d53_10 10000000000000000000000000000000000000000000.0000000000
232 f 0
233 f 1e38
234 f0 0
235 f0 12345.1
236 f0 1e38
237 f20_3 0.000
238 f20_3 56789.988
239 f20_3 99999998430674940.000
240 f23_0 0
241 f23_0 123457000
242 f23_0 1e38
243 r1_1 0.0
244 r1_1 0.9
245 r1_1 0.9
246 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999);
247 Warnings:
248 Warning 1264 Out of range value for column 'f' at row 1
249 Warning 1264 Out of range value for column 'f0' at row 1
250 Warning 1264 Out of range value for column 'r1_1' at row 1
251 Warning 1264 Out of range value for column 'f23_0' at row 1
252 Warning 1264 Out of range value for column 'f20_3' at row 1
253 Warning 1264 Out of range value for column 'd' at row 1
254 Warning 1264 Out of range value for column 'd1_0' at row 1
255 Warning 1264 Out of range value for column 'd10_10' at row 1
256 Warning 1264 Out of range value for column 'd53' at row 1
257 Warning 1264 Out of range value for column 'd53_10' at row 1
258 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
259 f 12345.1
260 d 0
261 d 0
262 d 11111111.111
263 d 1e81
264 d10_10 0.0000000000
265 d10_10 0.0000000000
266 d10_10 0.0123456789
267 d10_10 0.9999999999
268 d1_0 0
269 d1_0 0
270 d1_0 8
271 d1_0 9
272 d53 0
273 d53 0
274 d53 100000000000000000000000000000000000000000000000000000
275 d53 1234566789123456800
276 d53_10 0.0000000000
277 d53_10 0.0000000000
278 d53_10 100000000000000000.0000000000
279 d53_10 10000000000000000000000000000000000000000000.0000000000
280 f 0
281 f 0
282 f 1e38
283 f0 0
284 f0 0
285 f0 12345.1
286 f0 1e38
287 f20_3 0.000
288 f20_3 0.000
289 f20_3 56789.988
290 f20_3 99999998430674940.000
291 f23_0 0
292 f23_0 0
293 f23_0 123457000
294 f23_0 1e38
295 r1_1 0.0
296 r1_1 0.0
297 r1_1 0.9
298 r1_1 0.9
299 SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1;
300 MAX(f) 9.999999680285692e37
301 MAX(d) 1e81
302 MAX(d10_10) 0.9999999999
303 MAX(d1_0) 9
304 MAX(d53) 100000000000000000000000000000000000000000000000000000
305 MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000
306 MAX(f0) 9.999999680285692e37
307 MAX(f20_3) 99999998430674940.000
308 MAX(f23_0) 9.999999680285692e37
309 MAX(r1_1) 0.9
310 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) SELECT d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10 FROM t1 ORDER BY d53_10 DESC LIMIT 1;
311 Warnings:
312 Warning 1264 Out of range value for column 'f' at row 1
313 Warning 1264 Out of range value for column 'f0' at row 1
314 Warning 1264 Out of range value for column 'r1_1' at row 1
315 Warning 1264 Out of range value for column 'f23_0' at row 1
316 Warning 1264 Out of range value for column 'f20_3' at row 1
317 Warning 1264 Out of range value for column 'd1_0' at row 1
318 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
319 f 12345.1
320 d 0
321 d 0
322 d 11111111.111
323 d 1e43
324 d 1e81
325 d10_10 0.0000000000
326 d10_10 0.0000000000
327 d10_10 0.0123456789
328 d10_10 0.9999999999
329 d10_10 10000000000000000000000000000000000000000000.0000000000
330 d1_0 0
331 d1_0 0
332 d1_0 8
333 d1_0 9
334 d1_0 9
335 d53 0
336 d53 0
337 d53 10000000000000000000000000000000000000000000
338 d53 100000000000000000000000000000000000000000000000000000
339 d53 1234566789123456800
340 d53_10 0.0000000000
341 d53_10 0.0000000000
342 d53_10 100000000000000000.0000000000
343 d53_10 10000000000000000000000000000000000000000000.0000000000
344 d53_10 10000000000000000000000000000000000000000000.0000000000
345 f 0
346 f 0
347 f 1e38
348 f 3.40282e38
349 f0 0
350 f0 0
351 f0 12345.1
352 f0 1e38
353 f0 3.40282e38
354 f20_3 0.000
355 f20_3 0.000
356 f20_3 56789.988
357 f20_3 99999998430674940.000
358 f20_3 99999998430674940.000
359 f23_0 0
360 f23_0 0
361 f23_0 123457000
362 f23_0 1e38
363 f23_0 3.40282e38
364 r1_1 0.0
365 r1_1 0.0
366 r1_1 0.9
367 r1_1 0.9
368 r1_1 0.9
369 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
370 999999999999999999999999999999999999999,
371 999999999999999999999999999999999999999.9999999999999999,
372 1.9,
373 999999999999999999999999999999999999999.9,
374 999999999999999999.999,
375 9999999999999999999999999999999999999999999999999999999999999999999999999999999999,
376 99,
377 1.9999999999,
378 1999999999999999999999999999999999999999999999999999999,
379 19999999999999999999999999999999999999999999.9999999999
380 );
381 Warnings:
382 Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated
383 Warning 1264 Out of range value for column 'f' at row 1
384 Warning 1264 Out of range value for column 'f0' at row 1
385 Warning 1264 Out of range value for column 'r1_1' at row 1
386 Warning 1264 Out of range value for column 'f23_0' at row 1
387 Warning 1264 Out of range value for column 'f20_3' at row 1
388 Warning 1264 Out of range value for column 'd1_0' at row 1
389 Warning 1264 Out of range value for column 'd10_10' at row 1
390 Warning 1264 Out of range value for column 'd53' at row 1
391 Warning 1264 Out of range value for column 'd53_10' at row 1
392 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
393 f 12345.1
394 d 0
395 d 0
396 d 11111111.111
397 d 1e43
398 d 1e65
399 d 1e81
400 d10_10 0.0000000000
401 d10_10 0.0000000000
402 d10_10 0.0123456789
403 d10_10 0.9999999999
404 d10_10 0.9999999999
405 d10_10 10000000000000000000000000000000000000000000.0000000000
406 d1_0 0
407 d1_0 0
408 d1_0 8
409 d1_0 9
410 d1_0 9
411 d1_0 9
412 d53 0
413 d53 0
414 d53 10000000000000000000000000000000000000000000
415 d53 100000000000000000000000000000000000000000000000000000
416 d53 100000000000000000000000000000000000000000000000000000
417 d53 1234566789123456800
418 d53_10 0.0000000000
419 d53_10 0.0000000000
420 d53_10 100000000000000000.0000000000
421 d53_10 10000000000000000000000000000000000000000000.0000000000
422 d53_10 10000000000000000000000000000000000000000000.0000000000
423 d53_10 10000000000000000000000000000000000000000000.0000000000
424 f 0
425 f 0
426 f 1e38
427 f 3.40282e38
428 f 3.40282e38
429 f0 0
430 f0 0
431 f0 12345.1
432 f0 1e38
433 f0 3.40282e38
434 f0 3.40282e38
435 f20_3 0.000
436 f20_3 0.000
437 f20_3 56789.988
438 f20_3 99999998430674940.000
439 f20_3 99999998430674940.000
440 f20_3 99999998430674940.000
441 f23_0 0
442 f23_0 0
443 f23_0 123457000
444 f23_0 1e38
445 f23_0 3.40282e38
446 f23_0 3.40282e38
447 r1_1 0.0
448 r1_1 0.0
449 r1_1 0.9
450 r1_1 0.9
451 r1_1 0.9
452 r1_1 0.9
453 ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0);
454 # ERROR: Statement succeeded (expected results: ER_TOO_BIG_DISPLAYWIDTH)
455 # ------------ UNEXPECTED RESULT ------------
456 # [ ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0) ]
457 # The statement|command succeeded unexpectedly.
458 # ALTER TABLE or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors.
459 # You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.
460 # Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.
461 # Also, this problem may cause a chain effect (more errors of different kinds in the test).
462 # -------------------------------------------
463 ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(256,1);
464 ERROR 42000: Too big precision 256 specified for 'n66_6'. Maximum is 65.
465 ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(40,35);
466 ERROR 42000: Too big scale 35 specified for 'n66_66'. Maximum is 30.
467 DROP TABLE t1;
468 CREATE TABLE t1 (a DOUBLE UNSIGNED <CUSTOM_COL_OPTIONS>,
469 b FLOAT UNSIGNED <CUSTOM_COL_OPTIONS>
470 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
471 SHOW COLUMNS IN t1;
472 Field Type Null Key Default Extra
473 a double unsigned # # # #
474 b float unsigned # # # #
475 INSERT INTO t1 (a,b) VALUES (1.0,-1.0);
476 Warnings:
477 Warning 1264 Out of range value for column 'b' at row 1
478 INSERT INTO t1 (a,b) VALUES (-100,100);
479 Warnings:
480 Warning 1264 Out of range value for column 'a' at row 1
481 SELECT a,b FROM t1;
482 a b
483 0 100
484 1 0
485 DROP TABLE t1;
486 #
487 # INT columns
488 #
489 DROP TABLE IF EXISTS t1;
490 CREATE TABLE t1 (i INT UNSIGNED <CUSTOM_COL_OPTIONS>,
491 i0 INT(0) UNSIGNED <CUSTOM_COL_OPTIONS>,
492 i1 INT(1) UNSIGNED <CUSTOM_COL_OPTIONS>,
493 i20 INT(20) UNSIGNED <CUSTOM_COL_OPTIONS>,
494 t TINYINT UNSIGNED <CUSTOM_COL_OPTIONS>,
495 t0 TINYINT(0) UNSIGNED <CUSTOM_COL_OPTIONS>,
496 t1 TINYINT(1) UNSIGNED <CUSTOM_COL_OPTIONS>,
497 t20 TINYINT(20) UNSIGNED <CUSTOM_COL_OPTIONS>,
498 s SMALLINT UNSIGNED <CUSTOM_COL_OPTIONS>,
499 s0 SMALLINT(0) UNSIGNED <CUSTOM_COL_OPTIONS>,
500 s1 SMALLINT(1) UNSIGNED <CUSTOM_COL_OPTIONS>,
501 s20 SMALLINT(20) UNSIGNED <CUSTOM_COL_OPTIONS>,
502 m MEDIUMINT UNSIGNED <CUSTOM_COL_OPTIONS>,
503 m0 MEDIUMINT(0) UNSIGNED <CUSTOM_COL_OPTIONS>,
504 m1 MEDIUMINT(1) UNSIGNED <CUSTOM_COL_OPTIONS>,
505 m20 MEDIUMINT(20) UNSIGNED <CUSTOM_COL_OPTIONS>,
506 b BIGINT UNSIGNED <CUSTOM_COL_OPTIONS>,
507 b0 BIGINT(0) UNSIGNED <CUSTOM_COL_OPTIONS>,
508 b1 BIGINT(1) UNSIGNED <CUSTOM_COL_OPTIONS>,
509 b20 BIGINT(20) UNSIGNED <CUSTOM_COL_OPTIONS>
510 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
511 SHOW COLUMNS IN t1;
512 Field Type Null Key Default Extra
513 i int(10) unsigned # # #
514 i0 int(10) unsigned # # #
515 i1 int(1) unsigned # # #
516 i20 int(20) unsigned # # #
517 t tinyint(3) unsigned # # #
518 t0 tinyint(3) unsigned # # #
519 t1 tinyint(1) unsigned # # #
520 t20 tinyint(20) unsigned # # #
521 s smallint(5) unsigned # # #
522 s0 smallint(5) unsigned # # #
523 s1 smallint(1) unsigned # # #
524 s20 smallint(20) unsigned # # #
525 m mediumint(8) unsigned # # #
526 m0 mediumint(8) unsigned # # #
527 m1 mediumint(1) unsigned # # #
528 m20 mediumint(20) unsigned # # #
529 b bigint(20) unsigned # # #
530 b0 bigint(20) unsigned # # #
531 b1 bigint(1) unsigned # # #
532 b20 bigint(20) unsigned # # #
533 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);
534 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
535 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (2147483647,2147483647,2147483647,2147483647,127,127,127,127,32767,32767,32767,32767,8388607,8388607,8388607,8388607,9223372036854775807,9223372036854775807,9223372036854775807,9223372036854775807);
536 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
537 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
538 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
539 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
540 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
541 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483648,-2147483648,-2147483648,-2147483648,-128,-128,-128,-128,-32768,-32768,-32768,-32768,-8388608,-8388608,-8388608,-8388608,-9223372036854775808,-9223372036854775808,-9223372036854775808,-9223372036854775808);
542 Warnings:
543 Warning 1264 Out of range value for column 'i' at row 1
544 Warning 1264 Out of range value for column 'i0' at row 1
545 Warning 1264 Out of range value for column 'i1' at row 1
546 Warning 1264 Out of range value for column 'i20' at row 1
547 Warning 1264 Out of range value for column 't' at row 1
548 Warning 1264 Out of range value for column 't0' at row 1
549 Warning 1264 Out of range value for column 't1' at row 1
550 Warning 1264 Out of range value for column 't20' at row 1
551 Warning 1264 Out of range value for column 's' at row 1
552 Warning 1264 Out of range value for column 's0' at row 1
553 Warning 1264 Out of range value for column 's1' at row 1
554 Warning 1264 Out of range value for column 's20' at row 1
555 Warning 1264 Out of range value for column 'm' at row 1
556 Warning 1264 Out of range value for column 'm0' at row 1
557 Warning 1264 Out of range value for column 'm1' at row 1
558 Warning 1264 Out of range value for column 'm20' at row 1
559 Warning 1264 Out of range value for column 'b' at row 1
560 Warning 1264 Out of range value for column 'b0' at row 1
561 Warning 1264 Out of range value for column 'b1' at row 1
562 Warning 1264 Out of range value for column 'b20' at row 1
563 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967295,4294967295,4294967295,4294967295,255,255,255,255,65535,65535,65535,65535,16777215,16777215,16777215,16777215,18446744073709551615,18446744073709551615,18446744073709551615,18446744073709551615);
564 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
565 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
566 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
567 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
568 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
569 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
570 4294967295 4294967295 4294967295 4294967295 255 255 255 255 65535 65535 65535 65535 16777215 16777215 16777215 16777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
571 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483649,-2147483649,-2147483649,-2147483649,-129,-129,-129,-129,-32769,-32769,-32769,-32769,-8388609,-8388609,-8388609,-8388609,-9223372036854775809,-9223372036854775809,-9223372036854775809,-9223372036854775809);
572 Warnings:
573 Warning 1264 Out of range value for column 'i' at row 1
574 Warning 1264 Out of range value for column 'i0' at row 1
575 Warning 1264 Out of range value for column 'i1' at row 1
576 Warning 1264 Out of range value for column 'i20' at row 1
577 Warning 1264 Out of range value for column 't' at row 1
578 Warning 1264 Out of range value for column 't0' at row 1
579 Warning 1264 Out of range value for column 't1' at row 1
580 Warning 1264 Out of range value for column 't20' at row 1
581 Warning 1264 Out of range value for column 's' at row 1
582 Warning 1264 Out of range value for column 's0' at row 1
583 Warning 1264 Out of range value for column 's1' at row 1
584 Warning 1264 Out of range value for column 's20' at row 1
585 Warning 1264 Out of range value for column 'm' at row 1
586 Warning 1264 Out of range value for column 'm0' at row 1
587 Warning 1264 Out of range value for column 'm1' at row 1
588 Warning 1264 Out of range value for column 'm20' at row 1
589 Warning 1264 Out of range value for column 'b' at row 1
590 Warning 1264 Out of range value for column 'b0' at row 1
591 Warning 1264 Out of range value for column 'b1' at row 1
592 Warning 1264 Out of range value for column 'b20' at row 1
593 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967296,4294967296,4294967296,4294967296,256,256,256,256,65536,65536,65536,65536,16777216,16777216,16777216,16777216,18446744073709551616,18446744073709551616,18446744073709551616,18446744073709551616);
594 Warnings:
595 Warning 1264 Out of range value for column 'i' at row 1
596 Warning 1264 Out of range value for column 'i0' at row 1
597 Warning 1264 Out of range value for column 'i1' at row 1
598 Warning 1264 Out of range value for column 'i20' at row 1
599 Warning 1264 Out of range value for column 't' at row 1
600 Warning 1264 Out of range value for column 't0' at row 1
601 Warning 1264 Out of range value for column 't1' at row 1
602 Warning 1264 Out of range value for column 't20' at row 1
603 Warning 1264 Out of range value for column 's' at row 1
604 Warning 1264 Out of range value for column 's0' at row 1
605 Warning 1264 Out of range value for column 's1' at row 1
606 Warning 1264 Out of range value for column 's20' at row 1
607 Warning 1264 Out of range value for column 'm' at row 1
608 Warning 1264 Out of range value for column 'm0' at row 1
609 Warning 1264 Out of range value for column 'm1' at row 1
610 Warning 1264 Out of range value for column 'm20' at row 1
611 Warning 1264 Out of range value for column 'b' at row 1
612 Warning 1264 Out of range value for column 'b0' at row 1
613 Warning 1264 Out of range value for column 'b1' at row 1
614 Warning 1264 Out of range value for column 'b20' at row 1
615 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) SELECT b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b FROM t1 WHERE b IN (-9223372036854775808,9223372036854775807,18446744073709551615);
616 Warnings:
617 Warning 1264 Out of range value for column 'i' at row 1
618 Warning 1264 Out of range value for column 'i0' at row 1
619 Warning 1264 Out of range value for column 'i1' at row 1
620 Warning 1264 Out of range value for column 'i20' at row 1
621 Warning 1264 Out of range value for column 't' at row 1
622 Warning 1264 Out of range value for column 't0' at row 1
623 Warning 1264 Out of range value for column 't1' at row 1
624 Warning 1264 Out of range value for column 't20' at row 1
625 Warning 1264 Out of range value for column 's' at row 1
626 Warning 1264 Out of range value for column 's0' at row 1
627 Warning 1264 Out of range value for column 's1' at row 1
628 Warning 1264 Out of range value for column 's20' at row 1
629 Warning 1264 Out of range value for column 'm' at row 1
630 Warning 1264 Out of range value for column 'm0' at row 1
631 Warning 1264 Out of range value for column 'm1' at row 1
632 Warning 1264 Out of range value for column 'm20' at row 1
633 Warning 1264 Out of range value for column 'i' at row 2
634 Warning 1264 Out of range value for column 'i0' at row 2
635 Warning 1264 Out of range value for column 'i1' at row 2
636 Warning 1264 Out of range value for column 'i20' at row 2
637 Warning 1264 Out of range value for column 't' at row 2
638 Warning 1264 Out of range value for column 't0' at row 2
639 Warning 1264 Out of range value for column 't1' at row 2
640 Warning 1264 Out of range value for column 't20' at row 2
641 Warning 1264 Out of range value for column 's' at row 2
642 Warning 1264 Out of range value for column 's0' at row 2
643 Warning 1264 Out of range value for column 's1' at row 2
644 Warning 1264 Out of range value for column 's20' at row 2
645 Warning 1264 Out of range value for column 'm' at row 2
646 Warning 1264 Out of range value for column 'm0' at row 2
647 Warning 1264 Out of range value for column 'm1' at row 2
648 Warning 1264 Out of range value for column 'm20' at row 2
649 Warning 1264 Out of range value for column 'i' at row 3
650 Warning 1264 Out of range value for column 'i0' at row 3
651 Warning 1264 Out of range value for column 'i1' at row 3
652 Warning 1264 Out of range value for column 'i20' at row 3
653 Warning 1264 Out of range value for column 't' at row 3
654 Warning 1264 Out of range value for column 't0' at row 3
655 Warning 1264 Out of range value for column 't1' at row 3
656 Warning 1264 Out of range value for column 't20' at row 3
657 Warning 1264 Out of range value for column 's' at row 3
658 Warning 1264 Out of range value for column 's0' at row 3
659 Warning 1264 Out of range value for column 's1' at row 3
660 Warning 1264 Out of range value for column 's20' at row 3
661 Warning 1264 Out of range value for column 'm' at row 3
662 Warning 1264 Out of range value for column 'm0' at row 3
663 Warning 1264 Out of range value for column 'm1' at row 3
664 Warning 1264 Out of range value for column 'm20' at row 3
665 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
666 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
667 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
668 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
669 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
670 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
671 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
672 4294967295 4294967295 4294967295 4294967295 255 255 255 255 65535 65535 65535 65535 16777215 16777215 16777215 16777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
673 4294967295 4294967295 4294967295 4294967295 255 255 255 255 65535 65535 65535 65535 16777215 16777215 16777215 16777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
674 4294967295 4294967295 4294967295 4294967295 255 255 255 255 65535 65535 65535 65535 16777215 16777215 16777215 16777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
675 4294967295 4294967295 4294967295 4294967295 255 255 255 255 65535 65535 65535 65535 16777215 16777215 16777215 16777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
676 4294967295 4294967295 4294967295 4294967295 255 255 255 255 65535 65535 65535 65535 16777215 16777215 16777215 16777215 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
677 ALTER TABLE t1 ADD COLUMN i257 INT(257);
678 ERROR 42000: Display width out of range for 'i257' (max = 255)
679 DROP TABLE t1;
680 CREATE TABLE t1 (t TINYINT UNSIGNED <CUSTOM_COL_OPTIONS>,
681 s SMALLINT UNSIGNED <CUSTOM_COL_OPTIONS>,
682 m MEDIUMINT UNSIGNED <CUSTOM_COL_OPTIONS>,
683 i INT UNSIGNED <CUSTOM_COL_OPTIONS>,
684 b BIGINT UNSIGNED <CUSTOM_COL_OPTIONS>
685 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
686 SHOW COLUMNS IN t1;
687 Field Type Null Key Default Extra
688 t tinyint(3) unsigned # # # #
689 s smallint(5) unsigned # # # #
690 m mediumint(8) unsigned # # # #
691 i int(10) unsigned # # # #
692 b bigint(20) unsigned # # # #
693 INSERT INTO t1 (t,s,m,i,b) VALUES (255,65535,16777215,4294967295,18446744073709551615);
694 INSERT INTO t1 (t,s,m,i,b) VALUES (-1,-1,-1,-1,-1);
695 Warnings:
696 Warning 1264 Out of range value for column 't' at row 1
697 Warning 1264 Out of range value for column 's' at row 1
698 Warning 1264 Out of range value for column 'm' at row 1
699 Warning 1264 Out of range value for column 'i' at row 1
700 Warning 1264 Out of range value for column 'b' at row 1
701 SELECT t,s,m,i,b FROM t1;
702 t s m i b
703 0 0 0 0 0
704 255 65535 16777215 4294967295 18446744073709551615
705 DROP TABLE t1;