CREATE TABLE t(id int, vbin1 varbinary(32), vbin2 varbinary(32));
INSERT INTO t VALUES
(1, x'59', x'6a'),
(2, x'5939', x'6ac3'),
(3, x'5939a998', x'6ac35d2a'),
(4, x'5939a99861154f35', x'6ac35d2a3ab34bda'),
(5, x'5939a99861154f3587d5440618e9b28b', x'6ac35d2a3ab34bda8ac412ea0141852c'),
(6, x'5939a99861154f3587d5440618e9b28b166181c5ca514ab1b8e9c970ae5e421a', x'6ac35d2a3ab34bda8ac412ea0141852c3c8e38bb19934a7092a40bb19db13a80'),
(7, x'5939a99861154f3587d5440618e9b28b', x'8ac412ea0141852c'),
(8, x'5939a99861154f35', x'6ac35d2a3ab34bda8ac412ea0141852c');
#
# bitwise operations with varbinary args with different sizes
#
SELECT HEX(vbin1 & vbin2), HEX(vbin1 | vbin2), HEX(vbin1 ^ vbin2),
HEX(~vbin1), HEX(vbin1 << 3), HEX(vbin2 >> 3), BIT_COUNT(vbin1)
FROM t
WHERE id in(1,2,3,4,5,6);
HEX(vbin1 & vbin2)	HEX(vbin1 | vbin2)	HEX(vbin1 ^ vbin2)	HEX(~vbin1)	HEX(vbin1 << 3)	HEX(vbin2 >> 3)	BIT_COUNT(vbin1)
48	7B	33	A6	C8	0D	4
4801	7BFB	33FA	A6C6	C9C8	0D58	8
48010908	7BFBFDBA	33FAF4B2	A6C65667	C9CD4CC0	0D586BA5	15
4801090820114B10	7BFBFDBA7BB74FFF	33FAF4B25BA604EF	A6C656679EEAB0CA	C9CD4CC308AA79A8	0D586BA54756697B	30
4801090820114B1082C4000200418008	7BFBFDBA7BB74FFF8FD556EE19E9B7AF	33FAF4B25BA604EF0D1156EC19A837A7	A6C656679EEAB0CA782ABBF9E7164D74	C9CD4CC308AA79AC3EAA2030C74D9458	0D586BA54756697B5158825D402830A5	58
4801090820114B1082C40002004180081400008108114A3090A009308C100200	7BFBFDBA7BB74FFF8FD556EE19E9B7AF3EEFB9FFDBD34AF1BAEDCBF1BFFF7A9A	33FAF4B25BA604EF0D1156EC19A837A72AEFB97ED3C200C12A4DC2C133EF789A	A6C656679EEAB0CA782ABBF9E7164D74E99E7E3A35AEB54E4716368F51A1BDE5	C9CD4CC308AA79AC3EAA2030C74D9458B30C0E2E528A558DC74E4B8572F210D0	0D586BA54756697B5158825D402830A58791C7176332694E1254817633B62750	115
# bitwise operations result as raw binary
SELECT vbin1 & vbin2, vbin1 | vbin2, vbin1 ^ vbin2, ~vbin1, vbin1 << 3, vbin2 >> 3
FROM t
WHERE id in(1,2,3,4,5,6);
SELECT (vbin1 & vbin2)=0x4801090820114B1082C40002004180081400008108114A3090A009308C100200, (vbin1 | vbin2)=0x7BFBFDBA7BB74FFF8FD556EE19E9B7AF3EEFB9FFDBD34AF1BAEDCBF1BFFF7A9A, (vbin1 ^ vbin2)=0x33FAF4B25BA604EF0D1156EC19A837A72AEFB97ED3C200C12A4DC2C133EF789A, (~vbin1)=0xA6C656679EEAB0CA782ABBF9E7164D74E99E7E3A35AEB54E4716368F51A1BDE5, (vbin1 << 3)=0xC9CD4CC308AA79AC3EAA2030C74D9458B30C0E2E528A558DC74E4B8572F210D0, (vbin2 >> 3)=0x0D586BA54756697B5158825D402830A58791C7176332694E1254817633B62750
FROM t
WHERE id in(1,2,3,4,5,6);
(vbin1 & vbin2)=0x4801090820114B1082C40002004180081400008108114A3090A009308C100200	(vbin1 | vbin2)=0x7BFBFDBA7BB74FFF8FD556EE19E9B7AF3EEFB9FFDBD34AF1BAEDCBF1BFFF7A9A	(vbin1 ^ vbin2)=0x33FAF4B25BA604EF0D1156EC19A837A72AEFB97ED3C200C12A4DC2C133EF789A	(~vbin1)=0xA6C656679EEAB0CA782ABBF9E7164D74E99E7E3A35AEB54E4716368F51A1BDE5	(vbin1 << 3)=0xC9CD4CC308AA79AC3EAA2030C74D9458B30C0E2E528A558DC74E4B8572F210D0	(vbin2 >> 3)=0x0D586BA54756697B5158825D402830A58791C7176332694E1254817633B62750
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
1	1	1	1	1	1
select HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 | _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2), HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 ^ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2),
HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 & _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2), HEX(~ _binary 0x19c9bbcce9e0a88f5212572b0c5b9e6d0), HEX(~ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2);
HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 | _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2)	HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 ^ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2)	HEX(0x19c9bbcce9e0a88f5212572b0c5b9e6d0 & _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2)	HEX(~ _binary 0x19c9bbcce9e0a88f5212572b0c5b9e6d0)	HEX(~ _binary 0x13c19e5cfdf03b19518cbe3d65faf10d2)
01BC9BFDCFDF0BB9F539EFF3F6DFBFF6D2	00A08259014109396039EE91669A16F602	011C19A4CE9E0280950001629045A900D0	FE6364433161F5770ADEDA8D4F3A46192F	FEC3E61A3020FC4E6AE7341C29A050EF2D
SELECT HEX(vbin1 << 3), HEX(vbin2 << 3) FROM t WHERE id=7;
HEX(vbin1 << 3)	HEX(vbin2 << 3)
C9CD4CC308AA79AC3EAA2030C74D9458	562097500A0C2960
SELECT HEX(vbin1 >> 3), HEX(vbin2 >> 3) FROM t WHERE id=7;
HEX(vbin1 >> 3)	HEX(vbin2 >> 3)
0B2735330C22A9E6B0FAA880C31D3651	1158825D402830A5
SELECT HEX(~vbin1), HEX(~vbin2) FROM t WHERE id=7;
HEX(~vbin1)	HEX(~vbin2)
A6C656679EEAB0CA782ABBF9E7164D74	753BED15FEBE7AD3
SELECT HEX(vbin1 << 3), HEX(vbin2 << 3) FROM t WHERE id=8;
HEX(vbin1 << 3)	HEX(vbin2 << 3)
C9CD4CC308AA79A8	561AE951D59A5ED4562097500A0C2960
SELECT HEX(vbin1 >> 3), HEX(vbin2 >> 3) FROM t WHERE id=8;
HEX(vbin1 >> 3)	HEX(vbin2 >> 3)
0B2735330C22A9E6	0D586BA54756697B5158825D402830A5
SELECT HEX(~vbin1), HEX(~vbin2) FROM t WHERE id=8;
HEX(~vbin1)	HEX(~vbin2)
A6C656679EEAB0CA	953CA2D5C54CB425753BED15FEBE7AD3
SELECT HEX(vbin1 & vbin2) FROM t WHERE id=7;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT HEX(vbin1 | vbin2) FROM t WHERE id=7;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT HEX(vbin1 ^ vbin2) FROM t WHERE id=7;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT HEX(vbin1 & vbin2) FROM t WHERE id=8;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT HEX(vbin1 | vbin2) FROM t WHERE id=8;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT HEX(vbin1 ^ vbin2) FROM t WHERE id=8;
ERROR HY000: Binary operands of bitwise operators must be of equal length
#
# show output type
#
CREATE TABLE t0(vbin VARBINARY(6), bin BINARY(6));
CREATE TABLE t1  charset utf8mb4
AS SELECT vbin & vbin, vbin & bin, bin & vbin, bin & bin FROM t0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `vbin & vbin` varbinary(6) DEFAULT NULL,
  `vbin & bin` varbinary(6) DEFAULT NULL,
  `bin & vbin` varbinary(6) DEFAULT NULL,
  `bin & bin` varbinary(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t0;
DROP TABLE t1;
#
# bitwise operations with varbinary args in prepared statement
#
PREPARE s1 FROM
"SELECT HEX(vbin1 & vbin2), HEX(vbin1 | vbin2), HEX(vbin1 ^ vbin2),
  HEX(~vbin1), HEX(vbin1 << 3), HEX(vbin2 >> 3), BIT_COUNT(vbin1)
FROM t
WHERE id in(1, 2, 3, 4, 5, 6)";
EXECUTE s1;
HEX(vbin1 & vbin2)	HEX(vbin1 | vbin2)	HEX(vbin1 ^ vbin2)	HEX(~vbin1)	HEX(vbin1 << 3)	HEX(vbin2 >> 3)	BIT_COUNT(vbin1)
48	7B	33	A6	C8	0D	4
4801	7BFB	33FA	A6C6	C9C8	0D58	8
48010908	7BFBFDBA	33FAF4B2	A6C65667	C9CD4CC0	0D586BA5	15
4801090820114B10	7BFBFDBA7BB74FFF	33FAF4B25BA604EF	A6C656679EEAB0CA	C9CD4CC308AA79A8	0D586BA54756697B	30
4801090820114B1082C4000200418008	7BFBFDBA7BB74FFF8FD556EE19E9B7AF	33FAF4B25BA604EF0D1156EC19A837A7	A6C656679EEAB0CA782ABBF9E7164D74	C9CD4CC308AA79AC3EAA2030C74D9458	0D586BA54756697B5158825D402830A5	58
4801090820114B1082C40002004180081400008108114A3090A009308C100200	7BFBFDBA7BB74FFF8FD556EE19E9B7AF3EEFB9FFDBD34AF1BAEDCBF1BFFF7A9A	33FAF4B25BA604EF0D1156EC19A837A72AEFB97ED3C200C12A4DC2C133EF789A	A6C656679EEAB0CA782ABBF9E7164D74E99E7E3A35AEB54E4716368F51A1BDE5	C9CD4CC308AA79AC3EAA2030C74D9458B30C0E2E528A558DC74E4B8572F210D0	0D586BA54756697B5158825D402830A58791C7176332694E1254817633B62750	115
EXECUTE s1;
HEX(vbin1 & vbin2)	HEX(vbin1 | vbin2)	HEX(vbin1 ^ vbin2)	HEX(~vbin1)	HEX(vbin1 << 3)	HEX(vbin2 >> 3)	BIT_COUNT(vbin1)
48	7B	33	A6	C8	0D	4
4801	7BFB	33FA	A6C6	C9C8	0D58	8
48010908	7BFBFDBA	33FAF4B2	A6C65667	C9CD4CC0	0D586BA5	15
4801090820114B10	7BFBFDBA7BB74FFF	33FAF4B25BA604EF	A6C656679EEAB0CA	C9CD4CC308AA79A8	0D586BA54756697B	30
4801090820114B1082C4000200418008	7BFBFDBA7BB74FFF8FD556EE19E9B7AF	33FAF4B25BA604EF0D1156EC19A837A7	A6C656679EEAB0CA782ABBF9E7164D74	C9CD4CC308AA79AC3EAA2030C74D9458	0D586BA54756697B5158825D402830A5	58
4801090820114B1082C40002004180081400008108114A3090A009308C100200	7BFBFDBA7BB74FFF8FD556EE19E9B7AF3EEFB9FFDBD34AF1BAEDCBF1BFFF7A9A	33FAF4B25BA604EF0D1156EC19A837A72AEFB97ED3C200C12A4DC2C133EF789A	A6C656679EEAB0CA782ABBF9E7164D74E99E7E3A35AEB54E4716368F51A1BDE5	C9CD4CC308AA79AC3EAA2030C74D9458B30C0E2E528A558DC74E4B8572F210D0	0D586BA54756697B5158825D402830A58791C7176332694E1254817633B62750	115
PREPARE s2 FROM
"SELECT HEX(vbin1 & vbin2), HEX(vbin1 | vbin2), HEX(vbin1 ^ vbin2),
  HEX(~vbin1), HEX(vbin1 << 3), HEX(vbin2 >> 3), BIT_COUNT(vbin1)
FROM t
WHERE id in(7)";
EXECUTE s2;
ERROR HY000: Binary operands of bitwise operators must be of equal length
EXECUTE s2;
ERROR HY000: Binary operands of bitwise operators must be of equal length
DROP TABLE t;
CREATE TABLE networks (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
start varbinary(16) NOT NULL,
end varbinary(16) NOT NULL,
country_code varchar(2) NOT NULL,
country varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY start (start),
KEY end (end)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
#
# Testing bitiwise operations on a real-life test case
#
INSERT INTO networks(start, end, country_code, country) VALUES
(INET6_ATON('2c0f:fff0::'),INET6_ATON('2c0f:fff0:ffff:ffff:ffff:ffff:ffff:ffff'),'NG','Nigeria'),
(INET6_ATON('2405:1d00::'),INET6_ATON('2405:1d00:ffff:ffff:ffff:ffff:ffff:ffff'),'GR','Greenland'),
(INET6_ATON('2c0f:ffe8::'),INET6_ATON('2c0f:ffe8:ffff:ffff:ffff:ffff:ffff:ffff'),'NG','Nigeria');
SELECT id, HEX(start), HEX(end), country_code, country
FROM networks
WHERE INET6_ATON('2c0f:fff0:1234:5678:9101:1123::') & start = INET6_ATON('2c0f:fff0::');
id	HEX(start)	HEX(end)	country_code	country
1	2C0FFFF0000000000000000000000000	2C0FFFF0FFFFFFFFFFFFFFFFFFFFFFFF	NG	Nigeria
SELECT id, HEX(start), HEX(end), country_code, country
FROM networks
WHERE INET6_ATON('2c0f:ffe8:1234:5678:9101:1123::') & start = INET6_ATON('2c0f:ffe8::');
id	HEX(start)	HEX(end)	country_code	country
3	2C0FFFE8000000000000000000000000	2C0FFFE8FFFFFFFFFFFFFFFFFFFFFFFF	NG	Nigeria
SELECT id, HEX(start), HEX(end), country_code, country
FROM networks
WHERE INET6_ATON('2c0f:fff0::') | start = INET6_ATON('2c0f:fff0::');
id	HEX(start)	HEX(end)	country_code	country
1	2C0FFFF0000000000000000000000000	2C0FFFF0FFFFFFFFFFFFFFFFFFFFFFFF	NG	Nigeria
2	24051D00000000000000000000000000	24051D00FFFFFFFFFFFFFFFFFFFFFFFF	GR	Greenland
SELECT id, HEX(start), HEX(end), country_code, country
FROM  networks
WHERE INET6_ATON('2c0f:ffe8::') | start = INET6_ATON('2c0f:ffe8::');
id	HEX(start)	HEX(end)	country_code	country
2	24051D00000000000000000000000000	24051D00FFFFFFFFFFFFFFFFFFFFFFFF	GR	Greenland
3	2C0FFFE8000000000000000000000000	2C0FFFE8FFFFFFFFFFFFFFFFFFFFFFFF	NG	Nigeria
SELECT id, HEX(start), HEX(end), country_code, country
FROM networks
WHERE INET6_ATON('2c0f:fff0::') ^ start = INET6_ATON('::');
id	HEX(start)	HEX(end)	country_code	country
1	2C0FFFF0000000000000000000000000	2C0FFFF0FFFFFFFFFFFFFFFFFFFFFFFF	NG	Nigeria
SELECT id, HEX(start), HEX(end), country_code, country
FROM networks
WHERE INET6_ATON('2c0f:ffe8::') ^ start = INET6_ATON('::');
id	HEX(start)	HEX(end)	country_code	country
3	2C0FFFE8000000000000000000000000	2C0FFFE8FFFFFFFFFFFFFFFFFFFFFFFF	NG	Nigeria
DROP TABLE networks;
#
# Table containing columns of MySQL types
#
CREATE TABLE at(_bit bit(64),
_tin tinyint(8),
_boo bool,
_sms smallint signed,
_smu smallint unsigned,
_mes mediumint signed,
_meu mediumint unsigned,
_ins int signed,
_inu int unsigned,
_bis bigint signed,
_biu bigint unsigned,
_dec decimal (5,2),
_flo float,
_dou double,
_yea year,
_jsn json,
_chr char(12),
_vch varchar(12),
_bin binary(255),
_vbn varbinary(255),
_tbl tinyblob,
_ttx tinytext,
_blb blob,
_txt text,
_mbb mediumblob,
_mtx mediumtext,
_lbb longblob,
_ltx longtext,
_pnt point,
_dat date default '1988-12-15',
_dtt datetime default '2015-10-24 12:00:00',
_smp timestamp default '2015-10-24 14:00:00',
_tim time default' 07:08:09',
_enu enum('a', 'b', 'c'),
_set set('a', 'b', 'c')
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	4096	Delimiter ' ' in position 0 in datetime value ' 07:08:09' at row 1 is superfluous and is deprecated. Please remove.
INSERT INTO at (
_bit,
_tin,
_boo,
_sms,
_smu,
_mes,
_meu,
_ins,
_inu,
_bis,
_biu,
_dec,
_flo,
_dou,
_yea,
_jsn,
_chr,
_vch,
_bin,
_vbn,
_tbl,
_ttx,
_blb,
_txt,
_mbb,
_mtx,
_lbb,
_ltx,
_pnt,
_enu,
_set
) VALUES (
64,
64,
true,
64,
64,
64,
64,
64,
64,
64,
64,
64,
64,
64,
2005,
cast('{"a": 3}' as json),
"abcdefghijkl",
"abcdefghijkl",
x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFEBABE11111111',
x'CAFEBABE00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011111111CAFEBABE',
x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111',
"abcdefg",
x'cafebabe',
"abcdefg",
x'cafebabe',
"abcdefg",
x'cafebabe',
"abcdefg",
st_geomfromtext('point(1 1)'),
1,
1
);
#
# bitwise operations with an integer argument and mysql data-types
#
SELECT _bit | 2147483647 FROM at;
_bit | 2147483647
2147483647
SELECT _tin | 2147483647 FROM at;
_tin | 2147483647
2147483647
SELECT _boo | 2147483647 FROM at;
_boo | 2147483647
2147483647
SELECT _sms | 2147483647 FROM at;
_sms | 2147483647
2147483647
SELECT _smu | 2147483647 FROM at;
_smu | 2147483647
2147483647
SELECT _mes | 2147483647 FROM at;
_mes | 2147483647
2147483647
SELECT _meu | 2147483647 FROM at;
_meu | 2147483647
2147483647
SELECT _ins | 2147483647 FROM at;
_ins | 2147483647
2147483647
SELECT _inu | 2147483647 FROM at;
_inu | 2147483647
2147483647
SELECT _bis | 2147483647 FROM at;
_bis | 2147483647
2147483647
SELECT _biu | 2147483647 FROM at;
_biu | 2147483647
2147483647
SELECT _dec | 2147483647 FROM at;
_dec | 2147483647
2147483647
SELECT _flo | 2147483647 FROM at;
_flo | 2147483647
2147483647
SELECT _dou | 2147483647 FROM at;
_dou | 2147483647
2147483647
SELECT _yea | 2147483647 FROM at;
_yea | 2147483647
2147483647
SELECT _jsn | 2147483647 FROM at;
_jsn | 2147483647
2147483647
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column _jsn at row 1
SELECT _chr | 2147483647 FROM at;
_chr | 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _vch | 2147483647 FROM at;
_vch | 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _bin | 2147483647 FROM at;
_bin | 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '\xCA\xFE\xBA\xBE\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00'
SELECT _vbn | 2147483647 FROM at;
_vbn | 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '????'
SELECT _tbl | 2147483647 FROM at;
_tbl | 2147483647
2147483647
SELECT _ttx | 2147483647 FROM at;
_ttx | 2147483647
2147483647
SELECT _blb | 2147483647 FROM at;
_blb | 2147483647
2147483647
SELECT _txt | 2147483647 FROM at;
_txt | 2147483647
2147483647
SELECT _mbb | 2147483647 FROM at;
_mbb | 2147483647
2147483647
SELECT _mtx | 2147483647 FROM at;
_mtx | 2147483647
2147483647
SELECT _lbb | 2147483647 FROM at;
_lbb | 2147483647
2147483647
SELECT _ltx | 2147483647 FROM at;
_ltx | 2147483647
2147483647
SELECT _pnt | 2147483647 FROM at;
ERROR HY000: Incorrect arguments to |
SELECT _dat | 2147483647 FROM at;
_dat | 2147483647
2147483647
SELECT _dtt | 2147483647 FROM at;
_dtt | 2147483647
20151986552831
SELECT _smp | 2147483647 FROM at;
_smp | 2147483647
20151986552831
SELECT _tim | 2147483647 FROM at;
_tim | 2147483647
2147483647
SELECT _enu | 2147483647 FROM at;
_enu | 2147483647
2147483647
SELECT _set | 2147483647 FROM at;
_set | 2147483647
2147483647
SELECT _bit & 2147483647 FROM at;
_bit & 2147483647
64
SELECT _tin & 2147483647 FROM at;
_tin & 2147483647
64
SELECT _boo & 2147483647 FROM at;
_boo & 2147483647
1
SELECT _sms & 2147483647 FROM at;
_sms & 2147483647
64
SELECT _smu & 2147483647 FROM at;
_smu & 2147483647
64
SELECT _mes & 2147483647 FROM at;
_mes & 2147483647
64
SELECT _meu & 2147483647 FROM at;
_meu & 2147483647
64
SELECT _ins & 2147483647 FROM at;
_ins & 2147483647
64
SELECT _inu & 2147483647 FROM at;
_inu & 2147483647
64
SELECT _bis & 2147483647 FROM at;
_bis & 2147483647
64
SELECT _biu & 2147483647 FROM at;
_biu & 2147483647
64
SELECT _dec & 2147483647 FROM at;
_dec & 2147483647
64
SELECT _flo & 2147483647 FROM at;
_flo & 2147483647
64
SELECT _dou & 2147483647 FROM at;
_dou & 2147483647
64
SELECT _yea & 2147483647 FROM at;
_yea & 2147483647
2005
SELECT _jsn & 2147483647 FROM at;
_jsn & 2147483647
0
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column _jsn at row 1
SELECT _chr & 2147483647 FROM at;
_chr & 2147483647
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _vch & 2147483647 FROM at;
_vch & 2147483647
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _bin & 2147483647 FROM at;
_bin & 2147483647
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '\xCA\xFE\xBA\xBE\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00'
SELECT _vbn & 2147483647 FROM at;
_vbn & 2147483647
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '????'
SELECT _tbl & 2147483647 FROM at;
_tbl & 2147483647
0
SELECT _ttx & 2147483647 FROM at;
_ttx & 2147483647
0
SELECT _blb & 2147483647 FROM at;
_blb & 2147483647
0
SELECT _txt & 2147483647 FROM at;
_txt & 2147483647
0
SELECT _mbb & 2147483647 FROM at;
_mbb & 2147483647
0
SELECT _mtx & 2147483647 FROM at;
_mtx & 2147483647
0
SELECT _lbb & 2147483647 FROM at;
_lbb & 2147483647
0
SELECT _ltx & 2147483647 FROM at;
_ltx & 2147483647
0
SELECT _pnt & 2147483647 FROM at;
ERROR HY000: Incorrect arguments to &
SELECT _dat & 2147483647 FROM at;
_dat & 2147483647
19881215
SELECT _dtt & 2147483647 FROM at;
_dtt & 2147483647
1185050816
SELECT _smp & 2147483647 FROM at;
_smp & 2147483647
1185070816
SELECT _tim & 2147483647 FROM at;
_tim & 2147483647
70809
SELECT _enu & 2147483647 FROM at;
_enu & 2147483647
1
SELECT _set & 2147483647 FROM at;
_set & 2147483647
1
SELECT _bit ^ 2147483647 FROM at;
_bit ^ 2147483647
2147483583
SELECT _tin ^ 2147483647 FROM at;
_tin ^ 2147483647
2147483583
SELECT _boo ^ 2147483647 FROM at;
_boo ^ 2147483647
2147483646
SELECT _sms ^ 2147483647 FROM at;
_sms ^ 2147483647
2147483583
SELECT _smu ^ 2147483647 FROM at;
_smu ^ 2147483647
2147483583
SELECT _mes ^ 2147483647 FROM at;
_mes ^ 2147483647
2147483583
SELECT _meu ^ 2147483647 FROM at;
_meu ^ 2147483647
2147483583
SELECT _ins ^ 2147483647 FROM at;
_ins ^ 2147483647
2147483583
SELECT _inu ^ 2147483647 FROM at;
_inu ^ 2147483647
2147483583
SELECT _bis ^ 2147483647 FROM at;
_bis ^ 2147483647
2147483583
SELECT _biu ^ 2147483647 FROM at;
_biu ^ 2147483647
2147483583
SELECT _dec ^ 2147483647 FROM at;
_dec ^ 2147483647
2147483583
SELECT _flo ^ 2147483647 FROM at;
_flo ^ 2147483647
2147483583
SELECT _dou ^ 2147483647 FROM at;
_dou ^ 2147483647
2147483583
SELECT _yea ^ 2147483647 FROM at;
_yea ^ 2147483647
2147481642
SELECT _jsn ^ 2147483647 FROM at;
_jsn ^ 2147483647
2147483647
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column _jsn at row 1
SELECT _chr ^ 2147483647 FROM at;
_chr ^ 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _vch ^ 2147483647 FROM at;
_vch ^ 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _bin ^ 2147483647 FROM at;
_bin ^ 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '\xCA\xFE\xBA\xBE\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00'
SELECT _vbn ^ 2147483647 FROM at;
_vbn ^ 2147483647
2147483647
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '????'
SELECT _tbl ^ 2147483647 FROM at;
_tbl ^ 2147483647
2147483647
SELECT _ttx ^ 2147483647 FROM at;
_ttx ^ 2147483647
2147483647
SELECT _blb ^ 2147483647 FROM at;
_blb ^ 2147483647
2147483647
SELECT _txt ^ 2147483647 FROM at;
_txt ^ 2147483647
2147483647
SELECT _mbb ^ 2147483647 FROM at;
_mbb ^ 2147483647
2147483647
SELECT _mtx ^ 2147483647 FROM at;
_mtx ^ 2147483647
2147483647
SELECT _lbb ^ 2147483647 FROM at;
_lbb ^ 2147483647
2147483647
SELECT _ltx ^ 2147483647 FROM at;
_ltx ^ 2147483647
2147483647
SELECT _pnt ^ 2147483647 FROM at;
ERROR HY000: Incorrect arguments to ^
SELECT _dat ^ 2147483647 FROM at;
_dat ^ 2147483647
2127602432
SELECT _dtt ^ 2147483647 FROM at;
_dtt ^ 2147483647
20150801502015
SELECT _smp ^ 2147483647 FROM at;
_smp ^ 2147483647
20150801482015
SELECT _tim ^ 2147483647 FROM at;
_tim ^ 2147483647
2147412838
SELECT _enu ^ 2147483647 FROM at;
_enu ^ 2147483647
2147483646
SELECT _set ^ 2147483647 FROM at;
_set ^ 2147483647
2147483646
#
# bitwise operations with a hex literal argument and mysql data-types
#
SELECT _bit | x'cafebabe' FROM at;
_bit | x'cafebabe'
3405691646
SELECT _tin | x'cafebabe' FROM at;
_tin | x'cafebabe'
3405691646
SELECT _boo | x'cafebabe' FROM at;
_boo | x'cafebabe'
3405691583
SELECT _sms | x'cafebabe' FROM at;
_sms | x'cafebabe'
3405691646
SELECT _smu | x'cafebabe' FROM at;
_smu | x'cafebabe'
3405691646
SELECT _mes | x'cafebabe' FROM at;
_mes | x'cafebabe'
3405691646
SELECT _meu | x'cafebabe' FROM at;
_meu | x'cafebabe'
3405691646
SELECT _ins | x'cafebabe' FROM at;
_ins | x'cafebabe'
3405691646
SELECT _inu | x'cafebabe' FROM at;
_inu | x'cafebabe'
3405691646
SELECT _bis | x'cafebabe' FROM at;
_bis | x'cafebabe'
3405691646
SELECT _biu | x'cafebabe' FROM at;
_biu | x'cafebabe'
3405691646
SELECT _dec | x'cafebabe' FROM at;
_dec | x'cafebabe'
3405691646
SELECT _flo | x'cafebabe' FROM at;
_flo | x'cafebabe'
3405691646
SELECT _dou | x'cafebabe' FROM at;
_dou | x'cafebabe'
3405691646
SELECT _yea | x'cafebabe' FROM at;
_yea | x'cafebabe'
3405692927
SELECT _jsn | x'cafebabe' FROM at;
_jsn | x'cafebabe'
3405691582
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column _jsn at row 1
SELECT _chr | x'cafebabe' FROM at;
_chr | x'cafebabe'
3405691582
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _vch | x'cafebabe' FROM at;
_vch | x'cafebabe'
3405691582
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT hex(_bin | x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_bin | x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFEBBBFDBFF1111
SELECT _bin | x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT hex(_vbn | x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_vbn | x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DBFF1111CAFEBBBF
SELECT _vbn | x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT hex(_tbl | x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_tbl | x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111
SELECT _tbl | x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT _ttx | x'cafebabe' FROM at;
_ttx | x'cafebabe'
3405691582
SELECT hex(_blb | x'cafebabe') FROM at;
hex(_blb | x'cafebabe')
CAFEBABE
SELECT _txt | x'cafebabe' FROM at;
_txt | x'cafebabe'
3405691582
SELECT hex(_mbb | x'cafebabe') FROM at;
hex(_mbb | x'cafebabe')
CAFEBABE
SELECT _mtx | x'cafebabe' FROM at;
_mtx | x'cafebabe'
3405691582
SELECT hex(_lbb | x'cafebabe') FROM at;
hex(_lbb | x'cafebabe')
CAFEBABE
SELECT _ltx | x'cafebabe' FROM at;
_ltx | x'cafebabe'
3405691582
SELECT _pnt | x'cafebabe' FROM at;
ERROR HY000: Incorrect arguments to |
SELECT _dat | x'cafebabe' FROM at;
_dat | x'cafebabe'
3422551807
SELECT _dtt | x'cafebabe' FROM at;
_dtt | x'cafebabe'
20151164402430
SELECT _smp | x'cafebabe' FROM at;
_smp | x'cafebabe'
20151164387070
SELECT _tim | x'cafebabe' FROM at;
_tim | x'cafebabe'
3405758143
SELECT _enu | x'cafebabe' FROM at;
_enu | x'cafebabe'
3405691583
SELECT _set | x'cafebabe' FROM at;
_set | x'cafebabe'
3405691583
SELECT _bit & x'cafebabe' FROM at;
_bit & x'cafebabe'
0
SELECT _tin & x'cafebabe' FROM at;
_tin & x'cafebabe'
0
SELECT _boo & x'cafebabe' FROM at;
_boo & x'cafebabe'
0
SELECT _sms & x'cafebabe' FROM at;
_sms & x'cafebabe'
0
SELECT _smu & x'cafebabe' FROM at;
_smu & x'cafebabe'
0
SELECT _mes & x'cafebabe' FROM at;
_mes & x'cafebabe'
0
SELECT _meu & x'cafebabe' FROM at;
_meu & x'cafebabe'
0
SELECT _ins & x'cafebabe' FROM at;
_ins & x'cafebabe'
0
SELECT _inu & x'cafebabe' FROM at;
_inu & x'cafebabe'
0
SELECT _bis & x'cafebabe' FROM at;
_bis & x'cafebabe'
0
SELECT _biu & x'cafebabe' FROM at;
_biu & x'cafebabe'
0
SELECT _dec & x'cafebabe' FROM at;
_dec & x'cafebabe'
0
SELECT _flo & x'cafebabe' FROM at;
_flo & x'cafebabe'
0
SELECT _dou & x'cafebabe' FROM at;
_dou & x'cafebabe'
0
SELECT _yea & x'cafebabe' FROM at;
_yea & x'cafebabe'
660
SELECT _jsn & x'cafebabe' FROM at;
_jsn & x'cafebabe'
0
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column _jsn at row 1
SELECT _chr & x'cafebabe' FROM at;
_chr & x'cafebabe'
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _vch & x'cafebabe' FROM at;
_vch & x'cafebabe'
0
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT hex(_bin & x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_bin & x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE101000101111
SELECT _bin & x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT hex(_vbn & x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_vbn & x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
CAFEBABE00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000101111CAFE1010
SELECT _vbn & x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT hex(_tbl & x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_tbl & x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111
SELECT _tbl & x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT _ttx & x'cafebabe' FROM at;
_ttx & x'cafebabe'
0
SELECT hex(_blb & x'cafebabe') FROM at;
hex(_blb & x'cafebabe')
CAFEBABE
SELECT _txt & x'cafebabe' FROM at;
_txt & x'cafebabe'
0
SELECT hex(_mbb & x'cafebabe') FROM at;
hex(_mbb & x'cafebabe')
CAFEBABE
SELECT _mtx & x'cafebabe' FROM at;
_mtx & x'cafebabe'
0
SELECT hex(_lbb & x'cafebabe') FROM at;
hex(_lbb & x'cafebabe')
CAFEBABE
SELECT _ltx & x'cafebabe' FROM at;
_ltx & x'cafebabe'
0
SELECT _pnt & x'cafebabe' FROM at;
ERROR HY000: Incorrect arguments to &
SELECT _dat & x'cafebabe' FROM at;
_dat & x'cafebabe'
3020990
SELECT _dtt & x'cafebabe' FROM at;
_dtt & x'cafebabe'
3265409152
SELECT _smp & x'cafebabe' FROM at;
_smp & x'cafebabe'
3265444512
SELECT _tim & x'cafebabe' FROM at;
_tim & x'cafebabe'
4248
SELECT _enu & x'cafebabe' FROM at;
_enu & x'cafebabe'
0
SELECT _set & x'cafebabe' FROM at;
_set & x'cafebabe'
0
SELECT _bit ^ x'cafebabe' FROM at;
_bit ^ x'cafebabe'
3405691646
SELECT _tin ^ x'cafebabe' FROM at;
_tin ^ x'cafebabe'
3405691646
SELECT _boo ^ x'cafebabe' FROM at;
_boo ^ x'cafebabe'
3405691583
SELECT _sms ^ x'cafebabe' FROM at;
_sms ^ x'cafebabe'
3405691646
SELECT _smu ^ x'cafebabe' FROM at;
_smu ^ x'cafebabe'
3405691646
SELECT _mes ^ x'cafebabe' FROM at;
_mes ^ x'cafebabe'
3405691646
SELECT _meu ^ x'cafebabe' FROM at;
_meu ^ x'cafebabe'
3405691646
SELECT _ins ^ x'cafebabe' FROM at;
_ins ^ x'cafebabe'
3405691646
SELECT _inu ^ x'cafebabe' FROM at;
_inu ^ x'cafebabe'
3405691646
SELECT _bis ^ x'cafebabe' FROM at;
_bis ^ x'cafebabe'
3405691646
SELECT _biu ^ x'cafebabe' FROM at;
_biu ^ x'cafebabe'
3405691646
SELECT _dec ^ x'cafebabe' FROM at;
_dec ^ x'cafebabe'
3405691646
SELECT _flo ^ x'cafebabe' FROM at;
_flo ^ x'cafebabe'
3405691646
SELECT _dou ^ x'cafebabe' FROM at;
_dou ^ x'cafebabe'
3405691646
SELECT _yea ^ x'cafebabe' FROM at;
_yea ^ x'cafebabe'
3405692267
SELECT _jsn ^ x'cafebabe' FROM at;
_jsn ^ x'cafebabe'
3405691582
Warnings:
Warning	3156	Invalid JSON value for CAST to INTEGER from column _jsn at row 1
SELECT _chr ^ x'cafebabe' FROM at;
_chr ^ x'cafebabe'
3405691582
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT _vch ^ x'cafebabe' FROM at;
_vch ^ x'cafebabe'
3405691582
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'abcdefghijkl'
SELECT hex(_bin ^ x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_bin ^ x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000ABAFDBEF0000
SELECT _bin ^ x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT hex(_vbn ^ x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_vbn ^ x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DBEF00000000ABAF
SELECT _vbn ^ x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT hex(_tbl ^ x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFE1111CAFE1111') FROM at;
hex(_tbl ^ x'CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
SELECT _tbl ^ x'cafebabe' FROM at;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT _ttx ^ x'cafebabe' FROM at;
_ttx ^ x'cafebabe'
3405691582
SELECT hex(_blb ^ x'cafebabe') FROM at;
hex(_blb ^ x'cafebabe')
00000000
SELECT _txt ^ x'cafebabe' FROM at;
_txt ^ x'cafebabe'
3405691582
SELECT hex(_mbb ^ x'cafebabe') FROM at;
hex(_mbb ^ x'cafebabe')
00000000
SELECT _mtx ^ x'cafebabe' FROM at;
_mtx ^ x'cafebabe'
3405691582
SELECT hex(_lbb ^ x'cafebabe') FROM at;
hex(_lbb ^ x'cafebabe')
00000000
SELECT _ltx ^ x'cafebabe' FROM at;
_ltx ^ x'cafebabe'
3405691582
SELECT _pnt ^ x'cafebabe' FROM at;
ERROR HY000: Incorrect arguments to ^
SELECT _dat ^ x'cafebabe' FROM at;
_dat ^ x'cafebabe'
3419530817
SELECT _dtt ^ x'cafebabe' FROM at;
_dtt ^ x'cafebabe'
20147898993278
SELECT _smp ^ x'cafebabe' FROM at;
_smp ^ x'cafebabe'
20147898942558
SELECT _tim ^ x'cafebabe' FROM at;
_tim ^ x'cafebabe'
3405753895
SELECT _enu ^ x'cafebabe' FROM at;
_enu ^ x'cafebabe'
3405691583
SELECT _set ^ x'cafebabe' FROM at;
_set ^ x'cafebabe'
3405691583
#
# bitwise operations with binary and varbinary arguments
#
SELECT HEX(_bin & _bin), HEX(_bin & _vbn), HEX(_vbn & _bin), HEX(_vbn & _vbn) FROM at;
HEX(_bin & _bin)	HEX(_bin & _vbn)	HEX(_vbn & _bin)	HEX(_vbn & _vbn)
CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFEBABE11111111	CAFEBABE0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010101000101010	CAFEBABE0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010101000101010	CAFEBABE00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011111111CAFEBABE
SELECT HEX(_bin | _bin), HEX(_bin | _vbn), HEX(_vbn | _bin), HEX(_vbn | _vbn) FROM at;
HEX(_bin | _bin)	HEX(_bin | _vbn)	HEX(_vbn | _bin)	HEX(_vbn | _vbn)
CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CAFEBABE11111111	CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DBFFBBBFDBFFBBBF	CAFEBABE000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DBFFBBBFDBFFBBBF	CAFEBABE00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011111111CAFEBABE
SELECT HEX(_bin ^ _bin), HEX(_bin ^ _vbn), HEX(_vbn ^ _bin), HEX(_vbn ^ _vbn) FROM at;
HEX(_bin ^ _bin)	HEX(_bin ^ _vbn)	HEX(_vbn ^ _bin)	HEX(_vbn ^ _vbn)
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DBEFABAFDBEFABAF	00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DBEFABAFDBEFABAF	000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
DROP TABLE at;
#
# Test of bitwise aggregate functions on BINARY
#
CREATE TABLE t1 (a int, b VARBINARY(6));
INSERT INTO t1 VALUES (1,null);
INSERT INTO t1 VALUES (1,null);
INSERT INTO t1 VALUES (2,null);
SELECT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
a	HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
1	FFFFFFFFFFFF	000000000000	000000000000
2	FFFFFFFFFFFF	000000000000	000000000000
SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
FFFFFFFFFFFF	000000000000	000000000000
INSERT INTO t1 VALUES (2,0x12345678901);
SELECT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
a	HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
1	FFFFFFFFFFFF	000000000000	000000000000
2	012345678901	012345678901	012345678901
SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
012345678901	012345678901	012345678901
SELECT SQL_BIG_RESULT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
a	HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
1	FFFFFFFFFFFF	000000000000	000000000000
2	012345678901	012345678901	012345678901
INSERT INTO t1 VALUES (3,0xabcdef);
SELECT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
a	HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
1	FFFFFFFFFFFF	000000000000	000000000000
2	012345678901	012345678901	012345678901
3	ABCDEF	ABCDEF	ABCDEF
SELECT SQL_BIG_RESULT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
a	HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
1	FFFFFFFFFFFF	000000000000	000000000000
2	012345678901	012345678901	012345678901
3	ABCDEF	ABCDEF	ABCDEF
EXPLAIN SELECT SQL_BIG_RESULT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
Warnings:
Note	1003	/* select#1 */ select sql_big_result `test`.`t1`.`a` AS `a`,hex(bit_and(`test`.`t1`.`b`)) AS `HEX(BIT_AND(b))`,hex(bit_or(`test`.`t1`.`b`)) AS `HEX(BIT_OR(b))`,hex(bit_xor(`test`.`t1`.`b`)) AS `HEX(BIT_XOR(b))` from `test`.`t1` group by `test`.`t1`.`a`
INSERT INTO t1 VALUES (2,0x11347290158), (3, 0x149032);
SELECT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
a	HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
1	FFFFFFFFFFFF	000000000000	000000000000
2	010345210100	0133476F8959	0030024E8859
3	008022	BFDDFF	BF5DDD
SELECT SQL_BIG_RESULT a, HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
a	HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
1	FFFFFFFFFFFF	000000000000	000000000000
2	010345210100	0133476F8959	0030024E8859
3	008022	BFDDFF	BF5DDD
DROP TABLE t1;
CREATE TABLE t(a varbinary(10));
INSERT INTO t VALUES(0xFF00F0F0), (0xF0F0FF00);
# bitwise operations result as raw binary
SELECT BIT_AND(a) FROM t;
SELECT BIT_OR(a) FROM t;
SELECT BIT_XOR(a) FROM t;
SELECT HEX(BIT_AND(a)) FROM t;
HEX(BIT_AND(a))
F000F000
SELECT HEX(BIT_OR(a)) FROM t;
HEX(BIT_OR(a))
FFF0FFF0
SELECT HEX(BIT_XOR(a)) FROM t;
HEX(BIT_XOR(a))
0FF00FF0
truncate table t;
INSERT INTO t VALUES(NULL);
SELECT HEX(BIT_AND(a)) FROM t;
HEX(BIT_AND(a))
FFFFFFFFFFFFFFFFFFFF
SELECT HEX(BIT_OR(a)) FROM t;
HEX(BIT_OR(a))
00000000000000000000
SELECT HEX(BIT_XOR(a)) FROM t;
HEX(BIT_XOR(a))
00000000000000000000
truncate table t;
INSERT INTO t VALUES(NULL), (0xFF00F0F0), (0xF0F0FF00);
SELECT HEX(BIT_AND(a)) FROM t;
HEX(BIT_AND(a))
F000F000
SELECT HEX(BIT_OR(a)) FROM t;
HEX(BIT_OR(a))
FFF0FFF0
SELECT HEX(BIT_XOR(a)) FROM t;
HEX(BIT_XOR(a))
0FF00FF0
truncate table t;
INSERT INTO t VALUES(0xFF00F0F0), (0xF0F0FF00), (NULL);
SELECT HEX(BIT_AND(a)) FROM t;
HEX(BIT_AND(a))
F000F000
SELECT HEX(BIT_OR(a)) FROM t;
HEX(BIT_OR(a))
FFF0FFF0
SELECT HEX(BIT_XOR(a)) FROM t;
HEX(BIT_XOR(a))
0FF00FF0
truncate table t;
INSERT INTO t VALUES(0xFF00F0F0), (NULL), (0xF0F0FF00);
SELECT HEX(BIT_AND(a)) FROM t;
HEX(BIT_AND(a))
F000F000
SELECT HEX(BIT_OR(a)) FROM t;
HEX(BIT_OR(a))
FFF0FFF0
SELECT HEX(BIT_XOR(a)) FROM t;
HEX(BIT_XOR(a))
0FF00FF0
# Explicitly request aggregation through temporary table
# (force use of Item_sum_*::copy_or_same())
SELECT SQL_BUFFER_RESULT HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a)) FROM t;
HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX(BIT_XOR(a))
F000F000	FFF0FFF0	0FF00FF0
DROP TABLE t;
#
# bitwise aggregate functions having arguments with different sizes
#
CREATE TABLE t(group_id int, a varbinary(10));
INSERT INTO t VALUES(1, 0xFF00F0F0), (1, 0xFF00);
SELECT HEX(BIT_AND(lpad(a, 10, 0x00))) FROM t;
HEX(BIT_AND(lpad(a, 10, 0x00)))
0000000000000000F000
SELECT BIT_AND(a) FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT BIT_OR(a) FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT BIT_XOR(a) FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s1 FROM "SELECT group_id, HEX(BIT_AND(a)) FROM t GROUP BY group_id";
EXECUTE s1;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s2 FROM "SELECT group_id, HEX(BIT_OR(a)) FROM t GROUP BY group_id";
EXECUTE s2;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s3 FROM "SELECT group_id, HEX(BIT_XOR(a)) FROM t GROUP BY group_id";
EXECUTE s3;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s4 FROM "SELECT HEX(BIT_AND(a)) FROM t";
EXECUTE s4;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s5 FROM "SELECT HEX(BIT_OR(a)) FROM t";
EXECUTE s5;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s6 FROM "SELECT HEX(BIT_XOR(a)) FROM t";
EXECUTE s6;
ERROR HY000: Binary operands of bitwise operators must be of equal length
truncate table t;
INSERT INTO t VALUES(1, 0xFF00), (1, 0xFF00F0F0);
SELECT BIT_AND(a) FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT BIT_OR(a) FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT BIT_XOR(a) FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s1 FROM "SELECT group_id, HEX(BIT_AND(a)) FROM t GROUP BY group_id";
EXECUTE s1;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s2 FROM "SELECT group_id, HEX(BIT_OR(a)) FROM t GROUP BY group_id";
EXECUTE s2;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s3 FROM "SELECT group_id, HEX(BIT_XOR(a)) FROM t GROUP BY group_id";
EXECUTE s3;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s4 FROM "SELECT HEX(BIT_AND(a)) FROM t";
EXECUTE s4;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s5 FROM "SELECT HEX(BIT_OR(a)) FROM t";
EXECUTE s5;
ERROR HY000: Binary operands of bitwise operators must be of equal length
PREPARE s6 FROM "SELECT HEX(BIT_XOR(a)) FROM t";
EXECUTE s6;
ERROR HY000: Binary operands of bitwise operators must be of equal length
truncate table t;
# check group 5 results with hex literals
SELECT
HEX(0xABCDEF & 0x123456 & 0x789123),
HEX(0xABCDEF | 0x123456 | 0x789123),
HEX(0xABCDEF ^ 0x123456 ^ 0x789123);
HEX(0xABCDEF & 0x123456 & 0x789123)	HEX(0xABCDEF | 0x123456 | 0x789123)	HEX(0xABCDEF ^ 0x123456 ^ 0x789123)
2	FBFDFF	C1689A
INSERT INTO t(group_id, a) VALUES
(1, 0x34567101ABFF00F0F0),
(2, NULL),
(1, 0x34567102ABF0F0F0F0),
(3, 0x34567104ABF0F0F0F0),
(1, 0x34567103ABFF00F0F0),
(5, 0xABCDEF),
(5, 0x123456),
(5, 0x789123),
(1, 0x34567104ABF0F0F0F0),
(4, 0x34567100ABF0F0F0F0),
(4, NULL),
(4, 0x34567101ABFF00F0F0);
#
# aggregate functions
#
SELECT group_id, HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a))
FROM t
GROUP BY group_id;
group_id	HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX(BIT_XOR(a))
1	34567100ABF000F0F0	34567107ABFFF0F0F0	000000040000000000
2	FFFFFFFFFFFFFFFFFFFF	00000000000000000000	00000000000000000000
3	34567104ABF0F0F0F0	34567104ABF0F0F0F0	34567104ABF0F0F0F0
4	34567100ABF000F0F0	34567101ABFFF0F0F0	00000001000FF00000
5	000002	FBFDFF	C1689A
SELECT SQL_BUFFER_RESULT group_id, HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a))
FROM t
GROUP BY group_id;
group_id	HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX(BIT_XOR(a))
1	34567100ABF000F0F0	34567107ABFFF0F0F0	000000040000000000
2	FFFFFFFFFFFFFFFFFFFF	00000000000000000000	00000000000000000000
3	34567104ABF0F0F0F0	34567104ABF0F0F0F0	34567104ABF0F0F0F0
4	34567100ABF000F0F0	34567101ABFFF0F0F0	00000001000FF00000
5	000002	FBFDFF	C1689A
SELECT group_id, HEX(BIT_AND(a)), BIT_AND(192), BIT_AND(0x303233), BIT_AND(binary "foo")
FROM t
GROUP BY group_id;
group_id	HEX(BIT_AND(a))	BIT_AND(192)	BIT_AND(0x303233)	BIT_AND(binary "foo")
1	34567100ABF000F0F0	192	3158579	foo
2	FFFFFFFFFFFFFFFFFFFF	192	3158579	foo
3	34567104ABF0F0F0F0	192	3158579	foo
4	34567100ABF000F0F0	192	3158579	foo
5	000002	192	3158579	foo
Warning	1287	'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
Warnings:
SELECT BIT_COUNT(group_id), BIT_COUNT(a), BIT_COUNT(192), BIT_COUNT(0x303233),
BIT_COUNT(binary "foo"), BIT_COUNT(NULL)
FROM t;
BIT_COUNT(group_id)	BIT_COUNT(a)	BIT_COUNT(192)	BIT_COUNT(0x303233)	BIT_COUNT(binary "foo")	BIT_COUNT(NULL)
1	32	2	9	16	NULL
1	33	2	9	16	NULL
1	33	2	9	16	NULL
1	33	2	9	16	NULL
1	33	2	9	16	NULL
1	34	2	9	16	NULL
1	NULL	2	9	16	NULL
1	NULL	2	9	16	NULL
2	10	2	9	16	NULL
2	17	2	9	16	NULL
2	33	2	9	16	NULL
2	9	2	9	16	NULL
Warning	1287	'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
Warnings:
#
# aggregate functions in prepared statements
#
PREPARE s1
FROM "SELECT HEX(BIT_AND(a)),HEX(BIT_OR(a)),HEX(BIT_XOR(a)) FROM t WHERE group_id = 5";
EXECUTE s1;
HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX(BIT_XOR(a))
000002	FBFDFF	C1689A
EXECUTE s1;
HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX(BIT_XOR(a))
000002	FBFDFF	C1689A
PREPARE s2
FROM "SELECT group_id, HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX (BIT_XOR(a)) FROM t GROUP BY group_id";
EXECUTE s2;
group_id	HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX (BIT_XOR(a))
1	34567100ABF000F0F0	34567107ABFFF0F0F0	000000040000000000
2	FFFFFFFFFFFFFFFFFFFF	00000000000000000000	00000000000000000000
3	34567104ABF0F0F0F0	34567104ABF0F0F0F0	34567104ABF0F0F0F0
4	34567100ABF000F0F0	34567101ABFFF0F0F0	00000001000FF00000
5	000002	FBFDFF	C1689A
EXECUTE s2;
group_id	HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX (BIT_XOR(a))
1	34567100ABF000F0F0	34567107ABFFF0F0F0	000000040000000000
2	FFFFFFFFFFFFFFFFFFFF	00000000000000000000	00000000000000000000
3	34567104ABF0F0F0F0	34567104ABF0F0F0F0	34567104ABF0F0F0F0
4	34567100ABF000F0F0	34567101ABFFF0F0F0	00000001000FF00000
5	000002	FBFDFF	C1689A
TRUNCATE TABLE t;
#
# testing conversions
#
# Conversion from string to int
SELECT HEX(_binary 0x0003 << (_binary 0x38 | 0x38));
HEX(_binary 0x0003 << (_binary 0x38 | 0x38))
0300
# Conversion from invalid string to int
SELECT HEX(_binary 0x0003 << (_binary 0x40 | 0x40));
HEX(_binary 0x0003 << (_binary 0x40 | 0x40))
0003
# Conversion to String from int
SELECT CONCAT("M", (0x39 | 0x39));
CONCAT("M", (0x39 | 0x39))
M57
CREATE TABLE t2(gid int, a int);
INSERT INTO t2(gid, a) VALUES (1, 1), (1, 2), (2, 4), (2, 8);
# Conversion to String from int
SELECT CONCAT("M" ,BIT_OR(a)) FROM t2;
CONCAT("M" ,BIT_OR(a))
M15
SELECT CONCAT("M" ,BIT_OR(a)) FROM t2 GROUP BY gid;
CONCAT("M" ,BIT_OR(a))
M3
M12
SELECT BIT_OR(a) FROM t2;
BIT_OR(a)
15
# Conversion to decimal from int
SELECT 1.0 * BIT_OR(a) FROM t2;
1.0 * BIT_OR(a)
15.0
SELECT gid, BIT_OR(a) FROM t2 GROUP BY gid;
gid	BIT_OR(a)
1	3
2	12
# Conversion to decimal from int
SELECT gid, 1.0 * BIT_OR(a) FROM t2 GROUP BY gid;
gid	1.0 * BIT_OR(a)
1	3.0
2	12.0
DROP TABLE t2;
INSERT INTO t(group_id, a) VALUES
(1, NULL),
(1, 0x312E35),
(1, 0x312E35),
(4, 0x312E38),
(4, NULL),
(4, 0x312E38),
(5, 0x31),
(5, 0x31);
# testing conversion to real
SELECT
group_id,
1.0 * BIT_AND(a),
1.0 * BIT_OR(a)
FROM t
WHERE group_id != 5
GROUP BY group_id;
group_id	1.0 * BIT_AND(a)	1.0 * BIT_OR(a)
1	1.5	1.5
4	1.8	1.8
# testing conversion to int
SELECT
group_id,
0x30 << BIT_AND(a),
0x30 << BIT_OR(a)
FROM t
WHERE group_id = 5
GROUP BY group_id;
group_id	0x30 << BIT_AND(a)	0x30 << BIT_OR(a)
5	96	96
# testing conversion to str
SELECT
group_id,
CONCAT('My', BIT_AND(a)),
CONCAT('My', BIT_OR(a))
FROM t
GROUP BY group_id;
group_id	CONCAT('My', BIT_AND(a))	CONCAT('My', BIT_OR(a))
1	My1.5	My1.5
4	My1.8	My1.8
5	My1	My1
# testing conversion to int with invalid values
SELECT
group_id,
0x30 << BIT_XOR(a)
FROM t
GROUP BY group_id;
group_id	0x30 << BIT_XOR(a)
1	48
4	48
5	48
# testing conversion to real with invalid values
SELECT
group_id,
1.0 * BIT_XOR(a)
FROM t
GROUP BY group_id;
group_id	1.0 * BIT_XOR(a)
1	0.0
4	0.0
5	0.0
TRUNCATE TABLE t;
INSERT INTO t(group_id, a) VALUES
(1, 0x7BAFBF),
(1, 0x4A818A),
(4, 0xFFFEFF),
(4, NULL),
(4, 0xCED0C7),
(5, 0xFB),
(5, 0xCA);
# testing conversion to real/str
SELECT
group_id,
1.0 * BIT_XOR(a),
CONCAT('My', BIT_XOR(a))
FROM t
GROUP BY group_id;
group_id	1.0 * BIT_XOR(a)	CONCAT('My', BIT_XOR(a))
1	1.5	My1.5
4	1.8	My1.8
5	1.0	My1
# testing conversion to int
SELECT
group_id,
0x30 << BIT_XOR(a)
FROM t
WHERE group_id = 5
GROUP BY group_id;
group_id	0x30 << BIT_XOR(a)
5	96
# testing conversion to real with invalid values
SELECT
group_id,
1.0 * BIT_AND(a)
FROM t
GROUP BY group_id;
group_id	1.0 * BIT_AND(a)
1	0.0
4	0.0
5	0.0
# testing conversion to int
SELECT
group_id,
0x30 << BIT_AND(a)
FROM t
GROUP BY group_id;
group_id	0x30 << BIT_AND(a)
1	48
4	48
5	48
# testing conversion to real with invalid values
SELECT
group_id,
1.0 * BIT_OR(a)
FROM t
GROUP BY group_id;
group_id	1.0 * BIT_OR(a)
1	0.0
4	0.0
5	0.0
# testing conversion to int
SELECT
group_id,
0x30 << BIT_OR(a)
FROM t
GROUP BY group_id;
group_id	0x30 << BIT_OR(a)
1	48
4	48
5	48
DROP table t;
SET @a:= b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001';
SELECT BIT_COUNT(@a);
BIT_COUNT(@a)
13
SELECT BIT_COUNT(_binary 0x221040808040100200200100);
BIT_COUNT(_binary 0x221040808040100200200100)
11
CREATE TABLE t(b BLOB);
INSERT INTO t VALUES(NULL);
SELECT BIT_COUNT(BIT_AND(b)) FROM t GROUP BY b;
ERROR HY000: Aggregate bitwise functions cannot accept arguments longer than 511 bytes; consider using the SUBSTRING() function
SELECT BIT_COUNT(BIT_AND(CAST(b AS BINARY(500)))) FROM t GROUP BY b;
BIT_COUNT(BIT_AND(CAST(b AS BINARY(500))))
4000
SELECT BIT_COUNT(BIT_AND(SUBSTRING(b,1,500))) FROM t GROUP BY b;
BIT_COUNT(BIT_AND(SUBSTRING(b,1,500)))
4000
INSERT INTO t VALUES(REPEAT(x'0a',1000));
SELECT BIT_COUNT(BIT_AND(CAST(b AS BINARY(500)))) FROM t GROUP BY b;
BIT_COUNT(BIT_AND(CAST(b AS BINARY(500))))
4000
1000
Warnings:
Warning	1292	Truncated incorrect BINARY(500) value: '\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A\x0A'
SELECT BIT_COUNT(BIT_AND(SUBSTRING(b,1,500))) FROM t GROUP BY b;
BIT_COUNT(BIT_AND(SUBSTRING(b,1,500)))
4000
1000
DROP TABLE t;
SET @a:= b'1010010001000010000010000001000000010000000010000000001000000000010000000000010000000000001';
SELECT LENGTH(@a), HEX(@a);
LENGTH(@a)	HEX(@a)
12	052210408080401002002001
SELECT * FROM r ORDER by c;
c	sl	sr
0	052210408080401002002001	052210408080401002002001
1	0A4420810100802004004002	029108204040200801001000
2	148841020201004008008004	014884102020100400800800
3	291082040402008010010008	00A442081010080200400400
4	522104080804010020020010	005221040808040100200200
5	A44208101008020040040020	002910820404020080100100
6	488410202010040080080040	001488410202010040080080
7	910820404020080100100080	000A44208101008020040040
8	221040808040100200200100	000522104080804010020020
9	442081010080200400400200	000291082040402008010010
10	884102020100400800800400	000148841020201004008008
11	108204040200801001000800	0000A4420810100802004004
12	210408080401002002001000	000052210408080401002002
13	420810100802004004002000	000029108204040200801001
14	841020201004008008004000	000014884102020100400800
15	082040402008010010008000	00000A442081010080200400
16	104080804010020020010000	000005221040808040100200
17	208101008020040040020000	000002910820404020080100
18	410202010040080080040000	000001488410202010040080
19	820404020080100100080000	000000A44208101008020040
20	040808040100200200100000	000000522104080804010020
21	081010080200400400200000	000000291082040402008010
22	102020100400800800400000	000000148841020201004008
23	204040200801001000800000	0000000A4420810100802004
24	408080401002002001000000	000000052210408080401002
25	810100802004004002000000	000000029108204040200801
26	020201004008008004000000	000000014884102020100400
27	040402008010010008000000	00000000A442081010080200
28	080804010020020010000000	000000005221040808040100
29	101008020040040020000000	000000002910820404020080
30	202010040080080040000000	000000001488410202010040
31	404020080100100080000000	000000000A44208101008020
32	808040100200200100000000	000000000522104080804010
33	010080200400400200000000	000000000291082040402008
34	020100400800800400000000	000000000148841020201004
35	040200801001000800000000	0000000000A4420810100802
36	080401002002001000000000	000000000052210408080401
37	100802004004002000000000	000000000029108204040200
38	201004008008004000000000	000000000014884102020100
39	402008010010008000000000	00000000000A442081010080
40	804010020020010000000000	000000000005221040808040
41	008020040040020000000000	000000000002910820404020
42	010040080080040000000000	000000000001488410202010
43	020080100100080000000000	000000000000A44208101008
44	040100200200100000000000	000000000000522104080804
45	080200400400200000000000	000000000000291082040402
46	100400800800400000000000	000000000000148841020201
47	200801001000800000000000	0000000000000A4420810100
48	401002002001000000000000	000000000000052210408080
49	802004004002000000000000	000000000000029108204040
50	004008008004000000000000	000000000000014884102020
51	008010010008000000000000	00000000000000A442081010
52	010020020010000000000000	000000000000005221040808
53	020040040020000000000000	000000000000002910820404
54	040080080040000000000000	000000000000001488410202
55	080100100080000000000000	000000000000000A44208101
56	100200200100000000000000	000000000000000522104080
57	200400400200000000000000	000000000000000291082040
58	400800800400000000000000	000000000000000148841020
59	801001000800000000000000	0000000000000000A4420810
60	002002001000000000000000	000000000000000052210408
61	004004002000000000000000	000000000000000029108204
62	008008004000000000000000	000000000000000014884102
63	010010008000000000000000	00000000000000000A442081
64	020020010000000000000000	000000000000000005221040
65	040040020000000000000000	000000000000000002910820
66	080080040000000000000000	000000000000000001488410
67	100100080000000000000000	000000000000000000A44208
68	200200100000000000000000	000000000000000000522104
69	400400200000000000000000	000000000000000000291082
70	800800400000000000000000	000000000000000000148841
71	001000800000000000000000	0000000000000000000A4420
72	002001000000000000000000	000000000000000000052210
73	004002000000000000000000	000000000000000000029108
74	008004000000000000000000	000000000000000000014884
75	010008000000000000000000	00000000000000000000A442
76	020010000000000000000000	000000000000000000005221
77	040020000000000000000000	000000000000000000002910
78	080040000000000000000000	000000000000000000001488
79	100080000000000000000000	000000000000000000000A44
80	200100000000000000000000	000000000000000000000522
81	400200000000000000000000	000000000000000000000291
82	800400000000000000000000	000000000000000000000148
83	000800000000000000000000	0000000000000000000000A4
84	001000000000000000000000	000000000000000000000052
85	002000000000000000000000	000000000000000000000029
86	004000000000000000000000	000000000000000000000014
87	008000000000000000000000	00000000000000000000000A
88	010000000000000000000000	000000000000000000000005
89	020000000000000000000000	000000000000000000000002
90	040000000000000000000000	000000000000000000000001
91	080000000000000000000000	000000000000000000000000
92	100000000000000000000000	000000000000000000000000
93	200000000000000000000000	000000000000000000000000
94	400000000000000000000000	000000000000000000000000
95	800000000000000000000000	000000000000000000000000
96	000000000000000000000000	000000000000000000000000
DROP TABLE r;
CREATE TABLE t (gid int, a varbinary(514));
INSERT INTO t VALUES(1, REPEAT('2', 257)), (1, REPEAT('1', 257));
SELECT HEX(BIT_OR(a)) FROM t GROUP BY gid;
ERROR HY000: Aggregate bitwise functions cannot accept arguments longer than 511 bytes; consider using the SUBSTRING() function
DROP TABLE t;
CREATE TABLE t (gid int, a varbinary(511));
INSERT INTO t VALUES(1, REPEAT('2', 255)), (1, REPEAT('1', 255));
SELECT HEX(BIT_OR(a)) FROM t GROUP BY gid;
HEX(BIT_OR(a))
333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
DROP TABLE t;
CREATE TABLE t (gid int, a varbinary(65500), b blob);
INSERT INTO t VALUES(1, REPEAT('2', 32750), REPEAT('1', 32750));
SELECT BIT_COUNT(a & b) FROM t ;
BIT_COUNT(a & b)
65500
DROP TABLE t;
CREATE TABLE t(b LONGBLOB);
INSERT INTO t VALUES(NULL);
SELECT concat(BIT_COUNT(BIT_AND(b))) FROM t GROUP BY b;
ERROR HY000: Aggregate bitwise functions cannot accept arguments longer than 511 bytes; consider using the SUBSTRING() function
SELECT concat("" , BIT_COUNT(BIT_AND(b))) FROM t GROUP BY b;
ERROR HY000: Aggregate bitwise functions cannot accept arguments longer than 511 bytes; consider using the SUBSTRING() function
DROP TABLE t;
#
# tests that '' and NULL are handled differently.
#
CREATE TABLE t(id int, a varbinary(10));
insert into t values(1,''), (1,NULL), (2,NULL), (2, ''),
(3, ''), (3, 'x'), (4, 'x'), (4, '');
SELECT id, bit_or(a) FROM t where id=3 GROUP BY id;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT id, bit_or(a) FROM t where id=4 GROUP BY id;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT id, bit_or(a) FROM t where id in (1,2) GROUP BY id;
id	bit_or(a)
1	
2	
DROP TABLE t;
# All results here have been validated with Python's bitwise operators.
# First VARBINARY
CREATE TABLE u(a VARBINARY(18), b VARBINARY(18), c VARBINARY(18));
# All strings are 16-byte long
INSERT INTO u VALUES(0x6bc7cecf8b98558d7aa6048d0ebcf9fe, 0x7bb1512989ab38a2e91a9b17a268be16, 0xdb502fb16fded188fef1ea604820eb41);
SELECT a | b = 0x7bf7dfef8bbb7daffbbe9f9faefcfffe, a | c = 0xfbd7efffefded58dfef7eeed4ebcfbff, b | c = 0xfbf17fb9effff9aafffbfb77ea68ff57 FROM u;
a | b = 0x7bf7dfef8bbb7daffbbe9f9faefcfffe	a | c = 0xfbd7efffefded58dfef7eeed4ebcfbff	b | c = 0xfbf17fb9effff9aafffbfb77ea68ff57
1	1	1
SELECT a & b = 0x6b81400989881080680200050228b816, a & c = 0x4b400e810b9851887aa000000820e940, b & c = 0x5b100121098a1080e8108a000020aa00 FROM u;
a & b = 0x6b81400989881080680200050228b816	a & c = 0x4b400e810b9851887aa000000820e940	b & c = 0x5b100121098a1080e8108a000020aa00
1	1	1
SELECT a ^ b = 0x10769fe602336d2f93bc9f9aacd447e8, a ^ c = 0xb097e17ee44684058457eeed469c12bf, b ^ c = 0xa0e17e98e675e92a17eb7177ea485557 FROM u;
a ^ b = 0x10769fe602336d2f93bc9f9aacd447e8	a ^ c = 0xb097e17ee44684058457eeed469c12bf	b ^ c = 0xa0e17e98e675e92a17eb7177ea485557
1	1	1
SELECT (~a) = 0x943831307467aa728559fb72f1430601, ~b = 0x844eaed67654c75d16e564e85d9741e9, ~c = 0x24afd04e90212e77010e159fb7df14be FROM u;
(~a) = 0x943831307467aa728559fb72f1430601	~b = 0x844eaed67654c75d16e564e85d9741e9	~c = 0x24afd04e90212e77010e159fb7df14be
1	1	1
CREATE TABLE t(a VARBINARY(18), id INT);
INSERT INTO t VALUES
(0x6BC7CECF8B98558D7AA6048D0EBCF9FE, 1),
(0x7BB1512989AB38A2E91A9B17A268BE16, 1),
(0xDB502FB16FDED188FEF1EA604820EB41, 1),
(0xD78F9D9F1730AB1AF54C091A1D79F3FC, 2),
(0xF762A25313567145D235362F44D17C2C, 2),
(0xB6A05F62DFBDA311FDE3D4C09041D682, 2);
SELECT BIT_AND(a) = 0x4b00000109881080680000000020a800, BIT_OR(a) = 0xfbf7ffffeffffdafffffffffeefcffff, BIT_XOR(a) = 0xcb26b0576dedbca76d4d75fae4f4aca9 FROM t WHERE id = 1;
BIT_AND(a) = 0x4b00000109881080680000000020a800	BIT_OR(a) = 0xfbf7ffffeffffdafffffffffeefcffff	BIT_XOR(a) = 0xcb26b0576dedbca76d4d75fae4f4aca9
1	1	1
SELECT id, HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a)) FROM t GROUP BY id;
id	HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX(BIT_XOR(a))
1	4B00000109881080680000000020A800	FBF7FFFFEFFFFDAFFFFFFFFFEEFCFFFF	CB26B0576DEDBCA76D4D75FAE4F4ACA9
2	9600000213102100D000000000415000	F7EFFFFFDFFFFB5FFFFFFFFFDDF9FFFE	964D60AEDBDB794EDA9AEBF5C9E95952
DROP TABLE t, u;
# And now with BINARY
CREATE TABLE u(a BINARY(18), b BINARY(18), c BINARY(18));
# Right-padding with zeroes happens.
INSERT INTO u VALUES(0x6bc7cecf8b98558d7aa6048d0ebcf9fe, 0x7bb1512989ab38a2e91a9b17a268be16, 0xdb502fb16fded188fef1ea604820eb41);
SELECT a | b = 0x7bf7dfef8bbb7daffbbe9f9faefcfffe0000, a | c = 0xfbd7efffefded58dfef7eeed4ebcfbff0000, b | c = 0xfbf17fb9effff9aafffbfb77ea68ff570000 FROM u;
a | b = 0x7bf7dfef8bbb7daffbbe9f9faefcfffe0000	a | c = 0xfbd7efffefded58dfef7eeed4ebcfbff0000	b | c = 0xfbf17fb9effff9aafffbfb77ea68ff570000
1	1	1
SELECT a & b = 0x6b81400989881080680200050228b8160000, a & c = 0x4b400e810b9851887aa000000820e9400000, b & c = 0x5b100121098a1080e8108a000020aa000000 FROM u;
a & b = 0x6b81400989881080680200050228b8160000	a & c = 0x4b400e810b9851887aa000000820e9400000	b & c = 0x5b100121098a1080e8108a000020aa000000
1	1	1
SELECT a ^ b = 0x10769fe602336d2f93bc9f9aacd447e80000, a ^ c = 0xb097e17ee44684058457eeed469c12bf0000, b ^ c = 0xa0e17e98e675e92a17eb7177ea4855570000 FROM u;
a ^ b = 0x10769fe602336d2f93bc9f9aacd447e80000	a ^ c = 0xb097e17ee44684058457eeed469c12bf0000	b ^ c = 0xa0e17e98e675e92a17eb7177ea4855570000
1	1	1
SELECT (~a) = 0x943831307467aa728559fb72f1430601ffff, ~b = 0x844eaed67654c75d16e564e85d9741e9ffff, ~c = 0x24afd04e90212e77010e159fb7df14beffff FROM u;
(~a) = 0x943831307467aa728559fb72f1430601ffff	~b = 0x844eaed67654c75d16e564e85d9741e9ffff	~c = 0x24afd04e90212e77010e159fb7df14beffff
1	1	1
CREATE TABLE t(a binary(18), id INT);
INSERT INTO t VALUES
(0x6BC7CECF8B98558D7AA6048D0EBCF9FE, 1),
(0x7BB1512989AB38A2E91A9B17A268BE16, 1),
(0xDB502FB16FDED188FEF1EA604820EB41, 1),
(0xD78F9D9F1730AB1AF54C091A1D79F3FC, 2),
(0xF762A25313567145D235362F44D17C2C, 2),
(0xB6A05F62DFBDA311FDE3D4C09041D682, 2);
SELECT BIT_AND(a) = 0x4b00000109881080680000000020a8000000, BIT_OR(a) = 0xfbf7ffffeffffdafffffffffeefcffff0000, BIT_XOR(a) = 0xcb26b0576dedbca76d4d75fae4f4aca90000 FROM t WHERE id = 1;
BIT_AND(a) = 0x4b00000109881080680000000020a8000000	BIT_OR(a) = 0xfbf7ffffeffffdafffffffffeefcffff0000	BIT_XOR(a) = 0xcb26b0576dedbca76d4d75fae4f4aca90000
1	1	1
SELECT id, HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a)) FROM t GROUP BY id;
id	HEX(BIT_AND(a))	HEX(BIT_OR(a))	HEX(BIT_XOR(a))
1	4B00000109881080680000000020A8000000	FBF7FFFFEFFFFDAFFFFFFFFFEEFCFFFF0000	CB26B0576DEDBCA76D4D75FAE4F4ACA90000
2	9600000213102100D0000000004150000000	F7EFFFFFDFFFFB5FFFFFFFFFDDF9FFFE0000	964D60AEDBDB794EDA9AEBF5C9E959520000
DROP TABLE t, u;
SELECT BIT_COUNT(0b1100000010011010000000101000000111100010) = 13;
BIT_COUNT(0b1100000010011010000000101000000111100010) = 13
1
#
# Bug #23853628	BITWISE OPERATORS: ASSERTION 0 IN SORT_PARAM::MAKE_SORTKEY
#
DROP TABLE IF EXISTS t;
Warnings:
Note	1051	Unknown table 'test.t'
CREATE TABLE t(
a BINARY(2) NOT NULL,
b BINARY(1) NOT NULL
) engine=innodb;
INSERT INTO t VALUES('1',''),('','');
SELECT SQL_BIG_RESULT DISTINCT a&b FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
DROP TABLE t;
#
# Bug#24910958 BITWISE OPERATORS: CRASH IN ITEM_FUNC_BIT_COUNT::VAL_INT()
#
CREATE TABLE t (a BLOB NOT NULL) ENGINE=INNODB;
INSERT INTO t values(0xfefefefefefe);
SELECT bit_count(a & 0x2438) FROM t;
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT 1 FROM t WHERE ~reverse(a & 0x111111);
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT 1 FROM t WHERE insert(a & 0x111111,'','','');
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT 1 FROM t WHERE rtrim(a & 0x111111);
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT (SELECT a & 0xff FROM t);
ERROR HY000: Binary operands of bitwise operators must be of equal length
SELECT bit_count(a & NULL) FROM t;
bit_count(a & NULL)
NULL
SELECT 1 FROM t WHERE ~reverse(a & NULL);
1
SELECT 1 FROM t WHERE insert(a & NULL,'','','');
1
SELECT 1 FROM t WHERE rtrim(a & NULL);
1
SELECT (SELECT a & NULL from t);
(SELECT a & NULL from t)
NULL
DROP TABLE t;
#
# Bug#26704312 REEXECUTE PREPARED STATEMENT CRASH IN
# JSON_ARRAY::INSERT_ALIAS
#
PREPARE s FROM 'do json_array(1, database(), bit_or(@c:=0xe9))';
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
EXECUTE s;
EXECUTE s;
DEALLOCATE PREPARE s;
#
# Bug#23529977 ASAN: HEAP-USE-AFTER-FREE IN MY_CONVERT ON SECOND EXECUTION
#
SET @s:="do field('1',monthname(now()),bit_and(export_set(1,'',1,0x29,186)))";
PREPARE s FROM @s;
EXECUTE s;
EXECUTE s;
DEALLOCATE PREPARE s;
SET @s1:='do length(concat(1,bit_xor(aes_encrypt(1,1,1))));';
PREPARE s FROM @s;
EXECUTE s;
EXECUTE s;
DEALLOCATE PREPARE s;
#
# Bug#29963278 QUERY WITH GREATEST FUNCTION MALFUNCTIONS
#
CREATE TABLE t0(c0 TEXT);
INSERT INTO t0(c0) VALUES ("a");
SELECT * FROM t0 WHERE GREATEST((-1) & (-1), -t0.c0);
c0
a
SELECT GREATEST((-1) & (-1), -t0.c0) IS TRUE FROM t0;
GREATEST((-1) & (-1), -t0.c0) IS TRUE
1
SELECT * FROM t0 WHERE GREATEST(18446744073709551615, -t0.c0);
c0
a
SELECT GREATEST(18446744073709551615, -t0.c0) IS TRUE FROM t0;
GREATEST(18446744073709551615, -t0.c0) IS TRUE
1
DROP TABLE t0;
#
# Bug#29957969 QUERY INVOLVING &, <, AND AND OPERATORS
#              COMPUTES INCORRECT RESULT
#
CREATE TABLE t0(c0 INT, c1 TEXT);
INSERT INTO t0(c0, c1) VALUES(-1, "a");
SELECT * FROM t0 WHERE t0.c1 < (t0.c0 & t0.c0) AND t0.c0 = -1;
c0	c1
-1	a
SELECT t0.c1 < (t0.c0 & t0.c0) AND t0.c0 = -1 FROM t0;
t0.c1 < (t0.c0 & t0.c0) AND t0.c0 = -1
1
DROP TABLE t0;