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;
create table t1 (a bit(65)) engine=innodb;
ERROR 42000: Display width out of range for column 'a' (max = 64)
create table t1 (a bit(0)) engine=innodb;
ERROR HY000: Invalid size for column 'a'.
create table t1 (a bit(64)) engine=innodb;
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) engine=innodb;
insert ignore into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
Warnings:
Warning	1406	Data too long for column 'a' at row 4
select hex(a) from t1;
hex(a)
0
1
0
1
1
alter table t1 add unique (a);
ERROR 23000: Duplicate entry '\x01' for key 't1.a'
drop table t1;
create table t1 (a bit(2)) engine=innodb;
insert ignore into t1 values (b'00'), (b'01'), (b'10'), (b'100');
Warnings:
Warning	1406	Data too long for column 'a' at row 4
select a+0 from t1;
a+0
0
1
2
3
alter table t1 add key (a);
analyze table t1;
explain select a+0 from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	a	2	NULL	4	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`a` + 0) AS `a+0` from `test`.`t1`
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)) engine=innodb;
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);
analyze table t1;
explain select a+0 from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	a	5	NULL	38	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`a` + 0) AS `a+0` from `test`.`t1`
select a+0 from t1;
a+0
0
104
106
108
111
116
118
119
122
123
127
23
24
28
29
30
31
34
4
44
49
5
56
57
59
60
61
68
68
75
77
78
79
87
88
9
94
94
explain select b+0 from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	a	5	NULL	38	100.00	Using index
Note	1003	/* select#1 */ select (`test`.`t1`.`b` + 0) AS `b+0` from `test`.`t1`
Warnings:
select b+0 from t1;
b+0
118
123
133
135
152
177
178
188
202
206
245
280
307
343
345
349
351
36
363
368
368
379
380
390
398
399
403
411
411
42
438
446
454
46
468
499
67
83
explain select a+0, b+0 from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	a	5	NULL	38	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`a` + 0) AS `a+0`,(`test`.`t1`.`b` + 0) AS `b+0` from `test`.`t1`
select a+0, b+0 from t1;
a+0	b+0
0	177
104	280
106	446
108	67
111	368
116	390
118	380
119	368
122	118
123	411
127	403
23	36
24	398
28	499
29	399
30	83
31	438
34	202
4	245
44	307
49	345
5	178
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
9	363
94	46
94	468
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	a	a	2	NULL	27	33.33	Using where; Using index; Using filesort
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`a` + 0) AS `a+0`,(`test`.`t1`.`b` + 0) AS `b+0` from `test`.`t1` where ((`test`.`t1`.`a` > 40) and (`test`.`t1`.`b` > 200)) order by (`test`.`t1`.`a` + 0)
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	a	a	2	NULL	9	100.00	Using where; Using index; Using filesort
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`a` + 0) AS `a+0`,(`test`.`t1`.`b` + 0) AS `b+0` from `test`.`t1` where ((`test`.`t1`.`a` > 40) and (`test`.`t1`.`a` < 70)) order by (`test`.`t1`.`b` + 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)) engine=innodb;
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
23	0	1
4	0	23
4	0	3
4	0	34
4	0	54
4	1	100
4	NULL	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 b+0, a+0, c+0 from t1 order by b desc;
b+0	a+0	c+0
1	1	100
1	2	4
1	4	100
0	23	1
0	4	23
0	4	3
0	4	34
0	4	54
0	56	22
NULL	4	1
select c+0, a+0, b+0 from t1 order by c;
c+0	a+0	b+0
1	23	0
1	4	NULL
3	4	0
4	2	1
22	56	0
23	4	0
34	4	0
54	4	0
100	1	1
100	4	1
drop table t1;
create table t1(a bit(2), b bit(2)) engine=innodb;
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)) engine=innodb;
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)) engine=innodb;
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) engine=innodb;
alter table t1 add primary key (a);
drop table t1;
create table t1 (a bit, b bit(10)) engine=innodb;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bit(1) DEFAULT NULL,
  `b` bit(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
alter table t1 engine=heap;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bit(1) DEFAULT NULL,
  `b` bit(10) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
alter table t1 engine=innodb;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bit(1) DEFAULT NULL,
  `b` bit(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
create table t1 (a bit(7)) engine=innodb;
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;
End of 5.0 tests
#
# Bug #31564742 	LOST ULLONG_MAX VALUE IN TABLE WITH TYPE OF BIT(64)
#
CREATE TABLE t1 (a BIT(64));
INSERT INTO t1 VALUES
(b'1111111111111111111111111111111111111111111111111111111111111111');
SELECT HEX(a) FROM t1 WHERE a =
b'1111111111111111111111111111111111111111111111111111111111111111';
HEX(a)
FFFFFFFFFFFFFFFF
DROP TABLE t1;
#
# Bug #31019130 CASTING DOUBLE TO LONGLONG CAUSES UB
#               IN FIELD_*::STORE(DOUBLE)
#
CREATE TABLE t_double (a double not null);
INSERT INTO t_double (a) VALUES
(-20000000000000000000000000000.0001), (20000000000000000000000000000.0001);
CREATE TABLE t_bit (
b bit(3)
);
INSERT IGNORE INTO t_bit (b) SELECT a from t_double;
Warnings:
Warning	1406	Data too long for column 'b' at row 1
Warning	1406	Data too long for column 'b' at row 2
SELECT HEX(b) FROM t_bit;
HEX(b)
7
7
DROP TABLE t_double, t_bit;