← Back to branch summary

~ubuntu-branches/ubuntu/maverick/drizzle/maverick

« back to all changes in this revision

Viewing changes to tests/r/bigint.result

  • Committer: Bazaar Package Importer
  • Author(s): Monty Taylor
  • Date: 2010-03-18 12:12:31 UTC
  • Revision ID: james.westby@ubuntu.com-20100318121231-k6g1xe6cshbwa0f8
Tags: upstream-2010.03.1347
Import upstream version 2010.03.1347

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/bigint.result
 
1
drop table if exists t1, t2;
 
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
 
5
select 9223372036854775807,-009223372036854775808;
 
6
9223372036854775807     -009223372036854775808
 
7
9223372036854775807     -9223372036854775808
 
8
select +9999999999999999999,-9999999999999999999;
 
9
9999999999999999999     -9999999999999999999
 
10
9999999999999999999     -9999999999999999999
 
11
select 9223372036854775808 +1;
 
12
9223372036854775808 +1
 
13
9223372036854775809
 
14
select 9223372036854775808+1;
 
15
9223372036854775808+1
 
16
9223372036854775809
 
17
select -(0-3),round(-(0-3)), round(9999999999999999999);
 
18
-(0-3)  round(-(0-3))   round(9999999999999999999)
 
19
3       3       9999999999999999999
 
20
select 1,11,101,1001,10001,100001,1000001,10000001,100000001,1000000001,10000000001,100000000001,1000000000001,10000000000001,100000000000001,1000000000000001,10000000000000001,100000000000000001,1000000000000000001,10000000000000000001;
 
21
1       11      101     1001    10001   100001  1000001 10000001        100000001       1000000001      10000000001     100000000001    1000000000001   10000000000001  100000000000001 1000000000000001        10000000000000001       100000000000000001      1000000000000000001     10000000000000000001
 
22
1       11      101     1001    10001   100001  1000001 10000001        100000001       1000000001      10000000001     100000000001    1000000000001   10000000000001  100000000000001 1000000000000001        10000000000000001       100000000000000001      1000000000000000001     10000000000000000001
 
23
select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001;
 
24
-1      -11     -101    -1001   -10001  -100001 -1000001        -10000001       -100000001      -1000000001     -10000000001    -100000000001   -1000000000001  -10000000000001 -100000000000001        -1000000000000001       -10000000000000001      -100000000000000001     -1000000000000000001    -10000000000000000001
 
25
-1      -11     -101    -1001   -10001  -100001 -1000001        -10000001       -100000001      -1000000001     -10000000001    -100000000001   -1000000000001  -10000000000001 -100000000000001        -1000000000000001       -10000000000000001      -100000000000000001     -1000000000000000001    -10000000000000000001
 
26
create table t1 (a bigint not null, primary key(a));
 
27
insert into t1 values (9223372036854775808);
 
28
insert into t1 values (0x7000000000000000);
 
29
select * from t1;
 
30
a
 
31
-9223372036854775808
 
32
8070450532247928832
 
33
select * from t1 where a=18446744073709551615;
 
34
a
 
35
delete from t1 where a=18446744073709551615;
 
36
select * from t1;
 
37
a
 
38
-9223372036854775808
 
39
8070450532247928832
 
40
drop table t1;
 
41
create table t1 ( a int not null default 1, big bigint );
 
42
insert into t1 (big) values (-1),(9223372036854775808),(9223372036854775807),(9223372036854775808);
 
43
select * from t1;
 
44
a       big
 
45
1       -1
 
46
1       -9223372036854775808
 
47
1       9223372036854775807
 
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);
 
63
select * from t1;
 
64
a       big
 
65
1       -1
 
66
1       -9223372036854775808
 
67
1       9223372036854775807
 
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
 
76
alter table t1 add key (big);
 
77
select min(big),max(big),max(big)-1 from t1;
 
78
min(big)        max(big)        max(big)-1
 
79
-9223372036854775808    9223372036854775807     9223372036854775806
 
80
select min(big),max(big),max(big)-1 from t1 group by a;
 
81
min(big)        max(big)        max(big)-1
 
82
-9223372036854775808    9223372036854775807     9223372036854775806
 
83
alter table t1 modify big bigint not null;
 
84
select * from t1;
 
85
a       big
 
86
1       -1
 
87
1       -9223372036854775808
 
88
1       9223372036854775807
 
89
1       -9223372036854775808
 
90
1       -9223372036854775808
 
91
select min(big),max(big),max(big)-1 from t1;
 
92
min(big)        max(big)        max(big)-1
 
93
-9223372036854775808    9223372036854775807     9223372036854775806
 
