create schema test_binary;
set current_schema to 'test_binary';
create table binary_templates (a bytea, b binary(5), c varbinary(5));

-- invalid typmod
create table invalid_table (b binary(-1));
create table invalid_table (b binary(256));
create table invalid_table (b varbinary(-1));
create table invalid_table (b varbinary(65536));

insert into binary_templates values ('aaa', 'aaa', 'aaa');
insert into binary_templates values ('aaa', 'aaa', 'aaaaa');

-- invalid insert
insert into binary_templates values ('aaaaaa', 'aaaaaa', 'aaa');
insert into binary_templates values ('aaaaaa', 'aaa', 'aaaaaa');

select * from binary_templates;

create table test_bytea (a bytea);
create table test_binary (a binary(5));
create table test_varbinary (a varbinary(5));

insert into test_bytea select a from binary_templates;
insert into test_bytea select b from binary_templates;
insert into test_bytea select c from binary_templates;

insert into test_binary select a from binary_templates;
insert into test_binary select b from binary_templates;
insert into test_binary select c from binary_templates;

insert into test_varbinary select a from binary_templates;
insert into test_varbinary select b from binary_templates;
insert into test_varbinary select c from binary_templates;

create table shorter_binary (a binary(3));
create table shorter_varbinary (a varbinary(3));
insert into shorter_binary select b from binary_templates;
insert into shorter_varbinary select c from binary_templates;

create table test_index (a binary(10), b varbinary(10));
create index on test_index using btree (a);
create index on test_index using hash (a);
create index on test_index using gin (to_tsvector(a::text));
create index on test_index using btree (b);
create index on test_index using hash (b);
create index on test_index using gin (to_tsvector(b::text));
\d test_index

create cast (text as binary) without function as implicit;
create cast (text as varbinary) without function as implicit;

create table test_text2binary (a binary(255), b varbinary(65535));
insert into test_text2binary values(repeat('a', 256), repeat('a', 65536));
insert into test_text2binary values(repeat('a', 255), repeat('a', 65535));
select octet_length(a) as binary_length, octet_length(b) as varbinary_length from test_text2binary;
drop cast if exists (text as binary);
drop cast if exists (text as varbinary);
drop table binary_templates;
drop table test_binary;
drop table test_varbinary;
drop table shorter_binary;
drop table shorter_varbinary;
drop table test_index;
drop table test_text2binary;

drop table if exists t_binary_061;
create table t_binary_061(id int, field_name binary(10));
PREPARE insert_binary(int,binary(10)) as insert into t_binary_061 values($1,$2);
EXECUTE insert_binary(1, 'aaa'::bytea);  -- length 3
EXECUTE insert_binary(1, 'aaaaaaaaaaa'::bytea);  -- length 11

drop table if exists t_varbinary_061;
create table t_varbinary_061(id int, field_name varbinary(10));
PREPARE insert_varbinary(int,varbinary(10)) as insert into t_varbinary_061 values($1,$2);
EXECUTE insert_varbinary(1, 'aaa'::bytea);  -- length 3
EXECUTE insert_varbinary(1, 'aaaaaaaaaaa'::bytea); -- length 11

select * from t_binary_061;
select * from t_varbinary_061;

drop table if exists t_binary_061;
drop table if exists t_varbinary_061;

-- binary test enhance
select binary '\t';
select binary '\\';
select binary '\a';
select binary '\b';
select binary '\n';
select binary '\r';
select binary '\v';
select binary '\f';
select binary '\"';
select binary '\%';
select binary '\_';
select binary '\0';
select binary '\z';
select binary '\pqy';
select binary '数据库';
select binary E'\t';
select binary E'\\';
select binary E'\a';
select binary E'\b';
select binary E'\n';
select binary E'\r';
select binary E'\v';
select binary E'\f';
select binary E'\"';
select binary E'\%';
select binary E'\_';
select binary E'\0';
select binary E'\z';
select binary E'\pqy';
select binary E'数据库';
-- binary type cast test
select 'abc'::binary;
select 'abcdefgh'::binary;
select 'abc'::binary(20);
select 'a啊填啊'::binary;
-- other type
select 123::binary;
select 123.456::binary;
select '2020-01-01'::date::binary;
select '12:13:13'::time::binary;

--errreport
select 'abc'::binary(-1);
select 'abc'::binary(1);
--binary in like and escape

select 'abcd' like binary 'abc%';
select 'abcd' like binary 'abc\%';
select 'abcd' like binary 'abc\%' escape '\';
select 'abcd' like binary 'abc|%' escape '|';
select 'abc%' like binary 'abc|%' escape '|';
select 'abcd' like binary 'abc_';
select 'abcd' like binary 'abc\_';
select 'abcd' like binary 'abc\_' escape '\';
select 'abcd' like binary 'abc|_' escape '|';
select 'abc%' like binary 'abc|_' escape '|';

