1728 lines
75 KiB
Plaintext
1728 lines
75 KiB
Plaintext
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=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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,
|
|
_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.
|
|
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,
|
|
_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",
|
|
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 _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 _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 _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 _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 _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 _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 # # 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
|
|
5 000002 FBFDFF C1689A
|
|
4 34567100ABF000F0F0 34567101ABFFF0F0F0 00000001000FF00000
|
|
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
|
|
5 000002 FBFDFF C1689A
|
|
4 34567100ABF000F0F0 34567101ABFFF0F0F0 00000001000FF00000
|
|
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
|
|
5 000002 192 3158579 foo
|
|
4 34567100ABF000F0F0 192 3158579 foo
|
|
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 33 2 9 16 NULL
|
|
1 NULL 2 9 16 NULL
|
|
1 33 2 9 16 NULL
|
|
2 33 2 9 16 NULL
|
|
1 34 2 9 16 NULL
|
|
2 17 2 9 16 NULL
|
|
2 9 2 9 16 NULL
|
|
2 10 2 9 16 NULL
|
|
1 33 2 9 16 NULL
|
|
1 32 2 9 16 NULL
|
|
1 NULL 2 9 16 NULL
|
|
1 33 2 9 16 NULL
|
|
#
|
|
# 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
|
|
5 000002 FBFDFF C1689A
|
|
4 34567100ABF000F0F0 34567101ABFFF0F0F0 00000001000FF00000
|
|
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
|
|
5 000002 FBFDFF C1689A
|
|
4 34567100ABF000F0F0 34567101ABFFF0F0F0 00000001000FF00000
|
|
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
|
|
);
|
|
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);
|
|
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
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect INTEGER value: ''
|
|
Warning 1292 Truncated incorrect INTEGER value: ''
|
|
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;
|