bigint.result 15.5 KB
Newer Older
1
drop table if exists t1, t2;
unknown's avatar
unknown committed
2
select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296;
3
0	256	00000000000000065536	2147483647	-2147483648	2147483648	4294967296
4
0	256	65536	2147483647	-2147483648	2147483648	4294967296
unknown's avatar
unknown committed
5
select 9223372036854775807,-009223372036854775808;
6 7
9223372036854775807	-009223372036854775808
9223372036854775807	-9223372036854775808
unknown's avatar
unknown committed
8
select +9999999999999999999,-9999999999999999999;
9
9999999999999999999	-9999999999999999999
unknown's avatar
unknown committed
10
9999999999999999999	-9999999999999999999
11 12 13
select cast(9223372036854775808 as unsigned)+1;
cast(9223372036854775808 as unsigned)+1
9223372036854775809
14 15
select 9223372036854775808+1;
9223372036854775808+1
16
9223372036854775809
17 18
select -(0-3),round(-(0-3)), round(9999999999999999999);
-(0-3)	round(-(0-3))	round(9999999999999999999)
unknown's avatar
unknown committed
19
3	3	9999999999999999999
20 21 22 23 24
select 1,11,101,1001,10001,100001,1000001,10000001,100000001,1000000001,10000000001,100000000001,1000000000001,10000000000001,100000000000001,1000000000000001,10000000000000001,100000000000000001,1000000000000000001,10000000000000000001;
1	11	101	1001	10001	100001	1000001	10000001	100000001	1000000001	10000000001	100000000001	1000000000001	10000000000001	100000000000001	1000000000000001	10000000000000001	100000000000000001	1000000000000000001	10000000000000000001
1	11	101	1001	10001	100001	1000001	10000001	100000001	1000000001	10000000001	100000000001	1000000000001	10000000000001	100000000000001	1000000000000001	10000000000000001	100000000000000001	1000000000000000001	10000000000000000001
select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001;
-1	-11	-101	-1001	-10001	-100001	-1000001	-10000001	-100000001	-1000000001	-10000000001	-100000000001	-1000000000001	-10000000000001	-100000000000001	-1000000000000001	-10000000000000001	-100000000000000001	-1000000000000000001	-10000000000000000001
unknown's avatar
unknown committed
25
-1	-11	-101	-1001	-10001	-100001	-1000001	-10000001	-100000001	-1000000001	-10000000001	-100000000001	-1000000000001	-10000000000001	-100000000000001	-1000000000000001	-10000000000000001	-100000000000000001	-1000000000000000001	-10000000000000000001
26 27 28
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);
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)
1	3	3FE	FFFD	1FFFFFC	7FFFFFFB	FFFFFFFFA	7FFFFFFFFFF9	FFFFFFFFFFF8	7FFFFFFFFFFFF7	FFFFFFFFFFFFF6	7FFFFFFFFFFFFFF5
unknown's avatar
unknown committed
29
create table t1 (a bigint unsigned not null, primary key(a));
30
insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
unknown's avatar
unknown committed
31
select * from t1;
32
a
33 34
18446744073709551612
18446744073709551613
35
18446744073709551614
36
18446744073709551615
unknown's avatar
unknown committed
37
select * from t1 where a=18446744073709551615;
38 39
a
18446744073709551615
unknown's avatar
unknown committed
40 41
delete from t1 where a=18446744073709551615;
select * from t1;
42
a
43 44
18446744073709551612
18446744073709551613
45
18446744073709551614
unknown's avatar
unknown committed
46
drop table t1;
unknown's avatar
unknown committed
47 48
create table t1 ( a int not null default 1, big bigint );
insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);
49 50 51 52 53 54 55 56
Warnings:
Warning	1264	Out of range value adjusted for column 'big' at row 4
select * from t1;
a	big
1	-1
1	12345678901234567
1	9223372036854775807
1	9223372036854775807
unknown's avatar
unknown committed
57 58 59 60 61 62 63
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
alter table t1 modify big bigint unsigned not null;
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
Warnings:
Warning	1264	Out of range value adjusted for column 'big' at row 1
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
0	9223372036854775807	9223372036854775806
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
0	9223372036854775807	9223372036854775806
insert into t1 (big) values (18446744073709551615);
select * from t1;
a	big
1	0
1	12345678901234567
1	9223372036854775807
1	9223372036854775807
1	18446744073709551615
unknown's avatar
unknown committed
80 81
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
82
0	18446744073709551615	18446744073709551614
unknown's avatar
unknown committed
83 84
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
85
0	18446744073709551615	18446744073709551614
unknown's avatar
unknown committed
86 87 88
alter table t1 add key (big);
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
89
0	18446744073709551615	18446744073709551614
unknown's avatar
unknown committed
90 91
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
92
0	18446744073709551615	18446744073709551614
unknown's avatar
unknown committed
93
alter table t1 modify big bigint not null;
94 95 96 97 98 99 100 101 102
Warnings:
Warning	1264	Out of range value adjusted for column 'big' at row 5
select * from t1;
a	big
1	0
1	12345678901234567
1	9223372036854775807
1	9223372036854775807
1	9223372036854775807
unknown's avatar
unknown committed
103 104
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
105
0	9223372036854775807	9223372036854775806
unknown's avatar
unknown committed
106 107
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
108
0	9223372036854775807	9223372036854775806
unknown's avatar
unknown committed
109
drop table t1;
110 111 112 113 114 115 116 117 118
create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
insert into t1 values (null,1);
select * from t1;
id	a
9999999999	1
select * from t1 limit 9999999999;
id	a
9999999999	1
drop table t1;
119 120
CREATE TABLE t1 ( quantity decimal(60,0));
insert into t1 values (10000000000000000000);
121
insert into t1 values (10000000000000000000.0);
122 123 124
insert into t1 values ('10000000000000000000');
select * from t1;
quantity
125
10000000000000000000
126 127 128
10000000000000000000
10000000000000000000
drop table t1;
129 130 131
SELECT '0x8000000000000001'+0;
'0x8000000000000001'+0
0
132 133
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: '0x8000000000000001'
134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172
create table t1 (
value64  bigint unsigned  not null,
value32  integer          not null,
primary key(value64, value32)
);
create table t2 (
value64  bigint unsigned  not null,
value32  integer          not null,
primary key(value64, value32)
);
insert into t1 values(17156792991891826145, 1);
insert into t1 values( 9223372036854775807, 2);
insert into t2 values(17156792991891826145, 3);
insert into t2 values( 9223372036854775807, 4);
select * from t1;
value64	value32
9223372036854775807	2
17156792991891826145	1
select * from t2;
value64	value32
9223372036854775807	4
17156792991891826145	3
select * from t1, t2 where t1.value64=17156792991891826145 and
t2.value64=17156792991891826145;
value64	value32	value64	value32
17156792991891826145	1	17156792991891826145	3
select * from t1, t2 where t1.value64=17156792991891826145 and
t2.value64=t1.value64;
value64	value32	value64	value32
17156792991891826145	1	17156792991891826145	3
select * from t1, t2 where t1.value64= 9223372036854775807 and
t2.value64=9223372036854775807;
value64	value32	value64	value32
9223372036854775807	2	9223372036854775807	4
select * from t1, t2 where t1.value64= 9223372036854775807 and
t2.value64=t1.value64;
value64	value32	value64	value32
9223372036854775807	2	9223372036854775807	4
drop table t1, t2;
173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189
create table t1 select 1 as 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select 9223372036854775809 as 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(19) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
a
9223372036854775809
drop table t1;
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334
DROP DATABASE IF EXISTS `scott`;
Warnings:
Note	1008	Can't drop database 'scott'; database doesn't exist
create table t1 (a char(100), b varchar(100), c text, d blob);
insert into t1 values(
18446744073709551615,18446744073709551615,
18446744073709551615, 18446744073709551615
);
insert into t1 values (-1 | 0,-1 | 0,-1 | 0 ,-1 | 0);
select * from t1;
a	b	c	d
18446744073709551615	18446744073709551615	18446744073709551615	18446744073709551615
18446744073709551615	18446744073709551615	18446744073709551615	18446744073709551615
drop table t1;
create table t1 ( quantity decimal(2) unsigned);
insert into t1 values (500), (-500), (~0), (-1);
Warnings:
Warning	1264	Out of range value adjusted for column 'quantity' at row 1
Warning	1264	Out of range value adjusted for column 'quantity' at row 2
Warning	1264	Out of range value adjusted for column 'quantity' at row 3
Warning	1264	Out of range value adjusted for column 'quantity' at row 4
select * from t1;
quantity
99
0
99
0
drop table t1;
CREATE TABLE t1 (
`col1` INT(1) NULL,
`col2` INT(2) NULL,
`col3` INT(3) NULL,
`col4` INT(4) NULL,
`col5` INT(5) NULL,
`col6` INT(6) NULL,
`col7` INT(7) NULL,
`col8` INT(8) NULL,
`col9` INT(9) NULL,
`col10` BIGINT(10) NULL,
`col11` BIGINT(11) NULL,
`col12` BIGINT(12) NULL,
`col13` BIGINT(13) NULL,
`col14` BIGINT(14) NULL,
`col15` BIGINT(15) NULL,
`col16` BIGINT(16) NULL,
`col17` BIGINT(17) NULL,
`col18` BIGINT(18) NULL,
`col19` DECIMAL(19, 0) NULL,
`col20` DECIMAL(20, 0) NULL,
`col21` DECIMAL(21, 0) NULL,
`col22` DECIMAL(22, 0) NULL,
`col23` DECIMAL(23, 0) NULL,
`col24` DECIMAL(24, 0) NULL,
`col25` DECIMAL(25, 0) NULL,
`col26` DECIMAL(26, 0) NULL,
`col27` DECIMAL(27, 0) NULL,
`col28` DECIMAL(28, 0) NULL,
`col29` DECIMAL(29, 0) NULL,
`col30` DECIMAL(30, 0) NULL,
`col31` DECIMAL(31, 0) NULL,
`col32` DECIMAL(32, 0) NULL,
`col33` DECIMAL(33, 0) NULL,
`col34` DECIMAL(34, 0) NULL,
`col35` DECIMAL(35, 0) NULL,
`col36` DECIMAL(36, 0) NULL,
`col37` DECIMAL(37, 0) NULL,
`col38` DECIMAL(38, 0) NULL,
`fix1` DECIMAL(38, 1) NULL,
`fix2` DECIMAL(38, 2) NULL,
`fix3` DECIMAL(38, 3) NULL,
`fix4` DECIMAL(38, 4) NULL,
`fix5` DECIMAL(38, 5) NULL,
`fix6` DECIMAL(38, 6) NULL,
`fix7` DECIMAL(38, 7) NULL,
`fix8` DECIMAL(38, 8) NULL,
`fix9` DECIMAL(38, 9) NULL,
`fix10` DECIMAL(38, 10) NULL,
`fix11` DECIMAL(38, 11) NULL,
`fix12` DECIMAL(38, 12) NULL,
`fix13` DECIMAL(38, 13) NULL,
`fix14` DECIMAL(38, 14) NULL,
`fix15` DECIMAL(38, 15) NULL,
`fix16` DECIMAL(38, 16) NULL,
`fix17` DECIMAL(38, 17) NULL,
`fix18` DECIMAL(38, 18) NULL,
`fix19` DECIMAL(38, 19) NULL,
`fix20` DECIMAL(38, 20) NULL,
`fix21` DECIMAL(38, 21) NULL,
`fix22` DECIMAL(38, 22) NULL,
`fix23` DECIMAL(38, 23) NULL,
`fix24` DECIMAL(38, 24) NULL,
`fix25` DECIMAL(38, 25) NULL,
`fix26` DECIMAL(38, 26) NULL,
`fix27` DECIMAL(38, 27) NULL,
`fix28` DECIMAL(38, 28) NULL,
`fix29` DECIMAL(38, 29) NULL,
`fix30` DECIMAL(38, 30) NULL
);
INSERT INTO t1(`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`)
VALUES (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);
SELECT * FROM t1;
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
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
DROP TABLE t1;