orql 0.1.0

use pretty_assertions::assert_eq;

use crate::parser::tests::parse_stmt_to_string;

#[test]
fn test_generic_duplicates() {
    assert_sql!(
        stmt("select f(DISTINCT x) from dual"),
        debug("SELECT {{f}(DISTINCT {x})} FROM {dual}")
    );
    assert_sql!(
        stmt("select f(DISTINCT x, y) from dual"),
        debug("SELECT {{f}(DISTINCT {x}, {y})} FROM {dual}")
    );
    assert_sql!(
        stmt("select f(DISTINCT x, DISTINCT y) from dual"),
        debug("Unexpected DISTINCT; expected a value or an expression [line: 1, column: 22]")
    );
    assert_sql!(
        stmt("select f(DISTINCT) from dual"),
        debug("Invalid number of arguments [line: 1, column: 8]")
    );
}

#[test]
fn test_named_arguments() {
    assert_sql!(
        stmt("select f(a => 1, b => 'b') from dual"),
        debug("SELECT {{f}(a => 1, b => 'b')} FROM {dual}")
    );
    assert_sql!(
        stmt("Select g(distinct 1, a => 2, 3) from dual"),
        debug("SELECT {{g}(DISTINCT 1, a => 2, 3)} FROM {dual}")
    );

    // ~ errors
    assert_sql!(
        stmt("select g(dist =>) from dual"),
        debug("Unexpected `)`; expected a value or an expression [line: 1, column: 17]")
    );
    assert_sql!(
        stmt("select g(123 => 234) from dual"),
        debug("Invalid argument name, only simple identifiers allowed [line: 1, column: 10]")
    );
    assert_sql!(
        stmt("select g(abc.def => 234) from dual"),
        debug("Invalid argument name, only simple identifiers allowed [line: 1, column: 10]")
    );
    // ~ built-ins do _not_ support named args
    assert_sql!(
        stmt("select count(a => 1) from dual"),
        debug("Unexpected `=>`; expected a closing parenthesis [line: 1, column: 16]")
    );

    // comments
    assert_sql!(
        stmt("select f(/*1*/a/*2*/ =>/*3*/ 1/*4*/,/*5*/ b/*6*/ => /*7*/'b'/*8*/) from dual"),
        debug(with_meta(
            "SELECT {{f}( /*1*/ a /*2*/ => /*3*/ 1 /*4*/ , /*5*/ b /*6*/ => /*7*/ 'b' /*8*/ )} FROM {dual}"
        ))
    );
}

#[test]
fn test_any_value() {
    assert_sql!(
        stmt("select * from (select 1 a from dual) where any_value(a) = 1 group by ()"),
        debug("Aggregate functions not allowed here [line: 1, column: 44]")
    );

    assert_sql!(
        stmt("select any_value(a) from (select 1 a from dual) group by ()"),
        debug("SELECT {{any_value}({a})} FROM (SELECT {1 a} FROM {dual}) {GROUP BY ()}")
    );
    assert_sql!(
        stmt("select any_value(distinct a) from (select 1 a from dual) group by ()"),
        debug("SELECT {{any_value}(DISTINCT {a})} FROM (SELECT {1 a} FROM {dual}) {GROUP BY ()}")
    );
}

#[test]
fn test_count() {
    assert_sql!(
        stmt("select count(*) from t"),
        debug("SELECT {{count}(*)} FROM {t}")
    );
    assert_sql!(
        stmt("select count(42) from t"),
        debug("SELECT {{count}(42)} FROM {t}")
    );
    assert_sql!(
        stmt("select \"COUNT\"(42) from t"),
        debug("SELECT {{\"COUNT\"}(42)} FROM {t}")
    );
    assert_sql!(
        stmt("select count(UNIQUE 42) from t"),
        debug("SELECT {{count}(UNIQUE 42)} FROM {t}")
    );
    assert_sql!(
        stmt("select count(UNIQUE 42) OVER () from t"),
        debug("SELECT {{count}(UNIQUE 42) {OVER ()}} FROM {t}")
    );
    assert_sql!(
        stmt("select count(42, 12) from t"),
        debug("Unexpected `,`; expected a closing parenthesis [line: 1, column: 16]")
    );
}

#[test]
fn test_extract() {
    assert_sql!(
        stmt("select EXTRACT(hour from systimestamp) from t;"),
        debug("SELECT {{EXTRACT}(HOUR FROM {systimestamp})} FROM {t};")
    );
    // ~ "over" as an alias
    assert_sql!(
        stmt("select EXTRACT(timezone_abbr from systimestamp) over from t;"),
        debug("SELECT {{{EXTRACT}(TIMEZONE_ABBR FROM {systimestamp})} over} FROM {t};")
    );
    // ~ not allowed as an analytical function
    assert_sql!(
        stmt("select EXTRACT(timezone_abbr from systimestamp) over () from t;"),
        debug("Unexpected `(`; expected FROM [line: 1, column: 54]")
    );
    assert_sql!(
        stmt("select EXTRACT(foobar from systimestamp) over from t;"),
        debug(
            "Unexpected 'foobar'; expected the YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, \
             TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGIN, or TIMEZONE_ABBR keyword \
             [line: 1, column: 16]"
        )
    );
}

#[test]
fn test_min_max_avg() {
    assert_sql!(
        stmt(
            "select MIN(a), MIN(DISTINCT a), MAX(b+c), \
                    MIN(ALL b*c) over, min(a) over (partition by a), avg(a), \
                    STDDEV(ALL a + b *c), stddev_pop(b), stddev_SAMP(c) over (), sum(a+b+c), \
                    variance(a / c) var
               from t;"
        ),
        debug(
            "SELECT {{MIN}({a})}, \
                      {{MIN}(DISTINCT {a})}, \
                      {{MAX}({{b} + {c}})}, \
                      {{{MIN}(ALL {{b} * {c}})} over}, \
                      {{min}({a}) {OVER ({PARTITION BY {a}})}}, \
                      {{avg}({a})}, \
                      {{STDDEV}(ALL {{a} + {{b} * {c}}})}, \
                      {{stddev_pop}({b})}, \
                      {{stddev_SAMP}({c}) {OVER ()}}, \
                      {{sum}({{{a} + {b}} + {c}})}, \
                      {{{variance}({{a} / {c}})} var} \
                FROM {t};"
        )
    );
}

