{"action":"created","issue":{"url":"https://api.github.com/repos/quasar-analytics/quasar/issues/2659","repository_url":"https://api.github.com/repos/quasar-analytics/quasar","labels_url":"https://api.github.com/repos/quasar-analytics/quasar/issues/2659/labels{/name}","comments_url":"https://api.github.com/repos/quasar-analytics/quasar/issues/2659/comments","events_url":"https://api.github.com/repos/quasar-analytics/quasar/issues/2659/events","html_url":"https://github.com/quasar-analytics/quasar/issues/2659","id":251708686,"number":2659,"title":"LEFT OUTER JOIN in MongoDB does not compile down to using $lookup aggregation","user":{"login":"dugdun","id":828514,"avatar_url":"https://avatars2.githubusercontent.com/u/828514?v=4","gravatar_id":"","url":"https://api.github.com/users/dugdun","html_url":"https://github.com/dugdun","followers_url":"https://api.github.com/users/dugdun/followers","following_url":"https://api.github.com/users/dugdun/following{/other_user}","gists_url":"https://api.github.com/users/dugdun/gists{/gist_id}","starred_url":"https://api.github.com/users/dugdun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dugdun/subscriptions","organizations_url":"https://api.github.com/users/dugdun/orgs","repos_url":"https://api.github.com/users/dugdun/repos","events_url":"https://api.github.com/users/dugdun/events{/privacy}","received_events_url":"https://api.github.com/users/dugdun/received_events","type":"User","site_admin":false},"labels":[{"id":493374626,"url":"https://api.github.com/repos/quasar-analytics/quasar/labels/topic:%20MongoDB","name":"topic: MongoDB","color":"fef2c0","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2017-08-21T16:20:10Z","updated_at":"2017-08-21T16:24:31Z","closed_at":null,"body":"I am unable to get any SQL^2 query that contains a `LEFT OUTER JOIN` query to compile down and only use the aggregation frameoworks `$lookup` operator. Every test I've ran has made an `aggregate()` call on the first table and a `mapReduce()` call on the second table. Both of these will output their results to a temporary collection and then a final `aggregate()` call will be made on the temporary collection to output the results.\r\n\r\nHere is a simple set of data and a query for testing.\r\n\r\n**number** collection:\r\n\r\n```\r\ndb.numbers.insert({\"a\": 1})\r\ndb.numbers.insert({\"a\": 2})\r\ndb.numbers.insert({\"a\": 3})\r\ndb.numbers.insert({\"a\": 4})\r\ndb.numbers.insert({\"a\": 5})\r\ndb.numbers.insert({\"a\": 7})\r\n```\r\n\r\n**numbers2** collection\r\n\r\n```\r\ndb.numbers.insert({\"a\": 1})\r\ndb.numbers.insert({\"a\": 2})\r\ndb.numbers.insert({\"a\": 3})\r\ndb.numbers.insert({\"a\": 4})\r\ndb.numbers.insert({\"a\": 5})\r\ndb.numbers.insert({\"a\": 6})\r\n```\r\n\r\n**SQL^2 query** for testing\r\n\r\n```\r\nselect\r\n        numbers.a as aval\r\n    ,   numbers2.a as bval\r\nfrom\r\n        `/mongodb-3.4/test/numbers`\r\nleft outer join\r\n        `/mongodb-3.4/test/numbers2`\r\n    on\r\n        numbers.a = numbers2.a\r\n```\r\n\r\nI do have indexes on both collections, and I believe I have met all other criteria mentioned in our documentation:\r\n\r\n- Must be running MongoDB 3.2 or newer.\r\n- One collection must use an indexed field.\r\n- That collection must not be sharded.\r\n- Both collections must be in the same database.\r\n- Match must be an equijoin, based on equality only (a.field = b.field is ok, a.field < b.field is not).\r\n\r\nHere is what the Quasar 20.14.9 REPL shows as the MongoDB queries:\r\n\r\n```\r\n💪 $ select numbers.a as aval , numbers2.a as bval from `/mongodb-3.4/test/numbers` left outer join `/mongodb-3.4/test/numbers2` on numbers.a = numbers2.a\r\nMongoDB:\r\ndb.numbers.aggregate(\r\n  [\r\n    { \"$group\": { \"__tmp0\": { \"$push\": \"$$ROOT\" }, \"_id\": { \"0\": \"$a\" } } },\r\n    {\r\n      \"$project\": {\r\n        \"_id\": true,\r\n        \"value\": { \"left\": \"$__tmp0\", \"right\": { \"$literal\": [] }, \"_id\": \"$_id\" }\r\n      }\r\n    },\r\n    { \"$out\": \"tmp.gen_da579267_0\" }],\r\n  { \"allowDiskUse\": true });\r\ndb.numbers2.mapReduce(\r\n  function () {\r\n    emit.apply(\r\n      null,\r\n      (function (key, value) { return [{ \"0\": value.a }, { \"left\": [], \"right\": [value] }] })(\r\n        this._id,\r\n        this))\r\n  },\r\n  function (key, values) {\r\n    var result = { \"left\": [], \"right\": [] };\r\n    values.forEach(\r\n      function (value) {\r\n        result.left = result.left.concat(value.left);\r\n        result.right = result.right.concat(value.right)\r\n      });\r\n    return result\r\n  },\r\n  {\r\n    \"out\": { \"reduce\": \"tmp.gen_da579267_0\", \"db\": \"test\", \"nonAtomic\": true }\r\n  });\r\ndb.tmp.gen_da579267_0.aggregate(\r\n  [\r\n    { \"$match\": { \"value.left\": { \"$not\": { \"$size\": NumberInt(\"0\") } } } },\r\n    {\r\n      \"$project\": {\r\n        \"left\": \"$value.left\",\r\n        \"right\": {\r\n          \"$cond\": [\r\n            {\r\n              \"$eq\": [{ \"$size\": \"$value.right\" }, { \"$literal\": NumberInt(\"0\") }]\r\n            },\r\n            { \"$literal\": [{  }] },\r\n            \"$value.right\"]\r\n        }\r\n      }\r\n    },\r\n    { \"$unwind\": \"$left\" },\r\n    { \"$unwind\": \"$right\" },\r\n    { \"$limit\": NumberLong(\"11\") },\r\n    {\r\n      \"$project\": {\r\n        \"aval\": {\r\n          \"$cond\": [\r\n            {\r\n              \"$and\": [\r\n                { \"$lte\": [{ \"$literal\": {  } }, \"$left\"] },\r\n                { \"$lt\": [\"$left\", { \"$literal\": [] }] }]\r\n            },\r\n            \"$left.a\",\r\n            { \"$literal\": undefined }]\r\n        },\r\n        \"bval\": {\r\n          \"$cond\": [\r\n            {\r\n              \"$and\": [\r\n                { \"$lte\": [{ \"$literal\": {  } }, \"$right\"] },\r\n                { \"$lt\": [\"$right\", { \"$literal\": [] }] }]\r\n            },\r\n            \"$right.a\",\r\n            { \"$literal\": undefined }]\r\n        }\r\n      }\r\n    },\r\n    { \"$project\": { \"aval\": true, \"bval\": true, \"_id\": false } }],\r\n  { \"allowDiskUse\": true });\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/quasar-analytics/quasar/issues/comments/323790826","html_url":"https://github.com/quasar-analytics/quasar/issues/2659#issuecomment-323790826","issue_url":"https://api.github.com/repos/quasar-analytics/quasar/issues/2659","id":323790826,"user":{"login":"jsantos17","id":677310,"avatar_url":"https://avatars0.githubusercontent.com/u/677310?v=4","gravatar_id":"","url":"https://api.github.com/users/jsantos17","html_url":"https://github.com/jsantos17","followers_url":"https://api.github.com/users/jsantos17/followers","following_url":"https://api.github.com/users/jsantos17/following{/other_user}","gists_url":"https://api.github.com/users/jsantos17/gists{/gist_id}","starred_url":"https://api.github.com/users/jsantos17/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jsantos17/subscriptions","organizations_url":"https://api.github.com/users/jsantos17/orgs","repos_url":"https://api.github.com/users/jsantos17/repos","events_url":"https://api.github.com/users/jsantos17/events{/privacy}","received_events_url":"https://api.github.com/users/jsantos17/received_events","type":"User","site_admin":false},"created_at":"2017-08-21T16:24:31Z","updated_at":"2017-08-21T16:24:31Z","body":"Might be a duplicate of #2385. Would you mind checking that one?"}}
{"actor":{"display_login":"jsantos17"}}