-- test binary expr gram;
select binary sin(1);
drop table if exists t1;
create table t1(a int, b text);
insert into t1 values(1,'test');
select binary a from t1;
select binary b from t1;
select binary a =  binary '3' from t1;

--test create binary table 

drop table if exists t1;
create table t1(a binary, b text);
insert into t1 values('a','name1');
insert into t1 values('ad','name2');
select * from t1;

-- enhase origin request 
SELECT BINARY 'Geeksforgeeks';
select binary repeat('a', 3);
create table test33 (c binary(3));
insert into test33 set c = 'a';
select hex(c), c = 'a', c = 'a\0\0' from test33;
-- binary operator
select c > 'a\0\0' from test33;
select c <> 'a\0\0' from test33;
select c >= 'a\0\0' from test33;
select c <= 'a\0\0' from test33;
select c != 'a\0\0' from test33;

--test binary core dump bug
select cast('a' as binary(0));
select cast(''	as binary(0));
select cast('ab' as binary(12));
create table test34 (a binary(0));
insert into test34 values('a');
insert into test34 values('');

SELECT 'ab'::binary = 'ab';
SELECT 'ab'::binary < 'ab';
SELECT 'ab'::binary <= 'ab';
SELECT 'ab'::binary > 'ab';
SELECT 'ab'::binary >= 'ab';
SELECT 'ab'::binary <> 'ab';

SELECT 'ab' = 'ab'::binary;
SELECT 'ab' < 'ab'::binary;
SELECT 'ab' <= 'ab'::binary;
SELECT 'ab' > 'ab'::binary;
SELECT 'ab' >= 'ab'::binary;
SELECT 'ab' <> 'ab'::binary;

SELECT 'ab'::binary = 'ab'::binary;
SELECT 'ab'::binary < 'ab'::binary;
SELECT 'ab'::binary <= 'ab'::binary;
SELECT 'ab'::binary > 'ab'::binary;
SELECT 'ab'::binary >= 'ab'::binary;
SELECT 'ab'::binary <> 'ab'::binary;

SELECT 'ab'::binary = 'ab'::varbinary(2);
SELECT 'ab'::binary < 'ab'::varbinary(2);
SELECT 'ab'::binary <= 'ab'::varbinary(2);
SELECT 'ab'::binary > 'ab'::varbinary(2);
SELECT 'ab'::binary >= 'ab'::varbinary(2);
SELECT 'ab'::binary <> 'ab'::varbinary(2);

SELECT 'ab'::varbinary(2) = 'ab';
SELECT 'ab'::varbinary(2) < 'ab';
SELECT 'ab'::varbinary(2) <= 'ab';
SELECT 'ab'::varbinary(2) > 'ab';
SELECT 'ab'::varbinary(2) >= 'ab';
SELECT 'ab'::varbinary(2) <> 'ab';

SELECT 'ab' = 'ab'::varbinary(2);
SELECT 'ab' < 'ab'::varbinary(2);
SELECT 'ab' <= 'ab'::varbinary(2);
SELECT 'ab' > 'ab'::varbinary(2);
SELECT 'ab' >= 'ab'::varbinary(2);
SELECT 'ab' <> 'ab'::varbinary(2);

SELECT 'ab'::varbinary(2) = 'ab'::varbinary(2);
SELECT 'ab'::varbinary(2) < 'ab'::varbinary(2);
SELECT 'ab'::varbinary(2) <= 'ab'::varbinary(2);
SELECT 'ab'::varbinary(2) > 'ab'::varbinary(2);
SELECT 'ab'::varbinary(2) >= 'ab'::varbinary(2);
SELECT 'ab'::varbinary(2) <> 'ab'::varbinary(2);

SELECT 'ab'::varbinary(2) = 'ab'::binary;
SELECT 'ab'::varbinary(2) < 'ab'::binary;
SELECT 'ab'::varbinary(2) <= 'ab'::binary;
SELECT 'ab'::varbinary(2) > 'ab'::binary;
SELECT 'ab'::varbinary(2) >= 'ab'::binary;
SELECT 'ab'::varbinary(2) <> 'ab'::binary;

set dolphin.b_compatibility_mode to on;
create table binary_operator(c1 binary(10), c2 numeric(10, 2), c3 time(6), c4 uint1, c5 uint2, c6 uint4, c7 uint8);
insert into binary_operator values('34', '234.5', '234.5', 234, 234, 234, 234), ('1234', '234.5', '234.5', 234, 234, 234, 234);