94
select min(big),max(big),max(big)-1 from t1 group by a;
 
95
min(big)        max(big)        max(big)-1
 
96
-9223372036854775808    9223372036854775807     9223372036854775806
 
97
drop table t1;
 
98
create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
 
99
insert into t1 values (null,1);
 
100
select * from t1;
 
101
id      a
 
102
9999999999      1
 
103
select * from t1 limit 9999999999;
 
104
id      a
 
105
9999999999      1
 
106
drop table t1;
 
107
CREATE TABLE t1 ( quantity decimal(60,0));
 
108
insert into t1 values (10000000000000000000);
 
109
insert into t1 values (10000000000000000000.0);
 
110
insert into t1 values ('10000000000000000000');
 
111
select * from t1;
 
112
quantity
 
113
10000000000000000000
 
114
10000000000000000000
 
115
10000000000000000000
 
116
drop table t1;
 
117
SELECT '0x8000000000000001'+0;
 
118
'0x8000000000000001'+0
 
119
0
 
120
Warnings:
 
121
Warning 1292    Truncated incorrect DOUBLE value: '0x8000000000000001'
 
122
create table t1 (
 
123
value64  bigint  not null,
 
124
value32  integer          not null,
 
125
primary key(value64, value32)
 
126
);
 
127
create table t2 (
 
128
value64  bigint  not null,
 
129
value32  integer          not null,
 
130
primary key(value64, value32)
 
131
);
 
132
insert into t1 values(17156792991891826145, 1);
 
133
insert into t1 values( 9223372036854775807, 2);
 
134
insert into t2 values(17156792991891826145, 3);
 
135
insert into t2 values( 9223372036854775807, 4);
 
136
select * from t1;
 
137
value64 value32
 
138
-1289951081817725471    1
 
139
9223372036854775807     2
 
140
select * from t2;
 
141
value64 value32
 
142
-1289951081817725471    3
 
143
9223372036854775807     4
 
144
select * from t1, t2 where t1.value64=17156792991891826145 and
 
145
t2.value64=17156792991891826145;
 
146
value64 value32 value64 value32
 
147
-1289951081817725471    1       -1289951081817725471    3
 
148
select * from t1, t2 where t1.value64=17156792991891826145 and
 
149
t2.value64=t1.value64;
 
150
value64 value32 value64 value32
 
151
-1289951081817725471    1       -1289951081817725471    3
 
152
select * from t1, t2 where t1.value64= 9223372036854775807 and
 
153
t2.value64=9223372036854775807;
 
154
value64 value32 value64 value32
 
155
9223372036854775807     2       9223372036854775807     4
 
156
select * from t1, t2 where t1.value64= 9223372036854775807 and
 
157
t2.value64=t1.value64;
 
158
value64 value32 value64 value32
 
159
9223372036854775807     2       9223372036854775807     4
 
160
drop table t1, t2;
 
161
create table t1 (sint64 bigint not null);
 
162
insert into t1 values (-9223372036854775808);
 
163
select * from t1;
 
164
sint64
 
165
-9223372036854775808
 
166
drop table t1;
 
167
create table t1 select 1 as 'a';
 
168
show create table t1;
 
169
Table   Create Table
 
170
t1      CREATE TABLE `t1` (
 
171
  `a` int NOT NULL
 
172
) ENGINE=InnoDB
 
173
drop table t1;
 
174
create table t1 select 9223372036854775809 as 'a';
 
175
show create table t1;
 
176
Table   Create Table
 
177
t1      CREATE TABLE `t1` (
 
178
  `a` bigint NOT NULL
 
179
) ENGINE=InnoDB
 
180
select * from t1;
 
181
a
 
182
-9223372036854775807
 
183
drop table t1;
 
184
DROP DATABASE IF EXISTS `scott`;
 
185
Warnings:
 
186
Note    1008    Can't drop database './scott/'; database doesn't exist
 
187
create table t1 (a char(100), b varchar(100), c text, d blob);
 
188
insert into t1 values(
 
189
18446744073709551615,18446744073709551615,
 
190
18446744073709551615, 18446744073709551615
 
191
);
 
192
insert into t1 values (-1,-1,-1,-1);
 
193
select * from t1;
 
194
a       b       c       d
 
195
18446744073709551615    18446744073709551615    18446744073709551615    18446744073709551615
 
196
-1      -1      -1      -1
 
197
drop table t1;
 
198
create table t1 ( quantity decimal(2));
 
199
insert into t1 values (50), (-50), (0), (-1);
 
200
select * from t1;
 
201
quantity
 
202
50
 
203
-50
 
204
0
 
205
-1
 
206
drop table t1;
 
