esqueleto 3.6.0.0 → 3.6.0.1

raw patch · 7 files changed

+211/−8 lines, 7 filesdep ~timePVP: minor bump suggested

API additions: PVP suggests at least a minor version bump

Dependency ranges changed: time

API changes (from Hackage documentation)

Files

• changelog.md +17/−0
• esqueleto.cabal +2/−2
• src/Database/Esqueleto/Experimental/From/SqlSetOperation.hs +14/−3
• src/Database/Esqueleto/Experimental/ToAlias.hs +6/−1
• test/Common/Record.hs +29/−0
• test/Common/Test/CTE.hs +117/−0
• test/PostgreSQL/Test.hs +26/−2

@@ -1,3 +1,20 @@+3.6.0.1+=======+- @parsonsmatt+    - [#435](https://github.com/bitemyapp/esqueleto/pull/435)+        - Fix two sources of duplicate column aliases, which made outer+          references to the affected columns fail on PostgreSQL with+          `42702 column reference is ambiguous` and silently select the wrong+          column on SQLite: set operations whose branches allocate different+          numbers of aliases (a variant of+          [#299](https://github.com/bitemyapp/esqueleto/issues/299)), and+          selecting the same inner-scope reference more than once in a single+          select list.+        - Support declaring a CTE (`with`) inside a set operation branch by+          parenthesizing the branch; previously this rendered invalid SQL.+          SQLite does not support parenthesized set operation operands, so+          this remains PostgreSQL/MySQL-only.+ 3.6.0.0 ======= - @parsonsmatt

@@ -1,7 +1,7 @@ cabal-version: 1.12  name:           esqueleto-version:        3.6.0.0+version:        3.6.0.1 synopsis:       Type-safe EDSL for SQL queries on persistent backends. description:    @esqueleto@ is a bare bones, type-safe EDSL for SQL queries that works with unmodified @persistent@ SQL backends.  Its language closely resembles SQL, so you don't have to learn new concepts, just new syntax, and it's fairly easy to predict the generated SQL and optimize it for your backend. Most kinds of errors committed when writing SQL are caught as compile-time errors---although it is possible to write type-checked @esqueleto@ queries that fail at runtime.                 .@@ -24,7 +24,7 @@  source-repository head     type: git-    location: git://github.com/bitemyapp/esqueleto.git+    location: https://github.com/bitemyapp/esqueleto.git  library     exposed-modules:

@@ -56,8 +56,11 @@                   case p of                     Parens -> Parens                     Never ->+                      -- A WITH clause inside a branch is only valid SQL+                      -- when the branch is parenthesized.                       if (sdLimitClause sideData) /= mempty-                          || length (sdOrderByClause sideData) > 0 then+                          || length (sdOrderByClause sideData) > 0+                          || not (null (sdCteClause sideData)) then                         Parens                       else                         Never@@ -68,10 +71,18 @@ mkSetOperation :: (ToSqlSetOperation a a', ToSqlSetOperation b a')                => TLB.Builder -> a -> b -> SqlSetOperation a' mkSetOperation operation lhs rhs = SqlSetOperation $ \p -> do-    state <- Q $ lift S.get+    stateBefore <- Q $ lift S.get     (leftValue, leftClause) <- unSqlSetOperation (toSqlSetOperation lhs) p-    Q $ lift $ S.put state+    stateAfterLeft <- Q $ lift S.get+    -- Rewind so both branches allocate the same idents; they render as+    -- sibling SELECTs, so identical idents cannot collide.+    Q $ lift $ S.put stateBefore     (_, rightClause) <- unSqlSetOperation (toSqlSetOperation rhs) p+    -- Only 'leftValue' escapes, so resume from the left branch's state.+    -- Resuming from the right branch's state would reuse idents appearing+    -- in 'leftValue' whenever the right branch allocated fewer idents+    -- (a variant of issue #299).+    Q $ lift $ S.put stateAfterLeft     pure (leftValue, \info -> leftClause info <> (operation, mempty) <> rightClause info)  -- | Overloaded @union_@ function to support use in both 'SqlSetOperation'

@@ -14,7 +14,12 @@  instance ToAlias (SqlExpr (Value a)) where     toAlias e@(ERaw m f)-      | Just _ <- sqlExprMetaAlias m = pure e+      -- Idempotent for values aliased in the current scope, but a reference+      -- into an inner scope needs a fresh alias: the same reference can+      -- appear several times in one select list, and re-exporting the inner+      -- column name each time would produce duplicate column names.+      | Just _ <- sqlExprMetaAlias m+      , not (sqlExprMetaIsReference m) = pure e       | otherwise = do             ident <- newIdentFor (DBName "v")             pure $ ERaw noMeta{sqlExprMetaAlias = Just ident} f

@@ -352,6 +352,35 @@                           } -> addr1 == addr2 -- The keys should match.                  _ -> False) +    itDb "can union a record query with a CTE reference and alias new columns" $ do+        -- The record's ToAlias instance allocates one ident per field; the+        -- CTE-reference branch allocates none. The enclosing query must not+        -- reuse the record's aliases (a variant of issue #299).+        setup+        records <- select $ do+            recordCTE <- with myRecordQuery+            result <- from $ do+                record <- from $ myRecordQuery `union_` from recordCTE+                pure (record, val (1 :: Int))+            pure result+        let sortedRecords = sortOn (\(MyRecord {myName}, _) -> myName) records+        liftIO $ map (\(MyRecord {myName}, extra) -> (myName, extra)) sortedRecords+          `shouldBe` [("Rebecca", Value 1), ("Some Guy", Value 1)]++    itDb "can select a record alongside one of its own fields in a subquery" $ do+        -- Field access on a record from an inner scope returns the stored+        -- reference, so the select list contains the same reference twice;+        -- each occurrence must get its own output alias.+        setup+        records <- select $ do+            result <- from $ do+                record <- from myRecordQuery+                pure (record, getField @"myName" record)+            pure result+        let sortedRecords = sortOn (\(MyRecord {myName}, _) -> myName) records+        liftIO $ map (\(MyRecord {myName}, dup) -> (myName, dup)) sortedRecords+          `shouldBe` [("Rebecca", Value "Rebecca"), ("Some Guy", Value "Some Guy")]+     itDb "can select user-modified records" $ do         setup         records <- select myModifiedRecordQuery

@@ -8,6 +8,99 @@  testCTE :: SpecDb testCTE = describe "CTE" $ do+    itDb "aliases new columns after a union whose left branch is a CTE reference" $ do+        -- Mirror image of the test below: the left branch allocates fewer+        -- idents than the right one.+        let q :: SqlQuery (SqlExpr (Value Int), SqlExpr (Value Int), SqlExpr (Value Int))+            q = do+                bCte <- with $ do+                    b <- from $ table @B+                    pure (b ^. BK, b ^. BV)+                (k, v, extra) <- from $ do+                    (k, v) <- from $+                        from bCte+                        `union_`+                        (do+                            b <- from $ table @B+                            pure (b ^. BK, b ^. BV))+                    pure (k, v, val (42 :: Int))+                pure (k, v, extra)+        insert_ $ B { bK = 1, bV = 3 }+        ret <- select q+        asserting $ do+            ret `shouldMatchList`+                [ (Value 1, Value 3, Value 42)+                ]++    itDb "aliases a repeated reference in a subquery select list" $ do+        -- Each occurrence needs its own output alias, or outer references+        -- to the duplicated column name are ambiguous.+        let q :: SqlQuery (SqlExpr (Value Int), SqlExpr (Value Int))+            q = do+                (a, b) <- from $ do+                    (x, _) <- from $ do+                        b <- from $ table @B+                        pure (b ^. BK, b ^. BV)+                    pure (x, x)+                pure (a, b)+        insert_ $ B { bK = 1, bV = 3 }+        ret <- select q+        asserting $ do+            ret `shouldMatchList`+                [ (Value 1, Value 1)+                ]++    itDb "layered CTEs over subqueries with repeated references" $ do+        let recentTransactions :: SqlQuery (SqlExpr (Value Int), SqlExpr (Value (Maybe Int)), SqlExpr (Value Int))+            recentTransactions = do+                b <- from $ table @B+                limit 10+                pure (b ^. BK, just (b ^. BV), b ^. BK)+            getPrev tr = with $ do+                (k, mr, _) <- tr+                pure (k, mr, just (k +. val 1), just (k +. val 2))+            filterRuns tr = do+                prev <- getPrev tr+                (k, mr, p1, _) <- from prev+                where_ $ isNothing_ p1 ||. p1 !=. just (val 0)+                pure (k, mr, k)+            inferTime tr = do+                let removed = from $ filterRuns tr+                prev <- getPrev removed+                (k, mr, _, p2) <- from prev+                let newT = coalesceDefault [p2] k+                pure (k, mr, newT)+            q :: SqlQuery (SqlExpr (Value Int), SqlExpr (Value (Maybe Int)), SqlExpr (Value Int))+            q = do+                recent <- with $ inferTime (from recentTransactions)+                sent <- with $ do+                    (k, mr, t) <- from recent+                    where_ $ k >=. val 0+                    pure (k, just (coalesceDefault [mr] (val 0)), t)+                recRecs <- with $ distinct $ do+                    (_, mr, _) <- from sent+                    pure mr+                recRecTx <- with $ do+                    (mr :& b) <- from $ recRecs+                        `innerJoin` table @B+                            `on` do+                                \(mr :& b) -> mr ==. just (b ^. BV)+                    pure (b ^. BK, mr, b ^. BV)+                deduped <- with $ do+                    r@(k, _, _) <- from recRecTx+                    where_ $ not_ $ exists $ do+                        (k2, _, _) <- from sent+                        where_ $ k2 ==. k+                    pure r+                (k, mr, t) <- from $ from sent `union_` from deduped+                pure (k, mr, t)+        insert_ $ B { bK = 1, bV = 3 }+        ret <- select q+        asserting $ do+            ret `shouldMatchList`+                [ (Value 1, Value (Just 3), Value 3)+                ]+     itDb "can refer to the same CTE twice" $ do         let q :: SqlQuery (SqlExpr (Value Int), SqlExpr (Value Int))             q = do@@ -32,4 +125,28 @@         asserting $ do             ret `shouldMatchList`                 [ (Value 1, Value (2 + 3 + 3))+                ]++    itDb "aliases new columns after a union with a CTE reference" $ do+        -- The right branch allocates fewer idents than the left one; the+        -- enclosing query must not reuse the left branch's idents for later+        -- aliases (a variant of issue #299).+        let q :: SqlQuery (SqlExpr (Value Int), SqlExpr (Value Int), SqlExpr (Value Int))+            q = do+                bCte <- with $ do+                    b <- from $ table @B+                    pure (b ^. BK, b ^. BV)+                (k, v, extra) <- from $ do+                    (k, v) <- from $+                        (do+                            b <- from $ table @B+                            pure (b ^. BK, b ^. BV))+                        `union_` from bCte+                    pure (k, v, val (42 :: Int))+                pure (k, v, extra)+        insert_ $ B { bK = 1, bV = 3 }+        ret <- select q+        asserting $ do+            ret `shouldMatchList`+                [ (Value 1, Value 3, Value 42)                 ]

@@ -1663,6 +1663,30 @@                     pure (str, val @Int 1)             asserting noExceptions +        itDb "keeps a CTE declared inside a set operation branch scoped to it" $ do+            -- The branch declaring the CTE must render parenthesized (a bare+            -- WITH after UNION is invalid SQL), and the CTE's idents must not+            -- leak into the enclosing query.+            let lordQuery = do+                    l <- Experimental.from $ table @Lord+                    pure (l ^. LordCounty)+                lordCteQuery = do+                    lordCte <- with lordQuery+                    l <- Experimental.from lordCte+                    pure l+            _ <- select $+                Experimental.from $ do+                    (county, _) <- Experimental.from $ do+                        c <- Experimental.from $ lordQuery `union_` lordCteQuery+                        pure (c, val @Int 1)+                    pure (county, val @Int 2)+            -- The declaring branch may also be the first operand.+            _ <- select $+                Experimental.from $ do+                    c <- Experimental.from $ lordCteQuery `union_` lordQuery+                    pure (c, val @Int 1)+            asserting noExceptions+ testPostgresqlNullsOrdering :: SpecDb testPostgresqlNullsOrdering = do   describe "Postgresql NULLS orderings work" $ do@@ -1812,12 +1836,12 @@         then             runStderrLoggingT $                 createPostgresqlPool-                "host=localhost port=5432 user=esqutest password=esqutest dbname=esqutest"+                "host=127.0.0.1 port=5432 user=esqutest password=esqutest dbname=esqutest"                 4         else             runNoLoggingT $                 createPostgresqlPool-                "host=localhost port=5432 user=esqutest password=esqutest dbname=esqutest"+                "host=127.0.0.1 port=5432 user=esqutest password=esqutest dbname=esqutest"                 4     flip runSqlPool pool $ do         migrateIt