{"action":"created","issue":{"url":"https://api.github.com/repos/arangodb/arangodb/issues/7743","repository_url":"https://api.github.com/repos/arangodb/arangodb","labels_url":"https://api.github.com/repos/arangodb/arangodb/issues/7743/labels{/name}","comments_url":"https://api.github.com/repos/arangodb/arangodb/issues/7743/comments","events_url":"https://api.github.com/repos/arangodb/arangodb/issues/7743/events","html_url":"https://github.com/arangodb/arangodb/issues/7743","id":389888362,"node_id":"MDU6SXNzdWUzODk4ODgzNjI=","number":7743,"title":"Query processing discrepancy between Rocks and MMFiles databases","user":{"login":"odinhaus","id":9295064,"node_id":"MDQ6VXNlcjkyOTUwNjQ=","avatar_url":"https://avatars1.githubusercontent.com/u/9295064?v=4","gravatar_id":"","url":"https://api.github.com/users/odinhaus","html_url":"https://github.com/odinhaus","followers_url":"https://api.github.com/users/odinhaus/followers","following_url":"https://api.github.com/users/odinhaus/following{/other_user}","gists_url":"https://api.github.com/users/odinhaus/gists{/gist_id}","starred_url":"https://api.github.com/users/odinhaus/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/odinhaus/subscriptions","organizations_url":"https://api.github.com/users/odinhaus/orgs","repos_url":"https://api.github.com/users/odinhaus/repos","events_url":"https://api.github.com/users/odinhaus/events{/privacy}","received_events_url":"https://api.github.com/users/odinhaus/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-12-11T18:08:50Z","updated_at":"2018-12-11T18:14:52Z","closed_at":null,"author_association":"NONE","body":"## My Environment\r\n\r\n* __ArangoDB Version__:        3.3.13\r\n* __Storage Engine__:             MMFiles vs Rocks\r\n* __Deployment Mode__:       Single Server\r\n\r\n## Component, Query & Data\r\n```\r\nLET length = COUNT( LET page = ( \r\nFOR r in (LET q0 = (FOR r0 IN (LET qbf0 = (\r\n\tFOR bf0 IN DFSW_Encounter\r\n\tFILTER bf0.IsDeleted == false && bf0.Revision == null\r\n\tLET qcf0 = (\r\n\t\tLET eqcf0 = \r\n\t\t(\r\n\t\t\tFOR cf0 IN DFSW_isSubjectOf\r\n\t\t\tFILTER cf0.IsDeleted == false && cf0.Revision == null && cf0._to == bf0._id && cf0.SourceType == \"DFSW.Patient\"\r\n\t\t\tLET cf0O = (\r\n\t\t\t\tFOR v in 1 INBOUND cf0 owns\r\n\t\t\t\tFILTER v.ModelType == 'OrgUnit'\r\n\t\t\t\tLIMIT 1\r\n\t\t\t\tRETURN v\r\n\t\t\t)\r\n\t\t\tLET jqcf0 = \r\n\t\t\t(\r\n\t\t\t\tFOR v in DFSW_Patient\r\n\t\t\t\tFILTER v.IsDeleted == false && v.Revision == null  && cf0._from == v._id && cf0.SourceType == \"DFSW.Patient\"\r\n\t\t\t\tLIMIT 1\r\n\t\t\t\tRETURN v\r\n\t\t\t)\r\n\t\t\tLET ret = MERGE(MERGE(cf0, {\"_owner\": FIRST(cf0O)}), {\"_model\": MERGE(FIRST(jqcf0), {_owner: null})})\r\n\t\t\tRETURN ret\r\n\r\n\t\t)\r\n\t\tFOR edf0 IN eqcf0\r\n\t\tLET df0 = edf0._model\r\n\t\tFILTER df0.IsDeleted == false && df0.Revision == null && ((df0._NameLast_lower LIKE (CONCAT(CONCAT('%', LOWER(\"vera\")), '%'))) OR (df0._NameFirst_lower LIKE (CONCAT(CONCAT('%', LOWER(\"vera\")), '%'))))\r\n\t\tLET df0O = (\r\n\t\t\tFOR v in 1 INBOUND df0 owns\r\n\t\t\tFILTER v.ModelType == 'OrgUnit'\r\n\t\t\tLIMIT 1\r\n\t\t\tRETURN v\r\n\t\t)\r\n\t\tLET ret = MERGE_RECURSIVE(edf0, {\"_model\": {\"_owner\": FIRST(df0O)}})\r\n\t\tRETURN ret\r\n\t)\r\n\tFILTER LENGTH(qcf0) > 0\r\n\tLET bf0O = (\r\n\t\tFOR v in 1 INBOUND bf0 owns\r\n\t\tFILTER v.ModelType == 'OrgUnit'\r\n\t\tLIMIT 1\r\n\t\tRETURN v\r\n\t)\r\n\tLET ret = MERGE(MERGE(bf0, {\"_owner\": FIRST(bf0O)}), {\"_edges\": qcf0})\r\n\tRETURN ret\r\n)\r\nFOR v IN qbf0\r\nRETURN v\r\n) RETURN HAS(r0, \"_edges\") ? UNSET(r0, \"_edges\") : r0)\r\nFOR r in q0\r\nRETURN r\r\n)\r\nRETURN r )\r\nFOR r in page\r\nRETURN r\r\n )\r\nLET page = ( \r\nFOR r in (FOR r in (FOR s2 in (LET q0 = (FOR r0 IN (\tFOR af0 IN DFSW_Encounter\r\n\tFILTER af0.IsDeleted == false && af0.Revision == null\r\n\tLET qbf0 = (\r\n\t\tFOR bf0 IN DFSW_isSubjectOf\r\n\t\tFILTER bf0.IsDeleted == false && bf0.Revision == null && bf0._to == af0._id && bf0.SourceType == \"DFSW.Patient\"\r\n\t\tLET qcf0 = (\r\n\t\t\tFOR cf0 IN DFSW_Patient\r\n\t\t\tFILTER cf0.IsDeleted == false && cf0.Revision == null && cf0._id == bf0._from && ((cf0._NameLast_lower LIKE (CONCAT(CONCAT('%', LOWER(\"vera\")), '%'))) OR (cf0._NameFirst_lower LIKE (CONCAT(CONCAT('%', LOWER(\"vera\")), '%'))))\r\n\t\t\tLET ret = { DFSW_Patient1: cf0, DFSW_isSubjectOf1: bf0}\r\n\t\t\tRETURN ret\r\n\r\n\t\t)\r\n\t\tFILTER LENGTH(qcf0) > 0\r\n\t\tFOR cf00 IN (LENGTH(qcf0) > 0 ? qcf0 : [{DFSW_isSubjectOf1: bf0}])\r\n\t\tRETURN {E: af0}\r\n\t)\r\n\tFILTER LENGTH(qbf0) > 0\r\n\tFOR bf00 IN [{E: af0}]\r\n\tLET ret = MERGE(bf00)\r\n\tSORT ret.E.DateOfService Desc\r\n\tLIMIT 0, 50\r\n\tRETURN ret\r\n\r\n) RETURN r0)\r\nFOR r in q0\r\nRETURN r\r\n)\r\nCOLLECT Key = s2.E._key\r\nOPTIONS { method: \"hash\" } \r\n// start of query projection\r\nLET page = ( \r\nFOR r in (FOR s3 in (LET q0 = (FOR r0 IN (\tFOR af0 IN DFSW_Encounter\r\n\tFILTER af0.IsDeleted == false && af0.Revision == null && af0._key == Key\r\n\tLET qbf0 = (\r\n\t\tFOR bf0 IN DFSW_occursAt\r\n\t\tFILTER bf0.IsDeleted == false && bf0.Revision == null && bf0._from == af0._id && bf0.TargetType == \"DFSW.Facility\"\r\n\t\tLET qcf0 = (\r\n\t\t\tFOR cf0 IN DFSW_Facility\r\n\t\t\tFILTER cf0.IsDeleted == false && cf0.Revision == null && cf0._id == bf0._to\r\n\t\t\tLET ret = { Fac: cf0, DFSW_occursAt1: bf0}\r\n\t\t\tRETURN ret\r\n\r\n\t\t)\r\n\t\tFILTER LENGTH(qcf0) > 0\r\n\t\tFOR cf00 IN (LENGTH(qcf0) > 0 ? qcf0 : [{DFSW_occursAt1: bf0}])\r\n\t\tLET ret = MERGE(cf00, { E: af0 })\r\n\t\tRETURN ret\r\n\r\n\t)\r\n\tLET qdf0 = (\r\n\t\tFOR df0 IN isFor\r\n\t\tFILTER df0.IsDeleted == false && df0.Revision == null && df0._to == af0._id && df0.SourceType == \"DFSW.Procedure\"\r\n\t\tLET qef0 = (\r\n\t\t\tFOR ef0 IN DFSW_Procedure\r\n\t\t\tFILTER ef0.IsDeleted == false && ef0.Revision == null && ef0._id == df0._from\r\n\t\t\tLET ret = { Proc: ef0, isFor1: df0}\r\n\t\t\tRETURN ret\r\n\r\n\t\t)\r\n\t\tFILTER LENGTH(qef0) > 0\r\n\t\tFOR ef00 IN (LENGTH(qef0) > 0 ? qef0 : [{isFor1: df0}])\r\n\t\tLET ret = MERGE(ef00, { E: af0 })\r\n\t\tRETURN ret\r\n\r\n\t)\r\n\tLET qff0 = (\r\n\t\tFOR ff0 IN appliesTo\r\n\t\tFILTER ff0.IsDeleted == false && ff0.Revision == null && ff0._to == af0._id && ff0.SourceType == \"DFSW.CaseLog\"\r\n\t\tLET qgf0 = (\r\n\t\t\tFOR gf0 IN DFSW_CaseLog\r\n\t\t\tFILTER gf0.IsDeleted == false && gf0.Revision == null && gf0._id == ff0._from\r\n\t\t\tLET ret = { CL: gf0, appliesTo1: ff0}\r\n\t\t\tRETURN ret\r\n\r\n\t\t)\r\n\t\tFILTER LENGTH(qgf0) > 0\r\n\t\tFOR gf00 IN (LENGTH(qgf0) > 0 ? qgf0 : [{appliesTo1: ff0}])\r\n\t\tLET ret = MERGE(gf00, { E: af0 })\r\n\t\tRETURN ret\r\n\r\n\t)\r\n\tLET qhf0 = (\r\n\t\tFOR hf0 IN DFSW_participatesIn\r\n\t\tFILTER hf0.IsDeleted == false && hf0.Revision == null && hf0._to == af0._id && hf0.SourceType == \"DFSW.Participant\"\r\n\t\tLET qif0 = (\r\n\t\t\tFOR if0 IN DFSW_Participant\r\n\t\t\tFILTER if0.IsDeleted == false && if0.Revision == null && if0._id == hf0._from && if0.Type == 1\r\n\t\t\tLET ret = { Sur: if0, DFSW_participatesIn1: hf0}\r\n\t\t\tRETURN ret\r\n\r\n\t\t)\r\n\t\tFILTER LENGTH(qif0) > 0\r\n\t\tFOR if00 IN (LENGTH(qif0) > 0 ? qif0 : [{DFSW_participatesIn1: hf0}])\r\n\t\tLET ret = MERGE(if00, { E: af0 })\r\n\t\tRETURN ret\r\n\r\n\t)\r\n\tLET qjf0 = (\r\n\t\tFOR jf0 IN DFSW_isSubjectOf\r\n\t\tFILTER jf0.IsDeleted == false && jf0.Revision == null && jf0._to == af0._id && jf0.SourceType == \"DFSW.Patient\"\r\n\t\tLET qkf0 = (\r\n\t\t\tFOR kf0 IN DFSW_Patient\r\n\t\t\tFILTER kf0.IsDeleted == false && kf0.Revision == null && kf0._id == jf0._from\r\n\t\t\tLET ret = { Pat: kf0, DFSW_isSubjectOf1: jf0}\r\n\t\t\tRETURN ret\r\n\r\n\t\t)\r\n\t\tFILTER LENGTH(qkf0) > 0\r\n\t\tFOR kf00 IN (LENGTH(qkf0) > 0 ? qkf0 : [{DFSW_isSubjectOf1: jf0}])\r\n\t\tLET ret = MERGE(kf00, { E: af0 })\r\n\t\tRETURN ret\r\n\r\n\t)\r\n\tFILTER LENGTH(qbf0) > 0 && LENGTH(qdf0) > 0 && LENGTH(qhf0) > 0 && LENGTH(qjf0) > 0\r\n\tFOR bf00 IN (LENGTH(qbf0) > 0 ? qbf0 : [{E: af0}])\r\n\tFOR df00 IN (LENGTH(qdf0) > 0 ? qdf0 : [{E: af0}])\r\n\tFOR ff00 IN (LENGTH(qff0) > 0 ? qff0 : [{E: af0}])\r\n\tFOR hf00 IN (LENGTH(qhf0) > 0 ? qhf0 : [{E: af0}])\r\n\tFOR jf00 IN (LENGTH(qjf0) > 0 ? qjf0 : [{E: af0}])\r\n\tLET ret = MERGE(bf00, df00, ff00, hf00, jf00)\r\n\tRETURN ret\r\n\r\n) RETURN r0)\r\nFOR r in q0\r\nRETURN r\r\n)\r\nCOLLECT Key2 = s3.E._key\r\nAGGREGATE ReviewStatus = MAX(s3.E.ReviewStatus), Created = MAX(s3.E.Created), Modified = MAX(s3.E.Modified), ExternalId = MAX(s3.E.ExternalId), Status = MAX(s3.E.Status), DateOfService = MAX(s3.E.DateOfService), Room = MAX(s3.E.Room), Location = MAX(s3.E.Location), Details = MAX(s3.E.Details), IsFixedFee = MAX(s3.E.IsFixedFee), ScrubsheetStatus = MAX(s3.E.ScrubsheetStatus), Procedure = MAX(s3.Proc.DescriptionShort), PatientName = MAX(CONCAT(CONCAT(s3.Pat.NameLast, \", \"), s3.Pat.NameFirst)), PatientNameLast = MAX(s3.Pat.NameLast), PatientNameFirst = MAX(s3.Pat.NameFirst), PatientDateOfBirth = MAX(s3.Pat.DateOfBirth), CaseLogKey = MAX(s3.CL._key), CaseLog = MAX(s3.CL), SurgeonName = MAX(CONCAT(CONCAT(s3.Sur.NameLast, \", \"), CONCAT(CONCAT(s3.Sur.Title, \" \"), s3.Sur.NameFirst))), SurgeonNameLast = MAX(s3.Sur.NameLast), SurgeonNameFirst = MAX(s3.Sur.NameFirst)\r\nOPTIONS { method: \"hash\" } \r\nRETURN  { ReviewStatus: ReviewStatus, Key: Key, Created: Created, Modified: Modified, ExternalId: ExternalId, Status: Status, DateOfService: DateOfService, Room: Room, Location: Location, Details: Details, IsFixedFee: IsFixedFee, ScrubsheetStatus: ScrubsheetStatus, Procedure: Procedure, PatientName: PatientName, PatientNameLast: PatientNameLast, PatientNameFirst: PatientNameFirst, PatientDateOfBirth: PatientDateOfBirth, CaseLogKey: CaseLogKey, CaseLog: CaseLog, SurgeonName: SurgeonName, SurgeonNameLast: SurgeonNameLast, SurgeonNameFirst: SurgeonNameFirst } \r\n)\r\nRETURN r )\r\nFOR r in page\r\nRETURN r\r\n\r\n// end of query projection\r\n\r\n)\r\nSORT r.DateOfService Desc\r\nRETURN r\r\n)\r\nRETURN r )\r\nRETURN { Count: length, Results: LENGTH(page) > 0 ? page : [] }\r\n```\r\n\r\n## Steps to reproduce\r\n\r\n1.  Run the above query on MMFiles database and Rocks database\r\n2. Compare the values for the returned Key property in the projected results\r\n\r\n__Problem__:\r\nWhen run on MMFiles, the Key values are correct.  When  run on Rocks, the Key value always shows the last-most Key value in the initial (outermost) COLLECT group, so all results are returned with the same Key value, which is wrong\r\n\r\n__Workaround__:\r\nIf you change the final projection from:\r\n`RETURN  { ReviewStatus: ReviewStatus, Key: Key, Created: Created, Modified: Modified,...`\r\nto\r\n`RETURN  { ReviewStatus: ReviewStatus, Key: Key2, Created: Created, Modified: Modified,...`\r\nthen the correct results are returned for both engines.\r\n\r\nSorry for the length of the query, I just don't have time to try to boil this down to the simplest example, but it seems that when running sub-grouped queries, the Rocks query processor loses the correct group scope from the outer groupings.\r\n\r\nIs this a known issue, and has it already been fixed, or do I need to change my AQL query builder to always use the grouping scope from the current level?\r\n"},"comment":{"url":"https://api.github.com/repos/arangodb/arangodb/issues/comments/446304696","html_url":"https://github.com/arangodb/arangodb/issues/7743#issuecomment-446304696","issue_url":"https://api.github.com/repos/arangodb/arangodb/issues/7743","id":446304696,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0NjMwNDY5Ng==","user":{"login":"jsteemann","id":1423118,"node_id":"MDQ6VXNlcjE0MjMxMTg=","avatar_url":"https://avatars0.githubusercontent.com/u/1423118?v=4","gravatar_id":"","url":"https://api.github.com/users/jsteemann","html_url":"https://github.com/jsteemann","followers_url":"https://api.github.com/users/jsteemann/followers","following_url":"https://api.github.com/users/jsteemann/following{/other_user}","gists_url":"https://api.github.com/users/jsteemann/gists{/gist_id}","starred_url":"https://api.github.com/users/jsteemann/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jsteemann/subscriptions","organizations_url":"https://api.github.com/users/jsteemann/orgs","repos_url":"https://api.github.com/users/jsteemann/repos","events_url":"https://api.github.com/users/jsteemann/events{/privacy}","received_events_url":"https://api.github.com/users/jsteemann/received_events","type":"User","site_admin":false},"created_at":"2018-12-11T18:14:52Z","updated_at":"2018-12-11T18:14:52Z","author_association":"CONTRIBUTOR","body":"@odinhaus : it may be really hard to reproduce the problem without any info about the underlying collections, indexes and data. \r\nIs it possible to create a smaller example for this, and/or provide information about the index, and/or example data?"}}
{"actor":{"display_login":"jsteemann"}}