content: {-# OPTIONS_GHC -O0 #-}
  {-# LANGUAGE TypeOperators, OverloadedStrings, DeriveGeneric, CPP #-}
  -- | Tests that don't modify the database.
  module Tests.Query (queryTests) where
  import qualified Data.Char as Char (toUpper)
  import Data.Function (on)
  import Data.List hiding (groupBy, insert, union)
  import Data.Maybe (catMaybes)
  #if !MIN_VERSION_base(4, 11, 0)
  import Data.Semigroup (Semigroup (..))
  #endif
  import qualified Data.Text as Text (map)
  import Database.Selda as Selda
  import Database.Selda.Nullable
  import Database.Selda.Unsafe
  import Database.Selda.Validation
  import Test.HUnit
  import Utils
  import Tables
  
  queryTests :: (SeldaM b () -> IO ()) -> Test
  queryTests run = test
    [ "setup succeeds" ~: run setup
    , "simple select" ~: run simpleSelect
    , "simple product"  ~: run simpleProduct
    , "order ascending"  ~: run orderAscending
    , "filter equal"  ~: run filterEqual
    , "filter not equal"  ~: run filterNotEqual
    , "join-like product" ~: run joinLikeProduct
    , "simple left join" ~: run simpleLeftJoin
    , "row left join" ~: run rowLeftJoin
    , "left join followed by product" ~: run leftJoinThenProduct
    , "count aggregation" ~: run countAggregate
    , "aggregate with join and group" ~: run joinGroupAggregate
    , "nested left join" ~: run nestedLeftJoin
    , "order + limit" ~: run orderLimit
    , "limit gives correct number of results" ~: run limitCorrectNumber
    , "aggregate with doubles" ~: run aggregateWithDoubles
    , "select from value table" ~: run selectVals
    , "select from empty value table" ~: run selectEmptyValues
    , "aggregate from empty value table" ~: run aggregateEmptyValues
    , "inner join" ~: run testInnerJoin
    , "simple if-then-else" ~: run simpleIfThenElse
    , "rounding doubles to ints" ~: run roundToInt
    , "serializing doubles" ~: run serializeDouble
    , "such that works" ~: run testSuchThat
    , "prepared without args" ~: run preparedNoArgs
    , "prepared with args" ~: run preparedManyArgs
    , "prepared interleaved" ~: run preparedInterleaved
    , "interleaved with different results" ~: run preparedDifferentResults
    , "order in correct order" ~: run orderCorrectOrder
    , "multiple aggregates in sequence (#42)" ~: run multipleAggregates
    , "isIn inner query renaming (#46)" ~: run isInQueryRenaming
    , "distinct on multiple queries" ~: run selectDistinct
    , "distinct on single query" ~: run selectValuesDistinct
    , "distinct restrict" ~: run selectRestrictedDistinct
    , "matchNull" ~: run simpleMatchNull
    , "ifThenElse" ~: run simpleIfThenElse
    , "validateTable validates" ~: run validateTableValidates
    , "aggregate empty table" ~: run aggregateEmptyTable
    , "empty singleton values" ~: run selectValuesEmptySingletonTable
    , "coalesce row" ~: run coalesceRow
    , "coalesce equality" ~: run coalesceEquality
    , "coalesce num" ~: run coalesceNum
    , "coalesce frac" ~: run coalesceFrac
    , "coalesce sum" ~: run coalesceSum
    , "nonNull" ~: run nonNullYieldsEmptyResult
    , "rawQuery1" ~: run rawQuery1Works
    , "rawQuery" ~: run rawQueryWorks
    , "union" ~: run unionWorks
    , "union discards dupes" ~: run unionDiscardsDupes
    , "union works for whole rows" ~: run unionWorksForWholeRows
    , "expression cols under union (LHS)" ~: run unionWithLhsExpressionCols
    , "expression cols under union (RHS)" ~: run unionWithRhsExpressionCols
    , "unionAll" ~: run unionAllWorks
    , "unionAll works for whole rows" ~: run unionAllForWholeRows
    , "string concatenation" ~: run stringConcatenation
    , "teardown succeeds" ~: run teardown
    , "if not exists works" ~: run (setup >> resetup)
    , "two distinct clauses works" ~: run twoDistinctClauses
    ]
  
  simpleSelect = do
    ppl <- query $ select people
    assEq "wrong results from select" (sort peopleItems) (sort ppl)
  
  simpleProduct = do
    prod <- query $ do
      a <- select addresses
      person <- select people
      return (a!aName :*: a!aCity :*: person)
    assEq "wrong results from product" (sort ans) (sort prod)
    where
      ans = [n :*: c :*: p | p <- peopleItems, (n, c) <- addressItems]
  
  orderAscending = do
    ppl <- query $ do
      p <- select people
      order (p!pName) ascending
      return p
    assEq "result not properly sorted" (sort peopleItems) ppl
  
  filterEqual = do
    ppl <- query $ do
      name <- pName `from` select people
      restrict (name .== "Link")
      return name
    assEq "unequal elements not removed" ["Link"] ppl
  
  filterNotEqual = do
    ppl <- query $ do
      name <- pName `from` select people
      restrict (name ./= "Link")
      return name
    ass "filtered element still in list" (not $ "Link" `elem` ppl)
  
  joinLikeProduct = do
    res <- query $ do
      name <- pName `from` select people
      a <- select addresses
      restrict (name .== a!aName)
      return (name :*: a!aCity)
    assEq "join-like query gave wrong result" (sort ans) (sort res)
    where
      ans =
        [ name p :*: c
        | p <- peopleItems
        , (n', c) <- addressItems
        , name p == n'
        ]
  
  joinLikeProductWithSels = do
    res <- query $ do
      p <- select people
      a <- select addresses
      restrict (p ! pName .== a ! aName)
      return (p ! pName :*: a ! aCity :*: p ! pPet)
    assEq "join-like query gave wrong result" (sort ans) (sort res)
    where
      ans =
        [ n :*: c :*: p
        | Person n _ p _ <- peopleItems
        , (n', c) <- addressItems
        , n == n'
        ]
  
  simpleLeftJoin = do
    res <- query $ do
      name <- pName `from` select people
      a <- leftJoin (\a -> name .== a!aName)
                    (select addresses)
      return (name :*: a?aCity)
    assEq "join-like query gave wrong result" (sort ans) (sort res)
    where
      ans =
        [ "Link"      :*: Just "Kakariko"
        , "Velvet"    :*: Nothing
        , "Miyu"      :*: Just "Fuyukishi"
        , "Kobayashi" :*: Just "Tokyo"
        ]
  
  rowLeftJoin = do
    res <- query $ do
      name <- pName `from` select people
      a <- leftJoin (\a -> name .== a!aName)
                    (select addresses)
      return (name :*: a)
    assEq "join-like query gave wrong result" (sort ans) (sort res)
    where
      ans =
        [ "Link"      :*: Just ("Link", "Kakariko")
        , "Velvet"    :*: Nothing
        , "Miyu"      :*: Just ("Miyu", "Fuyukishi")
        , "Kobayashi" :*: Just ("Kobayashi", "Tokyo")
        ]
  
  leftJoinThenProduct = do
    res <- query $ do
      name <- pName `from` select people
      a <- leftJoin (\a -> name .== a!aName)
                    (select addresses)
      c <- select comments
      restrict (c!cName .== just name)
      return (name :*: a?aCity :*: c!cComment)
    assEq "join + product gave wrong result" ans res
    where
      linkComment = head [c | (_, n, c) <- commentItems, n == Just "Link"]
      ans = ["Link" :*: Just "Kakariko" :*: linkComment]
  
  countAggregate = do
    [res] <- query . aggregate $ do
      p <- select people
      return (count (p!pPet))
    assEq "count counted the wrong number of pets" ans res
    where
      ans = length [p | Just p <- map pet peopleItems]
  
  joinGroupAggregate = do
    res <- query . aggregate $ do
      p <- select people
      a <- leftJoin (\a -> p!pName .== a!aName)
                    (select addresses)
      nopet <- groupBy (isNull (p!pPet))
      return (nopet :*: count (a?aCity))
    assEq "wrong number of cities per pet owneship status" ans (sort res)
    where
      -- There are pet owners in Tokyo and Kakariko, there is no pet owner in
      -- Fuyukishi
      ans = [False :*: 2, True :*: 1]
  
  nestedLeftJoin = do
    res <- query $ do
      p <- select people
      _ :*: city :*: cs <- leftJoin (\(name' :*: _) -> p!pName .== name') $ do
        a <- select addresses
        _ :*: cs <- leftJoin (\(n :*: _) -> n .== just (a!aName)) $ aggregate $ do
          c <- select comments
          n <- groupBy (c!cName)
          return (n :*: count (c!cComment))
        return (a!aName :*: a!aCity :*: cs)
      return (p!pName :*: city :*: cs)
    ass ("user with comment not in result: " ++ show res) (link `elem` res)
    ass ("user without comment not in result: " ++ show res) (velvet `elem` res)
    where
      link = "Link" :*: Just "Kakariko" :*: Just (1 :: Int)
      velvet = "Velvet" :*: Nothing :*: Nothing
  
  orderLimit = do
    res <- query $ limit 1 2 $ do
      t <- select people
      order (t!pCash) descending
      return (t!pName)
    assEq "got wrong result" ["Link", "Velvet"] (sort res)
  
  limitCorrectNumber = do
    res <- query $ do
      p1 <- limit 1 2 $ select people
      p2 <- limit 1 2 $ select people
      return p1
    assEq ("wrong number of results from limit") 4 (length res)
  
  aggregateWithDoubles = do
    [Just res] <- query $ aggregate $ do
      cash <- pCash `from` select people
      return (avg cash)
    assEq "got wrong result" ans res
    where
      ans = sum (map cash peopleItems)/fromIntegral (length peopleItems)
  
  selectVals = do
    vals <- query $ selectValues peopleItems
    assEq "wrong columns returned" (sort peopleItems) (sort vals)
  
  selectEmptyValues = do
    res <- query $ do
      ppl <- select people
      vals <- selectValues ([] :: [Only (Maybe Text)])
      cs <- select comments
      return cs
    assEq "result set wasn't empty" [] res
  
  aggregateEmptyValues = do
    [res] <- query $ aggregate $ do
      ppl <- select people
      vals <- selectValues ([] :: [(Int, Int)])
      t <- select comments
      return (count (t!cId))
    assEq "wrong count for empty result set" 0 res
  
  testInnerJoin = do
      res <- query $ do
        p <- select people
        a <- innerJoin (\a -> p ! pName .== a ! aName) $ do
          select addresses
        return (p ! pPet :*: a ! aCity)
      assEq "wrong result" oracle res
    where
      oracle =
        [ Just "horse"  :*: "Kakariko"
        , Just "dragon" :*: "Tokyo"
        , Nothing       :*: "Fuyukishi"
        ]
  
  simpleIfThenElse = do
      ppl <- query $ do
        t <- select people
        let ageGroup = ifThenElse (t!pAge .< 18)  (text "Child") $
                       ifThenElse (t!pAge .>= 65) (text "Elder")
                                                  (text "Adult")
        return (t!pName :*: t!pAge :*: ageGroup)
      assEq "wrong results from ifThenElse" (sort res) (sort ppl)
    where
      res =
        [ "Link"      :*: 125 :*: "Elder"
        , "Velvet"    :*: 19  :*: "Adult"
        , "Kobayashi" :*: 23  :*: "Adult"
        , "Miyu"      :*: 10  :*: "Child"
        ]
  
  roundToInt = do
    res <- query $ do
      val <- selectValues [1.1, 1.5, 1.9 :: Only Double]
      return $ round_ (the val)
    assEq "bad rounding" [1, 2, 2 :: Int] res
  
  serializeDouble = do
    -- The "protocol" used by PostgreSQL is insane - better check that we speak
    -- it properly!
    res <- query $ do
      n <- selectValues [123456789 :: Only Int]
      d <- selectValues [123456789.3 :: Only Double]
      restrict (the d .> cast (the n))
      return (cast (the n) + float 1.123)
    assEq "wrong encoding" 1 (length res)
    assEq "wrong decoding" [123456790.123] res
  
  testSuchThat = do
    res <- query $ do
      n1 <- (pName `from` select people) `suchThat` (.== "Link")
      n2 <- (pName `from` select people) `suchThat` (.== "Velvet")
      return (n1 :*: n2)
    assEq "got wrong result" ["Link" :*: "Velvet"] res
  
  {-# NOINLINE allShortNames #-}
  allShortNames :: SeldaM b [Text]
  allShortNames = prepared $ do
    p <- select people
    restrict (length_ (p ! pName) .<= 4)
    order (p ! pName) ascending
    return (p ! pName)
  
  preparedNoArgs = do
      res1 <- allShortNames
      res2 <- allShortNames
      res3 <- allShortNames
      assEq "got wrong result" res res1
      ass "subsequent calls gave different results" (all (== res1) [res2, res3])
    where
      res = ["Link", "Miyu"]
  
  {-# NOINLINE allNamesLike #-}
  -- Extra restricts to force the presence of a few non-argument parameters.
  allNamesLike :: Int -> Text -> SeldaM b [Text]
  allNamesLike = prepared $ \len s -> do
    p <- select people
    restrict (length_ (p ! pName) .> 0)
    restrict (p ! pName `like` s)
    restrict (length_ (p ! pName) .> 1)
    restrict (length_ (p ! pName) .<= len)
    restrict (length_ (p ! pName) .<= 100)
    restrict (length_ (p ! pName) .<= 200)
    order (p ! pName) ascending
    return (p ! pName)
  
  preparedManyArgs = do
      res1 <- allNamesLike 4 "%y%"
      res2 <- allNamesLike 5 "%y%"
      res3 <- allNamesLike 6 "%y%"
      assEq "got wrong result" res res1
      ass "subsequent calls gave different results" (all (== res1) [res2, res3])
    where
      res = ["Miyu"]
  
  preparedInterleaved = do
      asn1 <- allShortNames
      anl1 <- allNamesLike 4 "%y%"
      asn2 <- allShortNames
      anl2 <- allNamesLike 5 "%y%"
      asn3 <- allShortNames
      anl3 <- allNamesLike 6 "%y%"
      assEq "wrong result from allShortNames" asnres asn1
      assEq "wrong result from allNamesLike" anlres anl1
      ass "subsequent allShortNames calls gave different results"
          (all (==asn1) [asn2, asn3])
      ass "subsequent allNamesLike calls gave different results"
          (all (==anl1) [anl2, anl3])
    where
      asnres = ["Link", "Miyu"]
      anlres = ["Miyu"]
  
  preparedDifferentResults = do
    res1 <- allNamesLike 4 "%y%"
    res2 <- allNamesLike 10 "%y%"
    assEq "wrong result from first query" ["Miyu"] res1
    assEq "wrong result from second query" ["Kobayashi", "Miyu"] res2
  
  orderCorrectOrder = do
      insert_ people [Person "Amber" 19 Nothing 123]
  
      res1 <- query $ do
        p <- select people
        order (p ! pName) ascending
        order (p ! pAge) ascending
        return (p ! pName)
  
      res2 <- query $ do
        p <- select people
        order (p ! pName) descending
        order (p ! pAge) ascending
        return (p ! pName)
  
      res3 <- query $ do
        p <- select people
        order (p ! pAge) descending
        order (p ! pName) ascending
        return (p ! pName)
  
      deleteFrom_ people $ \p -> p ! pName .== "Amber"
  
      assEq "latest ordering did not take precedence in first query" ans1 res1
      assEq "latest ordering did not take precedence in second query" ans2 res2
      assEq "latest ordering did not take precedence in third query" ans3 res3
    where
      ans1 = ["Miyu", "Amber", "Velvet", "Kobayashi", "Link"]
      ans2 = ["Miyu", "Velvet", "Amber", "Kobayashi", "Link"]
      ans3 = ["Amber", "Kobayashi", "Link", "Miyu", "Velvet"]
  
  -- Test case for #42: name supply was erroneously overwritten when using
  -- aggregates.
  multipleAggregates = do
    res <- query $ do
      p <- select people
  
      (owner :*: homes) <- aggregate $ do
        a <- select addresses
        owner' <- groupBy (a!aName)
        return (owner' :*: count (a!aCity))
      restrict (owner .== p!pName)
  
      (owner2 :*: homesInTokyo) <- aggregate $ do
        a <- select addresses
        restrict (a!aCity .== "Tokyo")
        owner' <- groupBy (a!aName)
        return (owner' :*: count (a!aCity))
      restrict (owner2 .== p!pName)
  
      order homes descending
      return (owner :*: homes :*: homesInTokyo)
    assEq "wrong result for aggregate query" ["Kobayashi" :*: 1 :*: 1] res
  
  isInQueryRenaming = do
    res <- query $ do
      t1 <- select people
      restrict $ (int 1) `isIn` (do
          t2 <- select people
          t3 <- select addresses
          restrict (t3!aName .== t2!pName)
          restrict (t1!pName .== t2!pName)
          restrict (t3!aCity .== "Kakariko")
          return (int 1)
        )
      return (t1!pName)
    assEq "wrong list of people returned" ["Link"] res
  
  selectDistinct = do
    res <- query $ distinct $ do
      t <- select people
      select people
      order (t!pName) ascending
      return (t!pName)
    assEq "wrong result set" ["Kobayashi", "Link", "Miyu", "Velvet"] res
  
  data L = L Text
    deriving (Generic, Show, Eq)
  instance SqlRow L
  
  selectValuesDistinct = do
    res <- query $ distinct $ selectValues $ replicate 5 (L "Link")
    assEq "wrong result set" [L "Link"] res
  
  data Distinct = D { a :: Int, b :: Int }
      deriving Generic
  instance SqlRow Distinct
  
  d_a = unsafeSelector 0 :: Selector Distinct Int
  d_b  = unsafeSelector 1 :: Selector Distinct Int
  
  selectRestrictedDistinct = do
    xs <- query $ distinct $ do
      x <- selectValues [D 42 1, D 42 2, D 42 3]
      restrict $ x ! d_b .>= 2
      return $ x ! d_a
    assEq "wrong result set" [42] xs
  
  simpleMatchNull = do
      res <- query $ do
        t <- select people
        order (t!pName) ascending
        return $ (t!pName :*: matchNull 0 length_ (t!pPet))
      assEq "wrong result set" expected res
    where
      expected =
        [ "Kobayashi" :*: 6
        , "Link" :*: 5
        , "Miyu" :*: 0
        , "Velvet" :*: 0
        ]
  
  validateTableValidates = do
      validateTable people
      assertFail $ validateTable bad
    where
      bad :: Table (RowID, RowID)
      bad = table "bad"
        [ Single (unsafeSelector 0 :: Selector (RowID, RowID) RowID) :- primary
        , Single (unsafeSelector 1 :: Selector (RowID, RowID) RowID) :- primary
        ]
  
  aggregateEmptyTable = do
      [res] <- query $ do
        aggregate $ count <$> s_fst `from` selectValues ([] :: [(Int, Int)])
      assEq "count of empty table not 0" (0::Int) res
  
      [res] <- query $ do
        aggregate $ sum_ <$> s_fst `from` selectValues ([] :: [(Int, Int)])
      (res :: Int) `seq` return ()
      assEq "sum of empty table not 0" (0::Int) res
  
      [res] <- query $ do
        aggregate $ avg <$> s_fst `from` selectValues ([] :: [(Int, Int)])
      assEq "average of empty table not Nothing" (Nothing::Maybe Int) res
  
      [res] <- query $ do
        aggregate $ min_ <$> s_fst `from` selectValues ([] :: [(Int, Int)])
      assEq "min of empty table not Nothing" (Nothing::Maybe Int) res
  
      [res] <- query $ do
        aggregate $ max_ <$> s_fst `from` selectValues ([] :: [(Int, Int)])
      assEq "max of empty table not Nothing" (Nothing::Maybe Int) res
    where
      s_fst = unsafeSelector 0 :: Selector (Int, Int) Int
  
  selectValuesEmptySingletonTable = do
    [res] <- query $ aggregate $ count <$> the <$> selectValues ([] :: [Only Int])
    assEq "non-zero count when selecting from empty values" 0 res
  
  coalesceRow = do
      empty <- query $ do
        _ <- selectValues [Only (1 :: Int)]
        xs <- leftJoin (const false) (select people)
        return (xs ?! pName)
      assEq "result not single null" [Nothing] empty
  
      res <- query $ do
        person <- select people
        restrict' (person ?! pName ?== text "Link")
        xs <- leftJoin (const true) (select people)
        order (xs ?! pName) ascending
        return $ xs ?! pName
      assEq "wrong result after coalescing" expected res
    where
      expected =
        [ Just "Kobayashi"
        , Just "Link"
        , Just "Miyu"
        , Just "Velvet"
        ]
  
  coalesceEquality :: SeldaM b ()
  coalesceEquality = do
    ["Link"] <- query $ do
      person <- select people
      restrict' (person ! pPet ?== text "horse")
      return (person ! pName)
    ["Kobayashi"] <- query $ do
      person <- select people
      restrict' (person ! pPet ?/= text "horse")
      return (person ! pName)
    return ()
  
  coalesceNum :: SeldaM b ()
  coalesceNum = do
    [Just 250 :*: Just 126 :*: Just 124] <- query $ do
      _ <- selectValues [Only (1 :: Int)]
      person <- leftJoin (const true) (select people)
      restrict' (person ?! pName ?== text "Link")
      return
        (   person ?! pAge ?* int 2
        :*: person ?! pAge ?+ int 1
        :*: person ?! pAge ?- int 1)
    return ()
  
  coalesceFrac = do
    result <- query $ do
      _ <- selectValues [Only (1 :: Int)]
      person <- leftJoin (const true) (select people)
      restrict' (person ?! pName ?== text "Miyu")
      return
        (   person ?! pCash ?/ float 2
        :*: person ?! pAge ?/ int 2)
    assEq "wrong calculation results" [Just (-250) :*: Just 5] result
  
  coalesceSum = do
    res <- query $ aggregate $ do
      _ <- selectValues [Only (1 :: Int)]
      person <- leftJoin (const true) (select people)
      age <- nonNull $ person ?! pAge
      return $ sum_ age
    assEq "wrong sum" [125+19+23+10::Int] res
  
  nonNullYieldsEmptyResult = do
    empty <- query $ do
      _ <- selectValues [Only (1 :: Int)]
      person <- leftJoin (const false) (select people)
      nonNull $ person ?! pAge
    assEq "empty list not empty" [] empty
  
  rawQuery1Works = do
    names <- query $ do
      n <- rawQuery1 "name" "SELECT name FROM people"
      order n ascending
      return n
    assEq "wrong name list returned" (sort $ map name peopleItems) names
  
  rawQueryWorks = do
    ppl <- query $ do
      p <- rawQuery ["name", "age", "pet", "cash"]
                    ("SELECT * FROM people WHERE name = " <> injLit ("Link"::Text))
      return p
    let correct = [p | p <- peopleItems, name p == "Link"]
    assEq "wrong name list returned" correct ppl
  
  unionWorks = assQueryEq "wrong name list returned" correct $ do
      let ppl = pName `from` select people
          pets = (pPet `from` select people) >>= nonNull
      name <- Selda.toUpper <$> union pets ppl
      order name ascending
      return name
    where
      correct = sort
        $ map (Text.map Char.toUpper)
        $ map name peopleItems
        ++ catMaybes (map pet peopleItems)
  
  unionDiscardsDupes = assQueryEq "wrong name list returned" correct $ do
      let ppl = pName `from` select people
          pets = (pPet `from` select people) >>= nonNull
      name <- Selda.toUpper <$> ppl `union` pets `union` ppl `union` ppl
      order name ascending
      return name
    where
      correct = sort
        $ map (Text.map Char.toUpper)
        $ map name peopleItems
        ++ catMaybes (map pet peopleItems)
  
  unionWorksForWholeRows = assQueryEq "wrong person list returned" correct $ do
      let ppl1 = select people `suchThat` \p -> p!pAge .<= 18
          ppl2 = select people `suchThat` \p -> p!pAge .> 18
      ppl <- ppl1 `union` ppl2 `union` ppl2 `union` ppl1
      order (Selda.toUpper (ppl ! pName)) ascending
      return ppl
    where
      correct = sortBy (compare `on` (Text.map Char.toUpper . name)) peopleItems
  
  unionWithLhsExpressionCols = assQueryEq "wrong person list returned" correct $ do
      let ppl1 = select people
          ppl2 = (`with` [pAge += 1]) <$> select people
      ppl <- ppl2 `union` ppl1
      order (ppl ! pAge) ascending
      return ppl
    where
      correct = sortBy (compare `on` age) (peopleItems ++ map (\p -> p {age = age p+1}) peopleItems)
  
  unionWithRhsExpressionCols = assQueryEq "wrong person list returned" correct $ do
      let ppl1 = select people
          ppl2 = (`with` [pAge += 1]) <$> select people
      ppl <- ppl1 `union` ppl2
      order (ppl ! pAge) ascending
      return ppl
    where
      correct = sortBy (compare `on` age) (peopleItems ++ map (\p -> p {age = age p+1}) peopleItems)
  
  unionAllWorks = assQueryEq "wrong name list returned" correct $ do
      let ppl = pName `from` select people
          pets = (pPet `from` select people) >>= nonNull
      name <- Selda.toUpper <$> ppl `unionAll` pets `unionAll` ppl
      order name ascending
      return name
    where
      correct = sort
        $ map (Text.map Char.toUpper)
        $ map name peopleItems
        ++ map name peopleItems
        ++ catMaybes (map pet peopleItems)
  
  unionAllForWholeRows = assQueryEq "wrong person list returned" correct $ do
      let ppl1 = select people
          ppl2 = (`with` [pAge += 1]) <$> select people
      ppl <- ppl1 `unionAll` ppl2
      order (ppl ! pAge) ascending
      return ppl
    where
      correct = sortBy (compare `on` age) (peopleItems ++ map (\p -> p {age = age p+1}) peopleItems)
  
  stringConcatenation = assQueryEq "wrong string returned" ["abcde"] $ do
    pure $ mconcat ["a" :: Col s Text, "bc", "", "de"]
  
  twoDistinctClauses = do
    res <- query $ aggregate $ do
        ppl <- distinct $ pName `from` select people
        _ <- distinct $ pName `from` select people
        return (count ppl)
    assEq "distinct people is wrong" [4] res
  