drop table if exists t1;
select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 ;
0x41	0x41+0	0x41 | 0x7fffffffffffffff | 0	0xffffffffffffffff | 0
A	65	9223372036854775807	18446744073709551615
select 0x31+1,concat(0x31)+1,-0xf;
0x31+1	concat(0x31)+1	-0xf
50	2	-15
select x'31',X'ffff'+0;
x'31'	X'ffff'+0
1	65535
create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) );
Warnings:
Warning	1681	The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t1 set UNIQ=0x38afba1d73e6a18a;
insert into t1 set UNIQ=123;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain select * from t1 where UNIQ=0x38afba1d73e6a18a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	UNIQ	UNIQ	8	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '00000001' AS `ID`,'004084688022709641610' AS `UNIQ` from `test`.`t1` where true
drop table t1;
select x'hello';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'hello'' at line 1
select 0xfg;
ERROR 42S22: Unknown column '0xfg' in 'field list'
create table t1 select 1 as x, 2 as xx;
select x,xx from t1;
x	xx
1	2
drop table t1;
drop table if exists table_28127_a;
drop table if exists table_28127_b;
create table table_28127_a(0b02 int);
show create table table_28127_a;
Table	Create Table
table_28127_a	CREATE TABLE `table_28127_a` (
  `0b02` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
create table table_28127_b(0b2 int);
show create table table_28127_b;
Table	Create Table
table_28127_b	CREATE TABLE `table_28127_b` (
  `0b2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table table_28127_a;
drop table table_28127_b;
select 0b01000001;
0b01000001
A
select 0x41;
0x41
A
select b'01000001';
b'01000001'
A
select x'41', 0+x'41';
x'41'	0+x'41'
A	65
select N'abc', length(N'abc');
abc	length(N'abc')
abc	3
Warnings:
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
select N'', length(N'');
	length(N'')
	0
Warnings:
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
Warning	3720	NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
select '', length('');
	length('')
	0
select b'', 0+b'';
b''	0+b''
	0
select x'', 0+x'';
x''	0+x''
	0
select 0x;
ERROR 42S22: Unknown column '0x' in 'field list'
select 0b;
ERROR 42S22: Unknown column '0b' in 'field list'
create TABLE t1(a INT, b VARBINARY(4), c VARBINARY(4));
INSERT INTO t1 VALUES
(1, 0x31393831, 0x31303037),
(2, 0x31393832, 0x31303038),
(3, 0x31393833, 0x31303039),
(3, 0x31393834, 0x31393831),
(4, 0x31393835, 0x31393832),
(5, 0x31393836, 0x31303038);
#
# deprecation warnings
#
SELECT
hex(b & c), hex(b & 0x31393838), b & NULL, hex(b & 0b00000000000000000000000000001011),
hex(0x31393838 & b), NULL & b, hex(0b00000000000000000000000000001011 & b)
FROM t1;
hex(b & c)	hex(b & 0x31393838)	b & NULL	hex(b & 0b00000000000000000000000000001011)	hex(0x31393838 & b)	NULL & b	hex(0b00000000000000000000000000001011 & b)
31303031	31393830	NULL	00000001	31393830	NULL	00000001
31303030	31393830	NULL	00000002	31393830	NULL	00000002
31303031	31393830	NULL	00000003	31393830	NULL	00000003
31393830	31393830	NULL	00000000	31393830	NULL	00000000
31393830	31393830	NULL	00000001	31393830	NULL	00000001
31303030	31393830	NULL	00000002	31393830	NULL	00000002
SELECT
hex(b | c), hex(b | 0x31393838), b | NULL, hex(b | 0b00000000000000000000000000001011),
hex(0x31393838 | b), NULL | b, hex(0b00000000000000000000000000001011 | b)
FROM t1;
hex(b | c)	hex(b | 0x31393838)	b | NULL	hex(b | 0b00000000000000000000000000001011)	hex(0x31393838 | b)	NULL | b	hex(0b00000000000000000000000000001011 | b)
31393837	31393839	NULL	3139383B	31393839	NULL	3139383B
3139383A	3139383A	NULL	3139383B	3139383A	NULL	3139383B
3139383B	3139383B	NULL	3139383B	3139383B	NULL	3139383B
31393835	3139383C	NULL	3139383F	3139383C	NULL	3139383F
31393837	3139383D	NULL	3139383F	3139383D	NULL	3139383F
3139383E	3139383E	NULL	3139383F	3139383E	NULL	3139383F
SELECT
hex(b ^ c), hex(b ^ 0x31393838), b ^ NULL, hex(b ^ 0b00000000000000000000000000001011),
hex(0x31393838 ^ b), NULL ^ b, hex(0b00000000000000000000000000001011 ^ b)
FROM t1;
hex(b ^ c)	hex(b ^ 0x31393838)	b ^ NULL	hex(b ^ 0b00000000000000000000000000001011)	hex(0x31393838 ^ b)	NULL ^ b	hex(0b00000000000000000000000000001011 ^ b)
00090806	00000009	NULL	3139383A	00000009	NULL	3139383A
0009080A	0000000A	NULL	31393839	0000000A	NULL	31393839
0009080A	0000000B	NULL	31393838	0000000B	NULL	31393838
00000005	0000000C	NULL	3139383F	0000000C	NULL	3139383F
00000007	0000000D	NULL	3139383E	0000000D	NULL	3139383E
0009080E	0000000E	NULL	3139383D	0000000E	NULL	3139383D
SELECT BIT_COUNT(b), HEX(~b), HEX(b << 1), HEX(b >> 1) from t1;
BIT_COUNT(b)	HEX(~b)	HEX(b << 1)	HEX(b >> 1)
13	CEC6C7CE	62727062	189C9C18
13	CEC6C7CD	62727064	189C9C19
14	CEC6C7CC	62727066	189C9C19
13	CEC6C7CB	62727068	189C9C1A
14	CEC6C7CA	6272706A	189C9C1A
14	CEC6C7C9	6272706C	189C9C1B
SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393831	31393831	31393831
31393832	31393832	31393832
31393830	31393837	00000007
31393835	31393835	31393835
31393836	31393836	31393836
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))
31393830	31393837	00000007
#
# stored procedures with warnings
#
CREATE PROCEDURE test_bin_op()
SELECT
HEX(b & c), HEX(b & 0x31393838), b & NULL, HEX(b & 0b00000000000000000000000000001011),
HEX(0x31393838 & b), NULL & b, HEX(0b00000000000000000000000000001011 & b),
HEX(b | c), HEX(b | 0x31393838), b | NULL, HEX(b | 0b00000000000000000000000000001011),
HEX(0x31393838 | b), NULL | b, HEX(0b00000000000000000000000000001011 | b),
HEX(b ^ c), HEX(b ^ 0x31393838), b ^ NULL, HEX(b ^ 0b00000000000000000000000000001011),
HEX(0x31393838 ^ b), NULL ^ b, HEX(0b00000000000000000000000000001011 ^ b),
BIT_COUNT(b), HEX(~b), HEX(b << 1), HEX(b >> 1)
FROM t1;
CALL test_bin_op();
HEX(b & c)	HEX(b & 0x31393838)	b & NULL	HEX(b & 0b00000000000000000000000000001011)	HEX(0x31393838 & b)	NULL & b	HEX(0b00000000000000000000000000001011 & b)	HEX(b | c)	HEX(b | 0x31393838)	b | NULL	HEX(b | 0b00000000000000000000000000001011)	HEX(0x31393838 | b)	NULL | b	HEX(0b00000000000000000000000000001011 | b)	HEX(b ^ c)	HEX(b ^ 0x31393838)	b ^ NULL	HEX(b ^ 0b00000000000000000000000000001011)	HEX(0x31393838 ^ b)	NULL ^ b	HEX(0b00000000000000000000000000001011 ^ b)	BIT_COUNT(b)	HEX(~b)	HEX(b << 1)	HEX(b >> 1)
31303031	31393830	NULL	00000001	31393830	NULL	00000001	31393837	31393839	NULL	3139383B	31393839	NULL	3139383B	00090806	00000009	NULL	3139383A	00000009	NULL	3139383A	13	CEC6C7CE	62727062	189C9C18
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383A	3139383A	NULL	3139383B	3139383A	NULL	3139383B	0009080A	0000000A	NULL	31393839	0000000A	NULL	31393839	13	CEC6C7CD	62727064	189C9C19
31303031	31393830	NULL	00000003	31393830	NULL	00000003	3139383B	3139383B	NULL	3139383B	3139383B	NULL	3139383B	0009080A	0000000B	NULL	31393838	0000000B	NULL	31393838	14	CEC6C7CC	62727066	189C9C19
31393830	31393830	NULL	00000000	31393830	NULL	00000000	31393835	3139383C	NULL	3139383F	3139383C	NULL	3139383F	00000005	0000000C	NULL	3139383F	0000000C	NULL	3139383F	13	CEC6C7CB	62727068	189C9C1A
31393830	31393830	NULL	00000001	31393830	NULL	00000001	31393837	3139383D	NULL	3139383F	3139383D	NULL	3139383F	00000007	0000000D	NULL	3139383E	0000000D	NULL	3139383E	14	CEC6C7CA	6272706A	189C9C1A
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383E	3139383E	NULL	3139383F	3139383E	NULL	3139383F	0009080E	0000000E	NULL	3139383D	0000000E	NULL	3139383D	14	CEC6C7C9	6272706C	189C9C1B
CALL test_bin_op();
HEX(b & c)	HEX(b & 0x31393838)	b & NULL	HEX(b & 0b00000000000000000000000000001011)	HEX(0x31393838 & b)	NULL & b	HEX(0b00000000000000000000000000001011 & b)	HEX(b | c)	HEX(b | 0x31393838)	b | NULL	HEX(b | 0b00000000000000000000000000001011)	HEX(0x31393838 | b)	NULL | b	HEX(0b00000000000000000000000000001011 | b)	HEX(b ^ c)	HEX(b ^ 0x31393838)	b ^ NULL	HEX(b ^ 0b00000000000000000000000000001011)	HEX(0x31393838 ^ b)	NULL ^ b	HEX(0b00000000000000000000000000001011 ^ b)	BIT_COUNT(b)	HEX(~b)	HEX(b << 1)	HEX(b >> 1)
31303031	31393830	NULL	00000001	31393830	NULL	00000001	31393837	31393839	NULL	3139383B	31393839	NULL	3139383B	00090806	00000009	NULL	3139383A	00000009	NULL	3139383A	13	CEC6C7CE	62727062	189C9C18
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383A	3139383A	NULL	3139383B	3139383A	NULL	3139383B	0009080A	0000000A	NULL	31393839	0000000A	NULL	31393839	13	CEC6C7CD	62727064	189C9C19
31303031	31393830	NULL	00000003	31393830	NULL	00000003	3139383B	3139383B	NULL	3139383B	3139383B	NULL	3139383B	0009080A	0000000B	NULL	31393838	0000000B	NULL	31393838	14	CEC6C7CC	62727066	189C9C19
31393830	31393830	NULL	00000000	31393830	NULL	00000000	31393835	3139383C	NULL	3139383F	3139383C	NULL	3139383F	00000005	0000000C	NULL	3139383F	0000000C	NULL	3139383F	13	CEC6C7CB	62727068	189C9C1A
31393830	31393830	NULL	00000001	31393830	NULL	00000001	31393837	3139383D	NULL	3139383F	3139383D	NULL	3139383F	00000007	0000000D	NULL	3139383E	0000000D	NULL	3139383E	14	CEC6C7CA	6272706A	189C9C1A
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383E	3139383E	NULL	3139383F	3139383E	NULL	3139383F	0009080E	0000000E	NULL	3139383D	0000000E	NULL	3139383D	14	CEC6C7C9	6272706C	189C9C1B
DROP PROCEDURE test_bin_op;
CREATE PROCEDURE test_bin_op()
SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
CALL test_bin_op();
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393831	31393831	31393831
31393832	31393832	31393832
31393830	31393837	00000007
31393835	31393835	31393835
31393836	31393836	31393836
CALL test_bin_op();
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393831	31393831	31393831
31393832	31393832	31393832
31393830	31393837	00000007
31393835	31393835	31393835
31393836	31393836	31393836
DROP PROCEDURE test_bin_op;
CREATE PROCEDURE test_bin_op()
SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1;
CALL test_bin_op();
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393830	31393837	00000007
CALL test_bin_op();
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393830	31393837	00000007
DROP PROCEDURE test_bin_op;
#
# prepared statements with warnings
#
PREPARE s1 FROM
"SELECT
HEX(b & c), HEX(b & 0x31393838), b & NULL, HEX(b & 0b00000000000000000000000000001011),
HEX(0x31393838 & b), NULL & b, HEX(0b00000000000000000000000000001011 & b),
HEX(b | c), HEX(b | 0x31393838), b | NULL, HEX(b | 0b00000000000000000000000000001011),
HEX(0x31393838 | b), NULL | b, HEX(0b00000000000000000000000000001011 | b),
HEX(b ^ c), HEX(b ^ 0x31393838), b ^ NULL, HEX(b ^ 0b00000000000000000000000000001011),
HEX(0x31393838 ^ b), NULL ^ b, HEX(0b00000000000000000000000000001011 ^ b),
BIT_COUNT(b), HEX( ~b), HEX(b << 1), HEX(b >> 1)
FROM t1;";
EXECUTE s1;
HEX(b & c)	HEX(b & 0x31393838)	b & NULL	HEX(b & 0b00000000000000000000000000001011)	HEX(0x31393838 & b)	NULL & b	HEX(0b00000000000000000000000000001011 & b)	HEX(b | c)	HEX(b | 0x31393838)	b | NULL	HEX(b | 0b00000000000000000000000000001011)	HEX(0x31393838 | b)	NULL | b	HEX(0b00000000000000000000000000001011 | b)	HEX(b ^ c)	HEX(b ^ 0x31393838)	b ^ NULL	HEX(b ^ 0b00000000000000000000000000001011)	HEX(0x31393838 ^ b)	NULL ^ b	HEX(0b00000000000000000000000000001011 ^ b)	BIT_COUNT(b)	HEX( ~b)	HEX(b << 1)	HEX(b >> 1)
31303031	31393830	NULL	00000001	31393830	NULL	00000001	31393837	31393839	NULL	3139383B	31393839	NULL	3139383B	00090806	00000009	NULL	3139383A	00000009	NULL	3139383A	13	CEC6C7CE	62727062	189C9C18
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383A	3139383A	NULL	3139383B	3139383A	NULL	3139383B	0009080A	0000000A	NULL	31393839	0000000A	NULL	31393839	13	CEC6C7CD	62727064	189C9C19
31303031	31393830	NULL	00000003	31393830	NULL	00000003	3139383B	3139383B	NULL	3139383B	3139383B	NULL	3139383B	0009080A	0000000B	NULL	31393838	0000000B	NULL	31393838	14	CEC6C7CC	62727066	189C9C19
31393830	31393830	NULL	00000000	31393830	NULL	00000000	31393835	3139383C	NULL	3139383F	3139383C	NULL	3139383F	00000005	0000000C	NULL	3139383F	0000000C	NULL	3139383F	13	CEC6C7CB	62727068	189C9C1A
31393830	31393830	NULL	00000001	31393830	NULL	00000001	31393837	3139383D	NULL	3139383F	3139383D	NULL	3139383F	00000007	0000000D	NULL	3139383E	0000000D	NULL	3139383E	14	CEC6C7CA	6272706A	189C9C1A
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383E	3139383E	NULL	3139383F	3139383E	NULL	3139383F	0009080E	0000000E	NULL	3139383D	0000000E	NULL	3139383D	14	CEC6C7C9	6272706C	189C9C1B
EXECUTE s1;
HEX(b & c)	HEX(b & 0x31393838)	b & NULL	HEX(b & 0b00000000000000000000000000001011)	HEX(0x31393838 & b)	NULL & b	HEX(0b00000000000000000000000000001011 & b)	HEX(b | c)	HEX(b | 0x31393838)	b | NULL	HEX(b | 0b00000000000000000000000000001011)	HEX(0x31393838 | b)	NULL | b	HEX(0b00000000000000000000000000001011 | b)	HEX(b ^ c)	HEX(b ^ 0x31393838)	b ^ NULL	HEX(b ^ 0b00000000000000000000000000001011)	HEX(0x31393838 ^ b)	NULL ^ b	HEX(0b00000000000000000000000000001011 ^ b)	BIT_COUNT(b)	HEX( ~b)	HEX(b << 1)	HEX(b >> 1)
31303031	31393830	NULL	00000001	31393830	NULL	00000001	31393837	31393839	NULL	3139383B	31393839	NULL	3139383B	00090806	00000009	NULL	3139383A	00000009	NULL	3139383A	13	CEC6C7CE	62727062	189C9C18
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383A	3139383A	NULL	3139383B	3139383A	NULL	3139383B	0009080A	0000000A	NULL	31393839	0000000A	NULL	31393839	13	CEC6C7CD	62727064	189C9C19
31303031	31393830	NULL	00000003	31393830	NULL	00000003	3139383B	3139383B	NULL	3139383B	3139383B	NULL	3139383B	0009080A	0000000B	NULL	31393838	0000000B	NULL	31393838	14	CEC6C7CC	62727066	189C9C19
31393830	31393830	NULL	00000000	31393830	NULL	00000000	31393835	3139383C	NULL	3139383F	3139383C	NULL	3139383F	00000005	0000000C	NULL	3139383F	0000000C	NULL	3139383F	13	CEC6C7CB	62727068	189C9C1A
31393830	31393830	NULL	00000001	31393830	NULL	00000001	31393837	3139383D	NULL	3139383F	3139383D	NULL	3139383F	00000007	0000000D	NULL	3139383E	0000000D	NULL	3139383E	14	CEC6C7CA	6272706A	189C9C1A
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383E	3139383E	NULL	3139383F	3139383E	NULL	3139383F	0009080E	0000000E	NULL	3139383D	0000000E	NULL	3139383D	14	CEC6C7C9	6272706C	189C9C1B
PREPARE s2 from "SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a";
EXECUTE s2;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393831	31393831	31393831
31393832	31393832	31393832
31393830	31393837	00000007
31393835	31393835	31393835
31393836	31393836	31393836
EXECUTE s2;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393831	31393831	31393831
31393832	31393832	31393832
31393830	31393837	00000007
31393835	31393835	31393835
31393836	31393836	31393836
PREPARE s2 from "SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1";
EXECUTE s2;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393830	31393837	00000007
EXECUTE s2;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393830	31393837	00000007
#
# views with warnings
#
CREATE VIEW v1 AS
SELECT
HEX(b & c), HEX(b & 0x31393838), b & NULL, HEX(b & 0b00000000000000000000000000001011),
HEX(0x31393838 & b), NULL & b, HEX(0b00000000000000000000000000001011 & b),
HEX(b | c), HEX(b | 0x31393838), b | NULL, HEX(b | 0b00000000000000000000000000001011),
HEX(0x31393838 | b), NULL | b, HEX(0b00000000000000000000000000001011 | b),
HEX(b ^ c), HEX(b ^ 0x31393838), b ^ NULL, HEX(b ^ 0b00000000000000000000000000001011),
HEX(0x31393838 ^ b), NULL ^ b, HEX(0b00000000000000000000000000001011 ^ b),
BIT_COUNT(b), HEX(~b), HEX(b << 1), HEX(b >> 1)
FROM t1;
SELECT * from v1;
HEX(b & c)	HEX(b & 0x31393838)	b & NULL	HEX(b & 0b00000000000000000000000000001011)	HEX(0x31393838 & b)	NULL & b	HEX(0b00000000000000000000000000001011 & b)	HEX(b | c)	HEX(b | 0x31393838)	b | NULL	HEX(b | 0b00000000000000000000000000001011)	HEX(0x31393838 | b)	NULL | b	HEX(0b00000000000000000000000000001011 | b)	HEX(b ^ c)	HEX(b ^ 0x31393838)	b ^ NULL	HEX(b ^ 0b00000000000000000000000000001011)	HEX(0x31393838 ^ b)	NULL ^ b	HEX(0b00000000000000000000000000001011 ^ b)	BIT_COUNT(b)	HEX(~b)	HEX(b << 1)	HEX(b >> 1)
31303031	31393830	NULL	00000001	31393830	NULL	00000001	31393837	31393839	NULL	3139383B	31393839	NULL	3139383B	00090806	00000009	NULL	3139383A	00000009	NULL	3139383A	13	CEC6C7CE	62727062	189C9C18
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383A	3139383A	NULL	3139383B	3139383A	NULL	3139383B	0009080A	0000000A	NULL	31393839	0000000A	NULL	31393839	13	CEC6C7CD	62727064	189C9C19
31303031	31393830	NULL	00000003	31393830	NULL	00000003	3139383B	3139383B	NULL	3139383B	3139383B	NULL	3139383B	0009080A	0000000B	NULL	31393838	0000000B	NULL	31393838	14	CEC6C7CC	62727066	189C9C19
31393830	31393830	NULL	00000000	31393830	NULL	00000000	31393835	3139383C	NULL	3139383F	3139383C	NULL	3139383F	00000005	0000000C	NULL	3139383F	0000000C	NULL	3139383F	13	CEC6C7CB	62727068	189C9C1A
31393830	31393830	NULL	00000001	31393830	NULL	00000001	31393837	3139383D	NULL	3139383F	3139383D	NULL	3139383F	00000007	0000000D	NULL	3139383E	0000000D	NULL	3139383E	14	CEC6C7CA	6272706A	189C9C1A
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383E	3139383E	NULL	3139383F	3139383E	NULL	3139383F	0009080E	0000000E	NULL	3139383D	0000000E	NULL	3139383D	14	CEC6C7C9	6272706C	189C9C1B
SELECT * from v1;
HEX(b & c)	HEX(b & 0x31393838)	b & NULL	HEX(b & 0b00000000000000000000000000001011)	HEX(0x31393838 & b)	NULL & b	HEX(0b00000000000000000000000000001011 & b)	HEX(b | c)	HEX(b | 0x31393838)	b | NULL	HEX(b | 0b00000000000000000000000000001011)	HEX(0x31393838 | b)	NULL | b	HEX(0b00000000000000000000000000001011 | b)	HEX(b ^ c)	HEX(b ^ 0x31393838)	b ^ NULL	HEX(b ^ 0b00000000000000000000000000001011)	HEX(0x31393838 ^ b)	NULL ^ b	HEX(0b00000000000000000000000000001011 ^ b)	BIT_COUNT(b)	HEX(~b)	HEX(b << 1)	HEX(b >> 1)
31303031	31393830	NULL	00000001	31393830	NULL	00000001	31393837	31393839	NULL	3139383B	31393839	NULL	3139383B	00090806	00000009	NULL	3139383A	00000009	NULL	3139383A	13	CEC6C7CE	62727062	189C9C18
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383A	3139383A	NULL	3139383B	3139383A	NULL	3139383B	0009080A	0000000A	NULL	31393839	0000000A	NULL	31393839	13	CEC6C7CD	62727064	189C9C19
31303031	31393830	NULL	00000003	31393830	NULL	00000003	3139383B	3139383B	NULL	3139383B	3139383B	NULL	3139383B	0009080A	0000000B	NULL	31393838	0000000B	NULL	31393838	14	CEC6C7CC	62727066	189C9C19
31393830	31393830	NULL	00000000	31393830	NULL	00000000	31393835	3139383C	NULL	3139383F	3139383C	NULL	3139383F	00000005	0000000C	NULL	3139383F	0000000C	NULL	3139383F	13	CEC6C7CB	62727068	189C9C1A
31393830	31393830	NULL	00000001	31393830	NULL	00000001	31393837	3139383D	NULL	3139383F	3139383D	NULL	3139383F	00000007	0000000D	NULL	3139383E	0000000D	NULL	3139383E	14	CEC6C7CA	6272706A	189C9C1A
31303030	31393830	NULL	00000002	31393830	NULL	00000002	3139383E	3139383E	NULL	3139383F	3139383E	NULL	3139383F	0009080E	0000000E	NULL	3139383D	0000000E	NULL	3139383D	14	CEC6C7C9	6272706C	189C9C1B
CREATE VIEW v2 AS
SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1 GROUP BY a;
SELECT * from v2;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393831	31393831	31393831
31393832	31393832	31393832
31393830	31393837	00000007
31393835	31393835	31393835
31393836	31393836	31393836
SELECT * from v2;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393831	31393831	31393831
31393832	31393832	31393832
31393830	31393837	00000007
31393835	31393835	31393835
31393836	31393836	31393836
CREATE VIEW v3 AS
SELECT HEX(BIT_AND(b)), HEX(BIT_OR(b)), HEX(BIT_XOR(b)) FROM t1;
SELECT * from v3;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393830	31393837	00000007
SELECT * from v3;
HEX(BIT_AND(b))	HEX(BIT_OR(b))	HEX(BIT_XOR(b))
31393830	31393837	00000007
DROP VIEW v1,v2,v3;
#
# working as before
#
SELECT
a & 0x31393838, 0x31393838 & a, 0x31393838 & 0x31393838, 0x31393838 & NULL,
0x31393838 & 0b1011, NULL & 0x31393838, 0b1011 & 0x31393838, NULL & NULL,
NULL & 0b1011, 0b1011 & NULL, 0b1011 & 0b1011, BIT_COUNT(a)
FROM t1;
a & 0x31393838	0x31393838 & a	0x31393838 & 0x31393838	0x31393838 & NULL	0x31393838 & 0b1011	NULL & 0x31393838	0b1011 & 0x31393838	NULL & NULL	NULL & 0b1011	0b1011 & NULL	0b1011 & 0b1011	BIT_COUNT(a)
0	0	825833528	NULL	8	NULL	8	NULL	NULL	NULL	11	1
0	0	825833528	NULL	8	NULL	8	NULL	NULL	NULL	11	1
0	0	825833528	NULL	8	NULL	8	NULL	NULL	NULL	11	2
0	0	825833528	NULL	8	NULL	8	NULL	NULL	NULL	11	2
0	0	825833528	NULL	8	NULL	8	NULL	NULL	NULL	11	1
0	0	825833528	NULL	8	NULL	8	NULL	NULL	NULL	11	2
SELECT
a | 0x31393838, 0x31393838 | a, 0x31393838 | 0x31393838, 0x31393838 | NULL,
0x31393838 | 0b1011, NULL | 0x31393838, 0b1011 | 0x31393838, NULL | NULL,
NULL | 0b1011, 0b1011 | NULL, 0b1011 | 0b1011
FROM t1;
a | 0x31393838	0x31393838 | a	0x31393838 | 0x31393838	0x31393838 | NULL	0x31393838 | 0b1011	NULL | 0x31393838	0b1011 | 0x31393838	NULL | NULL	NULL | 0b1011	0b1011 | NULL	0b1011 | 0b1011
825833529	825833529	825833528	NULL	825833531	NULL	825833531	NULL	NULL	NULL	11
825833530	825833530	825833528	NULL	825833531	NULL	825833531	NULL	NULL	NULL	11
825833531	825833531	825833528	NULL	825833531	NULL	825833531	NULL	NULL	NULL	11
825833531	825833531	825833528	NULL	825833531	NULL	825833531	NULL	NULL	NULL	11
825833532	825833532	825833528	NULL	825833531	NULL	825833531	NULL	NULL	NULL	11
825833533	825833533	825833528	NULL	825833531	NULL	825833531	NULL	NULL	NULL	11
SELECT
a ^ 0x31393838, 0x31393838 ^ a, 0x31393838 ^ 0x31393838, 0x31393838 ^ NULL,
0x31393838 ^ 0b1011, NULL ^ 0x31393838, 0b1011 ^ 0x31393838, NULL ^ NULL,
NULL ^ 0b1011, 0b1011 ^ NULL, 0b1011 ^ 0b1011
FROM t1;
a ^ 0x31393838	0x31393838 ^ a	0x31393838 ^ 0x31393838	0x31393838 ^ NULL	0x31393838 ^ 0b1011	NULL ^ 0x31393838	0b1011 ^ 0x31393838	NULL ^ NULL	NULL ^ 0b1011	0b1011 ^ NULL	0b1011 ^ 0b1011
825833529	825833529	0	NULL	825833523	NULL	825833523	NULL	NULL	NULL	0
825833530	825833530	0	NULL	825833523	NULL	825833523	NULL	NULL	NULL	0
825833531	825833531	0	NULL	825833523	NULL	825833523	NULL	NULL	NULL	0
825833531	825833531	0	NULL	825833523	NULL	825833523	NULL	NULL	NULL	0
825833532	825833532	0	NULL	825833523	NULL	825833523	NULL	NULL	NULL	0
825833533	825833533	0	NULL	825833523	NULL	825833523	NULL	NULL	NULL	0
SELECT a, BIT_AND(a), BIT_OR(a), BIT_XOR(a),
~NULL, NULL << 1, NULL >> 1, 1 << NULL, 1 >> NULL,
~0x31393838, 0x31393838 << 1, 0x31393838 >> 1,
~0b1011, 0b1011 << 1, 0b1011 >> 1
FROM t1
GROUP BY a;
a	BIT_AND(a)	BIT_OR(a)	BIT_XOR(a)	~NULL	NULL << 1	NULL >> 1	1 << NULL	1 >> NULL	~0x31393838	0x31393838 << 1	0x31393838 >> 1	~0b1011	0b1011 << 1	0b1011 >> 1
1	1	1	1	NULL	NULL	NULL	NULL	NULL	18446744072883718087	1651667056	412916764	18446744073709551604	22	5
2	2	2	2	NULL	NULL	NULL	NULL	NULL	18446744072883718087	1651667056	412916764	18446744073709551604	22	5
3	3	3	0	NULL	NULL	NULL	NULL	NULL	18446744072883718087	1651667056	412916764	18446744073709551604	22	5
4	4	4	4	NULL	NULL	NULL	NULL	NULL	18446744072883718087	1651667056	412916764	18446744073709551604	22	5
5	5	5	5	NULL	NULL	NULL	NULL	NULL	18446744072883718087	1651667056	412916764	18446744073709551604	22	5
#
# binary/varbinary vs char/varchar
#
SELECT '12' | '12';
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					'12' | '12'	8	21	2	N	32929	0	63
'12' | '12'
12
SELECT _binary '12' | '12';
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					_binary '12' | '12'	8	21	2	N	32929	0	63
_binary '12' | '12'
12
SELECT _binary '12' | _binary '12';
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					_binary '12' | _binary '12'	253	2	2	N	129	0	63
_binary '12' | _binary '12'
12
SELECT _binary '12' | 0x0001;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					_binary '12' | 0x0001	253	2	2	N	129	0	63
_binary '12' | 0x0001
13
SELECT _binary '12' | 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					_binary '12' | 1	8	21	2	N	32929	0	63
_binary '12' | 1
13
SELECT binary '12' | '12';
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					binary '12' | '12'	8	21	2	Y	32928	0	63
binary '12' | '12'
12
SELECT binary '12' | binary '12';
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					binary '12' | binary '12'	253	8	2	Y	128	0	63
binary '12' | binary '12'
12
SELECT binary '12' | 0x0001;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					binary '12' | 0x0001	253	8	2	Y	128	0	63
binary '12' | 0x0001
13
SELECT binary '12' | 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					binary '12' | 1	8	21	2	Y	32928	0	63
binary '12' | 1
13
SELECT '12' | 0x01;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					'12' | 0x01	8	21	2	N	32929	0	63
'12' | 0x01
13
SELECT '12' | 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					'12' | 1	8	21	2	N	32929	0	63
'12' | 1
13
SELECT CAST('12' AS binary) | 0x0001;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST('12' AS binary) | 0x0001	253	8	2	Y	128	0	63
CAST('12' AS binary) | 0x0001
13
SELECT CAST('12' AS binary) | 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST('12' AS binary) | 1	8	21	2	Y	32928	0	63
CAST('12' AS binary) | 1
13
SELECT CAST(b AS char) | 0x31393838 FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST(b AS char) | 0x31393838	8	21	9	Y	32928	0	63
CAST(b AS char) | 0x31393838
825835453
SELECT (b + 0) | 0x31393838 FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					(b + 0) | 0x31393838	8	21	9	Y	32928	0	63
(b + 0) | 0x31393838
825835453
SELECT CAST(0x01 AS char) | 0x31393838 FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST(0x01 AS char) | 0x31393838	8	21	9	Y	32928	0	63
CAST(0x01 AS char) | 0x31393838
825833528
SELECT 0x01 << 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					0x01 << 1	8	21	1	N	32929	0	63
0x01 << 1
2
SELECT _binary '12' << 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					_binary '12' << 1	253	2	2	N	129	0	63
_binary '12' << 1
bd
SELECT binary '12' << 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					binary '12' << 1	253	8	2	Y	128	0	63
binary '12' << 1
bd
SELECT CAST('12' AS binary) << 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST('12' AS binary) << 1	253	8	2	Y	128	0	63
CAST('12' AS binary) << 1
bd
SELECT CAST(b AS char) << 1 FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST(b AS char) << 1	8	21	4	Y	32928	0	63
CAST(b AS char) << 1
3962
SELECT CAST(b AS unsigned) << 1 FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST(b AS unsigned) << 1	8	21	4	Y	32928	0	63
CAST(b AS unsigned) << 1
3962
SELECT (b + 0) << 1 FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					(b + 0) << 1	8	21	4	Y	32928	0	63
(b + 0) << 1
3962
SELECT CAST(b AS unsigned) | 0x31393838 FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST(b AS unsigned) | 0x31393838	8	21	9	Y	32928	0	63
CAST(b AS unsigned) | 0x31393838
825835453
SELECT CAST(b AS unsigned) | CAST(c AS unsigned) FROM t1 LIMIT 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST(b AS unsigned) | CAST(c AS unsigned)	8	21	4	Y	32928	0	63
CAST(b AS unsigned) | CAST(c AS unsigned)
2047
DROP TABLE t1;
PREPARE ps FROM 'SELECT (~?)';
SET @a:=0;
EXECUTE ps USING @a;
(~?)
18446744073709551615
SET @a:='abcd';
EXECUTE ps USING @a;
(~?)
18446744073709551615
SET @a:=_binary 'abcd';
EXECUTE ps USING @a;
(~?)
ž�œ›
SET @a:=34;
EXECUTE ps USING @a;
(~?)
18446744073709551581
SELECT (_binary x'31' | x'31');
(_binary x'31' | x'31')
1
SELECT _binary '1' + 0;
_binary '1' + 0
1
SELECT (_binary x'31' | x'31') + 0;
(_binary x'31' | x'31') + 0
1
SELECT 1.0 * (_binary x'312E35' | x'312E35');
1.0 * (_binary x'312E35' | x'312E35')
1.5
SELECT 1.0 * (x'312E35' | x'312E35');
1.0 * (x'312E35' | x'312E35')
3223093.0
SELECT HEX(_binary 0x0003 << (_binary 0x38 | NULL));
HEX(_binary 0x0003 << (_binary 0x38 | NULL))
NULL
SELECT (_binary x'31' | NULL) + 0;
(_binary x'31' | NULL) + 0
NULL
SELECT CONCAT("", (0x39 | NULL));
CONCAT("", (0x39 | NULL))
NULL
SELECT ~(CONCAT ("", NULL));
~(CONCAT ("", NULL))
NULL
#
# Testing datetime and time without warnings
#
SELECT DATEDIFF((_binary '2012-05-19 09:06:07' | _binary '2012-05-19 09:06:07'), '2012-05-21 09:06:07');
DATEDIFF((_binary '2012-05-19 09:06:07' | _binary '2012-05-19 09:06:07'), '2012-05-21 09:06:07')
-2
SELECT DATEDIFF(20120519090607 | 20120519090607, '2012-05-21 09:06:07');
DATEDIFF(20120519090607 | 20120519090607, '2012-05-21 09:06:07')
-2
SELECT DATEDIFF(20120519090607, '2012-05-21 09:06:07');
DATEDIFF(20120519090607, '2012-05-21 09:06:07')
-2
SELECT SUBTIME((_binary '2012-05-19 09:06:07' | _binary '2012-05-19 09:06:07'),'1 1:1:1.000002');
SUBTIME((_binary '2012-05-19 09:06:07' | _binary '2012-05-19 09:06:07'),'1 1:1:1.000002')
2012-05-18 08:05:05.999998
SELECT SUBTIME(20120519090607 | 20120519090607 ,'1 1:1:1.000002');
SUBTIME(20120519090607 | 20120519090607 ,'1 1:1:1.000002')
2012-05-18 08:05:05.999998
SELECT SUBTIME(20120519090607 ,'1 1:1:1.000002');
SUBTIME(20120519090607 ,'1 1:1:1.000002')
2012-05-18 08:05:05.999998
#
# Testing datetime and time with warnings
#
SELECT DATEDIFF((_binary '12012-05-19 09:06:07' | _binary '12012-05-19 09:06:07'), '2012-05-21 09:06:07');
DATEDIFF((_binary '12012-05-19 09:06:07' | _binary '12012-05-19 09:06:07'), '2012-05-21 09:06:07')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '12012-05-19 09:06:07'
SELECT DATEDIFF(120120519090607 | 120120519090607, '2012-05-21 09:06:07');
DATEDIFF(120120519090607 | 120120519090607, '2012-05-21 09:06:07')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '120120519090607'
SELECT DATEDIFF(120120519090607, '12012-05-21 09:06:07');
DATEDIFF(120120519090607, '12012-05-21 09:06:07')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '120120519090607'
Warning	1292	Incorrect datetime value: '12012-05-21 09:06:07'
SELECT SUBTIME((_binary '12007-12-31 23:59:59.999999' | _binary '12007-12-31 23:59:59.999999'),'1 1:1:1.000002');
SUBTIME((_binary '12007-12-31 23:59:59.999999' | _binary '12007-12-31 23:59:59.999999'),'1 1:1:1.000002')
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '12007-12-31 23:59:59.999999'
SELECT SUBTIME(120120519090607 | 120120519090607 ,'1 1:1:1.000002');
SUBTIME(120120519090607 | 120120519090607 ,'1 1:1:1.000002')
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '120120519090607'
SELECT SUBTIME(120120519090607 ,'1 1:1:1.000002');
SUBTIME(120120519090607 ,'1 1:1:1.000002')
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '120120519090607'
CREATE TABLE t1(gid INT, a VARBINARY(20), b BIGINT);
INSERT INTO t1 VALUES(1, _binary '2012-05-19 09:06:07', 20120519090607),
(1, _binary '2012-05-19 09:06:07', 20120519090607),
(2, _binary '12012-05-19 09:06:07', 120120519090607),
(2, _binary '12012-05-19 09:06:07', 120120519090607);
SELECT DATEDIFF(BIT_OR(a), '2012-05-21 09:06:07') FROM t1 GROUP BY gid;
DATEDIFF(BIT_OR(a), '2012-05-21 09:06:07')
-2
NULL
Warnings:
Warning	1292	Incorrect datetime value: '12012-05-19 09:06:07'
SELECT DATEDIFF(BIT_OR(b), '2012-05-21 09:06:07') FROM t1 GROUP BY gid;
DATEDIFF(BIT_OR(b), '2012-05-21 09:06:07')
-2
NULL
Warnings:
Warning	1292	Incorrect datetime value: '120120519090607'
SELECT DATEDIFF(BIT_OR(a), '2012-05-21 09:06:07') FROM t1 WHERE gid = 1;
DATEDIFF(BIT_OR(a), '2012-05-21 09:06:07')
-2
SELECT DATEDIFF(BIT_OR(b), '2012-05-21 09:06:07') FROM t1 WHERE gid = 1;
DATEDIFF(BIT_OR(b), '2012-05-21 09:06:07')
-2
SELECT DATEDIFF(BIT_OR(a), '2012-05-21 09:06:07') FROM t1 WHERE gid = 2;
DATEDIFF(BIT_OR(a), '2012-05-21 09:06:07')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '12012-05-19 09:06:07'
SELECT DATEDIFF(BIT_OR(b), '2012-05-21 09:06:07') FROM t1 WHERE gid = 2;
DATEDIFF(BIT_OR(b), '2012-05-21 09:06:07')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '120120519090607'
SELECT SUBTIME(BIT_OR(a), '1 1:1:1.000002') FROM t1 GROUP BY gid;
SUBTIME(BIT_OR(a), '1 1:1:1.000002')
2012-05-18 08:05:05.999998
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '12012-05-19 09:06:07'
SELECT SUBTIME(BIT_OR(b), '1 1:1:1.000002') FROM t1 GROUP BY gid;
SUBTIME(BIT_OR(b), '1 1:1:1.000002')
2012-05-18 08:05:05.999998
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '120120519090607'
SELECT SUBTIME(BIT_OR(a), '1 1:1:1.000002') FROM t1 WHERE gid = 1;
SUBTIME(BIT_OR(a), '1 1:1:1.000002')
2012-05-18 08:05:05.999998
SELECT SUBTIME(BIT_OR(b), '1 1:1:1.000002') FROM t1 WHERE gid = 1;
SUBTIME(BIT_OR(b), '1 1:1:1.000002')
2012-05-18 08:05:05.999998
SELECT SUBTIME(BIT_OR(a), '1 1:1:1.000002') FROM t1 WHERE gid = 2;
SUBTIME(BIT_OR(a), '1 1:1:1.000002')
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '12012-05-19 09:06:07'
SELECT SUBTIME(BIT_OR(b), '1 1:1:1.000002') FROM t1 WHERE gid = 2;
SUBTIME(BIT_OR(b), '1 1:1:1.000002')
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '120120519090607'
DROP TABLE t1;
#
# Test conversions
#
CREATE TABLE t1(vb varbinary(10), i INT, d DECIMAL(5,2), f FLOAT(5,2), dd DOUBLE(5,2), gid INT);
Warnings:
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (_binary "-98765.23", 98765, 0.0, 0.0, 0.0, 1);
INSERT INTO t1 VALUES (_binary "-98765.23", 98765, 1.0, 1.0, 1.0, 2);
SELECT
d + (vb | vb) , f + (vb | vb), dd + (vb | vb), CAST((vb | vb) AS DECIMAL(10,2)),
d + (i | i) , f + (i | i), dd + (i | i), CAST((i | i) AS DECIMAL(10,2))
FROM t1
WHERE gid = 1;
d + (vb | vb)	f + (vb | vb)	dd + (vb | vb)	CAST((vb | vb) AS DECIMAL(10,2))	d + (i | i)	f + (i | i)	dd + (i | i)	CAST((i | i) AS DECIMAL(10,2))
-98765.23	-98765.23	-98765.23	-98765.23	98765.00	98765.00	98765.00	98765.00
SELECT
d * (vb | vb) , f * (vb | vb), dd * (vb | vb),
(vb | vb) / d , (vb | vb) / f, (vb | vb) / dd,
d * (i | i) , f * (i | i), dd * (i | i),
(i | i) / d , (i | i) / f, (i | i) / dd
FROM t1 WHERE gid = 2;
d * (vb | vb)	f * (vb | vb)	dd * (vb | vb)	(vb | vb) / d	(vb | vb) / f	(vb | vb) / dd	d * (i | i)	f * (i | i)	dd * (i | i)	(i | i) / d	(i | i) / f	(i | i) / dd
-98765.23	-98765.23	-98765.23	-98765.230000	-98765.230000	-98765.230000	98765.00	98765.00	98765.00	98765.0000	98765.000000	98765.000000
SET sql_mode='';
SELECT
d + BIT_OR(vb), f + BIT_OR(vb), dd + BIT_OR(vb), CAST(BIT_OR(vb) AS DECIMAL(10,2)),
d + BIT_OR(i), f + BIT_OR(i), dd + BIT_OR(i), CAST(BIT_OR(i) AS DECIMAL(10,2))
FROM t1
WHERE gid= 1;
d + BIT_OR(vb)	f + BIT_OR(vb)	dd + BIT_OR(vb)	CAST(BIT_OR(vb) AS DECIMAL(10,2))	d + BIT_OR(i)	f + BIT_OR(i)	dd + BIT_OR(i)	CAST(BIT_OR(i) AS DECIMAL(10,2))
-98765.23	-98765.23	-98765.23	-98765.23	98765.00	98765.00	98765.00	98765.00
SELECT
d * BIT_OR(vb), f * BIT_OR(vb), dd * BIT_OR(vb), BIT_OR(vb) / d,
CAST(BIT_OR(vb) AS DECIMAL(10,2)), BIT_OR(vb) / f, BIT_OR(vb) / dd,
d * BIT_OR(i), f * BIT_OR(i), dd * BIT_OR(i), BIT_OR(i) / d,
CAST(BIT_OR(i) AS DECIMAL(10,2)), BIT_OR(i) / f, BIT_OR(i) / dd
FROM t1
WHERE gid= 2;
d * BIT_OR(vb)	f * BIT_OR(vb)	dd * BIT_OR(vb)	BIT_OR(vb) / d	CAST(BIT_OR(vb) AS DECIMAL(10,2))	BIT_OR(vb) / f	BIT_OR(vb) / dd	d * BIT_OR(i)	f * BIT_OR(i)	dd * BIT_OR(i)	BIT_OR(i) / d	CAST(BIT_OR(i) AS DECIMAL(10,2))	BIT_OR(i) / f	BIT_OR(i) / dd
-98765.23	-98765.23	-98765.23	-98765.230000	-98765.23	-98765.230000	-98765.230000	98765.00	98765.00	98765.00	98765.0000	98765.00	98765.000000	98765.000000
SELECT
d + BIT_OR(vb), f + BIT_OR(vb), dd + BIT_OR(vb),
d + BIT_OR(i), f + BIT_OR(i), dd + BIT_OR(i)
FROM t1
WHERE gid= 1
GROUP BY gid;
d + BIT_OR(vb)	f + BIT_OR(vb)	dd + BIT_OR(vb)	d + BIT_OR(i)	f + BIT_OR(i)	dd + BIT_OR(i)
-98765.23	-98765.23	-98765.23	98765.00	98765.00	98765.00
SELECT
d * BIT_OR(vb), f * BIT_OR(vb), dd * BIT_OR(vb),  BIT_OR(vb) / d,
BIT_OR(vb) / f, BIT_OR(vb) / dd,
d * BIT_OR(i), f * BIT_OR(i), dd * BIT_OR(i),  BIT_OR(i) / d,
BIT_OR(i) / f, BIT_OR(i) / dd
FROM t1
WHERE gid= 2
GROUP BY gid;
d * BIT_OR(vb)	f * BIT_OR(vb)	dd * BIT_OR(vb)	BIT_OR(vb) / d	BIT_OR(vb) / f	BIT_OR(vb) / dd	d * BIT_OR(i)	f * BIT_OR(i)	dd * BIT_OR(i)	BIT_OR(i) / d	BIT_OR(i) / f	BIT_OR(i) / dd
-98765.23	-98765.23	-98765.23	-98765.230000	-98765.230000	-98765.230000	98765.00	98765.00	98765.00	98765.0000	98765.000000	98765.000000
drop table t1;