#
# testing of the BIT column type
#

select 0 + b'1';
select 0 + b'0';
select 0 + b'000001';
select 0 + b'000011';
select 0 + b'000101';
select 0 + b'000000';
select 0 + b'10000000';
select 0 + b'11111111';
select 0 + b'10000001';
select 0 + b'1000000000000000';
select 0 + b'1111111111111111';
select 0 + b'1000000000000001';

--disable_warnings
drop table if exists t1,t2;
--enable_warnings

--error 1439
create table t1 (a bit(65));

create table t1 (a bit(0));
show create table t1;
drop table t1;

create table t1 (a bit(64));
insert into t1 values 
(b'1111111111111111111111111111111111111111111111111111111111111111'),
(b'1000000000000000000000000000000000000000000000000000000000000000'),
(b'0000000000000000000000000000000000000000000000000000000000000001'),
(b'1010101010101010101010101010101010101010101010101010101010101010'),
(b'0101010101010101010101010101010101010101010101010101010101010101');
select hex(a) from t1;
drop table t1;

create table t1 (a bit);
insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
select hex(a) from t1;
--error 1062
alter table t1 add unique (a);
drop table t1;

create table t1 (a bit(2));
insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
select a+0 from t1;
alter table t1 add key (a);
explain select a+0 from t1;
select a+0 from t1;
drop table t1;

create table t1 (a bit(7), b bit(9), key(a, b));
insert into t1 values 
(94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177),    
(75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380),   
(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),    
(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),
(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),   
(44, 307), (68, 454), (57, 135);
explain select a+0 from t1;
select a+0 from t1;
explain select b+0 from t1;
select b+0 from t1;
explain select a+0, b+0 from t1;
select a+0, b+0 from t1;
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
set @@max_length_for_sort_data=0;
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
select hex(min(a)) from t1;
select hex(min(b)) from t1;
select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
drop table t1;

create table t1 (a int not null, b bit, c bit(9), key(a, b, c));
insert into t1 values
(4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54),
(56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34);
select a+0, b+0, c+0 from t1;
select hex(min(b)) from t1 where a = 4;
select hex(min(c)) from t1 where a = 4 and b = 0;
select hex(max(b)) from t1;
select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
select a+0, b+0, c+0 from t1 order by b desc;
select a+0, b+0, c+0 from t1 order by c;
drop table t1;

create table t1(a bit(2), b bit(2));
insert into t1 (a) values (0x01), (0x03), (0x02);
update t1 set b= concat(a);
select a+0, b+0 from t1;
drop table t1;

# Some magic numbers

create table t1 (a bit(7), key(a));
insert into t1 values (44), (57);
select a+0 from t1;
drop table t1;

#
# Test conversion to and from strings
#
create table t1 (a bit(3), b bit(12));
insert into t1 values (7,(1<<12)-2), (0x01,0x01ff);
select hex(a),hex(b) from t1;
select hex(concat(a)),hex(concat(b)) from t1;
drop table t1;

#
# Bug #9571: problem with primary key creation
#

create table t1(a int, b bit not null);
alter table t1 add primary key (a);
drop table t1;

#
# myisam <-> heap
#

create table t1 (a bit(19), b bit(5));
insert into t1 values (1000, 10), (3, 8), (200, 6), (2303, 2), (12345, 4), (1, 0);
select a+0, b+0 from t1;
alter table t1 engine=heap;
select a+0, b+0 from t1;
alter table t1 add key(a, b);
select a+0, b+0 from t1;
alter table t1 engine=myisam;
select a+0, b+0 from t1;
create table t2 engine=heap select * from t1;
select a+0, b+0 from t2;
drop table t1;
create table t1 select * from t2;
select a+0, b+0 from t1;
drop table t1, t2;

#
# Bug #10179: problem with NULLs and default values
#

create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), 
  g bit(1) NOT NULL default 1, h char(1) default 'a');
insert into t1 set a=1;
select hex(g), h from t1;
drop table t1;

create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
  g bit(1) NOT NULL default 1);
insert into t1 set a=1;
select hex(g) from t1;
drop table t1;

create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), 
  h char(1) default 'a') engine=myisam;
insert into t1 set a=1;
select h from t1;
drop table t1;

#
# Bug #10539
#

create table t1 (a bit(8)) engine=heap;
insert into t1 values ('1111100000');
select a+0 from t1;
drop table t1;

#
# Bug #11091: union
#

create table t1 (a bit(7));
insert into t1 values (120), (0), (111);
select a+0 from t1 union select a+0 from t1;
select a+0 from t1 union select NULL;
select NULL union select a+0 from t1;
create table t2 select a from t1 union select a from t1;
select a+0 from t2;
show create table t2;
drop table t1, t2;

#
# Bug #11572: view
#

create table t1 (id1 int(11), b1 bit(1));
create table t2 (id2 int(11), b2 bit(1));
insert into t1 values (1, 1), (2, 0), (3, 1);
insert into t2 values (2, 1), (3, 0), (4, 0);
create algorithm=undefined view v1 as 
  select b1+0, b2+0 from t1, t2 where id1 = id2 and b1 = 0
  union
  select b1+0, b2+0 from t1, t2 where id1 = id2 and b2 = 1;
select * from v1;
drop table t1, t2;
drop view v1;

#
# Bug #10617: bulk-insert
#

create table t1(a bit(4));
insert into t1(a) values (1), (2), (5), (4), (3);
insert into t1 select * from t1;
select a+0 from t1;
drop table t1;

#
# join
#

create table t1 (a1 int(11), b1 bit(2));
create table t2 (a2 int(11), b2 bit(2));
insert into t1 values (1, 1), (2, 0), (3, 1), (4, 2);
insert into t2 values (2, 1), (3, 0), (4, 1), (5, 2);
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2;
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2 order by a1;
select a1, a2, b1+0, b2+0 from t1 join t2 on b1 = b2;
select sum(a1), b1+0, b2+0 from t1 join t2 on b1 = b2 group by b1 order by 1;