> {-# LANGUAGE QuasiQuotes,OverloadedStrings #-}
>
> module Database.HsSqlPpp.Tests.Parsing.Selects (selectParsingTestData) where
>
> import Database.HsSqlPpp.Utils.Here
>
> import Database.HsSqlPpp.Ast

> import Database.HsSqlPpp.Tests.Parsing.Utils

> selectParsingTestData:: Item
> selectParsingTestData =
>    Group "parse selects" [
>    Group "simple select statements" [
>     Group "select no table" [
>       s "select 1;" [QueryStatement ea $ selectE (SelectList ea [SelExp ea (NumberLit ea "1")])]
>      ]
>    ,Group "select from table" [
>       s "select * from tbl;"
>       [QueryStatement ea $ selectFrom (selEL [Star ea]) (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select a,b from tbl;"
>       [QueryStatement ea $ selectFrom (selIL ["a", "b"]) (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select a,b from inf.tbl;"
>       [QueryStatement ea $ selectFrom (selIL ["a", "b"]) (Tref ea (qi "inf" "tbl") (NoAlias ea))]
>      ,s "select distinct * from tbl;"
>       [QueryStatement ea $ Select ea Distinct (SelectList ea (selEL [Star ea])) [Tref ea (i "tbl") (NoAlias ea)]
>        Nothing [] Nothing [] Nothing Nothing]
>      ,s "select a from tbl where b=2;"
>       [QueryStatement ea $ selectFromWhere
>         (selIL ["a"])
>         (Tref ea (i "tbl") (NoAlias ea))
>         (FunCall ea (name "=")
>          [Identifier ea "b", NumberLit ea "2"])]
>      ,s "select a from tbl where b=2 and c=3;"
>       [QueryStatement ea $ selectFromWhere
>         (selIL ["a"])
>         (Tref ea (i "tbl") (NoAlias ea))
>         (FunCall ea (name "!and")
>          [FunCall ea (name "=")  [Identifier ea "b", NumberLit ea "2"]
>          ,FunCall ea (name "=") [Identifier ea "c", NumberLit ea "3"]])]
>      {-,MSStmt "select a from t;\ngo"
>          [QueryStatement ea $ selectFrom (selIL ["a"]) (Tref ea (i "t") (NoAlias ea))]
>      ,MSStmt "select a from t;\nset rowcount -1\ngo"
>          [QueryStatement ea $ selectFrom (selIL ["a"]) (Tref ea (i "t") (NoAlias ea))]
>      ,MSStmt "select a from t;\nset rowcount 10\ngo"
>       [QueryStatement ea $ Select ea Dupes
>        (sl (selIL ["a"]))
>        [Tref ea (i "t") (NoAlias ea)]
>        Nothing [] Nothing [] (Just (NumberLit ea "10")) Nothing]-}
>      ,s "SELECT T.A::INT FROM TABLE AS T;"
>         [QueryStatement ea
>          (Select ea Dupes
>           (SelectList ea
>            [SelExp ea (Cast ea (QIdentifier ea [Nmc "T",Nmc "A"])
>                        (SimpleTypeName ea "int"))])
>           [Tref ea (Name ea [Nmc "TABLE"]) (TableAlias ea (Nmc "T"))]
>           Nothing [] Nothing [] Nothing Nothing)]
>      ]
>

>    ,Group "more select statements" [
>       s "select a from tbl\n\
>         \except\n\
>         \select a from tbl1;"
>       [QueryStatement ea $ CombineQueryExpr ea Except
>        (selectFrom (selIL ["a"]) (Tref ea (i "tbl") (NoAlias ea)))
>        (selectFrom (selIL ["a"]) (Tref ea (i "tbl1") (NoAlias ea)))]
>      ,s "select a from tbl where true\n\
>         \except\n\
>         \select a from tbl1 where true;"
>       [QueryStatement ea $ CombineQueryExpr ea Except
>        (selectFromWhere (selIL ["a"]) (Tref ea (i "tbl") (NoAlias ea)) (BooleanLit ea True))
>        (selectFromWhere (selIL ["a"]) (Tref ea (i "tbl1") (NoAlias ea)) (BooleanLit ea True))]
>      ,s "select a from tbl\n\
>         \union\n\
>         \select a from tbl1;"
>       [QueryStatement ea $ CombineQueryExpr ea Union
>        (selectFrom (selIL ["a"]) (Tref ea (i "tbl") (NoAlias ea)))
>        (selectFrom (selIL ["a"]) (Tref ea (i "tbl1") (NoAlias ea)))]
>      ,s "select a from tbl\n\
>         \union all\n\
>         \select a from tbl1;"
>       [QueryStatement ea $ CombineQueryExpr ea UnionAll
>        (selectFrom (selIL ["a"]) (Tref ea (i "tbl") (NoAlias ea)))
>        (selectFrom (selIL ["a"]) (Tref ea (i "tbl1") (NoAlias ea)))]
>      ,s "(select 1 union select 2) union select 3;"
>       [QueryStatement ea
>        (CombineQueryExpr ea Union
>         (CombineQueryExpr ea Union
>          (selectE (SelectList ea [SelExp ea (NumberLit ea "1")]))
>          (selectE (SelectList ea [SelExp ea (NumberLit ea "2")])))
>         (selectE (SelectList ea [SelExp ea (NumberLit ea "3")])))]
>      ,s "select 1 union (select 2 union select 3);"
>       [QueryStatement ea
>        (CombineQueryExpr ea Union
>         (selectE (SelectList ea [SelExp ea (NumberLit ea "1")]))
>         (CombineQueryExpr ea Union
>          (selectE (SelectList ea [SelExp ea (NumberLit ea "2")]))
>          (selectE (SelectList ea [SelExp ea (NumberLit ea "3")]))))]
>      ,s [here|
>          with a as (select 1 as a1),
>               b as (select * from a)
>               select * from b; |]
>          [QueryStatement ea
>           (WithQueryExpr ea
>            [WithQuery ea (Nmc "a") Nothing (selectE $ SelectList ea
>                                             [SelectItem ea (NumberLit ea "1") (Nmc "a1")])
>            ,WithQuery ea (Nmc "b") Nothing (selectFrom (selEL [Star ea]) (Tref ea (i "a") (NoAlias ea)))]
>            (selectFrom (selEL [Star ea]) (Tref ea (i "b") (NoAlias ea))))]
>      ,s [here|
>          with a as (select 1 as a1),
>               b as (select * from a)
>               select * from a
>               union select * from b; |]
>          [QueryStatement ea
>           (WithQueryExpr ea
>            [WithQuery ea (Nmc "a") Nothing (selectE $ SelectList ea
>                                             [SelectItem ea (NumberLit ea "1") (Nmc "a1")])
>            ,WithQuery ea (Nmc "b") Nothing (selectFrom (selEL [Star ea]) (Tref ea (i "a") (NoAlias ea)))]
>            (CombineQueryExpr ea Union
>              (selectFrom (selEL [Star ea]) (Tref ea (i "a") (NoAlias ea)))
>              (selectFrom (selEL [Star ea]) (Tref ea (i "b") (NoAlias ea)))))]
>      ,s "select a as b from tbl;"
>       [QueryStatement ea $ selectFrom [SelectItem ea (Identifier ea "a") (Nmc "b")] (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select a + b as b from tbl;"
>       [QueryStatement ea $ selectFrom
>        [SelectItem ea
>         (FunCall ea (name "+")
>          [Identifier ea "a", Identifier ea "b"]) (Nmc "b")]
>        (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select a.* from tbl a;"
>       [QueryStatement ea $ selectFrom (selEL [QStar ea (Nmc "a")]) (Tref ea (i "tbl") (TableAlias ea (Nmc "a")))]
>      ,s "select a.* from tbl a(b,c);"
>       [QueryStatement ea $ selectFrom (selEL [QStar ea (Nmc "a")]) (Tref ea (i "tbl") (FullAlias ea (Nmc "a") [Nmc "b",Nmc "c"]))]

>      ,s "select * from t1 a, t2 b;"
>             [QueryStatement ea
>              (Select ea Dupes
>               (SelectList ea
>                [SelExp ea (Star ea)])
>               [Tref ea (i "t1") (TableAlias ea (Nmc "a")),Tref ea (i "t2") (TableAlias ea (Nmc "b"))]
>               Nothing [] Nothing [] Nothing Nothing)]
>      ,s "select a from b inner join c on b.a=c.a;"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural Inner (Tref ea (i "c") (NoAlias ea))
>           (Just (JoinOn ea
>            (FunCall ea (name "=") [eqi "b" "a", eqi "c" "a"]))) (NoAlias ea))]
>      ,s "select a from b inner join c as d on b.a=d.a;"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural Inner (Tref ea (i "c") (TableAlias ea (Nmc "d")))
>           (Just (JoinOn ea
>            (FunCall ea (name "=") [eqi "b" "a", eqi "d" "a"]))) (NoAlias ea))]
>      ,s "select a from b inner join c using(d,e);"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural Inner (Tref ea (i "c") (NoAlias ea))
>           (Just (JoinUsing ea [Nmc "d",Nmc "e"])) (NoAlias ea))]
>      ,s "select a from b natural inner join c;"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Natural Inner (Tref ea (i "c") (NoAlias ea)) Nothing (NoAlias ea))]
>      ,s "select a from b left outer join c;"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural LeftOuter (Tref ea (i "c") (NoAlias ea)) Nothing (NoAlias ea))]
>      ,s "select a from b full outer join c;"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural FullOuter (Tref ea (i "c") (NoAlias ea)) Nothing (NoAlias ea))]
>      ,s "select a from b right outer join c;"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural RightOuter (Tref ea (i "c") (NoAlias ea)) Nothing (NoAlias ea))]
>      ,s "select a from b cross join c;"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural Cross (Tref ea (i "c") (NoAlias ea)) Nothing (NoAlias ea))]
>      ,s "select a from (b natural join c);"
>       [QueryStatement ea $ selectFrom
>        (selIL ["a"])
>        (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Natural Inner (Tref ea (i "c") (NoAlias ea)) Nothing (NoAlias ea))]
>      ,s "select x from a cross join b cross join c;"
>        [QueryStatement ea
>         (selectFrom (selIL ["x"])
>          (JoinTref ea
>          (JoinTref ea
>           (Tref ea (i "a") (NoAlias ea))
>            Unnatural Cross
>           (Tref ea (i "b") (NoAlias ea))
>           Nothing (NoAlias ea))
>          Unnatural Cross
>          (Tref ea (i "c") (NoAlias ea))
>          Nothing (NoAlias ea)))]
>      ,s "select x from ((a cross join b) cross join c);"
>        [QueryStatement ea
>         (selectFrom (selIL ["x"])
>          (JoinTref ea
>          (JoinTref ea
>           (Tref ea (i "a") (NoAlias ea))
>            Unnatural Cross
>           (Tref ea (i "b") (NoAlias ea))
>           Nothing (NoAlias ea))
>          Unnatural Cross
>          (Tref ea (i "c") (NoAlias ea))
>          Nothing (NoAlias ea)))]
>      ,s "select x from (a cross join (b cross join c));"
>        [QueryStatement ea
>         (selectFrom (selIL ["x"])
>          (JoinTref ea
>           (Tref ea (i "a") (NoAlias ea))
>           Unnatural Cross
>           (JoinTref ea
>            (Tref ea (i "b") (NoAlias ea))
>            Unnatural Cross
>            (Tref ea (i "c") (NoAlias ea))
>            Nothing (NoAlias ea))
>           Nothing (NoAlias ea)))]