#[test]
fn test_median() {
    assert_sql!(
        stmt("select median(a) over () from t"),
        debug("SELECT {{median}({a}) {OVER ()}} FROM {t}")
    );
    assert_sql!(
        stmt("select median(a) from t"),
        debug("SELECT {{median}({a})} FROM {t}")
    );

    assert_sql!(
        stmt("select median(a) over (partition by 1) from t"),
        debug("SELECT {{median}({a}) {OVER ({PARTITION BY 1})}} FROM {t}")
    );
    // error: ORDER BY not allowed
    assert_sql!(
        stmt("select median(a) over (partition by 1 order by 1) from t"),
        // XXX it would be much nicer if the error read "ORDER BY not allowed
        // here [line ..., column ...]" pointing to the `ORDER` token
        debug("Unexpected ORDER; expected a closing parenthesis [line: 1, column: 39]")
    );
}

#[test]
fn test_nth_value() {
    assert_sql!(
        stmt("select nth_value(a, 1) from FIRST over () FROM (select 1 a from dual)"),
        debug(
            "SELECT {{nth_value}({a}, 1) {FROM FIRST} {OVER ()}} FROM (SELECT {1 a} FROM {dual})"
        )
    );
    assert_sql!(
        stmt("select nth_value(a, 1) from FIRST over (partition by x) FROM t"),
        debug("SELECT {{nth_value}({a}, 1) {FROM FIRST} {OVER ({PARTITION BY {x}})}} FROM {t}")
    );
    assert_sql!(
        stmt("select nth_value(a, 1) respect nulls over() FROM t"),
        debug("SELECT {{nth_value}({a}, 1) {RESPECT NULLS} {OVER ()}} FROM {t}")
    );
    assert_sql!(
        stmt("select nth_value(a, 1) IGNORE NULLS over ()FROM t"),
        debug("SELECT {{nth_value}({a}, 1) {IGNORE NULLS} {OVER ()}} FROM {t}")
    );
    assert_sql!(
        stmt("select nth_value(a, 1) FROM LAST IGNORE NULLS over () FROM t"),
        debug("SELECT {{nth_value}({a}, 1) {FROM LAST} {IGNORE NULLS} {OVER ()}} FROM {t}")
    );

    // ~ errors
    assert_sql!(
        stmt("select nth_value(a, 1) FROM LAST IGNORE NULLS over FROM t"),
        debug(
            "Unexpected FROM; expected an identifier (window name) or an opening parenthesis (analytic clause) [line: 1, column: 52]"
        )
    );
    assert_sql!(
        stmt("select nth_value(a, 1) FROM LAST IGNORE NULLS FROM t"),
        debug("Unexpected FROM; expected the OVER keyword [line: 1, column: 47]")
    );
    assert_sql!(
        stmt("select 1 FROM t WHERE nth_value(a, 1) OVER () = 1"),
        debug("Analytic functions not allowed here [line: 1, column: 23]")
    );
}

#[test]
fn test_abs() {
    assert_sql!(
        stmt("select abs(-1 + 1) from dual"),
        debug("SELECT {{abs}({{-1} + 1})} FROM {dual}")
    );
}

#[test]
fn test_acos() {
    assert_sql!(
        stmt("SELECT ACOS(.3)\"Arc_Cosine\"  FROM DUAL"),
        debug("SELECT {{{ACOS}(.3)} \"Arc_Cosine\"} FROM {DUAL}")
    );
}

#[test]
fn test_add_months() {
    assert_sql!(
        stmt(
            r#"
SELECT TO_CHAR(ADD_MONTHS(hire_date, 1), 'DD-MON-YYYY') "Next month"
  FROM employees 
  WHERE last_name = 'Baer';"#
        ),
        debug(
            "SELECT {{{TO_CHAR}({{ADD_MONTHS}({hire_date}, 1)}, 'DD-MON-YYYY')} \"Next month\"} \
                FROM {employees} WHERE {{last_name} = 'Baer'};"
        )
    );
}

#[test]
fn test_ascii() {
    assert_sql!(
        stmt(
            r#"SELECT last_name
  FROM employees
  WHERE ASCII(SUBSTR(last_name, 1, 1)) = 76
  ORDER BY last_name;"#
        ),
        debug(
            "SELECT {last_name} \
                 FROM {employees} \
                WHERE {{{ASCII}({{SUBSTR}({last_name}, 1, 1)})} = 76} \
               {ORDER BY {last_name}};"
        )
    );

    assert_sql!(
        stmt("select ascii(distinct 'a') from dual;"),
        debug("Unexpected DISTINCT; expected a value or an expression [line: 1, column: 14]")
    );
}

#[test]
fn test_asciistr() {
    assert_sql!(
        stmt("SELECT ASCIISTR('ABÄCDE') FROM DUAL;"),
        debug("SELECT {{ASCIISTR}('ABÄCDE')} FROM {DUAL};")
    );
}