select c1 < c2, c1 > c2, c1 <= c2, c1 >= c2 from binary_operator;
select c1 < c3, c1 > c3, c1 <= c3, c1 >= c3 from binary_operator;
select c1 < c4, c1 > c4, c1 <= c4, c1 >= c4 from binary_operator;
select c1 < c5, c1 > c5, c1 <= c5, c1 >= c5 from binary_operator;
select c1 < c6, c1 > c6, c1 <= c6, c1 >= c6 from binary_operator;
select c1 < c7, c1 > c7, c1 <= c7, c1 >= c7 from binary_operator;

-- binary to bigint
select (20220101)::binary(30)::bigint;
select (-2075)::binary(30)::bigint;

--binary/varbinary index test
create table t_index_test(a binary(100), b varbinary(100));
insert into t_index_test select i,i from generate_series(1,10000) i;
create index i_b on t_index_test(a);
create index i_vb on t_index_test(b);
analyze t_index_test;
explain (costs off) select * from t_index_test where a='1';
explain (costs off) select * from t_index_test where a>='a1';
explain (costs off) select * from t_index_test where a>'a1';
explain (costs off) select * from t_index_test where a<='1';
explain (costs off) select * from t_index_test where a<'1';

explain (costs off) select * from t_index_test where b='1';
explain (costs off) select * from t_index_test where b>='a1';
explain (costs off) select * from t_index_test where b>'a1';
explain (costs off) select * from t_index_test where b<='1';
explain (costs off) select * from t_index_test where b<'1';

drop index i_b;
drop index i_vb;
create index i_b on t_index_test(a) using hash;
create index i_vb on t_index_test(b) using hash;
analyze t_index_test;
explain (costs off) select * from t_index_test where a='1';

explain (costs off) select * from t_index_test where b='1';

drop table t_index_test;

-- binary about concat
DROP TABLE IF EXISTS t1;
SET dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length,auto_recompile_function,error_for_division_by_zero,treat_bxconst_as_binary';
CREATE TABLE t1 (s1 binary(2), s2 varbinary(2));
INSERT INTO t1 VALUES (0x4100, 0x4100);
SELECT HEX(concat('*', s1, '*', s2, '*')) FROM t1;
SELECT HEX(concat('*', s1, '*', s2, '*')) FROM t1;
SELECT HEX(s1), HEX(s2), HEX('*') FROM t1;
DROP TABLE t1;

-- test about set to binary
set dolphin.b_compatibility_mode=on;
set bytea_output=escape;
drop table if exists t_set0004;
create table t_set0004(
c1 int not null auto_increment primary key,
c2 set('2011-11-11', '2023-02-28 11:23:00', '2024-01', '2025/01/01')
default null,
c3 set('red', 'yellow', 'blue') not null,
c4 set('0', '1', '1.01314'));
insert into t_set0004(c2, c3, c4) values ('2025/01/01', 'blue', '0');
insert into t_set0004(c2, c3, c4) values (
'2011-11-11,2023-02-28 11:23:00', 'red,yellow', '0,1');
insert into t_set0004(c2, c3, c4) values (
'2024-01,2011-11-11,2025/01/01', 'red,blue', '0,1.01314');
insert into t_set0004(c2, c3) values ('2023-02-28 11:23:00', 'red');
insert into t_set0004(c2, c3) values (
'2023-02-28 11:23:00,2025/01/01,2025/01/01', 'blue,blue,yellow');
insert into t_set0004(c3) values ('yellow');
insert into t_set0004(c3) values ('yellow,yellow,yellow,yellow');
insert into t_set0004(c3) values ('blue,yellow,red,red');
insert into t_set0004(c3) values ('blue,red');
insert into t_set0004(c3, c4) values ('red', '1');
insert into t_set0004(c3, c4) values ('red,red', '1.01314,1.01314');
insert into t_set0004(c3, c4) values ('red,blue', '0,1,1.01314');
select cast(c1 as binary(1)), cast(c2 as binary(1)), cast(c3 as binary(1)),
cast(c4 as binary(1)) from t_set0004 order by 1,2,3,4;
select convert(c1, binary(1)), convert(c2, binary(1)), convert(c3, binary(1)),
convert(c4, binary(1)) from t_set0004 order by 1,2,3,4;
select cast('2023-1-12' as binary(1));
drop table t_set0004;
drop table if exists test_ignore;
create table test_ignore (a binary(1), b varbinary(1));
insert into test_ignore(a) values(cast('2023-1-12' as binary(1)));
insert into test_ignore(b) values(cast('2023-1-12' as binary(1)));
insert ignore into test_ignore values(cast('2023-1-12' as binary(1)), cast('2023-1-12' as varbinary(1)));
drop table test_ignore;

