2
# testing of the BIT column type
11
select 0 + b'10000000';
12
select 0 + b'11111111';
13
select 0 + b'10000001';
14
select 0 + b'1000000000000000';
15
select 0 + b'1111111111111111';
16
select 0 + b'1000000000000001';
19
drop table if exists t1,t2;
23
create table t1 (a bit(65));
25
create table t1 (a bit(0));
29
create table t1 (a bit(64));
31
(b'1111111111111111111111111111111111111111111111111111111111111111'),
32
(b'1000000000000000000000000000000000000000000000000000000000000000'),
33
(b'0000000000000000000000000000000000000000000000000000000000000001'),
34
(b'1010101010101010101010101010101010101010101010101010101010101010'),
35
(b'0101010101010101010101010101010101010101010101010101010101010101');
36
select hex(a) from t1;
39
create table t1 (a bit);
40
insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
41
select hex(a) from t1;
43
alter table t1 add unique (a);
46
create table t1 (a bit(2));
47
insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
49
alter table t1 add key (a);
50
explain select a+0 from t1;
54
create table t1 (a bit(7), b bit(9), key(a, b));
56
(94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),
57
(75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380),
58
(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),
59
(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),
60
(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),
61
(44, 307), (68, 454), (57, 135);
62
explain select a+0 from t1;
64
explain select b+0 from t1;
66
explain select a+0, b+0 from t1;
67
select a+0, b+0 from t1;
68
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
69
select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
70
explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
71
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
72
set @@max_length_for_sort_data=0;
73
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
74
select hex(min(a)) from t1;
75
select hex(min(b)) from t1;
76
select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
79
create table t1 (a int not null, b bit, c bit(9), key(a, b, c));
81
(4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54),
82
(56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34);
83
select a+0, b+0, c+0 from t1;
84
select hex(min(b)) from t1 where a = 4;
85
select hex(min(c)) from t1 where a = 4 and b = 0;
86
select hex(max(b)) from t1;
87
select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
88
select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
89
select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
90
select a+0, b+0, c+0 from t1 order by b desc;
91
select a+0, b+0, c+0 from t1 order by c;
94
create table t1(a bit(2), b bit(2));
95
insert into t1 (a) values (0x01), (0x03), (0x02);
96
update t1 set b= concat(a);
97
select a+0, b+0 from t1;
102
create table t1 (a bit(7), key(a));
103
insert into t1 values (44), (57);
108
# Test conversion to and from strings
110
create table t1 (a bit(3), b bit(12));
111
insert into t1 values (7,(1<<12)-2), (0x01,0x01ff);
112
select hex(a),hex(b) from t1;
113
select hex(concat(a)),hex(concat(b)) from t1;
117
# Bug #9571: problem with primary key creation
120
create table t1(a int, b bit not null);
121
alter table t1 add primary key (a);
128
create table t1 (a bit(19), b bit(5));
129
insert into t1 values (1000, 10), (3, 8), (200, 6), (2303, 2), (12345, 4), (1, 0);
130
select a+0, b+0 from t1;
131
alter table t1 engine=heap;
132
select a+0, b+0 from t1;
133
alter table t1 add key(a, b);
134
select a+0, b+0 from t1;
135
alter table t1 engine=myisam;
136
select a+0, b+0 from t1;
137
create table t2 engine=heap select * from t1;
138
select a+0, b+0 from t2;
140
create table t1 select * from t2;
141
select a+0, b+0 from t1;
145
# Bug #10179: problem with NULLs and default values
148
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
149
g bit(1) NOT NULL default 1, h char(1) default 'a');
150
insert into t1 set a=1;
151
select hex(g), h from t1;
154
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
155
g bit(1) NOT NULL default 1);
156
insert into t1 set a=1;
157
select hex(g) from t1;
160
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
161
h char(1) default 'a') engine=myisam;
162
insert into t1 set a=1;
170
create table t1 (a bit(8)) engine=heap;
171
insert into t1 values ('1111100000');
179
create table t1 (a bit(7));
180
insert into t1 values (120), (0), (111);
181
select a+0 from t1 union select a+0 from t1;
182
select a+0 from t1 union select NULL;
183
select NULL union select a+0 from t1;
184
create table t2 select a from t1 union select a from t1;
186
show create table t2;
193
create table t1 (id1 int(11), b1 bit(1));
194
create table t2 (id2 int(11), b2 bit(1));
195
insert into t1 values (1, 1), (2, 0), (3, 1);
196
insert into t2 values (2, 1), (3, 0), (4, 0);
197
create algorithm=undefined view v1 as
198
select b1+0, b2+0 from t1, t2 where id1 = id2 and b1 = 0
200
select b1+0, b2+0 from t1, t2 where id1 = id2 and b2 = 1;
206
# Bug #10617: bulk-insert
209
create table t1(a bit(4));
210
insert into t1(a) values (1), (2), (5), (4), (3);
211
insert into t1 select * from t1;
219
create table t1 (a1 int(11), b1 bit(2));
220
create table t2 (a2 int(11), b2 bit(2));
221
insert into t1 values (1, 1), (2, 0), (3, 1), (4, 2);
222
insert into t2 values (2, 1), (3, 0), (4, 1), (5, 2);
223
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2;
224
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2 order by a1;
225
select a1, a2, b1+0, b2+0 from t1 join t2 on b1 = b2;
226
select sum(a1), b1+0, b2+0 from t1 join t2 on b1 = b2 group by b1 order by 1;
227
select 1 from t1 join t2 on b1 = b2 group by b1 order by 1;
228
select b1+0,sum(b1), sum(b2) from t1 join t2 on b1 = b2 group by b1 order by 1;
232
# Bug #13601: Wrong field length reported for BIT fields
234
create table t1 (a bit(7));
235
insert into t1 values (0x60);
242
# Bug#15583: BIN()/OCT()/CONV() do not work with BIT values
244
create table bug15583(b BIT(8), n INT);
245
insert into bug15583 values(128, 128);
246
insert into bug15583 values(null, null);
247
insert into bug15583 values(0, 0);
248
insert into bug15583 values(255, 255);
249
select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583;
250
select hex(b)=hex(n) as should_be_onetrue, bin(b)=bin(n) as should_be_onetrue, oct(b)=oct(n) as should_be_onetrue from bug15583;
251
select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583;
252
select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583;
256
# Bug #22271: data casting may affect data stored in the next column(s?)
259
create table t1(a bit(1), b smallint unsigned);
260
insert into t1 (b, a) values ('2', '1');
261
select hex(a), b from t1;
265
# type was not properly initalized, which caused key_copy to fail
268
create table t1(bit_field bit(2), int_field int, key a(bit_field));
269
insert into t1 values (1,2);
270
handler t1 open as t1;
271
handler t1 read a=(1);
276
# Bug #30219: GROUP BY a column of the BIT type
279
CREATE TABLE t1 (b BIT(2), a VARCHAR(5));
280
INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z");
281
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
284
CREATE TABLE t1 (a CHAR(5), b BIT(2));
285
INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z");
286
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
289
CREATE TABLE t1 (a INT, b BIT(2));
290
INSERT INTO t1 (b, a) VALUES (1, 1), (3, 2), (0, 3), (3, 4);
291
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
295
# Bug#30245: A wrong type of a BIT field is reported when grouped by it.
297
CREATE TABLE t1 (b BIT);
298
INSERT INTO t1 (b) VALUES (1), (0);
301
SELECT DISTINCT b FROM t1;
303
SELECT b FROM t1 GROUP BY b;
308
# BUG#30324 Wrong query result for COUNT(DISTINCT(bit_column))
310
CREATE TABLE t1 (a int, b bit(2));
311
INSERT INTO t1 VALUES (3, 2), (2, 3), (2, 0), (3, 2), (3, 1);
312
SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
315
create table t2 (a int, b bit(2), c char(10));
316
INSERT INTO t2 VALUES (3, 2, 'two'), (2, 3, 'three'), (2, 0, 'zero'),
317
(3, 2, 'two'), (3, 1, 'one');
318
SELECT COUNT(DISTINCT b,c) FROM t2 GROUP BY a;
322
# BUG#32556 assert in "using index for group-by" : is_last_prefix <= 0,
323
# file .\opt_range.cc
325
CREATE TABLE t1(a BIT(13), KEY(a));
327
INSERT INTO t1(a) VALUES
328
(65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535);
331
EXPLAIN SELECT 1 FROM t1 GROUP BY a;
332
SELECT 1 FROM t1 GROUP BY a;
336
--echo End of 5.0 tests
339
# Bug #28631: problem after alter
341
create table t1(a bit(7));
342
insert into t1 values(0x40);
343
alter table t1 modify column a bit(8);
344
select hex(a) from t1;
345
insert into t1 values(0x80);
346
select hex(a) from t1;
347
create index a on t1(a);
348
insert into t1 values(0x81);
349
select hex(a) from t1;
350
show create table t1;
353
--echo End of 5.1 tests