23
23
select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001;
24
24
-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001
25
25
-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001
26
select conv(1,10,16),conv((1<<2)-1,10,16),conv((1<<10)-2,10,16),conv((1<<16)-3,10,16),conv((1<<25)-4,10,16),conv((1<<31)-5,10,16),conv((1<<36)-6,10,16),conv((1<<47)-7,10,16),conv((1<<48)-8,10,16),conv((1<<55)-9,10,16),conv((1<<56)-10,10,16),conv((1<<63)-11,10,16);
27
conv(1,10,16) conv((1<<2)-1,10,16) conv((1<<10)-2,10,16) conv((1<<16)-3,10,16) conv((1<<25)-4,10,16) conv((1<<31)-5,10,16) conv((1<<36)-6,10,16) conv((1<<47)-7,10,16) conv((1<<48)-8,10,16) conv((1<<55)-9,10,16) conv((1<<56)-10,10,16) conv((1<<63)-11,10,16)
28
1 3 3FE FFFD 1FFFFFC 7FFFFFFB FFFFFFFFA 7FFFFFFFFFF9 FFFFFFFFFFF8 7FFFFFFFFFFFF7 FFFFFFFFFFFFF6 7FFFFFFFFFFFFFF5
29
create table t1 (a bigint unsigned not null, primary key(a));
30
insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
26
create table t1 (a bigint not null, primary key(a));
27
insert into t1 values (9223372036854775808);
28
insert into t1 values (0x7000000000000000);
37
33
select * from t1 where a=18446744073709551615;
40
35
delete from t1 where a=18446744073709551615;
47
41
create table t1 ( a int not null default 1, big bigint );
48
insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);
50
Warning 1264 Out of range value for column 'big' at row 4
57
select min(big),max(big),max(big)-1 from t1;
58
min(big) max(big) max(big)-1
59
-1 9223372036854775807 9223372036854775806
60
select min(big),max(big),max(big)-1 from t1 group by a;
61
min(big) max(big) max(big)-1
62
-1 9223372036854775807 9223372036854775806
63
alter table t1 modify big bigint unsigned not null;
65
Warning 1264 Out of range value for column 'big' at row 1
66
select min(big),max(big),max(big)-1 from t1;
67
min(big) max(big) max(big)-1
68
0 9223372036854775807 9223372036854775806
69
select min(big),max(big),max(big)-1 from t1 group by a;
70
min(big) max(big) max(big)-1
71
0 9223372036854775807 9223372036854775806
72
insert into t1 (big) values (18446744073709551615);
79
1 18446744073709551615
80
select min(big),max(big),max(big)-1 from t1;
81
min(big) max(big) max(big)-1
82
0 18446744073709551615 18446744073709551614
83
select min(big),max(big),max(big)-1 from t1 group by a;
84
min(big) max(big) max(big)-1
85
0 18446744073709551615 18446744073709551614
42
insert into t1 (big) values (-1),(9223372036854775808),(9223372036854775807),(9223372036854775808);
46
1 -9223372036854775808
48
1 -9223372036854775808
49
select min(big),max(big),max(big)-1 from t1;
50
min(big) max(big) max(big)-1
51
-9223372036854775808 9223372036854775807 9223372036854775806
52
select min(big),max(big),max(big)-1 from t1 group by a;
53
min(big) max(big) max(big)-1
54
-9223372036854775808 9223372036854775807 9223372036854775806
55
alter table t1 modify big bigint not null;
56
select min(big),max(big),max(big)-1 from t1;
57
min(big) max(big) max(big)-1
58
-9223372036854775808 9223372036854775807 9223372036854775806
59
select min(big),max(big),max(big)-1 from t1 group by a;
60
min(big) max(big) max(big)-1
61
-9223372036854775808 9223372036854775807 9223372036854775806
62
insert into t1 (big) values (9223372036854775808);
66
1 -9223372036854775808
68
1 -9223372036854775808
69
1 -9223372036854775808
70
select min(big),max(big),max(big)-1 from t1;
71
min(big) max(big) max(big)-1
72
-9223372036854775808 9223372036854775807 9223372036854775806
73
select min(big),max(big),max(big)-1 from t1 group by a;
74
min(big) max(big) max(big)-1
75
-9223372036854775808 9223372036854775807 9223372036854775806
86
76
alter table t1 add key (big);
87
77
select min(big),max(big),max(big)-1 from t1;
88
78
min(big) max(big) max(big)-1
89
0 18446744073709551615 18446744073709551614
79
-9223372036854775808 9223372036854775807 9223372036854775806
90
80
select min(big),max(big),max(big)-1 from t1 group by a;
91
81
min(big) max(big) max(big)-1
92
0 18446744073709551615 18446744073709551614
82
-9223372036854775808 9223372036854775807 9223372036854775806
93
83
alter table t1 modify big bigint not null;
95
Warning 1264 Out of range value for column 'big' at row 5
100
1 9223372036854775807
101
1 9223372036854775807
102
1 9223372036854775807
87
1 -9223372036854775808
89
1 -9223372036854775808
90
1 -9223372036854775808
103
91
select min(big),max(big),max(big)-1 from t1;
104
92
min(big) max(big) max(big)-1
105
0 9223372036854775807 9223372036854775806
93
-9223372036854775808 9223372036854775807 9223372036854775806
106
94
select min(big),max(big),max(big)-1 from t1 group by a;
107
95
min(big) max(big) max(big)-1
108
0 9223372036854775807 9223372036854775806
96
-9223372036854775808 9223372036854775807 9223372036854775806
110
98
create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
111
99
insert into t1 values (null,1);
201
189
18446744073709551615,18446744073709551615,
202
190
18446744073709551615, 18446744073709551615
204
insert into t1 values (-1 | 0,-1 | 0,-1 | 0 ,-1 | 0);
192
insert into t1 values (-1,-1,-1,-1);
205
193
select * from t1;
207
195
18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
208
18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
210
create table t1 ( quantity decimal(2) unsigned);
211
insert into t1 values (500), (-500), (~0), (-1);
213
Warning 1264 Out of range value for column 'quantity' at row 1
214
Warning 1264 Out of range value for column 'quantity' at row 2
215
Warning 1264 Out of range value for column 'quantity' at row 3
216
Warning 1264 Out of range value for column 'quantity' at row 4
198
create table t1 ( quantity decimal(2));
199
insert into t1 values (50), (-50), (0), (-1);
217
200
select * from t1;
224
207
CREATE TABLE t1 (
234
`col10` BIGINT(10) NULL,
235
`col11` BIGINT(11) NULL,
236
`col12` BIGINT(12) NULL,
237
`col13` BIGINT(13) NULL,
238
`col14` BIGINT(14) NULL,
239
`col15` BIGINT(15) NULL,
240
`col16` BIGINT(16) NULL,
241
`col17` BIGINT(17) NULL,
242
`col18` BIGINT(18) NULL,
243
226
`col19` DECIMAL(19, 0) NULL,
244
227
`col20` DECIMAL(20, 0) NULL,
245
228
`col21` DECIMAL(21, 0) NULL,
338
321
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23 col24 col25 col26 col27 col28 col29 col30 col31 col32 col33 col34 col35 col36 col37 col38 fix1 fix2 fix3 fix4 fix5 fix6 fix7 fix8 fix9 fix10 fix11 fix12 fix13 fix14 fix15 fix16 fix17 fix18 fix19 fix20 fix21 fix22 fix23 fix24 fix25 fix26 fix27 fix28 fix29 fix30
339
322
9 99 999 9999 99999 999999 9999999 99999999 999999999 9999999999 99999999999 999999999999 9999999999999 99999999999999 999999999999999 9999999999999999 99999999999999999 999999999999999999 9999999999999999999 99999999999999999999 999999999999999999999 9999999999999999999999 99999999999999999999999 999999999999999999999999 9999999999999999999999999 99999999999999999999999999 999999999999999999999999999 9999999999999999999999999999 99999999999999999999999999999 999999999999999999999999999999 9999999999999999999999999999999 99999999999999999999999999999999 999999999999999999999999999999999 9999999999999999999999999999999999 99999999999999999999999999999999999 999999999999999999999999999999999999 9999999999999999999999999999999999999 99999999999999999999999999999999999999 9999999999999999999999999999999999999.9 999999999999999999999999999999999999.99 99999999999999999999999999999999999.999 9999999999999999999999999999999999.9999 999999999999999999999999999999999.99999 99999999999999999999999999999999.999999 9999999999999999999999999999999.9999999 999999999999999999999999999999.99999999 99999999999999999999999999999.999999999 9999999999999999999999999999.9999999999 999999999999999999999999999.99999999999 99999999999999999999999999.999999999999 9999999999999999999999999.9999999999999 999999999999999999999999.99999999999999 99999999999999999999999.999999999999999 9999999999999999999999.9999999999999999 999999999999999999999.99999999999999999 99999999999999999999.999999999999999999 9999999999999999999.9999999999999999999 999999999999999999.99999999999999999999 99999999999999999.999999999999999999999 9999999999999999.9999999999999999999999 999999999999999.99999999999999999999999 99999999999999.999999999999999999999999 9999999999999.9999999999999999999999999 999999999999.99999999999999999999999999 99999999999.999999999999999999999999999 9999999999.9999999999999999999999999999 999999999.99999999999999999999999999999 99999999.999999999999999999999999999999
341
create table t1 (bigint_col bigint unsigned);
324
create table t1 (bigint_col bigint);
342
325
insert into t1 values (17666000000000000000);
343
326
select * from t1 where bigint_col=17666000000000000000;
346
329
select * from t1 where bigint_col='17666000000000000000';
351
333
bug 19955 -- mod is signed with bigint
352
select cast(10000002383263201056 as unsigned) mod 50 as result;
334
select 10000002383263201056 mod 50 as result;
355
create table t1 (c1 bigint unsigned);
337
create table t1 (c1 bigint);
356
338
insert into t1 values (10000002383263201056);
357
339
select c1 mod 50 as result from t1;
361
select cast(19999999999999999999 as signed);
362
cast(19999999999999999999 as signed)
365
Error 1292 Truncated incorrect DECIMAL value: ''
366
select cast(-19999999999999999999 as signed);
367
cast(-19999999999999999999 as signed)
370
Error 1292 Truncated incorrect DECIMAL value: ''
371
343
select -9223372036854775808;
372
344
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
373
def -9223372036854775808 8 20 20 N 32897 0 63
345
def -9223372036854775808 6 20 20 N 32897 0 63
374
346
-9223372036854775808
375
347
-9223372036854775808
376
348
select -(9223372036854775808);
377
349
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
378
def -(9223372036854775808) 8 20 20 N 32897 0 63
350
def -(9223372036854775808) 6 20 20 N 32897 0 63
379
351
-(9223372036854775808)
380
352
-9223372036854775808
381
353
select -((9223372036854775808));
382
354
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
383
def -((9223372036854775808)) 8 20 20 N 32897 0 63
355
def -((9223372036854775808)) 6 20 20 N 32897 0 63
384
356
-((9223372036854775808))
385
357
-9223372036854775808
386
358
select -(-(9223372036854775808));
387
359
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
388
def -(-(9223372036854775808)) 246 21 19 N 129 0 63
360
def -(-(9223372036854775808)) 253 21 19 N 129 0 63
389
361
-(-(9223372036854775808))
390
362
9223372036854775808
391
363
select --9223372036854775808, ---9223372036854775808, ----9223372036854775808;