>      ,s "select x from ((a cross join b) cross join c);"
>        [QueryStatement ea
>         (selectFrom (selIL ["x"])
>          (JoinTref ea
>          (JoinTref ea
>           (Tref ea (i "a") (NoAlias ea))
>            Unnatural Cross
>           (Tref ea (i "b") (NoAlias ea))
>           Nothing (NoAlias ea))
>          Unnatural Cross
>          (Tref ea (i "c") (NoAlias ea))
>          Nothing (NoAlias ea)))]
>      ,s "select x from (a cross join b) cross join c;"
>        [QueryStatement ea
>         (selectFrom (selIL ["x"])
>          (JoinTref ea
>          (JoinTref ea
>           (Tref ea (i "a") (NoAlias ea))
>            Unnatural Cross
>           (Tref ea (i "b") (NoAlias ea))
>           Nothing (NoAlias ea))
>          Unnatural Cross
>          (Tref ea (i "c") (NoAlias ea))
>          Nothing (NoAlias ea)))]
>      ,s "select x from ((a cross join b) cross join c) cross join d;"
>        [QueryStatement ea
>         (selectFrom (selIL ["x"])
>          (JoinTref ea
>           (JoinTref ea
>            (JoinTref ea
>             (Tref ea (i "a") (NoAlias ea))
>             Unnatural Cross
>             (Tref ea (i "b") (NoAlias ea))
>             Nothing (NoAlias ea))
>            Unnatural Cross
>            (Tref ea (i "c") (NoAlias ea))
>            Nothing (NoAlias ea))
>           Unnatural Cross
>           (Tref ea (i "d") (NoAlias ea))
>           Nothing (NoAlias ea)))]
>      ,s "select a from b\n\
>         \    inner join c\n\
>         \      on true\n\
>         \    inner join d\n\
>         \      on 1=1;"
>       [QueryStatement ea $ selectFrom
>        [SelExp ea (Identifier ea "a")]
>        (JoinTref ea
>         (JoinTref ea (Tref ea (i "b") (NoAlias ea)) Unnatural Inner (Tref ea (i "c") (NoAlias ea))
>          (Just $ JoinOn ea (BooleanLit ea True)) (NoAlias ea))
>         Unnatural Inner (Tref ea (i "d") (NoAlias ea))
>         (Just $ JoinOn ea (FunCall ea (name "=")
>                [NumberLit ea "1", NumberLit ea "1"])) (NoAlias ea))]

