# name: test/sql/functions/sql_macro.test
# description: duck_sql_macro 的返回类型形式、标量/表宏、参数形态、表达式类型、宏组合、include_str! / duck_sql_macro_files! 脚本导入与命名
# group: [functions]
require duckfn
# SQL 宏在扩展初始化阶段执行 CREATE OR REPLACE MACRO 注册，之后完全由 DuckDB 的
# SQL 层解析，所以测试重点在「注册结果」：
# - duckfn-macro 按返回类型分四条收尾路径（SqlMacro / DuckResult<SqlMacro> /
# String / &'static str 组合）—— 用同名风格的宏一一覆盖；
# - 标量宏 vs 表宏（AS TABLE）；
# - 宏体的类型推导（多态、STRUCT、LIST）；
# - 字符串返回路径可以直接 include_str! 内联外部 .sql 脚本，一份脚本多条语句；
# - duck_sql_macro_files! 一次导入多个 .sql 文件，省去「每个文件写一个函数」的样板；
# - 名字校验规则（大小写不敏感、大小写混合可注册）。
#
# 宏体里引用另一个宏时 DuckDB 会在 CREATE 时解析，故 dfn_macro_quad 用一条 String
# 同时建出被依赖的 dfn_macro_double（详见 sql_macro.rs 的「宏组合」一节）。
# ============================================================================
# 返回类型 1/4：-> SqlMacro（直接构造 builder）
# ============================================================================
# 参数被表达式复用；greatest/least 让它同时支持数值与字符串
query I
SELECT dfn_macro_clamp(15, 0, 10);
----
10
query I
SELECT dfn_macro_clamp(-5, 0, 10);
----
0
query T
SELECT dfn_macro_clamp('bb', 'a', 'c');
----
bb
query T
SELECT typeof(dfn_macro_clamp(1, 0, 2));
----
INTEGER
# 作为普通表达式参与列计算（含 NULL 行）
query I
SELECT dfn_macro_clamp(x, 0, 2) FROM (VALUES (0), (1), (5), (NULL)) t(x);
----
0
1
2
2
# ============================================================================
# 返回类型 2/4：-> DuckResult<SqlMacro>（构造错误可冒泡）
# ============================================================================
query I
SELECT dfn_macro_add(2, 3);
----
5
query T
SELECT typeof(dfn_macro_add(2, 3));
----
INTEGER
# 宏体是动态类型推导的：同样的 add 换浮点入参得到 DECIMAL
query R
SELECT dfn_macro_add(1.5, 2.5);
----
4.0
# ============================================================================
# 返回类型 3/4：-> String / DuckResult<String>（直接产出一条 SQL 语句）
# ============================================================================
query I
SELECT dfn_macro_double(21);
----
42
query I
SELECT dfn_macro_square(7);
----
49
# String 路径也能建表宏
query II
SELECT * FROM dfn_macro_series(3);
----
0 0
1 1
2 4
query TT
SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM dfn_macro_series(1));
----
x BIGINT
sq BIGINT
# ============================================================================
# 返回类型 4/4：-> &'static str / DuckResult<&'static str>（不经过堆分配）
# ============================================================================
query I
SELECT dfn_macro_negate(7);
----
-7
query I
SELECT dfn_macro_negate(-7);
----
7
# x / 2 在 DuckDB 里是浮点除法
query R
SELECT dfn_macro_half(7);
----
3.5
query T
SELECT typeof(dfn_macro_half(7));
----
DOUBLE
# 静态字符串建固定内容的表宏
query I
SELECT * FROM dfn_macro_static_gen();
----
0
1
query TT
SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM dfn_macro_static_gen());
----
range BIGINT
# 静态字符串的表宏同样能带参数
query I
SELECT * FROM dfn_macro_bounds(1, 4);
----
1
2
3
# ============================================================================
# 参数形态：零参数 / 多参数
# ============================================================================
# 零参数标量宏：SQL 侧必须写 ()
query T
SELECT CAST(dfn_macro_pi() AS VARCHAR);
----
3.14159265358979
query T
SELECT typeof(dfn_macro_pi());
----
DECIMAL(15,14)
# 多参数且每个参数都参与表达式：顺序不被搞混
query I
SELECT dfn_macro_weighted(1, 2, 3);
----
123
query I
SELECT dfn_macro_weighted(3, 2, 1);
----
321
query T
SELECT typeof(dfn_macro_weighted(1, 2, 3));
----
INTEGER
# ============================================================================
# 表达式类型：数值 / 字符串 / STRUCT / LIST
# ============================================================================
# 数值表达式
query R
SELECT dfn_macro_norm(5, 0, 10);
----
0.5
# 字符串表达式
query T
SELECT dfn_macro_concat('he', 'llo');
----
hello
# 返回 STRUCT 的宏：可以整块取值，也可以直接取子字段
query T
SELECT CAST(dfn_macro_pair(5) AS VARCHAR);
----
{'a': 5, 'b': 10}
query T
SELECT typeof(dfn_macro_pair(5));
----
STRUCT(a INTEGER, b INTEGER)
query II
SELECT dfn_macro_pair(5).a, dfn_macro_pair(5).b;
----
5 10
# 返回 LIST 的宏
query T
SELECT CAST(dfn_macro_mklist(5) AS VARCHAR);
----
[5, 10]
query T
SELECT typeof(dfn_macro_mklist(5));
----
INTEGER[]
# ============================================================================
# 宏组合：一个 Rust 函数注册多条 SQL，宏体调用另一个宏
# ============================================================================
query I
SELECT dfn_macro_quad(3);
----
12
# 被依赖的宏本身也单独可用
query I
SELECT dfn_macro_double(5);
----
10
# ============================================================================
# include_str!：字符串返回形式直接内联外部 .sql 脚本
#
# 脚本 test/extension/functions/sql/macro_inc.sql 在编译期被读成 &'static str，
# 一份脚本里有注释和三条语句（分号分隔），注册出三个宏。
# ============================================================================
# 脚本里的标量宏
query I
SELECT dfn_macro_inc_add(2, 3);
----
5
query T
SELECT typeof(dfn_macro_inc_add(2, 3));
----
INTEGER
query I
SELECT dfn_macro_inc_triple(4);
----
12
# 脚本里的表宏
query I
SELECT * FROM dfn_macro_inc_gen(3);
----
0
1
2
query TT
SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM dfn_macro_inc_gen(1));
----
range BIGINT
# 另一个脚本经 DuckResult<&'static str> 路径导入
query I
SELECT dfn_macro_inc_negate(7);
----
-7
# 两个脚本一共注册出 4 个宏，类型与参数名都按脚本书写保留
query TT
SELECT function_name, function_type FROM duckdb_functions()
WHERE function_name LIKE 'dfn_macro_inc%'
ORDER BY function_name;
----
dfn_macro_inc_add macro
dfn_macro_inc_gen table_macro
dfn_macro_inc_negate macro
dfn_macro_inc_triple macro
query TT
SELECT function_name, parameters FROM duckdb_functions()
WHERE function_name LIKE 'dfn_macro_inc%'
ORDER BY function_name;
----
dfn_macro_inc_add [a, b]
dfn_macro_inc_gen [n]
dfn_macro_inc_negate [x]
dfn_macro_inc_triple [x]
# 脚本里注册的宏和 Rust 里注册的宏可以互相组合
query I
SELECT dfn_macro_inc_triple(dfn_macro_inc_add(1, 1));
----
6
# ============================================================================
# duck_sql_macro_files!：一次注册多个 .sql 文件（快捷方式）
#
# macro_files_a.sql / macro_files_b.sql / macro_files_c.sql 由同一次调用导入：
# a 里两个标量宏、b 里一个表宏（a/b/c 也演示了顺序）。
# ============================================================================
query I
SELECT dfn_macro_files_add(2, 3);
----
5
query T
SELECT typeof(dfn_macro_files_add(2, 3));
----
INTEGER
query I
SELECT dfn_macro_files_mul(4, 5);
----
20
# 第 2 个文件里的表宏
query I
SELECT * FROM dfn_macro_files_gen(3);
----
0
1
2
query I
SELECT dfn_macro_files_negate(7);
----
-7
# 三个文件、四个宏全部注册，没有文件被漏掉
query I
SELECT count(*) FROM duckdb_functions() WHERE function_name LIKE 'dfn_macro_files%';
----
4
query TT
SELECT function_name, function_type FROM duckdb_functions()
WHERE function_name LIKE 'dfn_macro_files%'
ORDER BY function_name;
----
dfn_macro_files_add macro
dfn_macro_files_gen table_macro
dfn_macro_files_mul macro
dfn_macro_files_negate macro
query TT
SELECT function_name, parameters FROM duckdb_functions()
WHERE function_name LIKE 'dfn_macro_files%'
ORDER BY function_name;
----
dfn_macro_files_add [a, b]
dfn_macro_files_gen [n]
dfn_macro_files_mul [a, b]
dfn_macro_files_negate [x]
# 同一批文件注册出的宏之间可以互相组合
query I
SELECT dfn_macro_files_mul(dfn_macro_files_add(1, 1), 3);
----
6
# ============================================================================
# 表宏：在 FROM / JOIN / CTE / WHERE 中使用
# ============================================================================
query I
SELECT * FROM dfn_macro_gen(3);
----
0
1
2
query I
SELECT range FROM dfn_macro_gen(2);
----
0
1
# 表宏名大小写不敏感
query I
SELECT range FROM DFN_MACRO_GEN(2);
----
0
1
# 空结果集
query I
SELECT * FROM dfn_macro_gen(0);
----
query I
SELECT count(*) FROM dfn_macro_gen(0);
----
0
# 零参数表宏，多列
query IT
SELECT one, s FROM dfn_macro_constants();
----
1 x
query TT
SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM dfn_macro_constants());
----
one INTEGER
s VARCHAR
# JOIN：两个表宏结果集
query II
SELECT g1.range, g2.range
FROM dfn_macro_gen(2) g1 JOIN dfn_macro_gen(3) g2 ON g1.range = g2.range
ORDER BY 1;
----
0 0
1 1
# CTE 里套表宏，再和标量宏组合
query I
SELECT sum(dfn_macro_double(range)) FROM (SELECT * FROM dfn_macro_gen(4)) t;
----
12
# WHERE 里用标量宏
query I
SELECT range FROM dfn_macro_gen(5) WHERE dfn_macro_add(range, 0) > 2 ORDER BY 1;
----
3
4
# ============================================================================
# NULL 语义：由宏体表达式自己决定
# ============================================================================
# a + b 遇到 NULL 结果为 NULL
query I
SELECT dfn_macro_add(NULL::INTEGER, 1);
----
NULL
# concat 忽略 NULL
query T
SELECT dfn_macro_concat('a', NULL);
----
a
# greatest/least 忽略 NULL：clamp(NULL, 0, 10) 退化为边界值
query I
SELECT dfn_macro_clamp(NULL, 0, 10);
----
10
# ============================================================================
# 命名：允许大小写混合，SQL 侧大小写不敏感
# ============================================================================
query I
SELECT dfnmacrocamel(1);
----
2
query I
SELECT DFNMACROCAMEL(2);
----
3
query I
SELECT dfnMacroCamel(3);
----
4
# ============================================================================
# 注册元数据：全部宏都出现在 duckdb_functions()，并区分 macro / table_macro
# ============================================================================
query I
SELECT count(*) FROM duckdb_functions() WHERE function_name ILIKE 'dfn%macro%';
----
27
query TT
SELECT function_name, function_type FROM duckdb_functions()
WHERE function_name IN ('dfn_macro_clamp', 'dfn_macro_gen', 'dfn_macro_quad', 'dfn_macro_constants')
ORDER BY function_name;
----
dfn_macro_clamp macro
dfn_macro_constants table_macro
dfn_macro_gen table_macro
dfn_macro_quad macro
# 参数名按注册时的声明保留
query TT
SELECT function_name, parameters FROM duckdb_functions()
WHERE function_name IN ('dfn_macro_clamp', 'dfn_macro_pi', 'dfn_macro_gen')
ORDER BY function_name;
----
dfn_macro_clamp [x, lo, hi]
dfn_macro_gen [n]
dfn_macro_pi []
# ============================================================================
# 错误场景
# ============================================================================
# 参数个数不符
statement error
SELECT dfn_macro_add(1);
----
does not support the supplied arguments
statement error
SELECT dfn_macro_add(1, 2, 3);
----
does not support the supplied arguments
# 未注册的名字
statement error
SELECT dfn_macro_nope(1);
----
Scalar Function with name dfn_macro_nope does not exist
# 表宏当标量用
statement error
SELECT dfn_macro_gen(1);
----
is a table function but it was used as a scalar function
# 标量宏当表用
statement error
SELECT * FROM dfn_macro_add(1, 2);
----
Table Function with name dfn_macro_add does not exist