# The include statement below is a temp one for tests that are yet to
#be ported to run with InnoDB,
#but needs to be kept for tests that would need MyISAM in future.
--source include/force_myisam_default.inc

# This test uses chmod, can't be run with root permissions
-- source include/not_as_root.inc


# Initialise
--disable_warnings
drop table if exists t1;
--enable_warnings

#
# varbinary as string and number
#

select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 ;
select 0x31+1,concat(0x31)+1,-0xf;
select x'31',X'ffff'+0;

#
# Test of hex constants in WHERE:
#

create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) );
insert into t1 set UNIQ=0x38afba1d73e6a18a;
insert into t1 set UNIQ=123; 
explain extended select * from t1 where UNIQ=0x38afba1d73e6a18a;
drop table t1;

#
# Test error conditions
#
--error 1064
select x'hello';
--error 1054
select 0xfg;

#
# Test likely error conditions
#
create table t1 select 1 as x, 2 as xx;
select x,xx from t1;
drop table t1;

# End of 4.1 tests

#
# Bug #19371 VARBINARY() have trailing zeros after upgrade from 4.1
#

# Test with a saved table from 4.1
let $MYSQLD_DATADIR= `select @@datadir`;
copy_file std_data/bug19371.frm $MYSQLD_DATADIR/test/t1.frm;
chmod 0777 $MYSQLD_DATADIR/test/t1.frm;
copy_file std_data/bug19371.MYD $MYSQLD_DATADIR/test/t1.MYD;
chmod 0777 $MYSQLD_DATADIR/test/t1.MYD;
copy_file std_data/bug19371.MYI $MYSQLD_DATADIR/test/t1.MYI;
chmod 0777 $MYSQLD_DATADIR/test/t1.MYI;

# Everything _looks_ fine
show create table t1;

# But the length of the varbinary columns are too long
select length(a), length(b) from t1;

# Run CHECK TABLE, it should indicate table need a REPAIR TABLE
CHECK TABLE t1 FOR UPGRADE;

# Run REPAIR TABLE to alter the table and repair
# the varbinary fields
REPAIR TABLE t1;

# Now check it's back to normal
show create table t1;
select length(a), length(b) from t1;
insert into t1 values("ccc", "ddd");
select length(a), length(b) from t1;
select hex(a), hex(b) from t1;
select concat("'", a, "'"), concat("'", b, "'") from t1;

drop table t1;

# Check that the fix does not affect table created with current version
create table t1(a varbinary(255));
insert into t1 values("aaa   ");
select length(a) from t1;
alter table t1 modify a varchar(255);
select length(a) from t1;
drop table t1;


#
# Bug#28127 (Some valid identifiers names are not parsed correctly)
#

--disable_warnings
drop table if exists table_28127_a;
drop table if exists table_28127_b;
--enable_warnings

create table table_28127_a(0b02 int);
show create table table_28127_a;

create table table_28127_b(0b2 int);
show create table table_28127_b;

drop table table_28127_a;
drop table table_28127_b;

#
# Bug#35658 (An empty binary value leads to mysqld crash)
#

select 0b01000001;

select 0x41;

select b'01000001';

select x'41', 0+x'41';

select N'abc', length(N'abc');

select N'', length(N'');

select '', length('');

select b'', 0+b'';

select x'', 0+x'';

--error ER_BAD_FIELD_ERROR
select 0x;

--error ER_BAD_FIELD_ERROR
select 0b;


create TABLE t1(a INT, b VARBINARY(10), c VARBINARY(10));
INSERT INTO t1 VALUES
(1, 0x31393831, 0x31303037),
(2, 0x31393832, 0x31303038),
(3, 0x31393833, 0x31303039),
(3, 0x31393834, 0x31393831),
(4, 0x31393835, 0x31393832),
(5, 0x31393836, 0x31303038);

--echo #
--echo # deprecation warnings
--echo #

SELECT
b & c, b & 0x31393838, b & NULL, b & 0b1011,
0x31393838 & b, NULL & b, 0b1011 & b
FROM t1;

SELECT
b | c, b | 0x31393838, b | NULL, b | 0b1011,
0x31393838 | b, NULL | b, 0b1011 | b
FROM t1;

SELECT
b ^ c, b ^ 0x31393838, b ^ NULL, b ^ 0b1011,
0x31393838 ^ b, NULL ^ b, 0b1011 ^ b
FROM t1;

SELECT BIT_COUNT(b), ~b, b << 1, b >> 1 from t1;
SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1 GROUP BY a;
SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1;

--echo #
--echo # stored procedures with warnings
--echo #

