1
--source include/have_innodb.inc
3
# testing of the BIT column type
12
select 0 + b'10000000';
13
select 0 + b'11111111';
14
select 0 + b'10000001';
15
select 0 + b'1000000000000000';
16
select 0 + b'1111111111111111';
17
select 0 + b'1000000000000001';
20
drop table if exists t1;
24
create table t1 (a bit(65)) engine=innodb;
26
create table t1 (a bit(0)) engine=innodb;
30
create table t1 (a bit(64)) engine=innodb;
32
(b'1111111111111111111111111111111111111111111111111111111111111111'),
33
(b'1000000000000000000000000000000000000000000000000000000000000000'),
34
(b'0000000000000000000000000000000000000000000000000000000000000001'),
35
(b'1010101010101010101010101010101010101010101010101010101010101010'),
36
(b'0101010101010101010101010101010101010101010101010101010101010101');
37
select hex(a) from t1;
40
create table t1 (a bit) engine=innodb;
41
insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
42
select hex(a) from t1;
44
alter table t1 add unique (a);
47
create table t1 (a bit(2)) engine=innodb;
48
insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
50
alter table t1 add key (a);
51
explain select a+0 from t1;
55
create table t1 (a bit(7), b bit(9), key(a, b)) engine=innodb;
57
(94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),
58
(75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380),
59
(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),
60
(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),
61
(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),
62
(44, 307), (68, 454), (57, 135);
63
explain select a+0 from t1;
65
explain select b+0 from t1;
67
explain select a+0, b+0 from t1;
68
select a+0, b+0 from t1;
69
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
70
select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
71
explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
72
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
73
set @@max_length_for_sort_data=0;
74
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
75
select hex(min(a)) from t1;
76
select hex(min(b)) from t1;
77
select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
80
create table t1 (a int not null, b bit, c bit(9), key(a, b, c)) engine=innodb;
82
(4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54),
83
(56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34);
84
select a+0, b+0, c+0 from t1;
85
select hex(min(b)) from t1 where a = 4;
86
select hex(min(c)) from t1 where a = 4 and b = 0;
87
select hex(max(b)) from t1;
88
select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
89
select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
90
select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
91
select a+0, b+0, c+0 from t1 order by b desc;
92
select a+0, b+0, c+0 from t1 order by c;
95
create table t1(a bit(2), b bit(2)) engine=innodb;
96
insert into t1 (a) values (0x01), (0x03), (0x02);
97
update t1 set b= concat(a);
98
select a+0, b+0 from t1;
103
create table t1 (a bit(7), key(a)) engine=innodb;
104
insert into t1 values (44), (57);
109
# Test conversion to and from strings
111
create table t1 (a bit(3), b bit(12)) engine=innodb;
112
insert into t1 values (7,(1<<12)-2), (0x01,0x01ff);
113
select hex(a),hex(b) from t1;
114
select hex(concat(a)),hex(concat(b)) from t1;
118
# Bug #9571: problem with primary key creation
121
create table t1(a int, b bit not null) engine=innodb;
122
alter table t1 add primary key (a);
129
create table t1 (a bit, b bit(10)) engine=innodb;
130
show create table t1;
131
alter table t1 engine=heap;
132
show create table t1;
133
alter table t1 engine=innodb;
134
show create table t1;
138
# Bug #13601: Wrong field length reported for BIT fields
140
create table t1 (a bit(7)) engine=innodb;
141
insert into t1 values (0x60);
147
--echo End of 5.0 tests