orql 0.1.0

#[test]
fn test_empty() {
    assert_sql!(stmt(";"), ";");
    assert_sql!(stmt(";"), with_meta(";"));
}

#[test]
fn test_empty_with_comments() {
    assert_sql!(
        stmt("     -- wooho\n; /* some after words */ "),
        with_meta("-- wooho\n; /* some after words */")
    )
}

// ----------------------------------------------------------------------------

#[rustfmt::skip]
#[test]
fn test_select_simple() {
    assert_sql!(
        stmt("select * FROM foo f"),
        debug("SELECT * FROM {{foo} f}"));

    assert_sql!(
        stmt("select * FROM (foo) f"),
        debug("SELECT * FROM {({foo}) f}"));

    assert_sql!(
        stmt("select * FROM (foo f)"),
        debug("SELECT * FROM ({{foo} f})"));

    assert_sql!(
        stmt("select * FROM ((dual) f)"),
        debug("SELECT * FROM ({({dual}) f})"));

    assert_sql!(
        stmt("select * FROM (((dual) f))"),
        debug("SELECT * FROM (({({dual}) f}))"));

    assert_sql!(
        stmt("select 1 + 2, * FROM foo f"),
        debug("SELECT {1 + 2}, * FROM {{foo} f}"));

    assert_sql!(
        stmt("select 1, 2, * FROM foo f"),
        debug("SELECT 1, 2, * FROM {{foo} f}"));

    assert_sql!(
        stmt("select f.* FROM foo f"),
        debug("SELECT {f.*} FROM {{foo} f}"));

    assert_sql!(
        stmt("select schema.f.* FROM foo f"),
        debug("SELECT {schema.f.*} FROM {{foo} f}"));

    assert_sql!(
        stmt("select pkg.f() FROM foo f"),
        debug("SELECT {{pkg.f}()} FROM {{foo} f}"));

    assert_sql!(
        stmt("select pkg.f('a') FROM foo f"),
        debug("SELECT {{pkg.f}('a')} FROM {{foo} f}"));

    assert_sql!(
        stmt("select pkg.f('a') || 'b' FROM foo f"),
        debug("SELECT {{{pkg.f}('a')} || 'b'} FROM {{foo} f}"));

    // ~ only
    assert_sql!(
        stmt("select 1 from only"),
        debug("SELECT 1 FROM {only}"));
    assert_sql!(
        stmt("select 1 from only (only)"),
        debug("SELECT 1 FROM {ONLY ({only})}"));

    // ~ with aliases
    assert_sql!(
        stmt("select pkg.f('a') || 'b' as X FROM foo f"),
        debug("SELECT {{{{pkg.f}('a')} || 'b'} AS X} FROM {{foo} f}"));
    assert_sql!(
        stmt("select pkg.f('a') || 'b' X FROM foo f"),
        debug("SELECT {{{{pkg.f}('a')} || 'b'} X} FROM {{foo} f}"));
}

#[rustfmt::skip]
#[test]
fn test_hint() {
    assert_sql!(
        stmt("select /*not a hint*/ 1 from dual"),
        debug("SELECT 1 FROM {dual}"));
    assert_sql!(
        stmt("select /*not a hint*/ /*+also not a hint*/ 1 from dual"),
        debug("SELECT 1 FROM {dual}"));
    assert_sql!(
        stmt("select /*not a hint*/ /*+also not a hint*/ 1 from dual"),
        debug(with_meta("SELECT /*not a hint*/ /*+also not a hint*/ 1 FROM {dual}")));
    assert_sql!(
        stmt("select /*+but this one is*/ /*a regular comment*/ 1 from dual"),
        debug("SELECT /*+but this one is*/ 1 FROM {dual}"));
    assert_sql!(
        stmt("select /*+but this one is*/ /*+not a hint*/ 1 from dual"),
        debug("SELECT /*+but this one is*/ 1 FROM {dual}"));
    assert_sql!(
        stmt("select /*+but this one is*/ /*+not a hint*/ 1 from dual"),
        debug(with_meta("SELECT /*+but this one is*/ /*+not a hint*/ 1 FROM {dual}")));
}

#[rustfmt::skip]
#[test]
fn test_select_with_duplicates() {
    // ~ duplicates as first token in select projections
    assert_sql!(
        stmt("select distinct * FROM foo f"),
        debug("SELECT DISTINCT * FROM {{foo} f}"));
    assert_sql!(
        stmt("select unique * FROM foo f"),
        debug("SELECT UNIQUE * FROM {{foo} f}"));
    assert_sql!(
        stmt("select all * FROM foo f"),
        debug("SELECT ALL * FROM {{foo} f}"));

    // ~ duplicates as first token in function calls
    assert_sql!(
        stmt("select f(all) FROM foo"),
        debug("Invalid number of arguments [line: 1, column: 8]"));
    assert_sql!(
        stmt("select f(all 1) FROM foo"),
        debug("SELECT {{f}(ALL 1)} FROM {foo}"));
    assert_sql!(
        stmt("select f(unique 1) FROM foo"),
        debug("SELECT {{f}(UNIQUE 1)} FROM {foo}"));
    assert_sql!(
        stmt("select f(distinct 1) FROM foo"),
        debug("SELECT {{f}(DISTINCT 1)} FROM {foo}"));
    assert_sql!(
        stmt("select f(distinct 1, 2, 3) FROM foo"),
        debug("SELECT {{f}(DISTINCT 1, 2, 3)} FROM {foo}"));
}