207
CREATE TABLE t1 (
 
208
`col1` INT NULL,
 
209
`col2` INT NULL,
 
210
`col3` INT NULL,
 
211
`col4` INT NULL,
 
212
`col5` INT NULL,
 
213
`col6` INT NULL,
 
214
`col7` INT NULL,
 
215
`col8` INT NULL,
 
216
`col9` INT NULL,
 
217
`col10` BIGINT NULL,
 
218
`col11` BIGINT NULL,
 
219
`col12` BIGINT NULL,
 
220
`col13` BIGINT NULL,
 
221
`col14` BIGINT NULL,
 
222
`col15` BIGINT NULL,
 
223
`col16` BIGINT NULL,
 
224
`col17` BIGINT NULL,
 
225
`col18` BIGINT NULL,
 
226
`col19` DECIMAL(19, 0) NULL,
 
227
`col20` DECIMAL(20, 0) NULL,
 
228
`col21` DECIMAL(21, 0) NULL,
 
229
`col22` DECIMAL(22, 0) NULL,
 
230
`col23` DECIMAL(23, 0) NULL,
 
231
`col24` DECIMAL(24, 0) NULL,
 
232
`col25` DECIMAL(25, 0) NULL,
 
233
`col26` DECIMAL(26, 0) NULL,
 
234
`col27` DECIMAL(27, 0) NULL,
 
235
`col28` DECIMAL(28, 0) NULL,
 
236
`col29` DECIMAL(29, 0) NULL,
 
237
`col30` DECIMAL(30, 0) NULL,
 
238
`col31` DECIMAL(31, 0) NULL,
 
239
`col32` DECIMAL(32, 0) NULL,
 
240
`col33` DECIMAL(33, 0) NULL,
 
241
`col34` DECIMAL(34, 0) NULL,
 
242
`col35` DECIMAL(35, 0) NULL,
 
243
`col36` DECIMAL(36, 0) NULL,
 
244
`col37` DECIMAL(37, 0) NULL,
 
245
`col38` DECIMAL(38, 0) NULL,
 
246
`fix1` DECIMAL(38, 1) NULL,
 
247
`fix2` DECIMAL(38, 2) NULL,
 
248
`fix3` DECIMAL(38, 3) NULL,
 
249
`fix4` DECIMAL(38, 4) NULL,
 
250
`fix5` DECIMAL(38, 5) NULL,
 
251
`fix6` DECIMAL(38, 6) NULL,
 
252
`fix7` DECIMAL(38, 7) NULL,
 
253
`fix8` DECIMAL(38, 8) NULL,
 
254
`fix9` DECIMAL(38, 9) NULL,
 
255
`fix10` DECIMAL(38, 10) NULL,
 
256
`fix11` DECIMAL(38, 11) NULL,
 
257
`fix12` DECIMAL(38, 12) NULL,
 
258
`fix13` DECIMAL(38, 13) NULL,
 
259
`fix14` DECIMAL(38, 14) NULL,
 
260
`fix15` DECIMAL(38, 15) NULL,
 
261
`fix16` DECIMAL(38, 16) NULL,
 
262
`fix17` DECIMAL(38, 17) NULL,
 
263
`fix18` DECIMAL(38, 18) NULL,
 
264
`fix19` DECIMAL(38, 19) NULL,
 
265
`fix20` DECIMAL(38, 20) NULL,
 
266
`fix21` DECIMAL(38, 21) NULL,
 
267
`fix22` DECIMAL(38, 22) NULL,
 
268
`fix23` DECIMAL(38, 23) NULL,
 
269
`fix24` DECIMAL(38, 24) NULL,
 
270
`fix25` DECIMAL(38, 25) NULL,
 
271
`fix26` DECIMAL(38, 26) NULL,
 
272
`fix27` DECIMAL(38, 27) NULL,
 
273
`fix28` DECIMAL(38, 28) NULL,
 
274
`fix29` DECIMAL(38, 29) NULL,
 
275
`fix30` DECIMAL(38, 30) NULL
 
276
);
 
