# name: test/sql/catalog/function/test_simple_macro.test
# description: Test Simple Macro
# group: [function]

statement ok
PRAGMA enable_verification

statement ok
CREATE TABLE integers (a INT)

statement ok
INSERT INTO integers VALUES (1)

statement ok
CREATE MACRO one() AS (SELECT 1);

query T
SELECT one()
----
1

statement error
SELECT one(1)
----
does not support the supplied arguments

statement error
SELECT one(NULL)
----
does not support the supplied arguments

statement ok
DROP MACRO one;

# HAVING in a macro
statement ok
CREATE MACRO having_macro(x) AS (SELECT * FROM integers GROUP BY a HAVING a = x)

query T
SELECT having_macro(1)
----
1

query T
SELECT having_macro(6)
----
NULL

# UNION in a macro
statement ok
CREATE MACRO union_macro(x, y, z) AS (SELECT x IN (SELECT y UNION ALL SELECT z))

query T
SELECT union_macro(1, 2, 3)
----
false

query T
SELECT union_macro(1, 2, 1)
----
true

query T
SELECT union_macro(1, 1, 2)
----
true

# expression list
statement ok
CREATE MACRO in_expression_list(x, y, z) AS (SELECT x IN (VALUES (y), (z)))

query T
SELECT in_expression_list(1, 2, 3)
----
false

query T
SELECT in_expression_list(1, 2, 1)
----
true

query T
SELECT in_expression_list(1, 1, 2)
----
true

# FUNCTION alias
statement ok
CREATE FUNCTION two() AS (SELECT 2);

query T
SELECT two()
----
2

statement ok
DROP FUNCTION two;

# b cannot be found
statement error
CREATE MACRO add_macro(a) AS a + b
----
column "b" was not found because the FROM clause is missing

statement ok
CREATE MACRO add_macro(a, b) AS a + b

query T
SELECT add_macro(a,a) FROM integers
----
2

statement ok
CREATE TABLE floats (b FLOAT)

statement ok
INSERT INTO floats VALUES (0.5)

query T
SELECT add_macro(a,2) + add_macro(3,b) FROM integers, floats
----
6.5

# can create function with same name as a ScalarFunction
statement ok
CREATE MACRO string_split(a,b) AS a + b

query I
SELECT string_split(1, 2)
----
3

statement ok
CREATE MACRO IFELSE(a,b,c) AS CASE WHEN a THEN b ELSE c END

query T
SELECT IFELSE(1,'true','false')
----
true

query T
SELECT ifelse(1,'true','false')
----
true

query T
SELECT IFELSE(0,'true','false')
----
false

query T
SELECT IFELSE(a = 1, 'true', 'false') FROM integers
----
true

query T
SELECT IFELSE(a = 0, 'true', 'false') FROM integers
----
false

# incorrect number of arguments
statement error
SELECT IFELSE();
----
does not support the supplied arguments

statement error
SELECT IFELSE(1);
----
does not support the supplied arguments

statement error
SELECT IFELSE(1, 2);
----
does not support the supplied arguments

statement error
SELECT IFELSE(1, 2, 3, 4);
----
does not support the supplied arguments

# no duplicate macro function names
statement error
CREATE MACRO IFELSE(a,b) AS a+b
----
already exists

statement error
CREATE MACRO ifelse(a,b) AS a+b
----
already exists

query T
SELECT IFELSE('1', 'random', RANDOM()::VARCHAR)
----
random

# macro in a different schema
statement ok
CREATE SCHEMA macros

statement ok
CREATE MACRO macros.add_macro(a, b) AS a + b

query T
SELECT macros.add_macro(40,2)
----
42

# conflicting parameter names in macro definition
statement error
CREATE MACRO conflict(i, i) AS i + 1
----

# aggregation macro's
statement ok
CREATE MACRO myavg(x) AS SUM(x) / COUNT(x)

statement ok
INSERT INTO integers VALUES (21), (41);

query T
SELECT myavg(a) FROM integers
----
21

statement ok
CREATE MACRO weird_avg(x) AS (MIN(x) + MAX(x)) / COUNT(x)

query T
SELECT weird_avg(a) FROM integers
----
14

statement error
CREATE MACRO star() AS *
----

# macro's with default arguments
statement error
CREATE MACRO conflict(a, a := 1) AS a + a
----

statement ok
CREATE MACRO add_default5(a, b := 5) AS a + b

query I
SELECT add_default5(3, 6)
----
9

query T
SELECT add_default5(3)
----
8

query T
SELECT add_default5(3, b := 6)
----
9

statement error
SELECT add_default5(b := 6, 3)
----
positional argument following named argument

statement error
CREATE MACRO wrong_order(a, b := 3, c) AS a + b + c
----
Parameter without a default follows parameter with a default

statement error
CREATE MACRO wrong_order(a := 3, b) AS a + b
----
Parameter without a default follows parameter with a default

# only constant default values are allowed
statement error
CREATE MACRO select_plus_floats(a, f := b) AS (SELECT a + f FROM floats)
----
Binder Error: Default value for parameter 'f' cannot contain column names

# +(FLOAT, VARCHAR) does not work - but types are only checked once the macro is called
statement ok
CREATE MACRO wrong_type(s := 'not a float') AS (SELECT b + s FROM floats)

# this should fail
statement error
select wrong_type()
----
Could not convert

# but succeed if we pass the correct type
query I
select wrong_type(42)
----
42.5

statement error
CREATE MACRO two_default_params(a := 4, a := 2) AS a + a
----
Duplicate parameter

statement ok
CREATE MACRO two_default_params(a := 4, b := 2) AS a + b

query T
SELECT two_default_params()
----
6

query T
SELECT two_default_params(a := 5)
----
7

query T
SELECT two_default_params(b := 3)
----
7

statement error
SELECT two_default_params(a := 5, a := 3)
----
has named argument repeated

statement error
SELECT two_default_params(b := 5, b := 3)
----
has named argument repeated

statement error
CREATE MACRO macros.add_macro(a, b) AS a + b
----
already exists

statement error
CREATE MACRO my_macro(a.b) AS 42;
----
syntax error

statement error
CREATE MACRO my_macro(a.b.c) AS 42;
----
syntax error

statement ok
CREATE MACRO my_macro(a) AS 42;

statement error
SELECT my_macro(x := 42);
----
does not support the supplied arguments

statement error
SELECT my_macro(a := 42, a := 42);
----
has named argument repeated

# internal issue 1044
statement ok
create macro zz1(x) as (select 10+x);

statement ok
create macro zz2(x) as 20+x;

query II
select zz1(1),zz2(2);
----
11	22

# should not return any results (because we shouldn't display "macro_parameters"
query III
select function_name, parameters, macro_definition
from duckdb_functions()
where function_name like 'zz%'
and macro_definition like '%macro_parameters%';
----