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;
43 # It is not deterministic which duplicate will be seen first
44 --replace_regex /(.*Duplicate entry )'.*'( for key.*)/\1''\2/
46 alter table t1 add unique (a);
49 create table t1 (a bit(2)) engine=innodb;
50 insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
52 alter table t1 add key (a);
53 explain select a+0 from t1;
57 create table t1 (a bit(7), b bit(9), key(a, b)) engine=innodb;
59 (94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),
60 (75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380),
61 (111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),
62 (116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),
63 (30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),
64 (44, 307), (68, 454), (57, 135);
65 explain select a+0 from t1;
67 explain select b+0 from t1;
69 explain select a+0, b+0 from t1;
70 select a+0, b+0 from t1;
71 explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
72 select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
73 explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
74 select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
75 set @@max_length_for_sort_data=0;
76 select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
77 select hex(min(a)) from t1;
78 select hex(min(b)) from t1;
79 select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
82 create table t1 (a int not null, b bit, c bit(9), key(a, b, c)) engine=innodb;
84 (4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54),
85 (56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34);
86 select a+0, b+0, c+0 from t1;
87 select hex(min(b)) from t1 where a = 4;
88 select hex(min(c)) from t1 where a = 4 and b = 0;
89 select hex(max(b)) from t1;
90 select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
91 select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
92 select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
93 select a+0, b+0, c+0 from t1 order by b desc;
94 select a+0, b+0, c+0 from t1 order by c;
97 create table t1(a bit(2), b bit(2)) engine=innodb;
98 insert into t1 (a) values (0x01), (0x03), (0x02);
99 update t1 set b= concat(a);
100 select a+0, b+0 from t1;
105 create table t1 (a bit(7), key(a)) engine=innodb;
106 insert into t1 values (44), (57);
111 # Test conversion to and from strings
113 create table t1 (a bit(3), b bit(12)) engine=innodb;
114 insert into t1 values (7,(1<<12)-2), (0x01,0x01ff);
115 select hex(a),hex(b) from t1;
116 select hex(concat(a)),hex(concat(b)) from t1;
120 # Bug #9571: problem with primary key creation
123 create table t1(a int, b bit not null) engine=innodb;
124 alter table t1 add primary key (a);
131 create table t1 (a bit, b bit(10)) engine=innodb;
132 show create table t1;
133 alter table t1 engine=heap;
134 show create table t1;
135 alter table t1 engine=innodb;
136 show create table t1;
140 # Bug #13601: Wrong field length reported for BIT fields
142 create table t1 (a bit(7)) engine=innodb;
143 insert into t1 values (0x60);
149 --echo End of 5.0 tests