277
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`)
 
278
VALUES (9, 99, 999, 9999, 99999, 999999, 9999999, 99999999, 999999999,
 
279
9999999999, 99999999999, 999999999999, 9999999999999, 99999999999999,
 
280
999999999999999, 9999999999999999, 99999999999999999, 999999999999999999,
 
281
9999999999999999999, 99999999999999999999, 999999999999999999999,
 
282
9999999999999999999999, 99999999999999999999999, 999999999999999999999999,
 
283
9999999999999999999999999, 99999999999999999999999999,
 
284
999999999999999999999999999, 9999999999999999999999999999,
 
285
99999999999999999999999999999, 999999999999999999999999999999,
 
286
9999999999999999999999999999999, 99999999999999999999999999999999,
 
287
999999999999999999999999999999999, 9999999999999999999999999999999999,
 
288
99999999999999999999999999999999999, 999999999999999999999999999999999999,
 
289
9999999999999999999999999999999999999, 99999999999999999999999999999999999999,
 
290
9999999999999999999999999999999999999.9,
 
291
999999999999999999999999999999999999.99,
 
292
99999999999999999999999999999999999.999,
 
293
9999999999999999999999999999999999.9999,
 
294
999999999999999999999999999999999.99999,
 
295
99999999999999999999999999999999.999999,
 
296
9999999999999999999999999999999.9999999,
 
297
999999999999999999999999999999.99999999,
 
298
99999999999999999999999999999.999999999,
 
299
9999999999999999999999999999.9999999999,
 
300
999999999999999999999999999.99999999999,
 
301
99999999999999999999999999.999999999999,
 
302
9999999999999999999999999.9999999999999,
 
303
999999999999999999999999.99999999999999,
 
304
99999999999999999999999.999999999999999,
 
305
9999999999999999999999.9999999999999999,
 
306
999999999999999999999.99999999999999999,
 
307
99999999999999999999.999999999999999999,
 
308
9999999999999999999.9999999999999999999,
 
309
999999999999999999.99999999999999999999,
 
310
99999999999999999.999999999999999999999,
 
311
9999999999999999.9999999999999999999999,
 
312
999999999999999.99999999999999999999999,
 
313
99999999999999.999999999999999999999999,
 
314
9999999999999.9999999999999999999999999,
 
315
999999999999.99999999999999999999999999,
 
316
99999999999.999999999999999999999999999,
 
317
9999999999.9999999999999999999999999999,
 
318
999999999.99999999999999999999999999999,
 
319
99999999.999999999999999999999999999999);
 
320
SELECT * FROM t1;
 
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
 
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
 
323
DROP TABLE t1;
 
324
create table t1 (bigint_col bigint);
 
325
insert into t1 values (17666000000000000000);
 
326
select * from t1 where bigint_col=17666000000000000000;
 
327
bigint_col
 
328
-780744073709551616
 
329
select * from t1 where bigint_col='17666000000000000000';
 
330
bigint_col
 
331
drop table t1;
 
332
 
 
333
bug 19955 -- mod is signed with bigint
 
334
select 10000002383263201056 mod 50 as result;
 
335
result
 
336
6
 
337
create table t1 (c1 bigint);
 
338
insert into t1 values (10000002383263201056);
 
339
select c1 mod 50 as result from t1;
 
340
result
 
341
-10
 
342
drop table t1;
 
343
select -9223372036854775808;
 
344
Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
 
345
def                                     -9223372036854775808    5       20      20      N       32897   0       63
 
346
-9223372036854775808
 
347
-9223372036854775808
 
348
select -(9223372036854775808);
 
349
Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
 
350
def                                     -(9223372036854775808)  5       20      20      N       32897   0       63
 
351
-(9223372036854775808)
 
352
-9223372036854775808
 
353
select -((9223372036854775808));
 
354
Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
 
355
def                                     -((9223372036854775808))        5       20      20      N       32897   0       63
 
356
-((9223372036854775808))
 
357
-9223372036854775808
 
358
select -(-(9223372036854775808));
 
359
Catalog Database        Table   Table_alias     Column  Column_alias    Type    Length  Max length      Is_null Flags   Decimals        Charsetnr
 
360
def                                     -(-(9223372036854775808))       9       21      19      N       129     0       63
 
361
-(-(9223372036854775808))
 
362
9223372036854775808
 
363
select --9223372036854775808, ---9223372036854775808, ----9223372036854775808;
 
364
--9223372036854775808   ---9223372036854775808  ----9223372036854775808
 
365
9223372036854775808     -9223372036854775808    9223372036854775808
 
366
select -(-9223372036854775808), -(-(-9223372036854775808));
 
367
-(-9223372036854775808) -(-(-9223372036854775808))
 
368
9223372036854775808     -9223372036854775808
 
369
create table t1 select -9223372036854775808 bi;
 
370
describe t1;
 
371
Field   Type    Null    Default Default_is_NULL On_Update
 
372
bi      BIGINT  FALSE           FALSE   
 
373
drop table t1;
 
374
create table t1 select -9223372036854775809 bi;
 
375
describe t1;
 
376
Field   Type    Null    Default Default_is_NULL On_Update
 
377
bi      DECIMAL FALSE           FALSE   
 
378
drop table t1;