{"action":"created","issue":{"url":"https://api.github.com/repos/khibino/haskell-relational-record/issues/19","labels_url":"https://api.github.com/repos/khibino/haskell-relational-record/issues/19/labels{/name}","comments_url":"https://api.github.com/repos/khibino/haskell-relational-record/issues/19/comments","events_url":"https://api.github.com/repos/khibino/haskell-relational-record/issues/19/events","html_url":"https://github.com/khibino/haskell-relational-record/issues/19","id":77050085,"number":19,"title":"Crashing query: Aggregation can incorrectly capture variables","user":{"login":"tomjaguarpaw","id":1951567,"avatar_url":"https://avatars.githubusercontent.com/u/1951567?v=3","gravatar_id":"","url":"https://api.github.com/users/tomjaguarpaw","html_url":"https://github.com/tomjaguarpaw","followers_url":"https://api.github.com/users/tomjaguarpaw/followers","following_url":"https://api.github.com/users/tomjaguarpaw/following{/other_user}","gists_url":"https://api.github.com/users/tomjaguarpaw/gists{/gist_id}","starred_url":"https://api.github.com/users/tomjaguarpaw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomjaguarpaw/subscriptions","organizations_url":"https://api.github.com/users/tomjaguarpaw/orgs","repos_url":"https://api.github.com/users/tomjaguarpaw/repos","events_url":"https://api.github.com/users/tomjaguarpaw/events{/privacy}","received_events_url":"https://api.github.com/users/tomjaguarpaw/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2015-05-16T14:24:25Z","updated_at":"2015-05-16T17:32:34Z","closed_at":null,"body":"SQL does not allow the right-hand clause of an INNER JOIN to reference columns defined in the left-\r\nhand clause.  However, relational-record does not enforce this restriction in the type system.  It is trivial to create a well-typed yet crashing query:\r\n\r\n    import Data.Int (Int32)\r\n    import Database.Relational.Query\r\n\r\n    zero :: Relation () (Int32)\r\n    zero = relation $ return (value 0)\r\n    \r\n    aggregateCaptureVariable :: Projection Flat Int32 -> Relation () Int32\r\n    aggregateCaptureVariable i = relation $ do\r\n      z <- query zero\r\n      wheres (i .=. z)\r\n      return z\r\n    \r\n    foo :: Relation () (Int32, Int32)\r\n    foo = relation $ do\r\n      i <- query zero\r\n      j <- query (aggregateCaptureVariable i)\r\n    \r\n      return ((,) |$| i ! id'\r\n                  |*| j ! id')\r\n    \r\n    main :: IO ()\r\n    main = putStrLn $ show foo ++ \";\"\r\n\r\nThe generated SQL is\r\n\r\n    SELECT ALL T0.f0 AS f0, T2.f0 AS f1 FROM (SELECT ALL 0 AS f0) T0\r\n    INNER JOIN (SELECT ALL T1.f0 AS f0 FROM (SELECT ALL 0 AS f0) T1\r\n    WHERE (T0.f0 = T1.f0)) T2 ON (0=0);\r\n\r\nand Postgres reports\r\n\r\n    ERROR:  invalid reference to FROM-clause entry for table \"t0\"\r\n    LINE 1: ...L T1.f0 AS f0 FROM (SELECT ALL 0 AS f0) T1 WHERE (T0.f0 = T1...\r\n                                                                 ^\r\n    HINT:  There is an entry for table \"t0\", but it cannot\r\n    be referenced from this part of the query.\r\n\r\nNB this is why [Opaleye](https://github.com/tomjaguarpaw/haskell-opaleye/) uses a query arrow arrow instead of a query monad.\r\n"},"comment":{"url":"https://api.github.com/repos/khibino/haskell-relational-record/issues/comments/102657651","html_url":"https://github.com/khibino/haskell-relational-record/issues/19#issuecomment-102657651","issue_url":"https://api.github.com/repos/khibino/haskell-relational-record/issues/19","id":102657651,"user":{"login":"tomjaguarpaw","id":1951567,"avatar_url":"https://avatars.githubusercontent.com/u/1951567?v=3","gravatar_id":"","url":"https://api.github.com/users/tomjaguarpaw","html_url":"https://github.com/tomjaguarpaw","followers_url":"https://api.github.com/users/tomjaguarpaw/followers","following_url":"https://api.github.com/users/tomjaguarpaw/following{/other_user}","gists_url":"https://api.github.com/users/tomjaguarpaw/gists{/gist_id}","starred_url":"https://api.github.com/users/tomjaguarpaw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomjaguarpaw/subscriptions","organizations_url":"https://api.github.com/users/tomjaguarpaw/orgs","repos_url":"https://api.github.com/users/tomjaguarpaw/repos","events_url":"https://api.github.com/users/tomjaguarpaw/events{/privacy}","received_events_url":"https://api.github.com/users/tomjaguarpaw/received_events","type":"User","site_admin":false},"created_at":"2015-05-16T17:32:34Z","updated_at":"2015-05-16T17:32:34Z","body":"NB Not all flavours of SQL support something equivalent to `LATERAL`."}}