#[rustfmt::skip]
#[test]
fn test_select_for_update() {
    assert_sql!(
        stmt("select * from foo for update"),
        debug("SELECT * FROM {foo} {FOR UPDATE}"));
    assert_sql!(
        stmt("select * from foo for update of abc"),
        debug("SELECT * FROM {foo} {FOR UPDATE {OF {abc}}}"));
    assert_sql!(
        stmt("select * from foo for update of abc, def"),
        debug("SELECT * FROM {foo} {FOR UPDATE {OF {abc}, {def}}}"));
    assert_sql!(
        stmt("select * from foo for update of abc WAIT 0001"),
        debug("SELECT * FROM {foo} {FOR UPDATE {OF {abc}} {WAIT 0001}}"));
    assert_sql!(
        stmt("select * from foo for update of abc NOWAIT"),
        debug("SELECT * FROM {foo} {FOR UPDATE {OF {abc}} {NOWAIT}}"));
    assert_sql!(
        stmt("select * from foo for update of abc SKIP LOCKED"),
        debug("SELECT * FROM {foo} {FOR UPDATE {OF {abc}} {SKIP LOCKED}}"));
    assert_sql!(
        stmt("select * from foo for update of abc, skip SKIP LOCKED"),
        debug("SELECT * FROM {foo} {FOR UPDATE {OF {abc}, {skip}} {SKIP LOCKED}}"));

    // ~ with comments
    assert_sql!(
        stmt("select * from foo/*1*/for/*2*/update /*3*/ of /*4*/ abc /*5a*/, /*5b*/ def /*5c*/"),
        with_meta("SELECT * FROM foo /*1*/ FOR /*2*/ UPDATE /*3*/ OF /*4*/ abc /*5a*/ , /*5b*/ def /*5c*/"));
    assert_sql!(
        stmt("select * from foo /*1*/ for /*2*/ update /*3*/ of /*4*/ abc /*5*/ WAIT /*6*/ 123 /*7*/"),
        with_meta("SELECT * FROM foo /*1*/ FOR /*2*/ UPDATE /*3*/ OF /*4*/ abc /*5*/ WAIT /*6*/ 123 /*7*/"));
    assert_sql!(
        stmt("select * from foo /*1*/ for /*2*/ update /*3*/ of /*4*/ abc /*5*/ NOWAIT/*6*/"),
        with_meta("SELECT * FROM foo /*1*/ FOR /*2*/ UPDATE /*3*/ OF /*4*/ abc /*5*/ NOWAIT /*6*/"));
    assert_sql!(
        stmt("select * from foo /*1*/ for /*2*/ update /*3*/ of /*4*/ abc /*5*/ SKIP/*6*/ LOCKED/*7*/"),
        with_meta("SELECT * FROM foo /*1*/ FOR /*2*/ UPDATE /*3*/ OF /*4*/ abc /*5*/ SKIP /*6*/ LOCKED /*7*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_lateral() {
    assert_sql!(
        stmt("SELECT * FROM employees e, \"lateral\""),
        debug("SELECT * FROM {{employees} e}, {\"lateral\"}"));

    assert_sql!(
        stmt("SELECT * FROM employees e, LATERAL(SELECT * FROM departments d WHERE e.department_id = d.department_id)"),
        debug("\
SELECT * \
   FROM {{employees} e}, \
LATERAL (SELECT * \
            FROM {{departments} d} \
           WHERE {{e.department_id} = {d.department_id}})"));

    assert_sql!(
        stmt("select * from dual t left join lateral (select 1 from dual) on 1=1"),
        debug("SELECT * FROM {{dual} t} {LEFT JOIN LATERAL (SELECT 1 FROM {dual}) ON {1 = 1}}"));

    assert_sql!(
        stmt("select * from lateral (select * from dual)"),
        debug("SELECT * FROM LATERAL (SELECT * FROM {dual})"));
    assert_sql!(
        stmt("select * from (lateral (select * from dual))"),
        debug("SELECT * FROM (LATERAL (SELECT * FROM {dual}))"));

    // ~ check comment assignments
    assert_sql!(
        stmt("select * from dual t left join /*1*/ lateral /*2*/ (select 1 from dual) on 1=1"),
        with_meta("SELECT * FROM dual t LEFT JOIN /*1*/ LATERAL /*2*/ (SELECT 1 FROM dual) ON 1 = 1"));
}

#[rustfmt::skip]
#[test]
fn test_select_table_collection() {
    assert_sql!(
        stmt("SELECT * FROM table(foo(12))"),
        debug("SELECT * FROM {TABLE({{foo}(12)})}"));

    assert_sql!(
        stmt("SELECT * FROM table(foo(12)) (+)"),
        debug("SELECT * FROM {TABLE({{foo}(12)}) (+)}"));

    assert_sql!(
        stmt("SELECT * FROM /*1*/table/*2*/(/*3*/ foo(12) /*4*/ ) /*5*/"),
        with_meta("SELECT * FROM /*1*/ TABLE /*2*/ ( /*3*/ foo(12) /*4*/ ) /*5*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_from_containers_and_shards() {
    assert_sql!(
        stmt("select * FROM containers(foo)"),
        debug("SELECT * FROM {CONTAINERS({foo})}"));
    assert_sql!(
        stmt("select * FROM containers(foo) f"),
        debug("SELECT * FROM {{CONTAINERS({foo})} f}"));
    assert_sql!(
        stmt("select * FROM containers(foo) f left join containers(bar)"),
        debug("SELECT * FROM {{CONTAINERS({foo})} f} {LEFT JOIN {CONTAINERS({bar})}}"));

    assert_sql!(
        stmt("select * FROM shards(foo)"),
        debug("SELECT * FROM {SHARDS({foo})}"));
    assert_sql!(
        stmt("select * FROM SHarDS(foo) f"),
        debug("SELECT * FROM {{SHARDS({foo})} f}"));
    assert_sql!(
        stmt("select * FROM shards(foo) f left join shards(bar)"),
        debug("SELECT * FROM {{SHARDS({foo})} f} {LEFT JOIN {SHARDS({bar})}}"));

    assert_sql!(
        stmt("select * FROM containers(foo) f left join shards(bar)"),
        debug("SELECT * FROM {{CONTAINERS({foo})} f} {LEFT JOIN {SHARDS({bar})}}"));

    // ~ _not_ a "containers" or "shards" clause
    assert_sql!(
        stmt("select * FROM containers f left join shards"),
        debug("SELECT * FROM {{containers} f} {LEFT JOIN {shards}}"));
}

#[rustfmt::skip]
#[test]
fn test_select_multiple_from_tables() {
    assert_sql!(
        stmt("select * FROM foo f, bar b"),
        debug("SELECT * FROM {{foo} f}, {{bar} b}"));
    assert_sql!(
        stmt("select * FROM (foo f), (bar b)"),
        debug("SELECT * FROM ({{foo} f}), ({{bar} b})"));
    assert_sql!(
        stmt("select * FROM (foo) f, (bar) b"),
        debug("SELECT * FROM {({foo}) f}, {({bar}) b}"));
}

#[rustfmt::skip]
#[test]
fn test_select_set_operators() {
    assert_sql!(
        stmt("select 1 from abc union select 2 from def;"),
        debug("{{SELECT 1 FROM {abc}} UNION {SELECT 2 FROM {def}}};"));

    assert_sql!(
        stmt("select 1 from abc union all select 2 from def;"),
        debug("{{SELECT 1 FROM {abc}} UNION ALL {SELECT 2 FROM {def}}};"));

    assert_sql!(
        stmt("select 1 from abc union select 2 from def minus select 3 from ghi;"),
        debug("{{{{SELECT 1 FROM {abc}} UNION {SELECT 2 FROM {def}}}} MINUS {SELECT 3 FROM {ghi}}};"));

    assert_sql!(
        stmt("select 1 from abc union select 2 from def minus select 3 from ghi except all select 4 from jkl;"),
        debug("{{{{{{SELECT 1 FROM {abc}} UNION {SELECT 2 FROM {def}}}} MINUS {SELECT 3 FROM {ghi}}}} EXCEPT ALL {SELECT 4 FROM {jkl}}};"));

    assert_sql!(
        stmt("\
select 1 from abc \
 union \
select 2 from def \
 minus \
select 3 from ghi \
 except all \
select 4 from jkl \
 intersect \
select 5 from dual;"),
        debug("\
  {\
    {{\
      {{\
       {{{SELECT 1 FROM {abc}} UNION {SELECT 2 FROM {def}}}} \
       MINUS \
       {SELECT 3 FROM {ghi}}\
      }} \
      EXCEPT ALL \
      {SELECT 4 FROM {jkl}}\
    }} \
    INTERSECT \
    {SELECT 5 FROM {dual}}\
  };"));
}

#[rustfmt::skip]
#[test]
fn test_projection_wildcards_with_comments() {
    assert_sql!(
        stmt("select /*2a*/ a /*2b*/ . /*2c*/ * /*2d*/ from /*3*/ dual"),
        with_meta("SELECT /*2a*/ a /*2b*/ . /*2c*/ * /*2d*/ FROM /*3*/ dual"));

    assert_sql!(
        stmt("select /*1a*/ * /*1b*/ , /*2a*/ a /*2b*/ . /*2c*/ * /*2d*/ from /*3*/ dual"),
        with_meta("SELECT /*1a*/ * /*1b*/ , /*2a*/ a /*2b*/ . /*2c*/ * /*2d*/ FROM /*3*/ dual"));

    assert_sql!(
        stmt("select /*1a*/ a /*1b*/ . /*1c*/ b /*1d*/ . /*1e*/ * /*1f*/ from /*3*/ dual"),
        with_meta("SELECT /*1a*/ a /*1b*/ . /*1c*/ b /*1d*/ . /*1e*/ * /*1f*/ FROM /*3*/ dual"));
}

#[rustfmt::skip]
#[test]
fn test_select_simple_with_comments() {
    assert_sql!(
        stmt("/*1*/ select /*2*/ 'a' /*3*/ from /*4*/ dual /*5*/"),
        with_meta("/*1*/ SELECT /*2*/ 'a' /*3*/ FROM /*4*/ dual /*5*/"));

    assert_sql!(
        stmt("/*1*/ select /*2*/ 'a' /*3*/ from /*4*/ (/*5*/ dual /*6*/) /*7*/"),
        with_meta("/*1*/ SELECT /*2*/ 'a' /*3*/ FROM /*4*/ ( /*5*/ dual /*6*/ ) /*7*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_subqueries_in_from_clause() {
    // ~ nested
    assert_sql!(
        stmt("(select 1 FROM foo)"),
        debug("(SELECT 1 FROM {foo})"));
    assert_sql!(
        stmt("(select 1 FROM foo f)"),
        debug("(SELECT 1 FROM {{foo} f})"));
    // ~ not allowed: alias after a nested, top-level query
    assert_sql!(
        stmt("(select 1 FROM foo) f"),
        "Unexpected 'f'; expected a semicolon or end-of-file [line: 1, column: 21]");
    // ~ allowed: alias after nesting in FROM position
    assert_sql!(
        stmt("select 1 FROM (select 1 from dual) f"),
        debug("SELECT 1 FROM {(SELECT 1 FROM {dual}) f}"));
    assert_sql!(
        stmt("select 1 from (select 2 from (select 3 from dual f) g) h"),
        debug("SELECT 1 FROM {(SELECT 2 FROM {(SELECT 3 FROM {{dual} f}) g}) h}"));
}

#[rustfmt::skip]
#[test]
fn test_select_subqueries_in_from_clause_with_comments() {
    // ~ allowed: alias after nesting in FROM position
    assert_sql!(
        stmt("select 1 FROM /*1*/ ( /*2*/ select /*3*/ 2 /*4*/ from /*5*/ dual /*6*/) /*7*/ f /*8*/"),
        with_meta("SELECT 1 FROM /*1*/ ( /*2*/ SELECT /*3*/ 2 /*4*/ FROM /*5*/ dual /*6*/ ) /*7*/ f /*8*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_subqueries_in_projection_items() {
    assert_sql!(
        stmt("select (select 1 from dual) from dual"),
        debug("SELECT (SELECT 1 FROM {dual}) FROM {dual}"));
    assert_sql!(
        stmt("select (select 1 from dual) f from dual"),
        debug("SELECT {(SELECT 1 FROM {dual}) f} FROM {dual}"));
    assert_sql!(
        stmt("select (select 1 from dual) as f from dual"),
        debug("SELECT {(SELECT 1 FROM {dual}) AS f} FROM {dual}"));
    assert_sql!(
        stmt(r#"
select
    (select 1 from dual) as f,
    (select 2 from dual) as g,
    (select (select 3 from dual) as h from dual) i
  from dual"#),
        debug("\
SELECT \
 {(SELECT 1 FROM {dual}) AS f}, \
 {(SELECT 2 FROM {dual}) AS g}, \
 {(SELECT {(SELECT 3 FROM {dual}) AS h} FROM {dual}) i} \
 FROM {dual}\
"));
}

#[rustfmt::skip]
#[test]
fn test_select_subquery_in_projection_items_with_comments() {
    // sub-query in a projection
    assert_sql!(
        stmt("/*1*/ select /*2*/ (/*3*/ select /*4*/ 1 /*5*/ from /*6*/ dual /*7*/) /*8*/ from /*9*/ dual /*10*/"),
        with_meta("/*1*/ SELECT /*2*/ ( /*3*/ SELECT /*4*/ 1 /*5*/ FROM /*6*/ dual /*7*/ ) /*8*/ FROM /*9*/ dual /*10*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_not_a_subquery_with_comments() {
    assert_sql!(
        stmt("/*1*/ select /*2a*/ ((a)) /*2b*/, /*3a*/ 'x' /*3b*/ from /*4*/ dual /*5*/"),
        with_meta("/*1*/ SELECT /*2a*/ ((a)) /*2b*/ , /*3a*/ 'x' /*3b*/ FROM /*4*/ dual /*5*/"));
    // ~ the same without comments
    assert_sql!(
        stmt("/*1*/ select /*2a*/ ((a)) /*2b*/ , /*3a*/ 'x' /*3b*/ from /*4*/ dual /*5*/"),
        "SELECT ((a)), 'x' FROM dual");
}

#[rustfmt::skip]
#[test]
fn test_select_with_ctes() {
    assert_sql!(
        stmt("with abc as (select 1 from dual) select * from abc"),
        debug("{WITH {abc AS (SELECT 1 FROM {dual})}} SELECT * FROM {abc}"));
    assert_sql!(
        stmt("with abc (x) as (select 1 from dual) select * from abc"),
        debug("{WITH {abc(x) AS (SELECT 1 FROM {dual})}} SELECT * FROM {abc}"));

    assert_sql!(
        stmt("with abc as (select 1 from dual), def (y) as (select 2 from abc) select * from def"),
        debug("{WITH {abc AS (SELECT 1 FROM {dual})}, {def(y) AS (SELECT 2 FROM {abc})}} SELECT * FROM {def}"));
    assert_sql!(
        stmt("with abc as (select 1 from dual), def (y) as (select 2 from abc) select * from def"),
        // ~ no metadata
        "WITH abc AS (SELECT 1 FROM dual), def(y) AS (SELECT 2 FROM abc) SELECT * FROM def");

    assert_sql!(stmt(r#"
WITH foo (a) AS (SELECT 1 FROM dual)
SELECT * FROM foo
  UNION
SELECT * FROM (SELECT 2 FROM dual ORDER BY 1)
"#),
        "WITH foo(a) AS (SELECT 1 FROM dual) SELECT * FROM foo UNION SELECT * FROM (SELECT 2 FROM dual ORDER BY 1)");
}

#[rustfmt::skip]
#[test]
fn test_select_with_ctes_invalid() {
    // ~ a `with` nested within a CTE
    assert_sql!(stmt(r#"
with foo(a) as (
  with bar(a) as (select 1 from dual)
  select * from bar
)
select * from foo;
"#), "Unexpected WITH; expected a query block [line: 3, column: 3]");

    // ~ a `with` nested as a sub-query is allowed, though
    assert_sql!(stmt(r#"
WITH foo(a) AS (SELECT 1 FROM dual)
SELECT * FROM foo
UNION
SELECT * FROM (WITH bar(a) AS (SELECT 2 FROM dual) SELECT a from bar)
"#), "\
WITH foo(a) AS (SELECT 1 FROM dual) \
SELECT * FROM foo UNION \
SELECT * FROM (WITH bar(a) AS (SELECT 2 FROM dual) SELECT a FROM bar)");

    // ~ a nested `with` (not as a sub-query, though)
    assert_sql!(stmt(r#"
(with foo(a) as (select 1 from dual)
 select * from foo order by 1
)
"#), "Unexpected WITH; expected a query block [line: 2, column: 2]");
}

#[rustfmt::skip]
#[test]
fn test_select_with_ctes_with_comments() {
    assert_sql!(
        stmt("/*1*/ with /*2*/ abc /*3*/ as /*4*/ (\
/*5*/ select /*6*/ 1 /*7*/ from /*8*/ dual /*9*/) /*10*/, \
/*11*/ def /*12*/ ( /*13*/ y /*14*/, /*15*/ x /*16*/) /*17*/ as /*18*/ \
(/*19*/ select /*20*/ 1 /*21*/, /*22*/ 2 /*23*/ from /*24*/ abc /*25*/) /*26*/ \
 select /*27*/ * /*28*/ from /*28*/ def /*29*/"),
        with_meta("/*1*/ WITH /*2*/ abc /*3*/ AS /*4*/ ( /*5*/ \
SELECT /*6*/ 1 /*7*/ FROM /*8*/ dual /*9*/ \
) /*10*/ , \
/*11*/ def /*12*/ ( /*13*/ y /*14*/ , /*15*/ x /*16*/ ) /*17*/ AS /*18*/ \
( /*19*/ SELECT /*20*/ 1 /*21*/ , /*22*/ 2 /*23*/ FROM /*24*/ abc /*25*/ ) /*26*/ \
SELECT /*27*/ * /*28*/ FROM /*28*/ def /*29*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_where() {
    assert_sql!(
        stmt("select * from dual where 1 = 1"),
        debug("SELECT * FROM {dual} WHERE {1 = 1}"));
    assert_sql!(
        stmt("select * from dual where 1 = 1 and 2 = 2"),
        debug("SELECT * FROM {dual} WHERE {{1 = 1} AND {2 = 2}}"));
    assert_sql!(
        stmt("select * from dual where 1 <> 1 or 2 = 2 AND 3 = 3"),
        debug("SELECT * FROM {dual} WHERE {{1 <> 1} OR {{2 = 2} AND {3 = 3}}}"));
    assert_sql!(
        stmt("select * from dual where (((1 <> 1) or (2 = 2)) AND 3 = 3)"),
        debug("SELECT * FROM {dual} WHERE ({({({1 <> 1}) OR ({2 = 2})}) AND {3 = 3}})"));

    assert_sql!(
        stmt("select * from dual /*1*/ where /*2*/ 1 /*3*/ = /*4*/ 1 /*5*/"),
        with_meta("SELECT * FROM dual /*1*/ WHERE /*2*/ 1 /*3*/ = /*4*/ 1 /*5*/"));
    assert_sql!(
        stmt("select * from dual /*1*/ where /*2*/ 1 /*3*/ = /*4*/ 1 /*5*/ and /*6*/ 2 /*7*/ = /*8*/ 2 /*9*/"),
        with_meta("SELECT * FROM dual /*1*/ WHERE /*2*/ 1 /*3*/ = /*4*/ 1 /*5*/ AND /*6*/ 2 /*7*/ = /*8*/ 2 /*9*/"));
    assert_sql!(
        stmt("select * from dual where ((( /*x*/ 1 <> /*y*/ 1) /*z*/ or /*a*/ (2 = 2) /*b*/) AND 3 = 3)"),
        with_meta("SELECT * FROM dual WHERE ((( /*x*/ 1 <> /*y*/ 1) /*z*/ OR /*a*/ (2 = 2) /*b*/ ) AND 3 = 3)"));
}

#[rustfmt::skip]
#[test]
fn test_order_by() {
    assert_sql!(
        stmt("select 'a', 'b', 'c' from dual order siblings by 1"),
        debug("SELECT 'a', 'b', 'c' FROM {dual} {ORDER SIBLINGS BY 1}"));

    assert_sql!(
        stmt("select 'a', 'b', 'c' from dual order by 1, 2, 3"),
        debug("SELECT 'a', 'b', 'c' FROM {dual} {ORDER BY 1, 2, 3}"));
    assert_sql!(
        stmt("select 'a', 'b', 'c' from dual where 1 = 1 order by 1, 2, 3"),
        debug("SELECT 'a', 'b', 'c' FROM {dual} WHERE {1 = 1} {ORDER BY 1, 2, 3}"));
    assert_sql!(
        stmt("select * from dual order by 0, 1 ASC, 2 DESC, 3 NULLS FIRST, 4 DESC NULLS LAST"),
        debug("SELECT * FROM {dual} {ORDER BY 0, {1 ASC}, {2 DESC}, {3 NULLS FIRST}, {4 DESC NULLS LAST}}"));

    assert_sql!(
        stmt("select * from dual order by 0, 1 ASC, 2 DESC, 3 NULLS FIRST, 4 DESC NULLS LAST"),
        "SELECT * FROM dual ORDER BY 0, 1 ASC, 2 DESC, 3 NULLS FIRST, 4 DESC NULLS LAST");
    assert_sql!(
        stmt("\
select * from dual \
  /*1*/ order /*2*/ by /*3*/ 0 /*4*/, \
  /*5a*/ 1 /*5b*/ ASC /*6*/, \
  /*7*/ 2 /*8*/ DESC /*9*/, \
  /*a*/ 3 /*b*/ NULLS /*c*/ FIRST /*d*/, \
  /*e*/ 4 /*f*/ DESC /*g*/ NULLS /*h*/ LAST /*i*/"),
        with_meta("SELECT * FROM dual \
  /*1*/ ORDER /*2*/ BY \
  /*3*/ 0 /*4*/ , \
  /*5a*/ 1 /*5b*/ ASC /*6*/ , \
  /*7*/ 2 /*8*/ DESC /*9*/ , \
  /*a*/ 3 /*b*/ NULLS /*c*/ FIRST /*d*/ , \
  /*e*/ 4 /*f*/ DESC /*g*/ NULLS /*h*/ LAST /*i*/"));
}

#[rustfmt::skip]
#[test]
fn test_joins_inner() {
    // ~ a single inner join on a table with alias
    assert_sql!(
        stmt("select f.id, b.name from foo f inner join bar b on f.id = b.id"),
        debug("SELECT {f.id}, {b.name} FROM {{foo} f} {INNER JOIN {{bar} b} ON {{f.id} = {b.id}}}"));
    assert_sql!(
        stmt("select foo.id, bar.name from foo inner join bar on (1 = 1)"),
        debug("SELECT {foo.id}, {bar.name} FROM {foo} {INNER JOIN {bar} ON ({1 = 1})}"));
    // ~ a single inner join on a table _without_ alias
    assert_sql!(
        stmt("select foo.id, bar.name from foo join bar on (1 = 1)"),
        debug("SELECT {foo.id}, {bar.name} FROM {foo} {JOIN {bar} ON ({1 = 1})}"));
    // ~ using; no alias
    assert_sql!(
        stmt("select * from foo join bar using  (id, name)"),
        debug("SELECT * FROM {foo} {JOIN {bar} USING (id, name)}"));
    // ~ using; where using itself is an alias
    assert_sql!(
        stmt("select * from foo using join bar using using (id, name)"),
        debug("SELECT * FROM {{foo} using} {JOIN {{bar} using} USING (id, name)}"));

    // ~ no need to support `... join bar on on 1 = 1`; oracle fails on that as well

    assert_sql!(
        stmt("select left.a from (select 1 as a from dual) inner"),
        debug("SELECT {left.a} FROM {(SELECT {1 AS a} FROM {dual}) inner}"));

    assert_sql!(
        stmt("select join.a from (select 1 as a from dual) join where 1=1"),
        debug("SELECT {join.a} FROM {(SELECT {1 AS a} FROM {dual}) join} WHERE {1 = 1}"));

    assert_sql!(
        stmt("select join.a from (select 1 as a from dual) join, dual x"),
        debug("SELECT {join.a} FROM {(SELECT {1 AS a} FROM {dual}) join}, {{dual} x}"));

    // ~ the db will complain about an unknown "inner" alias of course, but it shall parse
    assert_sql!(
        stmt("select inner.a from (select 1 as a from dual) inner join (select 2 as a from dual) on 1=1"),
        debug("SELECT {inner.a} FROM (SELECT {1 AS a} FROM {dual}) {INNER JOIN (SELECT {2 AS a} FROM {dual}) ON {1 = 1}}"));

    // ~ cross join; with alias
    assert_sql!(
        stmt("SELECT tx.column, t2.column FROM t1 tx CROSS JOIN t2"),
        debug("SELECT {tx.column}, {t2.column} FROM {{t1} tx} {CROSS JOIN {t2}}"));
    // ~ cross join; without alias
    assert_sql!(
        stmt("SELECT t1.column, t2.column FROM t1 CROSS JOIN t2"),
        debug("SELECT {t1.column}, {t2.column} FROM {t1} {CROSS JOIN {t2}}"));

    // ~ natural join; with aliases
    assert_sql!(
        stmt("select * from foo_t t1 natural inner join foo_t t2"),
        debug("SELECT * FROM {{foo_t} t1} {NATURAL INNER JOIN {{foo_t} t2}}"));

    // ~ natural join; with no aliases
    assert_sql!(
        stmt("select * from foo_t natural natural inner join foo_t"),
        debug("SELECT * FROM {{foo_t} natural} {NATURAL INNER JOIN {foo_t}}"));
    // ~ natural join; with aliases and no inner keyword
    assert_sql!(
        stmt("select * from foo_t t1 natural join foo_t t2"),
        debug("SELECT * FROM {{foo_t} t1} {NATURAL JOIN {{foo_t} t2}}")
    );
    // ~ natural join; with no aliases and no inner keyword
    assert_sql!(
        stmt("select * from foo_t natural natural join foo_t"),
        debug("SELECT * FROM {{foo_t} natural} {NATURAL JOIN {foo_t}}")
    );
    assert_sql!(
        stmt("select * from t0 natural join t1 natural join t2"),
        debug("SELECT * FROM {t0} {NATURAL JOIN {t1}} {NATURAL JOIN {t2}}"));

    // ~ multiple (inner) joins without aliases
    assert_sql!(
        stmt("select * from foo join bar on 1 = 1 inner join quux on 2 = 2"),
        debug("SELECT * FROM {foo} {JOIN {bar} ON {1 = 1}} {INNER JOIN {quux} ON {2 = 2}}")
    );
}

#[rustfmt::skip]
#[test]
fn test_joins_inner_with_comments() {
    // inner join .. on
    assert_sql!(
        stmt("select f.id, b.name from foo f /*1*/ inner /*2*/ join /*3*/ bar /*4*/ b /*5*/ on /*6*/ f.id = b.id /*7*/"),
        with_meta("SELECT f.id, b.name FROM foo f /*1*/ INNER /*2*/ JOIN /*3*/ bar /*4*/ b /*5*/ ON /*6*/ f.id = b.id /*7*/"));

    // join .. on
    assert_sql!(
        stmt("select * from foo f /*1*/ /*2*/ join /*3*/ bar /*4*/ b /*5*/ on /*6*/ f.id = b.id /*7*/"),
        with_meta("SELECT * FROM foo f /*1*/ /*2*/ JOIN /*3*/ bar /*4*/ b /*5*/ ON /*6*/ f.id = b.id /*7*/"));
    // ~ same as above, but without aliases
    assert_sql!(
        stmt("select * from foo /*1*/ /*2*/ join /*3*/ bar /*4*//*5*/ on /*6*/ foo.id = bar.id /*7*/"),
        with_meta("SELECT * FROM foo /*1*/ /*2*/ JOIN /*3*/ bar /*4*/ /*5*/ ON /*6*/ foo.id = bar.id /*7*/"));

    // join .. using
    assert_sql!(
        stmt("select * from foo join bar /*1*/ using /*2*/ (/*3*/ id /*4*/ , /*5*/ name /*6*/) /*7*/"),
        with_meta("SELECT * FROM foo JOIN bar /*1*/ USING /*2*/ ( /*3*/ id /*4*/ , /*5*/ name /*6*/ ) /*7*/"));

    // cross join
    assert_sql!(
        stmt("select * from foo /*1*/ cross /*2*/ join /*3*/ bar /*4*/ where 1 = 1"),
        with_meta("SELECT * FROM foo /*1*/ CROSS /*2*/ JOIN /*3*/ bar /*4*/ WHERE 1 = 1"));
    assert_sql!(
        stmt("select * from dual cross /*1*/ cross /*2*/ join /*3*/ dual /*4*/ where 1 = 1"),
        with_meta("SELECT * FROM dual cross /*1*/ CROSS /*2*/ JOIN /*3*/ dual /*4*/ WHERE 1 = 1"));

    // natural inner join
    assert_sql!(
        stmt("select * from foo /*1*/ natural /*2*/ inner /*3*/ join /*4*/ bar /*5*/ where 1 = 1"),
        with_meta("SELECT * FROM foo /*1*/ NATURAL /*2*/ INNER /*3*/ JOIN /*4*/ bar /*5*/ WHERE 1 = 1"));
    // natural join
    assert_sql!(
        stmt("select * from dual /*0*/ natural /*1*/ natural /*2*/ /*3*/ join /*4*/ dual /*5*/ where 1 = 1"),
        with_meta("SELECT * FROM dual /*0*/ natural /*1*/ NATURAL /*2*/ /*3*/ JOIN /*4*/ dual /*5*/ WHERE 1 = 1"));
    // some weird naming
    assert_sql!(
        stmt("select * from dual inner /*1*/ natural /*2*/ inner /*3*/ join /*4*/ dual /*5*/ where 1 = 1"),
        with_meta("SELECT * FROM dual inner /*1*/ NATURAL /*2*/ INNER /*3*/ JOIN /*4*/ dual /*5*/ WHERE 1 = 1"));
}

#[rustfmt::skip]
#[test]
fn test_joins_outer() {
    // ~ outer join on a table with alias
    assert_sql!(
        stmt("select f.id, b.name from foo f left outer join bar b on f.id = b.id"),
        debug("SELECT {f.id}, {b.name} FROM {{foo} f} {LEFT OUTER JOIN {{bar} b} ON {{f.id} = {b.id}}}"));
    assert_sql!(
        stmt("select f.id, b.name from foo f left join bar b on f.id = b.id"),
        debug("SELECT {f.id}, {b.name} FROM {{foo} f} {LEFT JOIN {{bar} b} ON {{f.id} = {b.id}}}"));
    assert_sql!(
        stmt("select f.id, b.name from foo f left join bar b"),
        debug("SELECT {f.id}, {b.name} FROM {{foo} f} {LEFT JOIN {{bar} b}}"));
    // ~ natural outer join; with alias
    assert_sql!(
        stmt("select f.id, b.name from foo f natural left join bar b"),
        debug("SELECT {f.id}, {b.name} FROM {{foo} f} {NATURAL LEFT JOIN {{bar} b}}"));

    // ~ outer join on a table without alias
    assert_sql!(
        stmt("select f.id, b.name from foo left outer join bar on f.id = b.id"),
        debug("SELECT {f.id}, {b.name} FROM {foo} {LEFT OUTER JOIN {bar} ON {{f.id} = {b.id}}}"));
    // ~ natural outer join; without alias
    assert_sql!(
        stmt("select f.id, b.name from foo natural left join bar"),
        debug("SELECT {f.id}, {b.name} FROM {foo} {NATURAL LEFT JOIN {bar}}"));
}

#[rustfmt::skip]
#[test]
fn test_joins_outer_with_comments() {
    // ~ outer join on a table with alias
    assert_sql!(
        stmt("select * from foo f /*1*/ left /*2*/ outer /*3*/ join /*4*/ bar b /*5*/ on /*6*/ 'a' <> 'b' /*7*/"),
        with_meta("SELECT * FROM foo f /*1*/ LEFT /*2*/ OUTER /*3*/ JOIN /*4*/ bar b /*5*/ ON /*6*/ 'a' <> 'b' /*7*/"));
    assert_sql!(
        stmt("select * from foo f /*1*/ left /*2*/ /*3*/ join /*4*/ bar b /*5*/ on /*6*/ 'a' <> 'b' /*7*/"),
        with_meta("SELECT * FROM foo f /*1*/ LEFT /*2*/ /*3*/ JOIN /*4*/ bar b /*5*/ ON /*6*/ 'a' <> 'b' /*7*/"));
    assert_sql!(
        stmt("select * from foo f /*1a*/ natural /*1b*/ left /*2*/ outer /*3*/ join /*4*/ bar b /*5*/"),
        with_meta("SELECT * FROM foo f /*1a*/ NATURAL /*1b*/ LEFT /*2*/ OUTER /*3*/ JOIN /*4*/ bar b /*5*/"));
}

#[rustfmt::skip]
#[test]
fn test_joins_partitioned_outer() {
    // on the left; with alias
    assert_sql!(
        stmt("select * from foo f partition by (x, y) left outer join bar b on (f.id = b.id)"),
        debug("SELECT * FROM {{foo} f} {{PARTITION BY ({x}, {y})} LEFT OUTER JOIN {{bar} b} ON ({{f.id} = {b.id}})}"));
    assert_sql!(
        stmt("select a.*, partition.* from dual a left join dual partition on 1 = 1"),
        debug("SELECT {a.*}, {partition.*} FROM {{dual} a} {LEFT JOIN {{dual} partition} ON {1 = 1}}"));

    // on the right; with alias
    assert_sql!(
        stmt("select * from foo f left outer join bar b partition by (x, y) on (f.id = b.id)"),
        debug("SELECT * FROM {{foo} f} {LEFT OUTER JOIN {{bar} b} {PARTITION BY ({x}, {y})} ON ({{f.id} = {b.id}})}"));

    // full partition outer join
    assert_sql!(
        stmt("select * from dual partition by (a) full join dual on 1 = 1"),
        "FULL PARTITIONED OUTER JOIN is not supported (ORA-39754) [line: 1, column: 42]");

    assert_sql!(
        stmt("select * from dual full join dual partition by (a) on 1 = 1"),
        "FULL PARTITIONED OUTER JOIN is not supported (ORA-39754) [line: 1, column: 25]");
}

#[rustfmt::skip]
#[test]
fn test_joins_partitioned_outer_with_comments() {
    assert_sql!(
        stmt("\
select * from foo f \
 /*1*/ partition /*2*/ by /*3*/ (/*4a*/ x /*4b*/, /*4c*/ y /*4d*/) /*5*/ \
 left /*6*/ join /*7*/ bar b on (f.id = b.id)"),
        with_meta("\
SELECT * FROM foo f /*1*/ PARTITION /*2*/ BY /*3*/ ( /*4a*/ x /*4b*/ , /*4c*/ y /*4d*/ ) /*5*/ \
LEFT /*6*/ JOIN /*7*/ bar b ON (f.id = b.id)"));

    assert_sql!(
        stmt("select * from foo f \
/*a*/ left /*b*/ outer /*c*/ join /*d*/ bar b \
/*e*/ partition /*f1*/ by /*f2*/ (/*g*/x/*h*/,/*i*/y/*j*/)/*k*/ \
on /*l*/ (f.id = b.id)"),
        with_meta("\
SELECT * FROM foo f /*a*/ LEFT /*b*/ OUTER /*c*/ JOIN /*d*/ bar b /*e*/ \
PARTITION /*f1*/ BY /*f2*/ ( /*g*/ x /*h*/ , /*i*/ y /*j*/ ) /*k*/ \
ON /*l*/ (f.id = b.id)"));
}

#[rustfmt::skip]
#[test]
fn test_joins_apply() {
    assert_sql!(
        stmt("select * from dual t cross apply foo"),
        debug("SELECT * FROM {{dual} t} {CROSS APPLY {foo}}"));
    assert_sql!(
        stmt("select * from dual t outer apply (select * from dual) x"),
        debug("SELECT * FROM {{dual} t} {OUTER APPLY {(SELECT * FROM {dual}) x}}"));
    assert_sql!(
        stmt("select * from dual t outer apply return_table left join dual y on 1 = 1"),
        debug("SELECT * FROM {{dual} t} {OUTER APPLY {return_table}} {LEFT JOIN {{dual} y} ON {1 = 1}}"));

    // XXX with column-expression, e.g. function call
}

#[rustfmt::skip]
#[test]
fn test_joins_apply_with_comments() {
    assert_sql!(
        stmt("select * from dual t /*1*/ cross /*2*/ apply /*3*/ foo /*4*/"),
        with_meta("SELECT * FROM dual t /*1*/ CROSS /*2*/ APPLY /*3*/ foo /*4*/"));
    assert_sql!(
        stmt("select * from dual t /*1*/ outer /*2*/ apply /*3*/ (select * from dual) x"),
        with_meta("SELECT * FROM dual t /*1*/ OUTER /*2*/ APPLY /*3*/ (SELECT * FROM dual) x"));
}

#[rustfmt::skip]
#[test]
fn test_group_by() {
    // ~ empty ()
    assert_sql!(
        stmt("select count(42) from foo group by ()"),
        debug("SELECT {{count}(42)} FROM {foo} {GROUP BY ()}"));
    // (one)
    assert_sql!(
        stmt("select f.id, count(42) from foo f group by (f.id)"),
        debug("SELECT {f.id}, {{count}(42)} FROM {{foo} f} {GROUP BY ({f.id})}"));
    // (one, two)
    assert_sql!(
        stmt("select f.id, f.name, count(42) from foo f group by (f.id, f.name)"),
        debug("SELECT {f.id}, {f.name}, {{count}(42)} FROM {{foo} f} {GROUP BY ({f.id}, {f.name})}"));
    // no parens: one, foo
    assert_sql!(
        stmt("select f.id, f.name, count(42) from foo f group by f.id, f.name"),
        debug("SELECT {f.id}, {f.name}, {{count}(42)} FROM {{foo} f} {GROUP BY {f.id}, {f.name}}"));

    // ~ with a HAVING clause
    assert_sql!(
        stmt("select f.id, f.name, count(42) from foo f group by f.id, f.name having count(4) > 2 and 1 = 1"),
        debug("SELECT {f.id}, {f.name}, {{count}(42)} \
                 FROM {{foo} f} \
               {GROUP BY {f.id}, {f.name} \
                HAVING {{{{count}(4)} > 2} AND {1 = 1}}\
               }"));
}

#[rustfmt::skip]
#[test]
fn test_group_by_with_comments() {
    // ~ empty ()
    assert_sql!(
        stmt("select count(42) from foo /*1*/ group /*2*/ by /*3*/ (/*4a*/ /*4b*/) /*5*/"),
        with_meta("SELECT count(42) FROM foo /*1*/ GROUP /*2*/ BY /*3*/ ( /*4a*/ /*4b*/ ) /*5*/"));
    // (one)
    assert_sql!(
        stmt("select f.id, count(42) from foo f group by /*1*/ (/*2*/ f.id /*3*/) /*4*/"),
        with_meta("SELECT f.id, count(42) FROM foo f GROUP BY /*1*/ ( /*2*/ f.id /*3*/ ) /*4*/"));
    // (one, two)
    assert_sql!(
        stmt("select f.id, f.name, count(42) from foo f group by (/*0*/f.id /*1*/, /*2*/ f.name /*3*/)"),
        with_meta("SELECT f.id, f.name, count(42) FROM foo f GROUP BY ( /*0*/ f.id /*1*/ , /*2*/ f.name /*3*/ )"));
    // no parens: one, foo
    assert_sql!(
        stmt("select f.id, f.name, count(42) from foo f group by /*1*/ f.id /*2*/, /*3*/ f.name /*4*/"),
        with_meta("SELECT f.id, f.name, count(42) FROM foo f GROUP BY /*1*/ f.id /*2*/ , /*3*/ f.name /*4*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_is_null_conditions() {
    assert_sql!(
        stmt("select * from customers where NVL(city, 'foo') is /*1*/ null /*2*/ and age > 99"),
        debug("SELECT * FROM {customers} WHERE {{{{NVL}({city}, 'foo')} IS NULL} AND {{age} > 99}}"));
    assert_sql!(
        stmt("select * from customers where NVL(city, 'foo') is /*1*/ null /*2*/ and age > 99"),
        with_meta("SELECT * FROM customers WHERE NVL(city, 'foo') IS /*1*/ NULL /*2*/ AND age > 99"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_is_float_condition() {
    assert_sql!(
        stmt("select * from t where NOT x is NOT NAN order by y"),
        debug("SELECT * FROM {t} WHERE {NOT {{x} IS NOT NAN}} {ORDER BY {y}}"));
}

#[rustfmt::skip]
#[test]
fn test_select_exists_condition() {
    assert_sql!(
        stmt("select * from dual where exists (select 1 from dual)"),
        debug("SELECT * FROM {dual} WHERE {EXISTS (SELECT 1 FROM {dual})}"));
    assert_sql!(
        stmt("select * from dual where 1=1 and exists (select 1 from dual)"),
        debug("SELECT * FROM {dual} WHERE {{1 = 1} AND {EXISTS (SELECT 1 FROM {dual})}}"));
    assert_sql!(
        stmt("select * from dual where (((exists (select 1 from dual))))"),
        debug("SELECT * FROM {dual} WHERE ((({EXISTS (SELECT 1 FROM {dual})})))"));

    assert_sql!(
        stmt("select * from dual where /*1*/ exists /*2*/ (/*3*/ select 1 from dual /*4*/) /*5*/"),
        with_meta("SELECT * FROM dual WHERE /*1*/ EXISTS /*2*/ ( /*3*/ SELECT 1 FROM dual /*4*/ ) /*5*/"));

}

#[rustfmt::skip]
#[test]
fn test_select_with_like_condition() {
    assert_sql!(
        stmt("select * from t where t.ident like 'p$%%' escape '$' and count(t.refs) > 1"),
        "SELECT * FROM t WHERE t.ident LIKE 'p$%%' ESCAPE '$' AND count(t.refs) > 1");
}

#[rustfmt::skip]
#[test]
fn test_select_with_regexp_like() {
    assert_sql!(
        stmt(r"select last_name from employees where regexp_like (last_name, '([aeiou])\1') order by last_name;"),
        r"SELECT last_name FROM employees WHERE REGEXP_LIKE(last_name, '([aeiou])\1') ORDER BY last_name;");
    assert_sql!(
        stmt(r"select last_name from employees where regexp_like (last_name, '([aeiou])\1', 'i') order by last_name;"),
        r"SELECT last_name FROM employees WHERE REGEXP_LIKE(last_name, '([aeiou])\1', 'i') ORDER BY last_name;");

    assert_sql!(
        stmt(r#"SELECT bar_t.*  FROM bar_t WHERE "REGEXP_LIKE" is not null and REGEXP_LIKE(REGEXP_LIKE, '(^[pk])', 'i') ORDER BY regexp_like"#),
        debug(r#"SELECT {bar_t.*} FROM {bar_t} WHERE {{{"REGEXP_LIKE"} IS NOT NULL} AND {REGEXP_LIKE({REGEXP_LIKE}, '(^[pk])', 'i')}} {ORDER BY {regexp_like}}"#));

    assert_sql!(
        stmt(r"select last_name from employees where /*1*/ regexp_like /*2*/ (/*3*/ last_name /*4*/, /*5*/ '([aeiou])\1' /*6*/, /*7*/ 'i' /*8*/) /*9*/ order by last_name;"),
        with_meta("SELECT last_name FROM employees \
                     WHERE /*1*/ REGEXP_LIKE /*2*/ ( /*3*/ last_name /*4*/ , /*5*/ '([aeiou])\\1' /*6*/ , /*7*/ 'i' /*8*/ ) /*9*/ \
                     ORDER BY last_name;"));

}

#[rustfmt::skip]
#[test]
fn test_select_with_case_exprs_in_projections_0() {
    // ~ "simple" case
    assert_sql!(
        stmt(r"select cust_last_name,
                      case credit_limit when 100 then 'Low'
                      when 5000 then 'High'
                      else 'Medium'
                       end
                        as credit
                 from customers
                order by cust_last_name, credit;"),
        "SELECT cust_last_name, \
                 CASE credit_limit \
                  WHEN 100 THEN 'Low' \
                  WHEN 5000 THEN 'High' \
                  ELSE 'Medium' \
                   END AS credit \
          FROM customers \
         ORDER BY cust_last_name, credit;");
    assert_sql!(
        stmt("select case +1 WHEN 1 then 10 end from (select 1 case from dual) case;"),
        debug("SELECT {CASE {+1} {WHEN 1 THEN 10} END} FROM {(SELECT {1 case} FROM {dual}) case};"));
    // ~ "searched" case
    assert_sql!(
        stmt(r#"select AVG(case when e.salary > 2000 then e.salary else 2000 end) "Average Salary"
                  from employees e;"#),
        "SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) \"Average Salary\" \
           FROM employees e;");
    assert_sql!(
        stmt("select case when 1=1 then 10 end from (select 1 case from dual) case;"),
        debug("SELECT {CASE {WHEN {1 = 1} THEN 10} END} FROM {(SELECT {1 case} FROM {dual}) case};"));

    assert_sql!(
        stmt("SELECT REGID, \
           SUM(CASE WHEN CONTROLLING_STATE = :activeState AND IS_CHECKOUT <> :checkout THEN 1 ELSE 0 END), \
           SUM(CASE WHEN CONTROLLING_STATE = :resaleState AND IS_CHECKOUT <> :checkout THEN 1 ELSE 0 END), \
           SUM(CASE WHEN CONTROLLING_STATE = :activeState AND IS_CHECKOUT = :checkout THEN 1 ELSE 0 END) \
           FROM data GROUP BY regid"),
        "SELECT REGID, \
           SUM(CASE WHEN CONTROLLING_STATE = :activeState \
                     AND IS_CHECKOUT <> :checkout \
                    THEN 1 \
                    ELSE 0 \
                END), \
           SUM(CASE WHEN CONTROLLING_STATE = :resaleState \
                     AND IS_CHECKOUT <> :checkout \
                    THEN 1 \
                    ELSE 0 \
                END), \
           SUM(CASE WHEN CONTROLLING_STATE = :activeState \
                     AND IS_CHECKOUT = :checkout \
                    THEN 1 \
                    ELSE 0 \
                END) \
      FROM data \
      GROUP BY regid"
    );

    // Oracle chokes at `select case case when 1 then 10 end from (select 1 case from dual) case;` ...
    // ... but is fine with `select case case.case when 1 then 10 end from (select 1 case from dual) case;`
    // ... and so we do.
    assert_sql!(
        stmt("select case case.case when 1 then 10 end from (select 1 case from dual) case"),
        debug("SELECT {CASE {case.case} {WHEN 1 THEN 10} END} FROM {(SELECT {1 case} FROM {dual}) case}"));

    // ~ comments
    assert_sql!(
        stmt("select /*1*/ case /*2*/ foo /*x*/ + /*y*/ 3 /*3*/ when /*4*/ 1 /*5*/ then /*6*/ 10 /*7*/ end /*8*/ from dual;"),
        debug(with_meta("SELECT /*1*/ {CASE /*2*/ {{foo /*x*/ } + /*y*/ 3 /*3*/ } {WHEN /*4*/ 1 /*5*/ THEN /*6*/ 10 /*7*/ } END /*8*/ } FROM {dual};")));
}

#[test]
fn test_select_with_compound_case_exprs() {
    // ~ in projection
    assert_sql!(
        stmt("SELECT CASE WHEN foo = 0 THEN 1 ELSE 2 END - 3 X from dual"),
        debug("SELECT {{{CASE {WHEN {{foo} = 0} THEN 1} {ELSE 2} END} - 3} X} FROM {dual}")
    );
    // ~ in condition
    assert_sql!(
        stmt("SELECT 1 from dual where CASE WHEN foo = 0 THEN 1 ELSE 2 END - 3 = 4"),
        debug(
            "SELECT 1 FROM {dual} WHERE {{{CASE {WHEN {{foo} = 0} THEN 1} {ELSE 2} END} - 3} = 4}"
        )
    );
}

#[rustfmt::skip]
#[test]
fn test_select_with_case_as_identifier() {
    // ~ the `case` word as a normal identifier
    assert_sql!(
        stmt("select foo_t.name case from foo_t;"),
        debug("SELECT {{foo_t.name} case} FROM {foo_t};"));
    assert_sql!(
        stmt("select case.name case from foo_t case;"),
        debug("SELECT {{case.name} case} FROM {{foo_t} case};"));
    assert_sql!(
        stmt("select case.name case from foo_t case where case.name like 'p%';"),
        debug("SELECT {{case.name} case} FROM {{foo_t} case} WHERE {{case.name} LIKE 'p%'};"));
    assert_sql!(
        stmt("select case foo from (select 1 case from dual) case;"),
        debug("SELECT {{case} foo} FROM {(SELECT {1 case} FROM {dual}) case};"));
    assert_sql!(
        stmt("select case.* from (select 1, 2 from dual) case;"),
        debug("SELECT {case.*} FROM {(SELECT 1, 2 FROM {dual}) case};"));
    assert_sql!(stmt("select case * 1 when from (select 1 case from dual) case;"),
        debug("SELECT {{{case} * 1} when} FROM {(SELECT {1 case} FROM {dual}) case};"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_case_function() {
    // XXX
    // ~ if you created a function named `case`, oracle will accept the following statement:
    // assert_sql!(
    //     stmt("select case(12) WHEN 12 then 1 else 0 end from dual"),
    //     debug("SELECT {{case}(12)} {WHEN 12 then 1} {else 0} end} FROM {dual}"));

    // ~ but oracle will fail on this (even if the defined function does accept two args):
    assert_sql!(
        stmt("select CASE(12, 12) WHEN 12 then 1 else 0 end from dual"),
        // ~ the error should actually point to the "WHEN"
        debug("Unexpected `12`; expected FROM [line: 1, column: 26]"));

    // ~ these are fine on oracle
    assert_sql!(
        stmt("select CASE(12) from dual"),
        debug("SELECT {{CASE}(12)} FROM {dual}"));
    assert_sql!(
        stmt("select CASE((12), 23) from dual"),
        debug("SELECT {{CASE}((12), 23)} FROM {dual}"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_case_exprs_in_conditions() {
    assert_sql!(
        stmt("select * from dual where case 1 when 1 then 0 end = 1;"),
        debug("SELECT * FROM {dual} WHERE {{CASE 1 {WHEN 1 THEN 0} END} = 1};"));
    assert_sql!(
        stmt("select * from dual where (case 1 when 1 then 0 end) = 1;"),
        debug("SELECT * FROM {dual} WHERE {({CASE 1 {WHEN 1 THEN 0} END}) = 1};"));

    assert_sql!(
        stmt("select * from (select 1 first, 2 second from dual) t where (((case second when 1 then 0 else 1 end))) = 1;"),
        debug("SELECT * FROM {(SELECT {1 first}, {2 second} FROM {dual}) t} \
                WHERE {((({CASE {second} {WHEN 1 THEN 0} {ELSE 1} END}))) = 1};"));
    assert_sql!(
        stmt("select * from (select 1 first, 2 second from dual) t where 1 = (case second when 1 then 0 else 1 end);"),
        debug("SELECT * FROM {(SELECT {1 first}, {2 second} FROM {dual}) t} \
                WHERE {1 = ({CASE {second} {WHEN 1 THEN 0} {ELSE 1} END})};"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_row_limit_offset() {
    assert_sql!(
        stmt("select * from dual offset 1 rows"),
        debug("SELECT * FROM {dual} {OFFSET 1 ROWS}"));
    assert_sql!(
        stmt("select t.* from dual t offset 1 rows"),
        debug("SELECT {t.*} FROM {{dual} t} {OFFSET 1 ROWS}"));
    assert_sql!(
        stmt("(select offset.* from dual offset offset 1 row)"),
        debug("(SELECT {offset.*} FROM {{dual} offset} {OFFSET 1 ROW})"));
    assert_sql!(
        stmt("(select offset.* from dual offset) offset 1 row"),
        debug("(SELECT {offset.*} FROM {{dual} offset}) {OFFSET 1 ROW}"));

    // just an alias
    assert_sql!(
        stmt("select * from offset_t offset where 1=1"),
        debug("SELECT * FROM {{offset_t} offset} WHERE {1 = 1}"));
    assert_sql!(
        stmt("select * from offset_t offset order by 1"),
        debug("SELECT * FROM {{offset_t} offset} {ORDER BY 1}"));
    assert_sql!(
        stmt("select offset.* from dual offset"),
        debug("SELECT {offset.*} FROM {{dual} offset}"));

    assert_sql!(
        stmt("select * from offset_t offset order by 1 offset NULL rows"),
        debug("SELECT * FROM {{offset_t} offset} {ORDER BY 1} {OFFSET NULL ROWS}"));

    // ~ comments
    assert_sql!(
        stmt("select * from offset_t /*1*/ offset /*2*/ order /*3*/ by /*4*/ 1 /*5*/ offset /*6*/ NULL /*7*/ rows /*8*/"),
        with_meta("SELECT * FROM offset_t /*1*/ offset /*2*/ ORDER /*3*/ BY /*4*/ 1 /*5*/ OFFSET /*6*/ NULL /*7*/ ROWS /*8*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_row_limit_fetch() {
    // ~ just an alias
    assert_sql!(
        stmt("select * from dual fetch"),
        debug("SELECT * FROM {{dual} fetch}"));

    assert_sql!(
        stmt("select * from dual fetch first 1 rows only"),
        debug("SELECT * FROM {dual} {FETCH FIRST 1 ROWS ONLY}"));
    assert_sql!(
        stmt("select * from dual fetch fetch first 1 row only"),
        debug("SELECT * FROM {{dual} fetch} {FETCH FIRST 1 ROW ONLY}"));
    assert_sql!(
        stmt("select t.* from dual t fetch first 1 rows only"),
        debug("SELECT {t.*} FROM {{dual} t} {FETCH FIRST 1 ROWS ONLY}"));

    assert_sql!(
        stmt("select * from dual fetch next 1 rows only"),
        debug("SELECT * FROM {dual} {FETCH NEXT 1 ROWS ONLY}"));
    assert_sql!(
        stmt("select * from dual fetch next 1 row only"),
        debug("SELECT * FROM {dual} {FETCH NEXT 1 ROW ONLY}"));
    assert_sql!(
        stmt("select * from dual fetch next 1 percent rows only"),
        debug("SELECT * FROM {dual} {FETCH NEXT 1 PERCENT ROWS ONLY}"));
    assert_sql!(
        stmt("select * from dual fetch next 1 rows with ties"),
        debug("SELECT * FROM {dual} {FETCH NEXT 1 ROWS WITH TIES}"));
    assert_sql!(
        stmt("select * from dual fetch next 1 row with ties"),
        debug("SELECT * FROM {dual} {FETCH NEXT 1 ROW WITH TIES}"));

    // ~ comments
    assert_sql!(
        stmt("select * from dual /*1*/ fetch /*2*/ next /*3*/ 1 /*4*/ row /*5*/ with /*6*/ ties /*7*/"),
        with_meta("SELECT * FROM dual /*1*/ FETCH /*2*/ NEXT /*3*/ 1 /*4*/ ROW /*5*/ WITH /*6*/ TIES /*7*/"));
    assert_sql!(
        stmt("select * from dual /*1*/ fetch /*2*/ next /*3*/ 1 /*4*/ row /*5*/ only /*6*/"),
        with_meta("SELECT * FROM dual /*1*/ FETCH /*2*/ NEXT /*3*/ 1 /*4*/ ROW /*5*/ ONLY /*6*/"));
    assert_sql!(
        stmt("select * from dual /*1*/ fetch /*2*/ first /*3*/ 5 + 3 /*4*/ percent /*5*/ rows /*6*/ only /*7*/"),
        with_meta("SELECT * FROM dual /*1*/ FETCH /*2*/ FIRST /*3*/ 5 + 3 /*4*/ PERCENT /*5*/ ROWS /*6*/ ONLY /*7*/"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_row_limit_full() {
    // ~ 'fetch' as an expression, i.e. identifier
    assert_sql!(
        stmt("select * from dual offset fetch rows"),
        debug("SELECT * FROM {dual} {OFFSET {fetch} ROWS}"));

    assert_sql!(
        stmt("select * from dual offset 1 rows fetch first 1 row only"),
        debug("SELECT * FROM {dual} {OFFSET 1 ROWS} {FETCH FIRST 1 ROW ONLY}"));

    assert_sql!(
        stmt("select * from dual fetch offset 1 rows fetch first 1 row only"),
        debug("SELECT * FROM {{dual} fetch} {OFFSET 1 ROWS} {FETCH FIRST 1 ROW ONLY}"));

    // ~ comments
    assert_sql!(
        stmt("select * from dual fetch offset 1 rows /*xxx*/ fetch first 1 row only"),
        with_meta("SELECT * FROM dual fetch OFFSET 1 ROWS /*xxx*/ FETCH FIRST 1 ROW ONLY"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_analytical_functions_0() {
    // ~ merely an alias; for analytics in projections ...
    assert_sql!(
        stmt("select avg(id) over from foo_t a group by a.age"),
        debug("SELECT {{{avg}({id})} over} FROM {{foo_t} a} {GROUP BY {a.age}}"));
    assert_sql!(
        stmt("select (avg(id) over) from foo_t a group by a.age"),
        debug("Unexpected `)`; expected an identifier (window name) or an opening parenthesis (analytic clause) [line: 1, column: 21]"));
    assert_sql!(
        stmt("select avg(id) over * 1 from foo_t a group by a.age"),
        debug("Unexpected `*`; expected FROM [line: 1, column: 21]"));

    // ~ not an alias anymore
    assert_sql!(
        stmt("select count(42) over () from t"),
        debug("SELECT {{count}(42) {OVER ()}} FROM {t}"));
    assert_sql!(
        stmt("select (count(42) over ()) from t"),
        debug("SELECT ({{count}(42) {OVER ()}}) FROM {t}"));
    assert_sql!(
        stmt("select count(42) over () + 1 from t"),
        debug("SELECT {{{count}(42) {OVER ()}} + 1} FROM {t}"));

    // ~ in a case expression
    assert_sql!(
        stmt("select case count(1) over () when 1 then 'x' else 'y' end from dual"),
        debug("SELECT {CASE {{count}(1) {OVER ()}} {WHEN 1 THEN 'x'} {ELSE 'y'} END} FROM {dual}"));

    // ~ ... but not allowed in order_by clauses (a trailing OVER keyword)
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over, \
              foo"),
        debug("Unexpected `,`; expected an identifier (window name) or an opening parenthesis (analytic clause) [line: 1, column: 66]"));

    // ~ window name _not_ in parens
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over quux, \
              foo"),
        debug("SELECT {{avg}({id})} FROM {{foo_t} a} {GROUP BY {a.age}} \
              {ORDER BY {{count}(1) {OVER quux}}, \
                        {foo}}"));
    // ~ spec: empty
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by
              count(1) over (), \
              foo"),
        debug("SELECT {{avg}({id})} FROM {{foo_t} a} {GROUP BY {a.age}} \
              {ORDER BY {{count}(1) {OVER ()}}, \
                        {foo}}"));
    // ~ spec: with partition_by only
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over (partition by age), \
              foo"),
        debug("SELECT {{avg}({id})} FROM {{foo_t} a} {GROUP BY {a.age}} \
              {ORDER BY {{count}(1) {OVER ({PARTITION BY {age}})}}, \
                        {foo}}"));
    // ~ spec: with partition_by only and order_by
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over (partition by age order by x, y, z), \
              foo"),
        debug("SELECT {{avg}({id})} FROM {{foo_t} a} {GROUP BY {a.age}} \
              {ORDER BY {{count}(1) {OVER ({PARTITION BY {age}} {ORDER BY {x}, {y}, {z}})}}, \
                        {foo}}"));
    // ~ spec: with order_by only
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over (order by x, y, z), \
              foo"),
        debug("SELECT {{avg}({id})} FROM {{foo_t} a} {GROUP BY {a.age}} \
              {ORDER BY {{count}(1) {OVER ({ORDER BY {x}, {y}, {z}})}}, \
                        {foo}}"));

    // ~ spec: with window_name only
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over (quux), \
              foo"),
        debug("SELECT {{avg}({id})} FROM {{foo_t} a} {GROUP BY {a.age}} \
              {ORDER BY {{count}(1) {OVER (quux)}}, \
                        {foo}}"));
    // ~ spec: with window_name and order_by
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over (quux order by 1, 2), \
              foo"),
        debug("SELECT {{avg}({id})} FROM {{foo_t} a} {GROUP BY {a.age}} \
              {ORDER BY {{count}(1) {OVER (quux {ORDER BY 1, 2})}}, \
                        {foo}}"));

    // ~ comments
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count/*0*/(1) /*1*/ over /*2*/ quux /*3*/, foo"),
        with_meta("SELECT avg(id) FROM foo_t a GROUP BY a.age \
              ORDER BY count /*0*/ (1) /*1*/ OVER /*2*/ quux /*3*/ , foo"));
    assert_sql!(
        stmt("select avg(id) from foo_t a group by a.age order by \
              count(1) over /*1*/ (/*2*/ partition /*3*/ by /*4*/ age /*5*/ order /*6*/ by /*7*/ x /*8*/, y, z) /*9*/, foo"),
        with_meta(
            "SELECT avg(id) FROM foo_t a GROUP BY a.age \
              ORDER BY count(1) OVER /*1*/ ( /*2*/ PARTITION /*3*/ BY /*4*/ age /*5*/ ORDER /*6*/ BY /*7*/ x /*8*/ , y, z) /*9*/ , foo"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_analytical_functions_not_allowed_in_where() {
    // ~ oracle will actually already complain semantically about the "count"
    // (a group function) being used in the where clause; this something
    // beyond the parser, though.
    assert_sql!(
        stmt("select * from dual where count(1) = 1 over ()"),
        debug("Unexpected 'over'; expected a semicolon or end-of-file [line: 1, column: 39]"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_analytical_functions_window_frames() {
    // ~ betweens -------------------------------------------------------------
    assert_sql!(
        stmt("select count(42) over (order by x rows between 1 following and 1 preceding) from dual"),
        "Unexpected 'preceding'; expected the FOLLOWING keyword [line: 1, column: 66]");
    assert_sql!(
        stmt("select count(42) over (order by x rows between unbounded following and 1 preceding) from dual"),
        "Unexpected 'following'; expected the PRECEDING keyword [line: 1, column: 58]");
    assert_sql!(
        stmt("select count(42) over (order by x rows between unbounded following and 1 preceding) from dual"),
        "Unexpected 'following'; expected the PRECEDING keyword [line: 1, column: 58]");
    assert_sql!(
        stmt("select count(42) over (order by x rows between unbounded preceding and 1 preceding) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows between 1 preceding and 1 preceding) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows between unbounded preceding and unbounded following) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows between unbounded preceding and unbounded preceding) from dual"),
        "Unexpected 'preceding'; expected the FOLLOWING keyword [line: 1, column: 82]");
    assert_sql!(
        stmt("select count(42) over (order by x rows between 1 preceding and unbounded following) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows between 1 preceding and unbounded preceding) from dual"),
        "Unexpected 'preceding'; expected the FOLLOWING keyword [line: 1, column: 74]");
    assert_sql!(
        stmt("select count(42) over (order by x rows between 1 following and unbounded following) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows between 1 following and 1 following) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING})}} FROM {dual}"));

    assert_sql!(
        stmt("select count(42) over (order by x rows between current row and 1 preceding) from dual"),
        "Unexpected 'preceding'; expected the FOLLOWING keyword [line: 1, column: 66]");
    assert_sql!(
        stmt("select count(42) over (order by x rows between current row and unbounded following) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows between current row and current row) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN CURRENT ROW AND CURRENT ROW})}} FROM {dual}"));

    // ~ start only -----------------------------------------------------------
    assert_sql!(
        stmt("select count(42) over (order by x rows unbounded following) from dual"),
        debug("Unexpected 'following'; expected the PRECEDING keyword [line: 1, column: 50]"));
    assert_sql!(
        stmt("select count(42) over (order by x rows 1 following) from dual"),
        debug("Unexpected 'following'; expected the PRECEDING keyword [line: 1, column: 42]"));
    assert_sql!(
        stmt("select count(42) over (order by x rows unbounded preceding) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS UNBOUNDED PRECEDING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x rows current row) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS CURRENT ROW})}} FROM {dual}"));

    assert_sql!(
        stmt("select count(42) over (order by x range current row) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {RANGE CURRENT ROW})}} FROM {dual}"));
    assert_sql!(
        stmt("select count(42) over (order by x groups current row) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {GROUPS CURRENT ROW})}} FROM {dual}"));

    // ~ comments -------------------------------------------------------------
    assert_sql!(
        stmt("select count(42) over (order by x /*1*/ rows /*2*/ between /*3*/ 1 /*4*/ following /*5*/ and /*6*/ unbounded /*7*/ following /*8*/) from dual"),
        with_meta("SELECT count(42) OVER (ORDER BY x /*1*/ ROWS /*2*/ BETWEEN /*3*/ 1 /*4*/ FOLLOWING /*5*/ AND /*6*/ UNBOUNDED /*7*/ FOLLOWING /*8*/ ) FROM dual"));
    assert_sql!(
        stmt("select count(42) over (order by x rows between /*1*/current/*2*/ row/*3*/ and current /*7*/ row /*8*/) from dual"),
        with_meta("SELECT count(42) OVER (ORDER BY x ROWS BETWEEN /*1*/ CURRENT /*2*/ ROW /*3*/ AND CURRENT /*7*/ ROW /*8*/ ) FROM dual"));
}

#[rustfmt::skip]
#[test]
fn test_select_with_analytical_functions_window_frames_excludes() {
    assert_sql!(
        stmt("select count(42) over (order by x rows between 123 preceding and unbounded following exclude current row) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS BETWEEN 123 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW})}} FROM {dual}"));

    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding exclude current row) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING EXCLUDE CURRENT ROW})}} FROM {dual}"));
    // comments
    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding /*a*/ exclude /*b*/ current /*c*/ row /*d*/ ) from dual"),
        debug(with_meta("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING /*a*/ EXCLUDE /*b*/ CURRENT /*c*/ ROW /*d*/ })}} FROM {dual}")));

    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding exclude group) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING EXCLUDE GROUP})}} FROM {dual}"));
    // comments
    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding /*x*/ exclude /*y*/ group /*z*/) from dual"),
        debug(with_meta("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING /*x*/ EXCLUDE /*y*/ GROUP /*z*/ })}} FROM {dual}")));

    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding exclude ties) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING EXCLUDE TIES})}} FROM {dual}"));
    // comments
    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding /*1*/ exclude /*2*/ ties /*3*/) from dual"),
        debug(with_meta("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING /*1*/ EXCLUDE /*2*/ TIES /*3*/ })}} FROM {dual}")));

    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding exclude no   others) from dual"),
        debug("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING EXCLUDE NO OTHERS})}} FROM {dual}"));
    // comments
    assert_sql!(
        stmt("select count(42) over (order by x rows 123 preceding /*1*/ exclude /*2*/ no/*3*/   others/*4*/) from dual"),
        debug(with_meta("SELECT {{count}(42) {OVER ({ORDER BY {x}} {ROWS 123 PRECEDING /*1*/ EXCLUDE /*2*/ NO /*3*/ OTHERS /*4*/ })}} FROM {dual}")));

    // comments (in empty parens)
    assert_sql!(
        stmt("select count(42) over (/*x*/) from dual"),
        debug(with_meta("SELECT {{count}(42) {OVER ( /*x*/ )}} FROM {dual}")));
}

#[rustfmt::skip]
#[test]
fn test_select_with_windows() {
    assert_sql!(
        stmt("select * from dual window"),
        debug("SELECT * FROM {{dual} window}"));

    assert_sql!(
        stmt("select * from dual window w as ()"),
        debug("SELECT * FROM {dual} {WINDOW w AS ()}"));
    assert_sql!(
        stmt("select * from dual window window w as ()"),
        debug("SELECT * FROM {{dual} window} {WINDOW w AS ()}"));
    assert_sql!(
        stmt("select * from dual window w as (), x as ()"),
        debug("SELECT * FROM {dual} {WINDOW w AS (), x AS ()}"));
    assert_sql!(
        stmt("select * from dual window w as (order by val)"),
        debug("SELECT * FROM {dual} {WINDOW w AS ({ORDER BY {val}})}"));
    assert_sql!(
        stmt("select * from dual window w as (order by val), x as (partition by deptno order by sal rows unbounded preceding)"),
        debug("SELECT * FROM {dual} {WINDOW \
              w AS ({ORDER BY {val}}), \
              x AS ({PARTITION BY {deptno}} {ORDER BY {sal}} {ROWS UNBOUNDED PRECEDING})}"));
    // ~ a window referencing another cannot have a "partition by"
    assert_sql!(
        stmt("select * from dual window w as (order by val), x as (w partition by quux)"),
        debug("Unexpected 'partition'; expected a closing parenthesis [line: 1, column: 56]"));
    // ~ despite being documented as now allowed, oracle 21 has no problem with it:
    assert_sql!(
        stmt(r"
select count(42) over z
  from STORES
 group by countryid, whid
 window w as (partition by countryid order by countryid),
        z as (w rows unbounded preceding)
    "), debug("SELECT {{count}(42) {OVER z}} \
                 FROM {STORES} \
               {GROUP BY {countryid}, {whid}} \
               {WINDOW w AS ({PARTITION BY {countryid}} {ORDER BY {countryid}}), \
                       z AS (w {ROWS UNBOUNDED PRECEDING})}"));

    // comments (already tested within the spec in the preceding test)
    assert_sql!(
        stmt("select * from dual /*1*/ window /*2*/ w /*3*/ as /*4*/ (/*5*/) /*6*/, /*7*/ x /*8*/ as /*9*/ (/*10*/) /*11*/"),
        debug(with_meta("SELECT * FROM {dual /*1*/ } {WINDOW /*2*/ w /*3*/ AS /*4*/ ( /*5*/ ) /*6*/ , /*7*/ x /*8*/ AS /*9*/ ( /*10*/ ) /*11*/ }")));
}

#[rustfmt::skip]
#[test]
fn test_listagg_within_group() {
    assert_sql!(
        stmt("select listagg(name, ',') from foo_t group by ();"),
        debug("SELECT {{listagg}({name}, ',')} FROM {foo_t} {GROUP BY ()};"));
    assert_sql!(
        stmt("select foo_t.*, listagg(name, ',') within group (order by name) from foo_t;"),
        debug("SELECT {foo_t.*}, {{listagg}({name}, ',') {WITHIN GROUP ({ORDER BY {name}})}} FROM {foo_t};"));
    assert_sql!(
        stmt("select foo_t.*, listagg(name, ',') over (partition by age) from foo_t;"),
        debug("SELECT {foo_t.*}, {{listagg}({name}, ',') {OVER ({PARTITION BY {age}})}} FROM {foo_t};"));
    assert_sql!(
        stmt("select foo_t.*, listagg(name, ',') within group (order by name) over (partition by age) from foo_t;"),
        debug("SELECT {foo_t.*}, {{listagg}({name}, ',') {WITHIN GROUP ({ORDER BY {name}})} {OVER ({PARTITION BY {age}})}} FROM {foo_t};"));

    // ~ within group _not_ allowed in WHERE clauses ...
    assert_sql!(
        stmt("select * from foo_t where listagg(name, ',') within group (ORDER BY age) = 'xyz';"),
        debug("Aggregate functions not allowed here [line: 1, column: 27]"));
    // ~ ... but allowed in `ORDER BY` clauses
    assert_sql!(
        stmt(r"select age from foo_t group by (age) order by rank(99) within group (order by age)"),
        debug("SELECT {age} FROM {foo_t} {GROUP BY ({age})} {ORDER BY {{rank}(99) {WITHIN GROUP ({ORDER BY {age}})}}}"));
    // ~ ... and `HAVING` clauses
    assert_sql!(
        stmt(r"select age from foo_t group by (age) having rank(99) within group (order by age) > 1"),
        debug("SELECT {age} FROM {foo_t} {GROUP BY ({age}) HAVING {{{rank}(99) {WITHIN GROUP ({ORDER BY {age}})}} > 1}}"));

    // ~ comments
    assert_sql!(
        stmt("select foo_t.*, listagg(name, ',') /*1*/ within /*2*/ group/*3*/( /*4a*/ order /*4b*/ by /*5*/  name/*6*/) /*7*/ over /*8*/ ( /*9*/ partition /*a*/ by /*b*/ age /*c*/) /*d*/ from foo_t;"),
        debug(with_meta("SELECT {foo_t.*}, \
                                {{listagg}({name}, ',') \
                                  /*1*/ {WITHIN \
                                        /*2*/ GROUP \
                                        /*3*/ ({ /*4a*/ ORDER /*4b*/ BY /*5*/ {name /*6*/ }}) \
                                  /*7*/ } \
                                        {OVER /*8*/ ({ /*9*/ PARTITION /*a*/ BY /*b*/ {age /*c*/ }}) \
                                         /*d*/ }\
                                } \
                           FROM {foo_t};")));
}