# name: test/sql/functions/copy_function.test
# description: duck_copy_function 的 COPY TO（动态列）：bind 读动态 schema 与选项、sink 写动态行、嵌套类型写出、多数据块、空结果、选项与错误处理
# group: [functions]
require duckfn
# dfn_copy_tsv 是扩展注册的自定义 COPY TO 格式：制表符分隔、每行一条记录、NULL 写作 \N、
# 字符串里的反斜杠/制表符/换行会转义。
# 与静态列版本不同，输出列的类型是 bind 阶段才由 DuckDB 给出的，因此 LIST / STRUCT / MAP 也能写出。
# __TEST_DIR__ 是测试运行器提供的临时目录；标量部分用内置 read_csv 读回来做往返校验。
# ============================================================================
# 基本往返：BIGINT + VARCHAR，NULL 写作 \N（写出的文件本身仍是标准 TSV）
# ============================================================================
statement ok
COPY (SELECT 1 AS i, 'a' AS s UNION ALL SELECT 2, NULL) TO '__TEST_DIR__/dfn_copy_basic.tsv' (FORMAT dfn_copy_tsv);
query IT
SELECT * FROM read_csv('__TEST_DIR__/dfn_copy_basic.tsv', delim='\t', header=false, nullstr='\N', columns={'i': 'BIGINT', 's': 'VARCHAR'});
----
1 a
2 NULL
# ============================================================================
# 多数据块：5000 行会跨多个 DuckDB 向量（每个向量 2048 行），sink 阶段每块被调用一次
# ============================================================================
statement ok
COPY (SELECT range AS i, 'v' || range AS s FROM range(5000)) TO '__TEST_DIR__/dfn_copy_big.tsv' (FORMAT dfn_copy_tsv);
query II
SELECT count(*), sum(i) FROM read_csv('__TEST_DIR__/dfn_copy_big.tsv', delim='\t', header=false, columns={'i': 'BIGINT', 's': 'VARCHAR'});
----
5000 12497500
# ============================================================================
# 表形式（COPY <表> TO ...）与多种标量类型：BOOLEAN / DOUBLE / 可空 DOUBLE
# ============================================================================
statement ok
CREATE TABLE dfn_copy_src(i BIGINT, b BOOLEAN, d DOUBLE, s VARCHAR);
statement ok
INSERT INTO dfn_copy_src VALUES (3, true, 1.5, 'x'), (4, false, NULL, 'y');
statement ok
COPY dfn_copy_src TO '__TEST_DIR__/dfn_copy_table.tsv' (FORMAT dfn_copy_tsv);
query T
SELECT i::VARCHAR || '|' || b::VARCHAR || '|' || coalesce(d::VARCHAR, 'NULL') || '|' || s
FROM read_csv('__TEST_DIR__/dfn_copy_table.tsv', delim='\t', header=false, nullstr='\N', columns={'i': 'BIGINT', 'b': 'BOOLEAN', 'd': 'DOUBLE', 's': 'VARCHAR'});
----
3|true|1.5|x
4|false|NULL|y
# ============================================================================
# 动态列：嵌套类型也能写出（编码约定见 test/extension/functions/tsv_format.rs）
# 容器 → [1, 2] / {'k': 1} / {'m'=1}；BLOB → \xHH；嵌套里的字符串带引号
# ============================================================================
statement ok
COPY (SELECT [1, 2] AS l, {'k': 1} AS st, MAP {'m': 1} AS mp, '\x01\x02'::BLOB AS bl) TO '__TEST_DIR__/dfn_copy_nested.tsv' (FORMAT dfn_copy_tsv);
query TTTT
SELECT * FROM read_csv('__TEST_DIR__/dfn_copy_nested.tsv', delim='\t', header=false, columns={'l': 'VARCHAR', 'st': 'VARCHAR', 'mp': 'VARCHAR', 'bl': 'VARCHAR'});
----
[1, 2] {'k': 1} {'m'=1} \x01\x02
# 嵌套里的字符串带引号，因此字符串 "NULL" 与真正的 NULL 不会混淆
statement ok
COPY (SELECT ['NULL', NULL] AS l) TO '__TEST_DIR__/dfn_copy_null_string.tsv' (FORMAT dfn_copy_tsv);
query T
SELECT * FROM read_csv('__TEST_DIR__/dfn_copy_null_string.tsv', delim='\t', header=false, columns={'l': 'VARCHAR'});
----
['NULL', NULL]
# 含逗号与制表符的字符串：逗号在顶层单元格里无需转义，制表符必须转义（否则会破坏列分隔）
statement ok
COPY (SELECT 'a,b' AS c, 'tab' || chr(9) || 'tab' AS t) TO '__TEST_DIR__/dfn_copy_escape.tsv' (FORMAT dfn_copy_tsv);
query TT
SELECT * FROM read_csv('__TEST_DIR__/dfn_copy_escape.tsv', delim='\t', header=false, columns={'c': 'VARCHAR', 't': 'VARCHAR'});
----
a,b tab\ttab
# ============================================================================
# 空结果集：global_init 仍然会建出文件，只是没有任何记录
# ============================================================================
statement ok
COPY (SELECT 1 AS i WHERE false) TO '__TEST_DIR__/dfn_copy_empty.tsv' (FORMAT dfn_copy_tsv);
query I
SELECT count(*) FROM read_csv('__TEST_DIR__/dfn_copy_empty.tsv', delim='\t', header=false, columns={'i': 'BIGINT'});
----
0
# ============================================================================
# 选项：HEADER true 把列名写成第一行
# COPY 的输出列没有名字（它们来自查询而不是表），适配层合成 column_0、column_1…
# ============================================================================
statement ok
COPY (SELECT 1 AS i) TO '__TEST_DIR__/dfn_copy_header.tsv' (FORMAT dfn_copy_tsv, HEADER true);
query T
SELECT * FROM read_csv('__TEST_DIR__/dfn_copy_header.tsv', delim='\t', header=false, columns={'c': 'VARCHAR'});
----
column_0
1
# ============================================================================
# 错误：列类型无法用 DuckTypeDesc 表达时，在 bind 阶段就报错（而不是写出一份读不回来的文件）
# ============================================================================
statement error
COPY (SELECT 'a'::ENUM('a', 'b') AS e) TO '__TEST_DIR__/dfn_copy_enum.tsv' (FORMAT dfn_copy_tsv);
----
cannot be turned into a DuckTypeDesc
statement error
COPY (SELECT [1, 2, 3]::INTEGER[3] AS a) TO '__TEST_DIR__/dfn_copy_array.tsv' (FORMAT dfn_copy_tsv);
----
cannot be turned into a DuckTypeDesc