#[test]
fn test_asin() {
    assert_sql!(
        stmt(r#"SELECT ASIN(.3) "Arc_Sine"  FROM DUAL;"#),
        debug("SELECT {{{ASIN}(.3)} \"Arc_Sine\"} FROM {DUAL};")
    );
}

#[test]
fn test_atan() {
    assert_sql!(
        stmt("SELECT ATAN(.3) \"Arc_Tangent\"  FROM DUAL;"),
        debug("SELECT {{{ATAN}(.3)} \"Arc_Tangent\"} FROM {DUAL};")
    );
}

#[test]
fn test_atan2() {
    assert_sql!(
        stmt("SELECT ATAN2(.3, .2) \"Arc_Tangent2\"  FROM DUAL;"),
        debug("SELECT {{{ATAN2}(.3, .2)} \"Arc_Tangent2\"} FROM {DUAL};")
    );
}

#[test]
fn test_bin_to_num() {
    assert_sql!(
        stmt("select bin_to_num(1, 0, 1, 1) from dual"),
        debug("SELECT {{bin_to_num}(1, 0, 1, 1)} FROM {dual}")
    );
}

#[test]
fn test_bit_functions() {
    assert_sql!(
        stmt("select bitand(1, 2) from dual"),
        debug("SELECT {{bitand}(1, 2)} FROM {dual}")
    );

    assert_sql!(
        stmt("select BIT_and_agg(1) from t"),
        debug("SELECT {{BIT_and_agg}(1)} FROM {t}")
    );
    assert_sql!(
        stmt("select BIT_and_agg(UNIQUE 1) from t"),
        debug("SELECT {{BIT_and_agg}(UNIQUE 1)} FROM {t}")
    );

    assert_sql!(
        stmt("select bit_or_agg(x) from t"),
        debug("SELECT {{bit_or_agg}({x})} FROM {t}")
    );
    assert_sql!(
        stmt("select bit_or_agg(all 1 ) from t"),
        debug("SELECT {{bit_or_agg}(ALL 1)} FROM {t}")
    );

    assert_sql!(
        stmt("select bit_XOR_agg(x) from t"),
        debug("SELECT {{bit_XOR_agg}({x})} FROM {t}")
    );
    assert_sql!(
        stmt("select bit_XOR_agg(all 1 ) from t"),
        debug("SELECT {{bit_XOR_agg}(ALL 1)} FROM {t}")
    );
}

#[test]
fn test_bitmap_functions() {
    assert_sql!(
        stmt("select BITMAP_BIT_POSITION(3) from dual"),
        debug("SELECT {{BITMAP_BIT_POSITION}(3)} FROM {dual}")
    );

    assert_sql!(
        stmt("select BITMAP_BUCKET_NUMBER(4) from dual"),
        debug("SELECT {{BITMAP_BUCKET_NUMBER}(4)} FROM {dual}")
    );

    assert_sql!(
        stmt("select BITMAP_CONSTRUCT_AGG(5) from dual"),
        debug("SELECT {{BITMAP_CONSTRUCT_AGG}(5)} FROM {dual}")
    );

    assert_sql!(
        stmt("select BITMAP_COUNT(6) from dual"),
        debug("SELECT {{BITMAP_COUNT}(6)} FROM {dual}")
    );

    assert_sql!(
        stmt("select BITMAP_OR_AGG(7) from dual"),
        debug("SELECT {{BITMAP_OR_AGG}(7)} FROM {dual}")
    );
}

#[test]
fn test_ceil() {
    assert_sql!(
        stmt("select order_total, ceil(order_total) from orders where order_id = 2434;"),
        debug(
            "SELECT {order_total}, {{ceil}({order_total})} FROM {orders} WHERE {{order_id} = 2434};"
        )
    );
}

#[test]
fn test_checksum() {
    assert_sql!(
        stmt("select 1 from dual group by () having checksum(distinct 12) = 803726"),
        debug("SELECT 1 FROM {dual} {GROUP BY () HAVING {{{checksum}(DISTINCT 12)} = 803726}}")
    );
    assert_sql!(
        stmt("select 1 from dual group by () order by (checksum(distinct 12));"),
        debug("SELECT 1 FROM {dual} {GROUP BY ()} {ORDER BY ({{checksum}(DISTINCT 12)})};")
    );
    // ~ "over" as an alias
    assert_sql!(
        stmt("select checksum(ALL 42) over from dual"),
        debug("SELECT {{{checksum}(ALL 42)} over} FROM {dual}")
    );
    // ~ "over" as an analytical function call
    assert_sql!(
        stmt("select checksum(ALL 42) over () from dual"),
        debug("SELECT {{checksum}(ALL 42) {OVER ()}} FROM {dual}")
    );
    // errors
    assert_sql!(
        stmt("select 1 from dual group by () having checksum(distinct 12) over () = 1;"),
        // XXX this error message is confusing (and not correct actually);
        // should read something like "analytic functions not allowed here"
        debug(
            "Unexpected 'over'; expected the IS, IN, LIKE keyword or a comparison operator [line: 1, column: 61]"
        )
    );
    assert_sql!(
        stmt("select 1 from dual where checksum(12) = 1"),
        debug("Aggregate functions not allowed here [line: 1, column: 26]")
    );
}

#[test]
fn test_decode() {
    assert_sql!(
        stmt("select decode(x, 1, 'a') from dual"),
        debug("SELECT {{decode}({x}, 1, 'a')} FROM {dual}")
    );
    assert_sql!(
        stmt("select decode(x, 1, 'a', 'b') from dual"),
        debug("SELECT {{decode}({x}, 1, 'a', 'b')} FROM {dual}")
    );
    assert_sql!(
        stmt("select decode(x, 1, 'a', 2, 'b') from dual"),
        debug("SELECT {{decode}({x}, 1, 'a', 2, 'b')} FROM {dual}")
    );
    assert_sql!(
        stmt("select decode(x, 1, 'a', 2, 'b', 'default') from dual"),
        debug("SELECT {{decode}({x}, 1, 'a', 2, 'b', 'default')} FROM {dual}")
    );
}

#[test]
fn test_cardinality() {
    assert_sql!(
        stmt("select cardinality(null) from dual"),
        debug("SELECT {{cardinality}(NULL)} FROM {dual}")
    );
}

#[test]
fn test_chartorowid() {
    assert_sql!(
        stmt("select chartorowid('asdf') from dual"),
        debug("SELECT {{chartorowid}('asdf')} FROM {dual}")
    );
}

#[test]
fn test_coalesce() {
    assert_sql!(
        stmt("select coalesce(a), coalesce(a, b), coalesce(a, b, c) from t"),
        debug(
            "SELECT {{coalesce}({a})}, {{coalesce}({a}, {b})}, {{coalesce}({a}, {b}, {c})} FROM {t}"
        )
    );
}

#[test]
fn test_collation() {
    assert_sql!(
        stmt("select collation(name), COLLATION(id) from id_table;"),
        debug("SELECT {{collation}({name})}, {{COLLATION}({id})} FROM {id_table};")
    );
}

#[test]
fn test_compose() {
    assert_sql!(
        stmt("select COMPOSE( 'o' || UNISTR('\\0308') )from dual"),
        debug("SELECT {{COMPOSE}({'o' || {{UNISTR}('\\0308')}})} FROM {dual}")
    );
}

#[test]
fn test_concat() {
    assert_sql!(
        stmt(
            r#"select concat(concat(last_name, '''s job category is '), job_id) "Job" from employees "#
        ),
        debug(
            "SELECT {{{concat}({{concat}({last_name}, '''s job category is ')}, {job_id})} \"Job\"} FROM {employees}"
        )
    );
}

#[test]
fn test_convert() {
    assert_sql!(
        stmt("select convert('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual;"),
        debug(
            "SELECT {{convert}('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')} FROM {dual};"
        )
    );
}

#[test]
fn test_corr() {
    // ~ aggregate example
    assert_sql!(
        stmt(
            r#"SELECT weight_class, CORR(list_price, min_price) "Correlation"
  FROM product_information
  GROUP BY weight_class
  ORDER BY weight_class, "Correlation";"#
        ),
        debug(
            "\
SELECT {weight_class}, {{{CORR}({list_price}, {min_price})} \"Correlation\"} \
  FROM {product_information} \
  {GROUP BY {weight_class}} \
  {ORDER BY {weight_class}, {\"Correlation\"}};\
"
        )
    );

    // ~ analytical example
    assert_sql!(
        stmt(
            r#"SELECT CORR(SYSDATE-hire_date, salary) OVER(PARTITION BY job_id) AS "Correlation" FROM employees"#
        ),
        debug(
            "SELECT {{{CORR}({{SYSDATE} - {hire_date}}, {salary}) {OVER ({PARTITION BY {job_id}})}} AS \"Correlation\"} FROM {employees}"
        )
    );

    // ~ use as an aggregate in a having clause
    assert_sql!(
        stmt("select 1 from dual group by () having corr(1, 2) = 1;"),
        debug("SELECT 1 FROM {dual} {GROUP BY () HAVING {{{corr}(1, 2)} = 1}};")
    );
    // ~ erroneously used as an analytical in a having_clause
    assert_sql!(
        stmt("select 1 from dual group by () having corr(1, 2) over () = 1;"),
        // XXX this error message is confusing (and not correct actually);
        // should read "Analytic functions not allowed here [line: 1, column: 39]"
        debug(
            "Unexpected 'over'; expected the IS, IN, LIKE keyword or a comparison operator [line: 1, column: 50]"
        )
    );
    assert_sql!(
        stmt("select 1 from t where corr(1, 2) = 1"),
        debug("Aggregate functions not allowed here [line: 1, column: 23]")
    );
}

#[test]
fn test_corr_k_and_s() {
    assert_sql!(
        stmt(
            r#"SELECT COUNT(*) count,
       CORR_S(salary, commission_pct) commission,
       CORR_S(salary, employee_id) empid
  FROM employees;"#
        ),
        debug(
            "SELECT {{{COUNT}(*)} count}, \
                    {{{CORR_S}({salary}, {commission_pct})} commission}, \
                    {{{CORR_S}({salary}, {employee_id})} empid} \
              FROM {employees};"
        )
    );
    assert_sql!(
        stmt(
            r#"SELECT CORR_K(salary, commission_pct, 'COEFFICIENT') coefficient,
       CORR_K(salary, commission_pct, 'TWO_SIDED_SIG') two_sided_p_value
  FROM employees;"#
        ),
        debug(
            "SELECT {{{CORR_K}({salary}, {commission_pct}, 'COEFFICIENT')} coefficient}, \
                      {{{CORR_K}({salary}, {commission_pct}, 'TWO_SIDED_SIG')} two_sided_p_value} \
                 FROM {employees};"
        )
    );

    // used as an aggreate in a having_clause
    assert_sql!(
        stmt("SELECT 1 FROM dual group by () having (CORR_K(1, 5, 'COEFFICIENT') = 1);"),
        debug("SELECT 1 FROM {dual} {GROUP BY () HAVING ({{{CORR_K}(1, 5, 'COEFFICIENT')} = 1})};")
    );

    // errors
    assert_sql!(
        stmt("SELECT 1 FROM dual  join dual t on (CORR_K(1, 5, 'COEFFICIENT') = 1);"),
        debug("Aggregate functions not allowed here [line: 1, column: 37]")
    );
}

#[test]
fn test_cos_and_cosh() {
    assert_sql!(
        stmt("select cos(0.9), cosh(.34) from dual"),
        debug("SELECT {{cos}(0.9)}, {{cosh}(.34)} FROM {dual}")
    );
}

#[test]
fn test_current_and_local_timestamp() {
    assert_sql!(
        stmt("select current_timestamp(9) from dual"),
        debug("SELECT {{current_timestamp}(9)} FROM {dual}")
    );
    assert_sql!(
        stmt("select localtimestamp(6) from dual"),
        debug("SELECT {{localtimestamp}(6)} FROM {dual}")
    );

    // errors
    assert_sql!(
        stmt("select current_timestamp(+9) from dual"),
        debug("Unexpected `+`; expected an integer [line: 1, column: 26]")
    );
    assert_sql!(
        stmt("select current_timestamp(-9) from dual"),
        debug("Unexpected `-`; expected an integer [line: 1, column: 26]")
    );
    assert_sql!(
        stmt("select current_timestamp(abc) from dual"),
        debug("Unexpected 'abc'; expected an integer [line: 1, column: 26]")
    );
}

#[test]
fn test_chr() {
    assert_sql!(
        stmt("select chr(65), chr(65 using char_cs), chr(65 using nchar_cs) from dual"),
        debug(
            "SELECT {{chr}(65)}, {{chr}(65 USING CHAR_CS)}, {{chr}(65 USING NCHAR_CS)} FROM {dual}"
        )
    );
}

#[test]
fn test_translate() {
    // ~ pure translate without 'using <charset>'
    assert_sql!(
        stmt("select translate('hello', 'eo', 'EO') from dual"),
        debug("SELECT {{translate}('hello', 'eo', 'EO')} FROM {dual}")
    );
    // ~ now TRANSLATE ... USING
    assert_sql!(
        stmt("select translate('hello' using char_cs) from dual"),
        debug("SELECT {{translate}('hello' USING CHAR_CS)} FROM {dual}")
    );
    assert_sql!(
        stmt("select translate('hello' using nchar_cs) from dual"),
        debug("SELECT {{translate}('hello' USING NCHAR_CS)} FROM {dual}")
    );

    // errors
    assert_sql!(
        stmt("select translate('hello', 'e', 'a' using nchar_cs) from dual"),
        debug("Invalid number of arguments [line: 1, column: 8]")
    );
    assert_sql!(
        stmt("select translate('hello', 'e' using nchar_cs, 'a') from dual"),
        debug("Invalid number of arguments [line: 1, column: 8]")
    );
    assert_sql!(
        stmt("select translate('hello' using nchar_cs, 'e', 'a') from dual"),
        debug("Invalid number of arguments [line: 1, column: 8]")
    );
}

#[test]
fn test_exp() {
    assert_sql!(
        stmt("select exp(2) from dual"),
        debug("SELECT {{exp}(2)} FROM {dual}")
    );
}

#[test]
fn test_floor() {
    assert_sql!(
        stmt("select floor(2) from dual"),
        debug("SELECT {{floor}(2)} FROM {dual}")
    );
}

#[test]
fn test_greatest() {
    assert_sql!(
        stmt("select greatest ('a') from dual;"),
        debug("SELECT {{greatest}('a')} FROM {dual};")
    );
    assert_sql!(
        stmt("select greatest (1, '3.925', '2.4') \"Greatest\" from dual;"),
        debug("SELECT {{{greatest}(1, '3.925', '2.4')} \"Greatest\"} FROM {dual};")
    );
}

#[test]
fn test_least() {
    assert_sql!(
        stmt(r#"select least('HARRY','HARRIOT','HAROLD') "Least" from dual;"#),
        debug("SELECT {{{least}('HARRY', 'HARRIOT', 'HAROLD')} \"Least\"} FROM {dual};")
    );
    assert_sql!(
        stmt(r#"SELECT LEAST (1, '2.1', '.000832') "Least" FROM DUAL;"#),
        debug("SELECT {{{LEAST}(1, '2.1', '.000832')} \"Least\"} FROM {DUAL};")
    );
}

#[test]
fn test_first_value() {
    assert_sql!(
        stmt(
            "select id, age, name, first_value(name) ignore nulls over (partition by age order by name) from foo_t"
        ),
        debug(
            "SELECT {id}, {age}, {name}, {{first_value}({name}) {IGNORE NULLS} {OVER ({PARTITION BY {age}} {ORDER BY {name}})}} FROM {foo_t}"
        )
    );
    assert_sql!(
        stmt(
            "select id, age, name, first_value(name respect nulls) over (partition by age order by name) from foo_t"
        ),
        debug(
            "SELECT {id}, {age}, {name}, {{first_value}({name} {RESPECT NULLS}) {OVER ({PARTITION BY {age}} {ORDER BY {name}})}} FROM {foo_t}"
        )
    );
    assert_sql!(
        stmt(
            "select id, age, name, first_value(name) over (partition by age order by name) from foo_t"
        ),
        debug(
            "SELECT {id}, {age}, {name}, {{first_value}({name}) {OVER ({PARTITION BY {age}} {ORDER BY {name}})}} FROM {foo_t}"
        )
    );

    // error: missing over_clause
    assert_sql!(
        stmt("select id, age, name, first_value(name) respect nulls from foo_t"),
        debug("Unexpected FROM; expected the OVER keyword [line: 1, column: 55]")
    );
    assert_sql!(
        stmt("select id, age, name, first_value(name) from foo_t"),
        debug("Unexpected FROM; expected the OVER keyword [line: 1, column: 41]")
    );

    // error: (respect_nulls) as both a parameter and a after arguments list
    assert_sql!(
        stmt(
            "select id, age, name, first_value(name respect nulls) respect nulls over (partition by age order by name) from foo_t"
        ),
        debug("Unexpected 'respect'; expected the OVER keyword [line: 1, column: 55]")
    );
    assert_sql!(
        stmt(
            "select id, age, name, first_value(name ignore nulls) respect nulls over (partition by age order by name) from foo_t"
        ),
        debug("Unexpected 'respect'; expected the OVER keyword [line: 1, column: 54]")
    );
    assert_sql!(
        stmt(
            "select id, age, name, first_value(name respect nulls) ignore nulls over (partition by age order by name) from foo_t"
        ),
        debug("Unexpected 'ignore'; expected the OVER keyword [line: 1, column: 55]")
    );
}

#[test]
fn test_last_value() {
    assert_sql!(
        stmt(
            "select id, age, name, last_value(name) ignore nulls over (partition by age order by name) from foo_t"
        ),
        debug(
            "SELECT {id}, {age}, {name}, {{last_value}({name}) {IGNORE NULLS} {OVER ({PARTITION BY {age}} {ORDER BY {name}})}} FROM {foo_t}"
        )
    );
    assert_sql!(
        stmt(
            "select id, age, name, last_value(name respect nulls) over (partition by age order by name) from foo_t"
        ),
        debug(
            "SELECT {id}, {age}, {name}, {{last_value}({name} {RESPECT NULLS}) {OVER ({PARTITION BY {age}} {ORDER BY {name}})}} FROM {foo_t}"
        )
    );
    assert_sql!(
        stmt(
            "select id, age, name, last_value(name) over (partition by age order by name) from foo_t"
        ),
        debug(
            "SELECT {id}, {age}, {name}, {{last_value}({name}) {OVER ({PARTITION BY {age}} {ORDER BY {name}})}} FROM {foo_t}"
        )
    );

    // error: missing over_clause
    assert_sql!(
        stmt("select id, age, name, last_value(name) respect nulls from foo_t"),
        debug("Unexpected FROM; expected the OVER keyword [line: 1, column: 54]")
    );
    assert_sql!(
        stmt("select id, age, name, last_value(name) from foo_t"),
        debug("Unexpected FROM; expected the OVER keyword [line: 1, column: 40]")
    );

    // error: (respect_nulls) as both a parameter and a after arguments list
    assert_sql!(
        stmt(
            "select id, age, name, last_value(name respect nulls) respect nulls over (partition by age order by name) from foo_t"
        ),
        debug("Unexpected 'respect'; expected the OVER keyword [line: 1, column: 54]")
    );
    assert_sql!(
        stmt(
            "select id, age, name, last_value(name ignore nulls) respect nulls over (partition by age order by name) from foo_t"
        ),
        debug("Unexpected 'respect'; expected the OVER keyword [line: 1, column: 53]")
    );
    assert_sql!(
        stmt(
            "select id, age, name, last_value(name respect nulls) ignore nulls over (partition by age order by name) from foo_t"
        ),
        debug("Unexpected 'ignore'; expected the OVER keyword [line: 1, column: 54]")
    );
}

#[rustfmt::skip]
#[test]
fn test_listagg() {
    assert_sql!(
        stmt("select LISTaGG(name, ',')   from t"),
        debug("SELECT {{LISTaGG}({name}, ',')} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name)   from t"),
        debug("SELECT {{LISTaGG}({name})} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(all name, ',')   from t"),
        debug("SELECT {{LISTaGG}(ALL {name}, ',')} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(distinct name)   from t"),
        debug("SELECT {{LISTaGG}(DISTINCT {name})} FROM {t}")
    );

    assert_sql!(
        stmt("select LISTaGG(name)   over () from t"),
        debug("SELECT {{LISTaGG}({name}) {OVER ()}} FROM {t}")
    );
    // XXX
    // assert_sql!(
    //     stmt("select LISTaGG(name)   over (order by t.x) from t"),
    //     debug("error: ORDER BY not allowed here")
    // );

    assert_sql!(
        stmt("select LISTaGG(name) within group (order by age) over () from t"),
        debug("SELECT {{LISTaGG}({name}) {WITHIN GROUP ({ORDER BY {age}})} {OVER ()}} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name) within group (order by age) from t"),
        debug("SELECT {{LISTaGG}({name}) {WITHIN GROUP ({ORDER BY {age}})}} FROM {t}")
    );

    assert_sql!(
        stmt("select LISTaGG(name on overflow error)   from t"),
        debug("SELECT {{LISTaGG}({name} {ON OVERFLOW ERROR})} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name, '##' on overflow error)   from t"),
        debug("SELECT {{LISTaGG}({name}, '##' {ON OVERFLOW ERROR})} FROM {t}")
    );

    assert_sql!(
        stmt("select LISTaGG(name, ',' on overflow truncate)   from t"),
        debug("SELECT {{LISTaGG}({name}, ',' {ON OVERFLOW TRUNCATE})} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name, ',' on overflow truncate '...')   from t"),
        debug("SELECT {{LISTaGG}({name}, ',' {ON OVERFLOW TRUNCATE '...'})} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name, ',' on overflow truncate '...' with count)   from t"),
        debug("SELECT {{LISTaGG}({name}, ',' {ON OVERFLOW TRUNCATE '...' WITH COUNT})} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name, ',' on overflow truncate with count)   from t"),
        debug("SELECT {{LISTaGG}({name}, ',' {ON OVERFLOW TRUNCATE WITH COUNT})} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name, ',' on overflow truncate '...' without count)   from t"),
        debug("SELECT {{LISTaGG}({name}, ',' {ON OVERFLOW TRUNCATE '...' WITHOUT COUNT})} FROM {t}")
    );
    assert_sql!(
        stmt("select LISTaGG(name, ',' on overflow truncate without count)   from t"),
        debug("SELECT {{LISTaGG}({name}, ',' {ON OVERFLOW TRUNCATE WITHOUT COUNT})} FROM {t}")
    );
}

#[test]
fn test_rank() {
    // ~ aggregate example
    assert_sql!(
        stmt("select rank(1, 3) within group (order by 1, 2) from dual;"),
        debug("SELECT {{rank}(1, 3) {WITHIN GROUP ({ORDER BY 1, 2})}} FROM {dual};")
    );
    // ~ aggregate as part of having
    assert_sql!(
        stmt("select 1 from dual group by () having rank(1) within group (order by 1)  > 1"),
        debug(
            "SELECT 1 FROM {dual} {GROUP BY () HAVING {{{rank}(1) {WITHIN GROUP ({ORDER BY 1})}} > 1}}"
        )
    );
    // ~ error: not in WHERE (or JOIN) clauses
    assert_sql!(
        stmt("select 1 from dual where rank(1) within group (order by 1)  > 1"),
        debug("Aggregate functions not allowed here [line: 1, column: 26]")
    );
    // ~ error: num args of rank must match the order by expressions
    assert_sql!(
        stmt("select rank(1, 3) within group (order by 1, 2, 3) from dual;"),
        debug("Invalid number of arguments [line: 1, column: 49]")
    );

    // ~ analytic example
    assert_sql!(
        stmt("select rank() over (partition by 1 order by 1)  from dual"),
        debug("SELECT {{rank}() {OVER ({PARTITION BY 1} {ORDER BY 1})}} FROM {dual}")
    );
    assert_sql!(
        stmt("select rank() over (partition by 1 order by 1)  from dual"),
        debug("SELECT {{rank}() {OVER ({PARTITION BY 1} {ORDER BY 1})}} FROM {dual}")
    );
    assert_sql!(
        stmt("select rank() over (partition by 1 order by 1 rows unbounded preceding)  from dual"),
        debug("Unexpected 'rows'; expected a closing parenthesis [line: 1, column: 47]")
    );
    // ~ analytic as part of having not allowed
    assert_sql!(
        stmt("select 1 from dual group by () having rank() over (partition by 1 order by 1) > 1"),
        debug("Analytic functions not allowed here [line: 1, column: 39]")
    );
}

#[test]
fn test_width_bucket() {
    assert_sql!(
        stmt("SELECT WIDTH_BUCKET(a, 1000, 5000, 10) from (select 2000 a from dual)"),
        debug("SELECT {{WIDTH_BUCKET}({a}, 1000, 5000, 10)} FROM (SELECT {2000 a} FROM {dual})")
    );
}

#[test]
fn test_vsize() {
    assert_sql!(
        stmt("SELECT last_name, VSIZE (last_name) \"BYTES\" FROM employees"),
        debug("SELECT {last_name}, {{{VSIZE}({last_name})} \"BYTES\"} FROM {employees}")
    );
}

#[test]
fn test_var_pop_and_samp() {
    assert_sql!(
        stmt("SELECT VAR_POP(salary) FROM employees;"),
        debug("SELECT {{VAR_POP}({salary})} FROM {employees};")
    );
    assert_sql!(
        stmt("SELECT VAR_SAMP(salary) FROM employees;"),
        debug("SELECT {{VAR_SAMP}({salary})} FROM {employees};")
    );
}

#[test]
fn test_covar_pop_and_samp() {
    // ~ aggregate example
    assert_sql!(
        stmt(
            r#"SELECT job_id, 
       COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
       COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
  FROM employees
  WHERE department_id in (50, 80)
  GROUP BY job_id
  ORDER BY job_id, covar_pop, covar_samp;"#
        ),
        debug(
            "SELECT {job_id}, \
                    {{{COVAR_POP}({{SYSDATE} - {hire_date}}, {salary})} AS covar_pop}, \
                    {{{COVAR_SAMP}({{SYSDATE} - {hire_date}}, {salary})} AS covar_samp} \
               FROM {employees} \
              WHERE {{department_id} IN (50, 80)} \
             {GROUP BY {job_id}} \
             {ORDER BY {job_id}, {covar_pop}, {covar_samp}};"
        )
    );

    // ~ analytic example
    assert_sql!(
        stmt(
            r#"SELECT product_id, supplier_id,
       COVAR_POP(list_price, min_price) 
         OVER (ORDER BY product_id, supplier_id)
         AS CUM_COVP,
       COVAR_SAMP(list_price, min_price)
         OVER (ORDER BY product_id, supplier_id)
         AS CUM_COVS 
  FROM product_information p
  WHERE category_id = 29
  ORDER BY product_id, supplier_id;"#
        ),
        debug(
            "SELECT {product_id}, \
                      {supplier_id}, \
                      {{{COVAR_POP}({list_price}, {min_price}) {OVER ({ORDER BY {product_id}, {supplier_id}})}} AS CUM_COVP}, \
                      {{{COVAR_SAMP}({list_price}, {min_price}) {OVER ({ORDER BY {product_id}, {supplier_id}})}} AS CUM_COVS} \
                FROM {{product_information} p} \
               WHERE {{category_id} = 29} \
              {ORDER BY {product_id}, {supplier_id}};"
        )
    );
}

#[test]
fn test_lower_upper_unistr() {
    assert_sql!(
        stmt(r#"SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL;"#),
        debug(r#"SELECT {{{LOWER}('MR. SCOTT MCMILLAN')} "Lowercase"} FROM {DUAL};"#)
    );
    assert_sql!(
        stmt(r#"SELECT UPPER(last_name) "Uppercase"   FROM employees;"#),
        debug(r#"SELECT {{{UPPER}({last_name})} "Uppercase"} FROM {employees};"#)
    );
    assert_sql!(
        stmt(r#"SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;"#),
        debug(r#"SELECT {{UNISTR}('abc\00e5\00f1\00f6')} FROM {DUAL};"#)
    );
}

#[test]
fn test_lag() {
    assert_sql!(
        stmt("SELECT LAG(a, 1, 0) OVER (ORDER BY 1) from t"),
        debug("SELECT {{LAG}({a}, 1, 0) {OVER ({ORDER BY 1})}} FROM {t}")
    );
    // error: missing over_clause
    assert_sql!(
        stmt("SELECT LAG(a, 1, 0) from t"),
        debug("Unexpected FROM; expected the OVER keyword [line: 1, column: 21]")
    );
    // error: not an analytical context
    assert_sql!(
        stmt("SELECT 1 from t where LAG(a, 1, 0) over (order by 1) = 1"),
        debug("Analytic functions not allowed here [line: 1, column: 23]")
    );

    // error: no window frame allowed
    #[rustfmt::skip]
    assert_sql!(
        stmt("SELECT LAG(a, 1, 0) OVER (partition by 1 ORDER BY 1 rows unbounded preceding) from t"),
        debug("Unexpected 'rows'; expected a closing parenthesis [line: 1, column: 53]")
    );
}

#[test]
fn test_lead() {
    assert_sql!(
        stmt("SELECT LEAD(a, 1, 0) OVER (ORDER BY 1) from t"),
        debug("SELECT {{LEAD}({a}, 1, 0) {OVER ({ORDER BY 1})}} FROM {t}")
    );
    // error: missing over_clause
    assert_sql!(
        stmt("SELECT LEAD(a, 1, 0) from t"),
        debug("Unexpected FROM; expected the OVER keyword [line: 1, column: 22]")
    );
    // error: not an analytical context
    assert_sql!(
        stmt("SELECT 1 from t where LEAD(a, 1, 0) over (order by 1) = 1"),
        debug("Analytic functions not allowed here [line: 1, column: 23]")
    );

    // error: no window frame allowed
    #[rustfmt::skip]
    assert_sql!(
        stmt("SELECT LEAD(a, 1, 0) OVER (partition by 1 ORDER BY 1 rows unbounded preceding) from t"),
        debug("Unexpected 'rows'; expected a closing parenthesis [line: 1, column: 54]")
    );
}

#[test]
fn test_last_day() {
    assert_sql!(
        stmt(
            r#"
 SELECT last_name, hire_date,
       TO_CHAR(ADD_MONTHS(LAST_DAY(hire_date), 5)) "Eval Date"
  FROM employees
  ORDER BY last_name, hire_date;"#
        ),
        debug(
            "SELECT {last_name}, \
                      {hire_date}, \
                      {{{TO_CHAR}({{ADD_MONTHS}({{LAST_DAY}({hire_date})}, 5)})} \"Eval Date\"} \
                 FROM {employees} \
               {ORDER BY {last_name}, {hire_date}};"
        )
    );
}

#[test]
fn test_log() {
    assert_sql!(
        stmt(r#"SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL;"#),
        debug("SELECT {{{LOG}(10, 100)} \"Log base 10 of 100\"} FROM {DUAL};")
    );
}

#[test]
fn test_ln() {
    assert_sql!(
        stmt("SELECT LN(95) from dual"),
        debug("SELECT {{LN}(95)} FROM {dual}")
    );
}

#[test]
fn test_mod() {
    assert_sql!(
        stmt("SELECT MOD(11,4) \"Modulus\" FROM DUAL;"),
        debug("SELECT {{{MOD}(11, 4)} \"Modulus\"} FROM {DUAL};")
    );
}

#[test]
fn test_invalid_number_of_arguments() {
    use std::fmt::Write;
    // ~ function to call and the number invalid argument to lead to an
    // expected error
    let functions = [
        ("abs", 0),
        ("abs", 2),
        ("acos", 0),
        ("acos", 2),
        ("acos", 3),
        ("add_months", 0),
        ("add_months", 1),
        ("add_months", 3),
        ("ascii", 0),
        ("ascii", 2),
        ("asciistr", 0),
        ("asciistr", 2),
        ("asciistr", 3),
        ("asin", 0),
        ("asin", 2),
        ("atan", 0),
        ("atan", 2),
        ("atan2", 0),
        ("atan2", 1),
        ("atan2", 3),
        ("bin_to_num", 0),
        ("bit_and_agg", 0),
        ("bit_and_agg", 2),
        ("bit_or_agg", 0),
        ("bit_or_agg", 2),
        ("bit_xor_agg", 0),
        ("bit_xor_agg", 2),
        ("bitand", 0),
        ("bitand", 3),
        ("bitmap_bit_position", 0),
        ("bitmap_bit_position", 2),
        ("bitmap_bucket_number", 0),
        ("bitmap_bucket_number", 2),
        ("bitmap_construct_agg", 0),
        ("bitmap_construct_agg", 2),
        ("bitmap_count", 0),
        ("bitmap_count", 2),
        ("bitmap_or_agg", 0),
        ("bitmap_or_agg", 2),
        ("cardinality", 0),
        ("cardinality", 2),
        ("ceil", 0),
        ("ceil", 2),
        ("chartorowid", 0),
        ("chartorowid", 2),
        ("checksum", 0),
        ("checksum", 2),
        ("chr", 0),
        ("chr", 2),
        ("coalesce", 0),
        ("collation", 0),
        ("collation", 2),
        ("compose", 0),
        ("compose", 2),
        ("concat", 0),
        ("concat", 1),
        ("concat", 3),
        ("convert", 0),
        ("convert", 1),
        ("convert", 4),
        ("corr", 0),
        ("corr", 3),
        ("corr_k", 0),
        ("corr_k", 1),
        ("corr_k", 4),
        ("corr_s", 0),
        ("corr_s", 1),
        ("corr_s", 4),
        ("cos", 0),
        ("cos", 2),
        ("cosh", 0),
        ("cosh", 2),
        ("covar_pop", 0),
        ("covar_pop", 1),
        ("covar_pop", 3),
        ("covar_samp", 0),
        ("covar_samp", 1),
        ("covar_samp", 3),
        ("current_timestamp", 0),
        ("current_timestamp", 2),
        ("decode", 0),
        ("decode", 1),
        ("decode", 2),
        ("exp", 0),
        ("exp", 2),
        ("first_value", 0),
        ("first_value", 2),
        ("floor", 0),
        ("floor", 2),
        ("greatest", 0),
        ("lag", 0),
        ("lag", 4),
        ("last_day", 0),
        ("last_day", 2),
        ("last_value", 0),
        ("last_value", 2),
        ("lead", 0),
        ("lead", 4),
        ("least", 0),
        ("listagg", 0),
        ("listagg", 3),
        ("localtimestamp", 0),
        ("localtimestamp", 2),
        ("log", 0),
        ("log", 3),
        ("lower", 0),
        ("lower", 2),
        ("ln", 0),
        ("ln", 2),
        ("median", 0),
        ("median", 2),
        ("nth_value", 0),
        ("nth_value", 1),
        ("nth_value", 3),
        ("translate", 0),
        ("translate", 1),
        ("translate", 2),
        ("translate", 4),
        ("unistr", 0),
        ("unistr", 2),
        ("upper", 0),
        ("upper", 2),
        ("var_pop", 0),
        ("var_pop", 2),
        ("var_samp", 0),
        ("var_samp", 2),
        ("vsize", 0),
        ("vsize", 2),
        ("width_bucket", 0),
        ("width_bucket", 1),
        ("width_bucket", 2),
        ("width_bucket", 3),
        ("width_bucket", 5),
    ];
    for (name, n_fail) in functions {
        let mut sql = String::new();
        let _ = write!(&mut sql, "select {name}(");
        for i in 0..n_fail {
            if i > 0 {
                sql.push_str(", ");
            }
            let _ = write!(&mut sql, "{i}");
        }
        sql.push_str(") from dual");
        assert_eq!(
            parse_stmt_to_string(&sql, crate::parser::tests::Style::Default),
            "Invalid number of arguments [line: 1, column: 8]",
            "for query: {sql:?}"
        );
    }
}