set dolphin.b_compatibility_mode=on;
set b_format_behavior_compat_options=enable_set_variables;
set bytea_output=escape;
drop table if exists t_binary0002 cascade;
create table t_binary0002(
c1 int not null,
c2 binary,
c3 binary(10),
c4 binary(255),
c5 varbinary(1),
c6 varbinary(10),
c7 varbinary(255)) charset utf8mb3;
set @v1='abcdefghijklmnopqrstuvwxyz';
set @v2='a熊猫竹竹爱吃竹子';
set @v3=hex(@v2);
set @v4=unhex(@v3);
set @v5=bin(121314);
set @v6=oct(999999);
insert into t_binary0002 values (
1, substr(@v1,1,1), substr(@v1,1,10), repeat(@v1, 9),
substr(@v1,1,1), substr(@v1,1,10), repeat(@v1, 9));
insert into t_binary0002 values (
2, substr(@v2,1,1), substr(@v2,1,4), repeat(@v2, 9),
substr(@v2,1,1), substr(@v2,1,4), repeat(@v2, 9));
insert into t_binary0002 values (
3, substr(@v3,1,1), substr(@v3,1,4), substr(repeat(@v3, 10),1,255),
substr(@v3,1,1), substr(@v3,1,4), substr(repeat(@v3, 10),1,255));
insert into t_binary0002 values (
4, substr(@v4,1,1), substr(@v4,1,4), substr(repeat(@v4, 10),1,255),
substr(@v4,1,1), substr(@v4,1,4), substr(repeat(@v4, 10),1,255));
insert into t_binary0002 values (
5, substr(@v5,1,1), substr(@v5,1,4), substr(repeat(@v5, 10),1,255),
substr(@v5,1,1), substr(@v5,1,4), substr(repeat(@v5, 10),1,255));
insert into t_binary0002 values (
6, substr(@v6,1,1), substr(@v6,1,4), substr(repeat(@v6, 10),1,255),
substr(@v6,1,1), substr(@v6,1,4), substr(repeat(@v6, 10),1,255));
select c1, cast(c2 as char), cast(c3 as char), cast(c4 as char), cast(c5 as
char), cast(c6 as char), cast(c7 as char) from t_binary0002 order by
1,2,3,4,5,6,7;

SELECT repeat('a'::tinyblob, 5)::binary(10);
SELECT repeat('a'::tinyblob, 100)::binary(10);
SELECT repeat('a'::tinyblob, 5)::varbinary(10);
SELECT repeat('a'::tinyblob, 100)::varbinary(10);
SELECT repeat('a'::blob, 5)::binary(10);
SELECT repeat('a'::blob, 100)::binary(10);
SELECT repeat('a'::blob, 5)::varbinary(10);
SELECT repeat('a'::blob, 100)::varbinary(10);
SELECT repeat('a'::mediumblob, 5)::binary(10);
SELECT repeat('a'::mediumblob, 100)::binary(10);
SELECT repeat('a'::mediumblob, 5)::varbinary(10);
SELECT repeat('a'::mediumblob, 100)::varbinary(10);
SELECT repeat('a'::longblob, 5)::binary(10);
SELECT repeat('a'::longblob, 100)::binary(10);
SELECT repeat('a'::longblob, 5)::varbinary(10);
SELECT repeat('a'::longblob, 100)::varbinary(10);

drop table if exists binary_operator;
reset dolphin.b_compatibility_mode;

create table t_v22(col1 char(100));
create table t_v23(col1 varchar(100));
create table t_v30(col1 text);
create table t_v24(col1 binary(100));
create table t_v25(col1 varbinary(100));
create table t_v27(col1 blob);


set dolphin.b_compatibility_mode to on;

insert into t_v22 values('1.23a');
insert into t_v23 values('1.23a');
insert into t_v30 values('1.23a');
insert into t_v24 values('1.23a');
insert into t_v25 values('1.23a');
insert into t_v27 values('1.23a');

set dolphin.sql_mode=''; -- 关闭pad_char_to_full_length
select * from t_v22 inner join t_v24 on t_v22.col1=t_v24.col1;
select * from t_v22 inner join t_v25 on t_v22.col1=t_v25.col1;
select * from t_v22 inner join t_v27 on t_v22.col1=t_v27.col1;

select * from t_v23 inner join t_v24 on t_v23.col1=t_v24.col1;
select * from t_v23 inner join t_v25 on t_v23.col1=t_v25.col1;
select * from t_v23 inner join t_v27 on t_v23.col1=t_v27.col1;

