simple-sql-parser/tests/Language/SQL/SimpleSQL/GroupBy.hs

-- Here are the tests for the group by component of query exprs

{-# LANGUAGE OverloadedStrings #-}
module Language.SQL.SimpleSQL.GroupBy (groupByTests) where

import Language.SQL.SimpleSQL.TestTypes
import Language.SQL.SimpleSQL.Syntax
import Language.SQL.SimpleSQL.TestRunners
import Data.Text (Text)


groupByTests :: TestItem
groupByTests = Group "groupByTests"
    [simpleGroupBy
    ,newGroupBy
    ,randomGroupBy
    ]

q :: HasCallStack => Text -> QueryExpr -> TestItem
q src a = testQueryExpr ansi2011 src a

p :: HasCallStack => Text -> TestItem
p src = testParseQueryExpr ansi2011 src



simpleGroupBy :: TestItem
simpleGroupBy = Group "simpleGroupBy"
    [q "select a,sum(b) from t group by a"
     $ toQueryExpr $ makeSelect {msSelectList = [(Iden [Name Nothing "a"],Nothing)
                                 ,(App [Name Nothing "sum"] [Iden [Name Nothing "b"]],Nothing)]
                 ,msFrom = [TRSimple [Name Nothing "t"]]
                 ,msGroupBy = [SimpleGroup $ Iden [Name Nothing "a"]]
                 }

    ,q "select a,b,sum(c) from t group by a,b"
     $ toQueryExpr $ makeSelect {msSelectList = [(Iden [Name Nothing "a"],Nothing)
                                 ,(Iden [Name Nothing "b"],Nothing)
                                 ,(App [Name Nothing "sum"] [Iden [Name Nothing "c"]],Nothing)]
                 ,msFrom = [TRSimple [Name Nothing "t"]]
                 ,msGroupBy = [SimpleGroup $ Iden [Name Nothing "a"]
                              ,SimpleGroup $ Iden [Name Nothing "b"]]
                 }
    ]

{-
test the new group by (), grouping sets, cube and rollup syntax (not
sure which sql version they were introduced, 1999 or 2003 I think).
-}

newGroupBy :: TestItem
newGroupBy = Group "newGroupBy"
    [q "select * from t group by ()" $ ms [GroupingParens []]
    ,q "select * from t group by grouping sets ((), (a))"
     $ ms [GroupingSets [GroupingParens []
                       ,GroupingParens [SimpleGroup $ Iden [Name Nothing "a"]]]]
    ,q "select * from t group by cube(a,b)"
     $ ms [Cube [SimpleGroup $ Iden [Name Nothing "a"], SimpleGroup $ Iden [Name Nothing "b"]]]
    ,q "select * from t group by rollup(a,b)"
     $ ms [Rollup [SimpleGroup $ Iden [Name Nothing "a"], SimpleGroup $ Iden [Name Nothing "b"]]]
    ]
  where
    ms g = toQueryExpr $ makeSelect {msSelectList = [(Star,Nothing)]
                      ,msFrom = [TRSimple [Name Nothing "t"]]
                      ,msGroupBy = g}

randomGroupBy :: TestItem
randomGroupBy = Group "randomGroupBy"
    [p "select * from t GROUP BY a"
    ,p "select * from t GROUP BY GROUPING SETS((a))"
    ,p "select * from t GROUP BY a,b,c"
    ,p "select * from t GROUP BY GROUPING SETS((a,b,c))"
    ,p "select * from t GROUP BY ROLLUP(a,b)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b),\n\
     \(a),\n\
     \() )"
    ,p "select * from t GROUP BY ROLLUP(b,a)"
    ,p "select * from t GROUP BY GROUPING SETS((b,a),\n\
     \(b),\n\
     \() )"
    ,p "select * from t GROUP BY CUBE(a,b,c)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b,c),\n\
     \(a,b),\n\
     \(a,c),\n\
     \(b,c),\n\
     \(a),\n\
     \(b),\n\
     \(c),\n\
     \() )"
    ,p "select * from t GROUP BY ROLLUP(Province, County, City)"
    ,p "select * from t GROUP BY ROLLUP(Province, (County, City))"
    ,p "select * from t GROUP BY ROLLUP(Province, (County, City))"
    ,p "select * from t GROUP BY GROUPING SETS((Province, County, City),\n\
     \(Province),\n\
     \() )"
    ,p "select * from t GROUP BY GROUPING SETS((Province, County, City),\n\
     \(Province, County),\n\
     \(Province),\n\
     \() )"
    ,p "select * from t GROUP BY a, ROLLUP(b,c)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b,c),\n\
     \(a,b),\n\
     \(a) )"
    ,p "select * from t GROUP BY a, b, ROLLUP(c,d)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b,c,d),\n\
     \(a,b,c),\n\
     \(a,b) )"
    ,p "select * from t GROUP BY ROLLUP(a), ROLLUP(b,c)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b,c),\n\
     \(a,b),\n\
     \(a),\n\
     \(b,c),\n\
     \(b),\n\
     \() )"
    ,p "select * from t GROUP BY ROLLUP(a), CUBE(b,c)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b,c),\n\
     \(a,b),\n\
     \(a,c),\n\
     \(a),\n\
     \(b,c),\n\
     \(b),\n\
     \(c),\n\
     \() )"
    ,p "select * from t GROUP BY CUBE(a,b), ROLLUP(c,d)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b,c,d),\n\
     \(a,b,c),\n\
     \(a,b),\n\
     \(a,c,d),\n\
     \(a,c),\n\
     \(a),\n\
     \(b,c,d),\n\
     \(b,c),\n\
     \(b),\n\
     \(c,d),\n\
     \(c),\n\
     \() )"
    ,p "select * from t GROUP BY a, ROLLUP(a,b)"
    ,p "select * from t GROUP BY GROUPING SETS((a,b),\n\
     \(a) )"
    ,p "select * from t GROUP BY Region,\n\
     \ROLLUP(Sales_Person, WEEK(Sales_Date)),\n\
     \CUBE(YEAR(Sales_Date), MONTH (Sales_Date))"
    ,p "select * from t GROUP BY ROLLUP (Region, Sales_Person, WEEK(Sales_Date),\n\
     \YEAR(Sales_Date), MONTH(Sales_Date) )"

    ,p "SELECT WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \WHERE WEEK(SALES_DATE) = 13\n\
     \GROUP BY WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON\n\
     \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"

    ,p "SELECT WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \WHERE WEEK(SALES_DATE) = 13\n\
     \GROUP BY GROUPING SETS ( (WEEK(SALES_DATE), SALES_PERSON),\n\
     \(DAYOFWEEK(SALES_DATE), SALES_PERSON))\n\
     \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"

    ,p "SELECT WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \WHERE WEEK(SALES_DATE) = 13\n\
     \GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )\n\
     \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"

    ,p "SELECT WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \WHERE WEEK(SALES_DATE) = 13\n\
     \GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )\n\
     \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"

    ,p "SELECT SALES_PERSON,\n\
     \MONTH(SALES_DATE) AS MONTH,\n\
     \SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)),\n\
     \()\n\
     \)\n\
     \ORDER BY SALES_PERSON, MONTH"

    ,p "SELECT WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) )\n\
     \ORDER BY WEEK, DAY_WEEK"

    ,p "SELECT MONTH(SALES_DATE) AS MONTH,\n\
     \REGION,\n\
     \SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \GROUP BY ROLLUP ( MONTH(SALES_DATE), REGION )\n\
     \ORDER BY MONTH, REGION"

    ,p "SELECT WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \MONTH(SALES_DATE) AS MONTH,\n\
     \REGION,\n\
     \SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES\n\
     \GROUP BY GROUPING SETS ( ROLLUP( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ),\n\
     \ROLLUP( MONTH(SALES_DATE), REGION ) )\n\
     \ORDER BY WEEK, DAY_WEEK, MONTH, REGION"

    ,p "SELECT R1, R2,\n\
     \WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \MONTH(SALES_DATE) AS MONTH,\n\
     \REGION, SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)\n\
     \GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),\n\
     \DAYOFWEEK(SALES_DATE))),\n\
     \(R2,ROLLUP( MONTH(SALES_DATE), REGION ) ))\n\
     \ORDER BY WEEK, DAY_WEEK, MONTH, REGION"

    {-,p "SELECT COALESCE(R1,R2) AS GROUP,\n\
     \WEEK(SALES_DATE) AS WEEK,\n\
     \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\
     \MONTH(SALES_DATE) AS MONTH,\n\
     \REGION, SUM(SALES) AS UNITS_SOLD\n\
     \FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)\n\
     \GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),\n\
     \DAYOFWEEK(SALES_DATE))),\n\
     \(R2,ROLLUP( MONTH(SALES_DATE), REGION ) ))\n\
     \ORDER BY GROUP, WEEK, DAY_WEEK, MONTH, REGION"-}
     -- as group - needs more subtle keyword blacklisting

    -- decimal as a function not allowed due to the reserved keyword
    -- handling: todo, review if this is ansi standard function or
    -- if there are places where reserved keywords can still be used
    ,p "SELECT MONTH(SALES_DATE) AS MONTH,\n\
     \REGION,\n\
     \SUM(SALES) AS UNITS_SOLD,\n\
     \MAX(SALES) AS BEST_SALE,\n\
     \CAST(ROUND(AVG(DECIMALx(SALES)),2) AS DECIMAL(5,2)) AS AVG_UNITS_SOLD\n\
     \FROM SALES\n\
     \GROUP BY CUBE(MONTH(SALES_DATE),REGION)\n\
     \ORDER BY MONTH, REGION"

    ]