~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
#
2
# Initialize
3
4
--disable_warnings
5
drop table if exists t1, t2;
6
--enable_warnings
7
8
#
9
# Test of reading of bigint values
10
#
11
select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296;
12
select 9223372036854775807,-009223372036854775808;
13
select +9999999999999999999,-9999999999999999999;
14
select cast(9223372036854775808 as unsigned)+1;
15
select 9223372036854775808+1;
16
select -(0-3),round(-(0-3)), round(9999999999999999999);
17
select 1,11,101,1001,10001,100001,1000001,10000001,100000001,1000000001,10000000001,100000000001,1000000000001,10000000000001,100000000000001,1000000000000001,10000000000000001,100000000000000001,1000000000000000001,10000000000000000001;
18
select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001;
19
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);
20
21
#
22
# In 3.23 we have to disable the test of column to bigint as
23
# this fails on AIX powerpc (the resolution for double is not good enough)
24
# This will work on 4.0 as we then have internal handling of bigint variables.
25
#
26
27
create table t1 (a bigint unsigned not null, primary key(a));
28
insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
29
select * from t1;
30
select * from t1 where a=18446744073709551615;
31
# select * from t1 where a='18446744073709551615';
32
delete from t1 where a=18446744073709551615;
33
select * from t1;
34
drop table t1;
35
36
create table t1 ( a int not null default 1, big bigint );
37
insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);
38
select * from t1;
39
select min(big),max(big),max(big)-1 from t1;
40
select min(big),max(big),max(big)-1 from t1 group by a;
41
alter table t1 modify big bigint unsigned not null;
42
select min(big),max(big),max(big)-1 from t1;
43
select min(big),max(big),max(big)-1 from t1 group by a;
44
insert into t1 (big) values (18446744073709551615);
45
select * from t1;
46
select min(big),max(big),max(big)-1 from t1;
47
select min(big),max(big),max(big)-1 from t1 group by a;
48
alter table t1 add key (big);
49
select min(big),max(big),max(big)-1 from t1;
50
select min(big),max(big),max(big)-1 from t1 group by a;
51
alter table t1 modify big bigint not null;
52
select * from t1;
53
select min(big),max(big),max(big)-1 from t1;
54
select min(big),max(big),max(big)-1 from t1 group by a;
55
drop table t1;
56
57
#
58
# Test problem with big values for auto_increment
59
#
60
61
create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
62
insert into t1 values (null,1);
63
select * from t1;
64
select * from t1 limit 9999999999;
65
drop table t1;
66
67
#
68
# Item_uint::save_to_field()
69
# BUG#1845
70
# This can't be fixed in MySQL 4.0 without loosing precisions for bigints
71
#
72
73
CREATE TABLE t1 ( quantity decimal(60,0));
74
insert into t1 values (10000000000000000000);
75
insert into t1 values (10000000000000000000.0);
76
insert into t1 values ('10000000000000000000');
77
select * from t1;
78
drop table t1;
79
80
# atof() behaviour is different of different systems. to be fixed in 4.1
81
SELECT '0x8000000000000001'+0;
82
83
# Test for BUG#8562: joins over BIGINT UNSIGNED value + constant propagation
84
create table t1 (
85
 value64  bigint unsigned  not null,
86
 value32  integer          not null,
87
 primary key(value64, value32)
88
);
89
90
create table t2 (
91
 value64  bigint unsigned  not null,
92
 value32  integer          not null,
93
 primary key(value64, value32)
94
);
95
96
insert into t1 values(17156792991891826145, 1);
97
insert into t1 values( 9223372036854775807, 2);
98
insert into t2 values(17156792991891826145, 3);
99
insert into t2 values( 9223372036854775807, 4);
100
101
select * from t1;
102
select * from t2;
103
104
select * from t1, t2 where t1.value64=17156792991891826145 and
105
t2.value64=17156792991891826145;
106
select * from t1, t2 where t1.value64=17156792991891826145 and
107
t2.value64=t1.value64;
108
109
select * from t1, t2 where t1.value64= 9223372036854775807 and
110
t2.value64=9223372036854775807;
111
select * from t1, t2 where t1.value64= 9223372036854775807 and
112
t2.value64=t1.value64;
113
114
drop table t1, t2;
115
116
# Test for BUG#30069, can't handle bigint -9223372036854775808 on
117
# x86_64, with some GCC versions and optimizations.
118
119
create table t1 (sint64 bigint not null);
120
insert into t1 values (-9223372036854775808);
121
select * from t1;
122
123
drop table t1;
124
125
# End of 4.1 tests
126
127
#
128
# Test of CREATE ... SELECT and unsigned integers
129
#
130
131
create table t1 select 1 as 'a';
132
show create table t1;
133
drop table t1;
134
create table t1 select 9223372036854775809 as 'a';
135
show create table t1;
136
select * from t1;
137
drop table t1;
138
DROP DATABASE IF EXISTS `scott`;
139
140
141
#
142
# Check various conversions from/to unsigned bigint.
143
#
144
145
create table t1 (a char(100), b varchar(100), c text, d blob);
146
insert into t1 values(
147
  18446744073709551615,18446744073709551615,
148
  18446744073709551615, 18446744073709551615
149
);
150
151
insert into t1 values (-1 | 0,-1 | 0,-1 | 0 ,-1 | 0);
152
select * from t1;
153
drop table t1;
154
155
create table t1 ( quantity decimal(2) unsigned);
156
insert into t1 values (500), (-500), (~0), (-1);
157
select * from t1;
158
drop table t1;
159
160
#
161
# Test of storing decimal values in BIGINT range
162
# (Bug #12750: Incorrect storage of 9999999999999999999 in DECIMAL(19, 0))
163
#
164
165
CREATE TABLE t1 (
166
  `col1` INT(1) NULL,
167
  `col2` INT(2) NULL,
168
  `col3` INT(3) NULL,
169
  `col4` INT(4) NULL,
170
  `col5` INT(5) NULL,
171
  `col6` INT(6) NULL,
172
  `col7` INT(7) NULL,
173
  `col8` INT(8) NULL,
174
  `col9` INT(9) NULL,
175
  `col10` BIGINT(10) NULL,
176
  `col11` BIGINT(11) NULL,
177
  `col12` BIGINT(12) NULL,
178
  `col13` BIGINT(13) NULL,
179
  `col14` BIGINT(14) NULL,
180
  `col15` BIGINT(15) NULL,
181
  `col16` BIGINT(16) NULL,
182
  `col17` BIGINT(17) NULL,
183
  `col18` BIGINT(18) NULL,
184
  `col19` DECIMAL(19, 0) NULL,
185
  `col20` DECIMAL(20, 0) NULL,
186
  `col21` DECIMAL(21, 0) NULL,
187
  `col22` DECIMAL(22, 0) NULL,
188
  `col23` DECIMAL(23, 0) NULL,
189
  `col24` DECIMAL(24, 0) NULL,
190
  `col25` DECIMAL(25, 0) NULL,
191
  `col26` DECIMAL(26, 0) NULL,
192
  `col27` DECIMAL(27, 0) NULL,
193
  `col28` DECIMAL(28, 0) NULL,
194
  `col29` DECIMAL(29, 0) NULL,
195
  `col30` DECIMAL(30, 0) NULL,
196
  `col31` DECIMAL(31, 0) NULL,
197
  `col32` DECIMAL(32, 0) NULL,
198
  `col33` DECIMAL(33, 0) NULL,
199
  `col34` DECIMAL(34, 0) NULL,
200
  `col35` DECIMAL(35, 0) NULL,
201
  `col36` DECIMAL(36, 0) NULL,
202
  `col37` DECIMAL(37, 0) NULL,
203
  `col38` DECIMAL(38, 0) NULL,
204
  `fix1` DECIMAL(38, 1) NULL,
205
  `fix2` DECIMAL(38, 2) NULL,
206
  `fix3` DECIMAL(38, 3) NULL,
207
  `fix4` DECIMAL(38, 4) NULL,
208
  `fix5` DECIMAL(38, 5) NULL,
209
  `fix6` DECIMAL(38, 6) NULL,
210
  `fix7` DECIMAL(38, 7) NULL,
211
  `fix8` DECIMAL(38, 8) NULL,
212
  `fix9` DECIMAL(38, 9) NULL,
213
  `fix10` DECIMAL(38, 10) NULL,
214
  `fix11` DECIMAL(38, 11) NULL,
215
  `fix12` DECIMAL(38, 12) NULL,
216
  `fix13` DECIMAL(38, 13) NULL,
217
  `fix14` DECIMAL(38, 14) NULL,
218
  `fix15` DECIMAL(38, 15) NULL,
219
  `fix16` DECIMAL(38, 16) NULL,
220
  `fix17` DECIMAL(38, 17) NULL,
221
  `fix18` DECIMAL(38, 18) NULL,
222
  `fix19` DECIMAL(38, 19) NULL,
223
  `fix20` DECIMAL(38, 20) NULL,
224
  `fix21` DECIMAL(38, 21) NULL,
225
  `fix22` DECIMAL(38, 22) NULL,
226
  `fix23` DECIMAL(38, 23) NULL,
227
  `fix24` DECIMAL(38, 24) NULL,
228
  `fix25` DECIMAL(38, 25) NULL,
229
  `fix26` DECIMAL(38, 26) NULL,
230
  `fix27` DECIMAL(38, 27) NULL,
231
  `fix28` DECIMAL(38, 28) NULL,
232
  `fix29` DECIMAL(38, 29) NULL,
233
  `fix30` DECIMAL(38, 30) NULL
234
);
235
236
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`)
237
VALUES (9, 99, 999, 9999, 99999, 999999, 9999999, 99999999, 999999999,
238
9999999999, 99999999999, 999999999999, 9999999999999, 99999999999999,
239
999999999999999, 9999999999999999, 99999999999999999, 999999999999999999,
240
9999999999999999999, 99999999999999999999, 999999999999999999999,
241
9999999999999999999999, 99999999999999999999999, 999999999999999999999999,
242
9999999999999999999999999, 99999999999999999999999999,
243
999999999999999999999999999, 9999999999999999999999999999,
244
99999999999999999999999999999, 999999999999999999999999999999,
245
9999999999999999999999999999999, 99999999999999999999999999999999,
246
999999999999999999999999999999999, 9999999999999999999999999999999999,
247
99999999999999999999999999999999999, 999999999999999999999999999999999999,
248
9999999999999999999999999999999999999, 99999999999999999999999999999999999999,
249
9999999999999999999999999999999999999.9,
250
999999999999999999999999999999999999.99,
251
99999999999999999999999999999999999.999,
252
9999999999999999999999999999999999.9999,
253
999999999999999999999999999999999.99999,
254
99999999999999999999999999999999.999999,
255
9999999999999999999999999999999.9999999,
256
999999999999999999999999999999.99999999,
257
99999999999999999999999999999.999999999,
258
9999999999999999999999999999.9999999999,
259
999999999999999999999999999.99999999999,
260
99999999999999999999999999.999999999999,
261
9999999999999999999999999.9999999999999,
262
999999999999999999999999.99999999999999,
263
99999999999999999999999.999999999999999,
264
9999999999999999999999.9999999999999999,
265
999999999999999999999.99999999999999999,
266
99999999999999999999.999999999999999999,
267
9999999999999999999.9999999999999999999,
268
999999999999999999.99999999999999999999,
269
99999999999999999.999999999999999999999,
270
9999999999999999.9999999999999999999999,
271
999999999999999.99999999999999999999999,
272
99999999999999.999999999999999999999999,
273
9999999999999.9999999999999999999999999,
274
999999999999.99999999999999999999999999,
275
99999999999.999999999999999999999999999,
276
9999999999.9999999999999999999999999999,
277
999999999.99999999999999999999999999999,
278
99999999.999999999999999999999999999999);
279
280
SELECT * FROM t1;
281
DROP TABLE t1;
282
283
#bug #9088 BIGINT WHERE CLAUSE
284
create table t1 (bigint_col bigint unsigned);
285
insert into t1 values (17666000000000000000);
286
select * from t1 where bigint_col=17666000000000000000;
287
select * from t1 where bigint_col='17666000000000000000';
288
drop table t1;
289
290
--echo
291
--echo bug 19955 -- mod is signed with bigint
292
293
select cast(10000002383263201056 as unsigned) mod 50 as result;
294
295
create table t1 (c1 bigint unsigned);
296
insert into t1 values (10000002383263201056);
297
select c1 mod 50 as result from t1;
298
drop table t1;
299
300
#
301
# Bug #8663 cant use bgint unsigned as input to cast
302
#
303
304
select cast(19999999999999999999 as signed);
305
select cast(-19999999999999999999 as signed);
306
307
#
308
# Bug #28625: -9223372036854775808 doesn't fit in BIGINT.
309
#
310
311
# PS protocol gives different metadata for `Max length' column
312
--disable_ps_protocol
313
--enable_metadata
314
select -9223372036854775808;
315
select -(9223372036854775808);
316
select -((9223372036854775808));
317
select -(-(9223372036854775808));
318
--disable_metadata
319
--enable_ps_protocol
320
select --9223372036854775808, ---9223372036854775808, ----9223372036854775808;
321
select -(-9223372036854775808), -(-(-9223372036854775808));
322
323
# Bug #28005 Partitions: can't use -9223372036854775808 
324
create table t1 select -9223372036854775808 bi;
325
describe t1;
326
drop table t1;
327
create table t1 select -9223372036854775809 bi;
328
describe t1;
329
drop table t1;