select * from t_v30 inner join t_v24 on t_v30.col1=t_v24.col1;
select * from t_v30 inner join t_v25 on t_v30.col1=t_v25.col1;
select * from t_v30 inner join t_v27 on t_v30.col1=t_v27.col1;

set dolphin.sql_mode='pad_char_to_full_length';

select * from t_v22 inner join t_v24 on t_v22.col1=t_v24.col1;
select * from t_v22 inner join t_v25 on t_v22.col1=t_v25.col1;
select * from t_v22 inner join t_v27 on t_v22.col1=t_v27.col1;

select * from t_v23 inner join t_v24 on t_v23.col1=t_v24.col1;
select * from t_v23 inner join t_v25 on t_v23.col1=t_v25.col1;
select * from t_v23 inner join t_v27 on t_v23.col1=t_v27.col1;

select * from t_v30 inner join t_v24 on t_v30.col1=t_v24.col1;
select * from t_v30 inner join t_v25 on t_v30.col1=t_v25.col1;
select * from t_v30 inner join t_v27 on t_v30.col1=t_v27.col1;

reset dolphin.sql_mode;

set b_format_behavior_compat_options = 'enable_multi_charset';
create table t_v31(a bool, b tinyblob, c blob, d mediumblob, e longblob);
insert into t_v31 values(true, '+1.1', '+1.1', '+1.1', '+1.1');
explain (costs off) select * from t_v31 where a < b;
select a < b, a < c, a < d, a < e from t_v31;
drop table t_v31;

set collation_connection = 'utf8mb4_general_ci';
create table t_binary_text_8175_l(a binary(100));
create table t_binary_text_8175_r(a text);
insert into t_binary_text_8175_l values('1.23a');
insert into t_binary_text_8175_r values('1.23a');
select t_binary_text_8175_l.a = t_binary_text_8175_r.a as b_eq_t,
       t_binary_text_8175_l.a <> t_binary_text_8175_r.a as b_ne_t,
       t_binary_text_8175_l.a < t_binary_text_8175_r.a as b_lt_t,
       t_binary_text_8175_l.a <= t_binary_text_8175_r.a as b_le_t,
       t_binary_text_8175_l.a > t_binary_text_8175_r.a as b_gt_t,
       t_binary_text_8175_l.a >= t_binary_text_8175_r.a as b_ge_t
from t_binary_text_8175_l, t_binary_text_8175_r;
select t_binary_text_8175_r.a = t_binary_text_8175_l.a as t_eq_b,
       t_binary_text_8175_r.a <> t_binary_text_8175_l.a as t_ne_b,
       t_binary_text_8175_r.a < t_binary_text_8175_l.a as t_lt_b,
       t_binary_text_8175_r.a <= t_binary_text_8175_l.a as t_le_b,
       t_binary_text_8175_r.a > t_binary_text_8175_l.a as t_gt_b,
       t_binary_text_8175_r.a >= t_binary_text_8175_l.a as t_ge_b
from t_binary_text_8175_l, t_binary_text_8175_r;
drop table t_binary_text_8175_l, t_binary_text_8175_r;
set collation_connection = 'binary';

set dolphin.sql_mode = 'pipes_as_concat';
CREATE TABLE test_multi_type_table
(
    `int1` tinyint,
    `uint1` tinyint unsigned,
    `int2` smallint,
    `uint2` smallint unsigned,
    `int4` integer,
    `uint4` integer unsigned,
    `int8` bigint,
    `uint8` bigint unsigned,
    `float4` float4,
    `float8` float8,
    `numeric` decimal(20, 6),
    `bit1` bit(1),
    `bit64` bit(64),
    `boolean` boolean,
    `char` char(100),
    `varchar` varchar(100),
    `binary` binary(100),
    `varbinary` varbinary(100),
    `text` text
);
INSERT INTO test_multi_type_table VALUES(0x7F, 0xFF, 0x7FFF, 0xFFFF, 0x7FFFFFFF, 0xFFFFFFFF, 0x7FFFFFFFFFFFFFFF, 0xFFFFFFFFFFFFFFFF, 0.9884282, 5.288433915413254, 43391541, b'1', b'100110100110', true, '1.23a', '1.23a', '1.23a', '1.23a', '1.23a');

CREATE TABLE test_multi_type SELECT
`int1` || `binary` AS `int1_||_binary`,
`int1` || `varbinary` AS `int1_||_varbinary`
FROM test_multi_type_table;
drop table test_multi_type;
drop table test_multi_type_table;

drop schema test_binary cascade;
reset current_schema;