# name: test/sql/functions/copy_from_function.test
# description: duck_copy_from_function 的 COPY FROM（行级读取）：目标表 schema、命名选项 SKIP_ROWS、嵌套类型往返、多数据块、空文件与错误处理
# group: [functions]
require duckfn
# dfn_copy_tsv_from 是扩展注册的自定义 COPY FROM 格式，与 copy_function.test 里的 dfn_copy_tsv 成对：
# COPY TO 写出的文件可以被它原样读回来，包括 LIST / STRUCT / MAP / BLOB 与 NULL。
# 注意两点：reader 不声明结果列（schema 由目标表固定），位置参数恰好一个（文件路径）。
# ============================================================================
# 嵌套类型往返：COPY <表> TO → COPY <同构表> FROM → 双向 diff 为 0
# ============================================================================
statement ok
CREATE TABLE dfn_from_src(id BIGINT, s VARCHAR, l BIGINT[], st STRUCT(k BIGINT, v VARCHAR), mp MAP(VARCHAR, BIGINT), bl BLOB, d DOUBLE);
statement ok
INSERT INTO dfn_from_src VALUES
(1, 'a,b', [1, 2], {'k': 1, 'v': 'q'}, MAP {'m': 1}, '\x01\x02'::BLOB, 1.5),
(2, NULL, NULL, NULL, MAP {}, NULL, NULL),
(3, 'tab' || chr(9) || 'tab', [NULL, 4], {'k': NULL, 'v': NULL}, MAP {'n': 7}, '\xFF'::BLOB, 3.5);
statement ok
COPY dfn_from_src TO '__TEST_DIR__/dfn_from.tsv' (FORMAT dfn_copy_tsv);
statement ok
CREATE TABLE dfn_from_dst(id BIGINT, s VARCHAR, l BIGINT[], st STRUCT(k BIGINT, v VARCHAR), mp MAP(VARCHAR, BIGINT), bl BLOB, d DOUBLE);
statement ok
COPY dfn_from_dst FROM '__TEST_DIR__/dfn_from.tsv' (FORMAT dfn_copy_tsv_from);
# 两个方向都没有差异 => 完整往返（含 NULL、空容器、字符串里的逗号/制表符、BLOB 字节）
query I
SELECT count(*) FROM (SELECT * FROM dfn_from_src EXCEPT ALL SELECT * FROM dfn_from_dst);
----
0
query I
SELECT count(*) FROM (SELECT * FROM dfn_from_dst EXCEPT ALL SELECT * FROM dfn_from_src);
----
0
# 逐列抽查一下（字符串里的制表符用长度看，避免和结果分隔符混淆）
query ITI
SELECT id, len(s), len(l) FROM dfn_from_dst ORDER BY id;
----
1 3 2
2 NULL NULL
3 7 2
query I
SELECT count(*) FROM dfn_from_dst WHERE st.v = 'q' AND mp['m'] = 1 AND bl = '\x01\x02'::BLOB;
----
1
# ============================================================================
# 多数据块：5000 行
# ============================================================================
statement ok
CREATE TABLE dfn_from_big AS SELECT range AS id, 'v' || range AS s, [range, range + 1] AS l FROM range(5000);
statement ok
COPY dfn_from_big TO '__TEST_DIR__/dfn_from_big.tsv' (FORMAT dfn_copy_tsv);
statement ok
CREATE TABLE dfn_from_big2(id BIGINT, s VARCHAR, l BIGINT[]);
statement ok
COPY dfn_from_big2 FROM '__TEST_DIR__/dfn_from_big.tsv' (FORMAT dfn_copy_tsv_from);
query III
SELECT count(*), min(id), max(id) FROM dfn_from_big2;
----
5000 0 4999
query I
SELECT count(*) FROM (SELECT * FROM dfn_from_big EXCEPT ALL SELECT * FROM dfn_from_big2);
----
0
# ============================================================================
# 命名选项：SKIP_ROWS 跳过 HEADER true 写出的表头
# COPY 选项以命名参数到达（大小写不敏感），由 Reader::Args 声明
# ============================================================================
statement ok
COPY dfn_from_src TO '__TEST_DIR__/dfn_from_hdr.tsv' (FORMAT dfn_copy_tsv, HEADER true);
statement ok
CREATE TABLE dfn_from_hdr(id BIGINT, s VARCHAR, l BIGINT[], st STRUCT(k BIGINT, v VARCHAR), mp MAP(VARCHAR, BIGINT), bl BLOB, d DOUBLE);
statement ok
COPY dfn_from_hdr FROM '__TEST_DIR__/dfn_from_hdr.tsv' (FORMAT dfn_copy_tsv_from, SKIP_ROWS 1);
query I
SELECT count(*) FROM dfn_from_hdr;
----
3
# 选项名大小写不敏感
statement ok
CREATE TABLE dfn_from_hdr2(id BIGINT, s VARCHAR, l BIGINT[], st STRUCT(k BIGINT, v VARCHAR), mp MAP(VARCHAR, BIGINT), bl BLOB, d DOUBLE);
statement ok
COPY dfn_from_hdr2 FROM '__TEST_DIR__/dfn_from_hdr.tsv' (FORMAT dfn_copy_tsv_from, skip_rows 1);
query I
SELECT count(*) FROM dfn_from_hdr2;
----
3
# ============================================================================
# 空文件：写出零行后读回零行
# ============================================================================
statement ok
COPY (SELECT 1 AS i WHERE false) TO '__TEST_DIR__/dfn_from_empty.tsv' (FORMAT dfn_copy_tsv);
statement ok
CREATE TABLE dfn_from_empty_t(i BIGINT);
statement ok
COPY dfn_from_empty_t FROM '__TEST_DIR__/dfn_from_empty.tsv' (FORMAT dfn_copy_tsv_from);
query I
SELECT count(*) FROM dfn_from_empty_t;
----
0
# ============================================================================
# 目标表 schema：reader 读的是目标表自己的列，而不是声明结果列
# ============================================================================
query TT
SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM dfn_from_dst);
----
id BIGINT
s VARCHAR
l BIGINT[]
st STRUCT(k BIGINT, v VARCHAR)
mp MAP(VARCHAR, BIGINT)
bl BLOB
d DOUBLE
# ============================================================================
# 错误处理
# ============================================================================
# 列数不符：文件 7 列、目标表 3 列
statement ok
CREATE TABLE dfn_from_bad(i BIGINT, s VARCHAR, extra BIGINT);
statement error
COPY dfn_from_bad FROM '__TEST_DIR__/dfn_from.tsv' (FORMAT dfn_copy_tsv_from);
----
but the target table has 3
# 文件打不开
statement error
COPY dfn_from_bad FROM '__TEST_DIR__/no_such_file.tsv' (FORMAT dfn_copy_tsv_from);
----
cannot open
# 单元格内容与目标列类型不符（VARCHAR 的文本进 BIGINT 列）
statement ok
COPY (SELECT 'not a number' AS v) TO '__TEST_DIR__/dfn_from_bad_type.tsv' (FORMAT dfn_copy_tsv);
statement ok
CREATE TABLE dfn_from_typed(i BIGINT);
statement error
COPY dfn_from_typed FROM '__TEST_DIR__/dfn_from_bad_type.tsv' (FORMAT dfn_copy_tsv_from);
----
is not a valid BIGINT
# 未声明的 COPY 选项：DuckDB 在 bind 之前就拦下，且报错信息指向 reader 表函数名
# （这也是「reader 表函数必须与格式同名」的原因：DuckDB 报的是表函数的名字）
statement error
COPY dfn_from_dst FROM '__TEST_DIR__/dfn_from.tsv' (FORMAT dfn_copy_tsv_from, NOPE 1);
----
is not a supported option for copy function 'dfn_copy_tsv_from'