>      ,s "select row_number() over(order by a) as place from tbl;"
>       [QueryStatement ea $ selectFrom [SelectItem ea
>                    (WindowFn ea
>                     (FunCall ea (name "row_number") [])
>                     []
>                     [(Identifier ea "a", Asc)] FrameUnboundedPreceding)
>                    (Nmc "place")]
>        (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select row_number() over(order by a asc) as place from tbl;"
>       [QueryStatement ea $ selectFrom [SelectItem ea
>                    (WindowFn ea
>                     (FunCall ea (name "row_number") [])
>                     []
>                     [(Identifier ea "a",Asc)] FrameUnboundedPreceding)
>                    (Nmc "place")]
>        (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select row_number() over(order by a desc) as place from tbl;"
>       [QueryStatement ea $ selectFrom [SelectItem ea
>                    (WindowFn ea
>                     (FunCall ea (name "row_number") [])
>                     []
>                     [(Identifier ea "a", Desc)] FrameUnboundedPreceding)
>                    (Nmc "place")]
>        (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select row_number()\n\
>         \over(partition by (a,b) order by c) as place\n\
>         \from tbl;"
>       [QueryStatement ea $ selectFrom [SelectItem ea
>                    (WindowFn ea
>                     (FunCall ea (name "row_number") [])
>                     [FunCall ea (name "!rowctor") [Identifier ea "a",Identifier ea "b"]]
>                     [(Identifier ea "c", Asc)] FrameUnboundedPreceding)
>                    (Nmc "place")]
>        (Tref ea (i "tbl") (NoAlias ea))]
>      ,s "select * from a natural inner join (select * from b) as a;"
>       [QueryStatement ea $ selectFrom
>        (selEL [Star ea])
>        (JoinTref ea (Tref ea (i "a") (NoAlias ea)) Natural
>         Inner (SubTref ea (selectFrom
>                         (selEL [Star ea])
>                         (Tref ea (i "b") (NoAlias ea))) (TableAlias ea $ Nmc "a"))
>         Nothing (NoAlias ea))]
>      ,s "select * from a order by c;"
>       [QueryStatement ea $ Select ea  Dupes
>        (sl (selEL [Star ea]))
>        [Tref ea (i "a") (NoAlias ea)]
>        Nothing [] Nothing [(Identifier ea "c",Asc)] Nothing Nothing]
>      ,s "select *\n\
>            \from Adventure\n\
>            \order by Clicks desc, AdventureID;"
>       [QueryStatement ea $ Select ea Dupes
>        (sl (selEL [Star ea]))
>        [Tref ea (i "Adventure") (NoAlias ea)]
>        Nothing [] Nothing [(Identifier ea "Clicks",Desc)
>                           ,(Identifier ea "AdventureID",Asc)] Nothing Nothing]
>      ,s "select * from a order by c,d asc;"
>       [QueryStatement ea $ Select ea Dupes
>        (sl (selEL [Star ea]))
>        [Tref ea (i "a") (NoAlias ea)]
>        Nothing [] Nothing [(Identifier ea "c", Asc)
>                           ,(Identifier ea "d", Asc)] Nothing Nothing]
>      ,s "select * from a order by c,d desc;"
>       [QueryStatement ea $ Select ea Dupes
>        (sl (selEL [Star ea]))
>        [Tref ea (i "a") (NoAlias ea)]
>        Nothing [] Nothing [(Identifier ea "c", Asc)
>                           ,(Identifier ea "d", Desc)] Nothing Nothing]
>      ,s "select * from a order by c limit 1;"
>       [QueryStatement ea $ Select ea Dupes
>        (sl (selEL [Star ea]))
>        [Tref ea (i "a") (NoAlias ea)]
>        Nothing [] Nothing [(Identifier ea "c",Asc)] (Just (NumberLit ea "1")) Nothing]
>      ,s "select top 3 * from a order by c;"
>       [QueryStatement ea $ Select ea Dupes
>        (sl (selEL [Star ea]))
>        [Tref ea (i "a") (NoAlias ea)]
>        Nothing [] Nothing [(Identifier ea "c",Asc)] (Just (NumberLit ea "3")) Nothing]
>      ,s "select * from a order by c offset 3;"
>       [QueryStatement ea $ Select ea Dupes
>        (sl (selEL [Star ea]))
>        [Tref ea (i "a") (NoAlias ea)]
>        Nothing [] Nothing [(Identifier ea "c",Asc)] Nothing (Just $ NumberLit ea "3")]
>      ,s "select a from (select b from c) as d;"
>         [QueryStatement ea $ selectFrom
>          (selIL ["a"])
>          (SubTref ea (selectFrom
>                    (selIL ["b"])
>                    (Tref ea (i "c") (NoAlias ea)))
>           (TableAlias ea $ Nmc "d"))]
>      ,s "select * from gen();"
>         [QueryStatement ea $ selectFrom (selEL [Star ea]) (FunTref ea (FunCall ea (name "gen") []) (NoAlias ea))]
>      ,s "select * from gen() as t;"
>       [QueryStatement ea $ selectFrom
>        (selEL [Star ea])
>        (FunTref ea (FunCall ea (name "gen") [])(TableAlias ea $ Nmc "t"))]
>      ,s "select count(distinct b) from c;"
>         [QueryStatement ea $ Select ea Dupes
>          (sl [SelExp ea (AggregateFn ea Distinct
>                          (FunCall ea (name "count") [Identifier ea "b"])
>                          [])])
>          [Tref ea (i "c") (NoAlias ea)] Nothing []
>          Nothing [] Nothing Nothing]
>      ,s "select count(all b) from c;"
>         [QueryStatement ea $ Select ea Dupes
>          (sl [SelExp ea (AggregateFn ea Dupes
>                          (FunCall ea (name "count") [Identifier ea "b"])
>                          [])])
>          [Tref ea (i "c") (NoAlias ea)] Nothing []
>          Nothing [] Nothing Nothing]
>      ,s "select string_agg(distinct relname,',' order by relname1) from pg_class;"
>         [QueryStatement ea $ Select ea Dupes
>          (sl [SelExp ea (AggregateFn ea Distinct
>                          (FunCall ea (name "string_agg") [Identifier ea "relname"
>                                                   ,StringLit ea ","])
>                          [(Identifier ea "relname1", Asc)])])
>          [Tref ea (i "pg_class") (NoAlias ea)] Nothing []
>          Nothing [] Nothing Nothing]
>      ,s "select a, count(b) from c group by a;"
>         [QueryStatement ea $ Select ea Dupes
>          (sl [selI "a", SelExp ea (FunCall ea (name "count") [Identifier ea "b"])])
>          [Tref ea (i "c") (NoAlias ea)] Nothing [Identifier ea "a"]
>          Nothing [] Nothing Nothing]
>      ,s "select a, count(b) as cnt from c group by a having cnt > 4;"
>         [QueryStatement ea $ Select ea Dupes
>          (sl [selI "a", SelectItem ea (FunCall ea (name "count") [Identifier ea "b"]) $ Nmc "cnt"])
>          [Tref ea (i "c") (NoAlias ea)] Nothing [Identifier ea "a"]
>          (Just $ FunCall ea (name ">") [Identifier ea "cnt", NumberLit ea "4"])
>          [] Nothing Nothing]
>      ,s "select a from (select 1 as a, 2 as b) x;"
>         [QueryStatement ea $ selectFrom
>          [selI "a"]
>          (SubTref ea (selectE $ SelectList ea
>                                [SelectItem ea (NumberLit ea "1") $ Nmc "a"
>                                ,SelectItem ea (NumberLit ea "2") $ Nmc "b"])
>                   (TableAlias ea $ Nmc "x"))]
>      ]
>      ]
>    ,Group "some misc stuff" [
>       s "select (p).x, (p).y from pos;"
>         [QueryStatement ea $ selectFrom (selEL [eqi "p" "x"
>                                                ,eqi "p" "y"])
>                                          (Tref ea (i "pos") (NoAlias ea))]
>      ,s "select ($1).x, ($1).y from pos;"
>         [QueryStatement ea $ selectFrom
>          (selEL [member (PositionalArg ea 1) (Identifier ea "x")
>                 ,member (PositionalArg ea 1) (Identifier ea "y")])
>          (Tref ea (i "pos") (NoAlias ea))]
>      ,s "select row_number() over(), x from tb;"
>       [QueryStatement ea $ selectFrom
>        [SelExp ea
>                     (WindowFn ea
>                     (FunCall ea (name "row_number") [])
>                     []
>                     [] FrameUnboundedPreceding)
>        , selI "x"]
>        (Tref ea (i "tb") (NoAlias ea))]
>      ]]
>  where
>    s = Stmt