CREATE PROCEDURE test_bin_op()
SELECT
b & c, b & 0x31393838, b & NULL, b & 0b1011,
0x31393838 & b, NULL & b, 0b1011 & b,
b | c, b | 0x31393838, b | NULL, b | 0b1011,
0x31393838 | b, NULL | b, 0b1011 | b,
b ^ c, b ^ 0x31393838, b ^ NULL, b ^ 0b1011,
0x31393838 ^ b, NULL ^ b, 0b1011 ^ b,
BIT_COUNT(b), ~b, b << 1, b >> 1
FROM t1;

CALL test_bin_op();
RESET QUERY CACHE;
CALL test_bin_op();
DROP PROCEDURE test_bin_op;

CREATE PROCEDURE test_bin_op()
SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1 GROUP BY a;

CALL test_bin_op();
RESET QUERY CACHE;
CALL test_bin_op();
DROP PROCEDURE test_bin_op;

CREATE PROCEDURE test_bin_op()
SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1;

CALL test_bin_op();
RESET QUERY CACHE;
CALL test_bin_op();
DROP PROCEDURE test_bin_op;

--echo #
--echo # prepared statements with warnings
--echo #

PREPARE s1 FROM
"SELECT
b & c, b & 0x31393838, b & NULL, b & 0b1011,
0x31393838 & b, NULL & b, 0b1011 & b,
b | c, b | 0x31393838, b | NULL, b | 0b1011,
0x31393838 | b, NULL | b, 0b1011 | b,
b ^ c, b ^ 0x31393838, b ^ NULL, b ^ 0b1011,
0x31393838 ^ b, NULL ^ b, 0b1011 ^ b,
BIT_COUNT(b), ~b, b << 1, b >> 1
FROM t1;";

EXECUTE s1;
RESET QUERY CACHE;
EXECUTE s1;

PREPARE s2 from "SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1 GROUP BY a";
EXECUTE s2;
RESET QUERY CACHE;
EXECUTE s2;

PREPARE s2 from "SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1";
EXECUTE s2;
RESET QUERY CACHE;
EXECUTE s2;

--echo #
--echo # views with warnings
--echo #

CREATE VIEW v1 AS
SELECT
b & c, b & 0x31393838, b & NULL, b & 0b1011,
0x31393838 & b, NULL & b, 0b1011 & b,
b | c, b | 0x31393838, b | NULL, b | 0b1011,
0x31393838 | b, NULL | b, 0b1011 | b,
b ^ c, b ^ 0x31393838, b ^ NULL, b ^ 0b1011,
0x31393838 ^ b, NULL ^ b, 0b1011 ^ b,
BIT_COUNT(b), ~b, b << 1, b >> 1
FROM t1;
SELECT * from v1;
RESET QUERY CACHE;
SELECT * from v1;

CREATE VIEW v2 AS
SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1 GROUP BY a;
SELECT * from v2;
RESET QUERY CACHE;
SELECT * from v2;

CREATE VIEW v3 AS
SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM t1;
SELECT * from v3;
RESET QUERY CACHE;
SELECT * from v3;

DROP VIEW v1,v2,v3;

--echo #
--echo # working as before
--echo #

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;

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;

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;

SELECT a, BIT_AND(a), BIT_OR(a), BIT_XOR(a),
~NULL, NULL << 1, NULL >> 1,
~0x31393838, 0x31393838 << 1, 0x31393838 >> 1,
~0b1011, 0b1011 << 1, 0b1011 >> 1
FROM t1
GROUP BY a;

--echo #
--echo # binary/varbinary vs char/varchar
--echo #

SELECT '12' | '12';
SELECT _binary '12' | '12';
SELECT _binary '12' | _binary '12';
SELECT _binary '12' | 0x01;
SELECT _binary '12' | 1;
SELECT binary '12' | '12';
SELECT binary '12' | binary '12';
SELECT binary '12' | 0x01;
SELECT binary '12' | 1;
SELECT '12' | 0x01;
SELECT '12' | 1;
SELECT CAST('12' AS binary) | 0x01;
SELECT CAST('12' AS binary) | 1;
SELECT CAST(b AS char) | 0x31393838 FROM t1 LIMIT 1;
SELECT (b + 0) | 0x31393838 FROM t1 LIMIT 1;
SELECT CAST(0x01 AS char) | 0x31393838 FROM t1 LIMIT 1;
SELECT 0x01 << 1;
SELECT _binary '12' << 1;
SELECT binary '12' << 1;
SELECT CAST('12' AS binary) | 1;
SELECT CAST(b AS char) << 1 FROM t1 LIMIT 1;
SELECT CAST(b AS unsigned) << 1 FROM t1 LIMIT 1;
SELECT (b + 0) << 1 FROM t1 LIMIT 1;
SELECT CAST(b AS unsigned) | 0x31393838 FROM t1 LIMIT 1;
SELECT CAST(b AS unsigned) | CAST(c AS unsigned) FROM t1 LIMIT 1;

DROP TABLE t1;