set default_storage_engine='tokudb';
select 0 + b'1';
0 + b'1'
1
select 0 + b'0';
0 + b'0'
0
select 0 + b'000001';
0 + b'000001'
1
select 0 + b'000011';
0 + b'000011'
3
select 0 + b'000101';
0 + b'000101'
5
select 0 + b'000000';
0 + b'000000'
0
select 0 + b'10000000';
0 + b'10000000'
128
select 0 + b'11111111';
0 + b'11111111'
255
select 0 + b'10000001';
0 + b'10000001'
129
select 0 + b'1000000000000000';
0 + b'1000000000000000'
32768
select 0 + b'1111111111111111';
0 + b'1111111111111111'
65535
select 0 + b'1000000000000001';
0 + b'1000000000000001'
32769
drop table if exists t1,t2;
create table t1 (a bit(65));
ERROR 42000: Display width out of range for 'a' (max = 64)
create table t1 (a bit(0));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bit(1) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1
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;
hex(a)
FFFFFFFFFFFFFFFF
8000000000000000
1
AAAAAAAAAAAAAAAA
5555555555555555
drop table t1;
create table t1 (a bit);
insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
Warnings:
Warning	1264	Out of range value for column 'a' at row 4
select hex(a) from t1;
hex(a)
0
1
0
1
1
alter table t1 add unique (a);
Got one of the listed errors
drop table t1;
create table t1 (a bit(2));
insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
Warnings:
Warning	1264	Out of range value for column 'a' at row 4
select a+0 from t1;
a+0
0
1
2
3
alter table t1 add key (a);
explain select a+0 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	2	NULL	4	Using index
select a+0 from t1;
a+0
0
1
2
3
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	38	Using index
select a+0 from t1;
a+0
0
4
5
9
23
24
28
29
30
31
34
44
49
56
57
59
60
61
68
68
75
77
78
79
87
88
94
94
104
106
108
111
116
118
119
122
123
127
explain select b+0 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	38	Using index
select b+0 from t1;
b+0
177
245
178
363
36
398
499
399
83
438
202
307
345
379
135
188
343
152
206
454
42
133
123
349
351
411
46
468
280
446
67
368
390
380
368
118
411
403
explain select a+0, b+0 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	38	Using index
select a+0, b+0 from t1;
a+0	b+0
0	177
4	245
5	178
9	363
23	36
24	398
28	499
29	399
30	83
31	438
34	202
44	307
49	345
56	379
57	135
59	188
60	343
61	152
68	206
68	454
75	42
77	133
78	123
79	349
87	351
88	411
94	46
94	468
104	280
106	446
108	67
111	368
116	390
118	380
119	368
122	118
123	411
127	403
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	2	NULL	NA	Using where; Using index; Using filesort
select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
a+0	b+0
44	307
49	345
56	379
60	343
68	206
68	454
79	349
87	351
88	411
94	468
104	280
106	446
111	368
116	390
118	380
119	368
123	411
127	403
explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	2	NULL	NA	Using where; Using index; Using filesort
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
a+0	b+0
57	135
61	152
59	188
68	206
44	307
60	343
49	345
56	379
68	454
set @@max_length_for_sort_data=0;
Warnings:
Warning	1292	Truncated incorrect max_length_for_sort_data value: '0'
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
a+0	b+0
57	135
61	152
59	188
68	206
44	307
60	343
49	345
56	379
68	454
select hex(min(a)) from t1;
hex(min(a))
0
select hex(min(b)) from t1;
hex(min(b))
24
select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
hex(min(a))	hex(max(a))	hex(min(b))	hex(max(b))
0	7F	24	1F3
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;
a+0	b+0	c+0
1	1	100
2	1	4
4	NULL	1
4	0	3
4	0	23
4	0	34
4	0	54
4	1	100
23	0	1
56	0	22
select hex(min(b)) from t1 where a = 4;
hex(min(b))
0
select hex(min(c)) from t1 where a = 4 and b = 0;
hex(min(c))
3
select hex(max(b)) from t1;
hex(max(b))
1
select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
a+0	b+0	c+0
4	0	3
4	0	23
select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
a+0	b+0	c+0
4	1	100
select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
a+0	b+0	c+0
4	1	100
select a+0, b+0, c+0 from t1 order by b desc;
a+0	b+0	c+0
2	1	4
1	1	100
4	1	100
4	0	3
4	0	23
4	0	54
56	0	22
23	0	1
4	0	34
4	NULL	1
select a+0, b+0, c+0 from t1 order by c;
a+0	b+0	c+0
4	NULL	1
23	0	1
4	0	3
2	1	4
56	0	22
4	0	23
4	0	34
4	0	54
1	1	100
4	1	100
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;
a+0	b+0
1	1
3	3
2	2
drop table t1;
create table t1 (a bit(7), key(a));
insert into t1 values (44), (57);
select a+0 from t1;
a+0
44
57
drop table t1;
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;
hex(a)	hex(b)
7	FFE
1	1FF
select hex(concat(a)),hex(concat(b)) from t1;
hex(concat(a))	hex(concat(b))
07	0FFE
01	01FF
drop table t1;
create table t1(a int, b bit not null);
alter table t1 add primary key (a);
drop table t1;
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;
a+0	b+0
1000	10
3	8
200	6
2303	2
12345	4
1	0
alter table t1 engine=heap;
select a+0, b+0 from t1;
a+0	b+0
1000	10
3	8
200	6
2303	2
12345	4
1	0
alter table t1 add key(a, b);
select a+0, b+0 from t1;
a+0	b+0
1000	10
3	8
200	6
2303	2
12345	4
1	0
alter table t1 engine=myisam;
select a+0, b+0 from t1;
a+0	b+0
1	0
3	8
200	6
1000	10
2303	2
12345	4
create table t2 engine=heap select * from t1;
select a+0, b+0 from t2;
a+0	b+0
1	0
3	8
200	6
1000	10
2303	2
12345	4
drop table t1;
create table t1 select * from t2;
select a+0, b+0 from t1;
a+0	b+0
1	0
3	8
200	6
1000	10
2303	2
12345	4
drop table t1, t2;
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;
hex(g)	h
1	a
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;
hex(g)
1
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;
h
a
drop table t1;
create table t1 (a bit(8)) engine=heap;
insert into t1 values ('1111100000');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
select a+0 from t1;
a+0
255
drop table t1;
create table t1 (a bit(7));
insert into t1 values (120), (0), (111);
select a+0 from t1 union select a+0 from t1;
a+0
120
0
111
select a+0 from t1 union select NULL;
a+0
120
0
111
NULL
select NULL union select a+0 from t1;
NULL
NULL
120
0
111
create table t2 select a from t1 union select a from t1;
select a+0 from t2;
a+0
120
0
111
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` bit(7) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1
drop table t1, t2;
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;
b1+0	b2+0
0	1
drop table t1, t2;
drop view v1;
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;
a+0
1
2
5
4
3
1
2
5
4
3
drop table t1;
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;
a1	a2	b1+0	b2+0
2	2	0	1
3	3	1	0
4	4	2	1
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2 order by a1;
a1	a2	b1+0	b2+0
2	2	0	1
3	3	1	0
4	4	2	1
select a1, a2, b1+0, b2+0 from t1 join t2 on b1 = b2;
a1	a2	b1+0	b2+0
1	2	1	1
3	2	1	1
2	3	0	0
1	4	1	1
3	4	1	1
4	5	2	2
select sum(a1), b1+0, b2+0 from t1 join t2 on b1 = b2 group by b1 order by 1;
sum(a1)	b1+0	b2+0
2	0	0
4	2	2
8	1	1
select 1 from t1 join t2 on b1 = b2 group by b1 order by 1;
1
1
1
1
select b1+0,sum(b1), sum(b2) from t1 join t2 on b1 = b2 group by b1 order by 1;
b1+0	sum(b1)	sum(b2)
0	0	0
1	4	4
2	2	2
drop table t1, t2;
create table t1 (a bit(7));
insert into t1 values (0x60);
select * from t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	a	a	16	7	1	Y	32	0	63
a
`
drop table t1;
create table bug15583(b BIT(8), n INT);
insert into bug15583 values(128, 128);
insert into bug15583 values(null, null);
insert into bug15583 values(0, 0);
insert into bug15583 values(255, 255);
select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583;
hex(b)	bin(b)	oct(b)	hex(n)	bin(n)	oct(n)
80	10000000	200	80	10000000	200
NULL	NULL	NULL	NULL	NULL	NULL
0	0	0	0	0	0
FF	11111111	377	FF	11111111	377
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;
should_be_onetrue	should_be_onetrue	should_be_onetrue
1	1	1
NULL	NULL	NULL
1	1	1
1	1	1
select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583;
hex(b + 0)	bin(b + 0)	oct(b + 0)	hex(n)	bin(n)	oct(n)
80	10000000	200	80	10000000	200
NULL	NULL	NULL	NULL	NULL	NULL
0	0	0	0	0	0
FF	11111111	377	FF	11111111	377
select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583;
conv(b, 10, 2)	conv(b + 0, 10, 2)
10000000	10000000
NULL	NULL
0	0
11111111	11111111
drop table bug15583;
create table t1(a bit(1), b smallint unsigned);
insert into t1 (b, a) values ('2', '1');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
select hex(a), b from t1;
hex(a)	b
1	2
drop table t1;
create table t1(bit_field bit(2), int_field int, key a(bit_field));
insert into t1 values (1,2);
drop table t1;
CREATE TABLE t1 (b BIT(2), a VARCHAR(5));
INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z");
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
b+0	COUNT(DISTINCT a)
0	1
1	1
3	2
DROP TABLE t1;
CREATE TABLE t1 (a CHAR(5), b BIT(2));
INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z");
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
b+0	COUNT(DISTINCT a)
0	1
1	1
3	2
DROP TABLE t1;
CREATE TABLE t1 (a INT, b BIT(2));
INSERT INTO t1 (b, a) VALUES (1, 1), (3, 2), (0, 3), (3, 4);
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
b+0	COUNT(DISTINCT a)
0	1
1	1
3	2
DROP TABLE t1;
CREATE TABLE t1 (b BIT);
INSERT INTO t1 (b) VALUES (1), (0);
SELECT DISTINCT b FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	b	b	16	1	1	Y	32	0	63
b
#
#
SELECT b FROM t1 GROUP BY b;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	b	b	16	1	1	Y	32	0	63
b
#
#
DROP TABLE t1;
CREATE TABLE t1 (a int, b bit(2));
INSERT INTO t1 VALUES (3, 2), (2, 3), (2, 0), (3, 2), (3, 1);
SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
COUNT(DISTINCT b)
2
2
DROP TABLE t1;
create table t2 (a int, b bit(2), c char(10));
INSERT INTO t2 VALUES (3, 2, 'two'), (2, 3, 'three'), (2, 0, 'zero'), 
(3, 2, 'two'), (3, 1, 'one');
SELECT COUNT(DISTINCT b,c) FROM t2 GROUP BY a;
COUNT(DISTINCT b,c)
2
2
DROP TABLE t2;
CREATE TABLE t1(a BIT(13), KEY(a));
INSERT INTO t1(a) VALUES
(65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535);
EXPLAIN SELECT 1 FROM t1 GROUP BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	3	NULL	10	Using index
SELECT 1 FROM t1 GROUP BY a;
1
1
DROP TABLE t1;
CREATE TABLE t1 (b BIT NOT NULL, i2 INTEGER NOT NULL, s VARCHAR(255) NOT NULL);
INSERT INTO t1 VALUES(0x01,100,''), (0x00,300,''), (0x01,200,''), (0x00,100,'');
SELECT HEX(b), i2 FROM t1 WHERE (i2>=100 AND i2<201) AND b=TRUE;
HEX(b)	i2
1	100
1	200
CREATE TABLE t2 (b1 BIT NOT NULL, b2 BIT NOT NULL, i2 INTEGER NOT NULL,
s VARCHAR(255) NOT NULL);
INSERT INTO t2 VALUES (0x01,0x00,100,''), (0x00,0x01,300,''),
(0x01,0x00,200,''), (0x00,0x01,100,'');
SELECT HEX(b1), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b1=TRUE;
HEX(b1)	i2
1	100
1	200
SELECT HEX(b2), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b2=FALSE;
HEX(b2)	i2
0	100
0	200
SELECT HEX(b1), HEX(b2), i2 FROM t2
WHERE (i2>=100 AND i2<201) AND b1=TRUE AND b2=FALSE;
HEX(b1)	HEX(b2)	i2
1	0	100
1	0	200
DROP TABLE t1, t2;
CREATE TABLE IF NOT EXISTS t1 (
f1 bit(2) NOT NULL default b'10',
f2 bit(14) NOT NULL default b'11110000111100'
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` bit(2) NOT NULL DEFAULT b'10',
  `f2` bit(14) NOT NULL DEFAULT b'11110000111100'
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
INSERT INTO t1 (f1) VALUES (DEFAULT);
INSERT INTO t1 VALUES (b'', b'');
SELECT HEX(f1), HEX(f2) FROM t1;
HEX(f1)	HEX(f2)
2	3C3C
0	0
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 (
f1 bit(2) NOT NULL default b''
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
DROP TABLE t1;
create table t1bit7 (a1 bit(7) not null);
create table t2bit7 (b1 bit(7));
insert into t1bit7 values (b'1100000');
insert into t1bit7 values (b'1100001');
insert into t1bit7 values (b'1100010');
insert into t2bit7 values (b'1100001');
insert into t2bit7 values (b'1100010');
insert into t2bit7 values (b'1100110');
select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1;
bin(a1)
1100001
1100010
drop table t1bit7, t2bit7;
create table t1bit7 (a1 bit(15) not null);
create table t2bit7 (b1 bit(15));
insert into t1bit7 values (b'110000011111111');
insert into t1bit7 values (b'110000111111111');
insert into t1bit7 values (b'110001011111111');
insert into t2bit7 values (b'110000111111111');
insert into t2bit7 values (b'110001011111111');
insert into t2bit7 values (b'110011011111111');
select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1;
bin(a1)
110000111111111
110001011111111
drop table t1bit7, t2bit7;
#
# Bug42803: Field_bit does not have unsigned_flag field, 
# can lead to bad memory access
#
CREATE TABLE t1 (a BIT(7), b BIT(9), KEY(a, b));
INSERT INTO t1 VALUES(0, 0), (5, 3), (5, 6), (6, 4), (7, 0);
EXPLAIN SELECT a+0, b+0 FROM t1 WHERE a > 4 and b < 7 ORDER BY 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	2	NULL	4	Using where; Using index; Using filesort
DROP TABLE t1;
End of 5.0 tests
create table t1(a bit(7));
insert into t1 values(0x40);
alter table t1 modify column a bit(8);
select hex(a) from t1;
hex(a)
40
insert into t1 values(0x80);
select hex(a) from t1;
hex(a)
40
80
create index a on t1(a);
insert into t1 values(0x81);
select hex(a) from t1;
hex(a)
40
80
81
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bit(8) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=ENGINE DEFAULT CHARSET=latin1
drop table t1;
#
# Bug#50591 bit(31) causes Duplicate entry '1-NULL' for key 'group_key'
#
CREATE TABLE t1(a INT, b BIT(7) NOT NULL);
INSERT INTO t1 VALUES (NULL, 0),(NULL, 0);
SELECT SUM(a) FROM t1 GROUP BY b, a;
SUM(a)
NULL
DROP TABLE t1;
CREATE TABLE t1(a INT, b BIT(7) NOT NULL, c BIT(8) NOT NULL);
INSERT INTO t1 VALUES (NULL, 0, 0),(NULL, 0, 0);
SELECT SUM(a) FROM t1 GROUP BY c, b, a;
SUM(a)
NULL
DROP TABLE t1;
End of 5.1 tests
#
# Test insert of no bits. Should be treated as 0.
#
CREATE TABLE IF NOT EXISTS t1 (
f1 bit(2) NOT NULL
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO t1 VALUES (b'');
SELECT bin(f1) FROM t1;
bin(f1)
0
DROP TABLE t1;