-- name: test_binary_in_nested

-- Verify that BINARY / VARBINARY data inside nested types (STRUCT / ARRAY / MAP)

-- is serialised as a hex-encoded string rather than raw bytes in the MySQL

-- result set, which keeps the JSON-like output well-formed.

create database db_${uuid0};

use db_${uuid0};

-- -----------------------------------------------------------------------

-- STRUCT with BINARY and VARBINARY fields.

-- Both should appear as lowercase hex; STRING field stays as plain text.

-- -----------------------------------------------------------------------

create table t_struct_binary (

id int,

payload struct<bin_field varbinary, str_field varchar(64)>

) duplicate key(id)

distributed by hash(id)

buckets 1

properties('replication_num'='1');

insert into t_struct_binary

select 1, named_struct('bin_field', unhex('DEADBEEF'), 'str_field', 'hello')

union all

select 2, named_struct('bin_field', unhex('CAFE'), 'str_field', 'world')

union all

select 3, named_struct('bin_field', null, 'str_field', 'null_bin');

select id, payload from t_struct_binary order by id;

-- -----------------------------------------------------------------------

-- ARRAY<VARBINARY>: each element should appear as lowercase hex.

-- -----------------------------------------------------------------------

create table t_array_binary (

id int,

bins array<varbinary>

) duplicate key(id)

distributed by hash(id)

buckets 1

properties('replication_num'='1');

insert into t_array_binary values

(1, [unhex('AABB'), unhex('CCDD')]),

(2, [unhex('00'), null]),

(3, null);

select id, bins from t_array_binary order by id;

-- -----------------------------------------------------------------------

-- MAP<VARCHAR, VARBINARY>: values should appear as lowercase hex.

-- -----------------------------------------------------------------------

create table t_map_binary (

id int,

kv map<varchar(32), varbinary>

) duplicate key(id)

distributed by hash(id)

buckets 1

properties('replication_num'='1');

insert into t_map_binary values

(1, map{'k1': unhex('CAFE'), 'k2': unhex('BABE')}),

(2, map{'only': null});

select id, kv from t_map_binary order by id;

-- -----------------------------------------------------------------------

-- Constant nested literals should also be encoded correctly.

-- Use TO_BINARY(..., 'hex') here so the test stays scoped to VARBINARY output

-- without changing UNHEX() semantics in this PR.

-- -----------------------------------------------------------------------

select [to_binary('AABB', 'hex'), null];

select named_struct('bin_field', to_binary('DEADBEEF', 'hex'), 'arr_field',

[to_binary('00', 'hex'), to_binary('01', 'hex')]);

select map{'k1': to_binary('CAFE', 'hex'), 'k2': null};

set binary_encoding_format = 'base64';

select [to_binary('AABB', 'hex'), null];

set binary_encoding_format = 'hex';

set binary_encoding_level = 'all';

select to_binary('AABB', 'hex');

set binary_encoding_format = 'base64';

select to_binary('AABB', 'hex');

set binary_encoding_format = 'raw';

set binary_encoding_level = 'nested';

select [to_binary('414243', 'hex'), null];

set binary_encoding_level = 'all';

select to_binary('414243', 'hex');

set binary_encoding_format = 'hex';

set binary_encoding_level = 'nested';

drop database db_${uuid0};