# AQL - what do you expect as results for these example **Category:** [AQL](https://discourse.openehr.org/c/aql/43) **Created:** 2020-11-16 13:08 UTC **Views:** 544 **Replies:** 6 **URL:** https://discourse.openehr.org/t/aql-what-do-you-expect-as-results-for-these-example/1104 --- ## Post #1 by @bna # Topic The following and quite simple AQL will be evaluated with different datasets (A,B,C,D) using either __AND__ or __OR__ . For each case we have defined alternative and possible resultsets. Look at the examples. Which of the answers do you expect? Are there something missing? Base AQL to be used for all the examples. ``` select o, e from composition c contains (observation o AND/OR evaluation e) ``` Dataset A ``` composition o1, o2 e1, e2 ``` ## Alt A - `AND` ``` AND o1, e1 o1, e2 o2, e1 o2, e2 ``` ## Alt A - OR1 ``` OR (alt1) o1, e1 o1, e2 o2, e1 o2, e2 ``` ## Alt A - OR2 ``` OR (alt2) o1 e1 o2 e2 ``` # Dataset B ``` composition o1, o2 e1 ``` ## Alt B - AND ``` AND o1, e1 o2, e1 ``` ## Alt B - OR 1 ``` OR (alt1) o1, , e1 o2, ``` ## Alt B - OR 2 ``` OR (alt2) o1, e1 o2, e1 ``` # Dataset C ``` composition o1, o2 ``` ## Alt C - AND ``` AND ``` ## Alt C - OR1 ``` OR (alt1) o1, o2, ``` ## Alt C - OR2 ``` OR (alt2) o1 o2 ``` # Dataset D ``` composition o1 e1 ``` ## Alt D - AND ``` AND o1, e1 ``` ## Alt D - OR 1 ``` OR (alt1) o1, e1 ``` ## Alt D - OR2 ``` OR (alt2) o1, e1, ``` --- ## Post #2 by @ian.mcnicoll [quote="bna, post:1, topic:1104"] ``` composition o1, o2 e1, e2 ``` [/quote] Is this 2 compositions or a single composition with all 4 archetypes, sorry being dumb :frowning: --- ## Post #3 by @thomas.beale [quote="bna, post:1, topic:1104"] ``` composition o1, o2 e1, e2 ``` [/quote] I don't understand what this construction means - does it mean a Composition containing 4 Entries (i.e. 2 x Obs, 2 x Evals)? --- ## Post #4 by @bna Hi I will rewrite this post with the syntax and formalism learned from @Seref. Give me a day 🤔 --- ## Post #5 by @ian.mcnicoll I'm glad I was not the only one who was bit confused - Nordic minimalism? ;) --- ## Post #6 by @Seref @bna here it is (bah, I realised I included C in the select. I won't make a difference here though..) ![image|690x314](upload://9lD6lIkmalshIzjDF1s308UXWbZ.png) `A: AND TPQ` ![image|450x174](upload://3jgH05eYY8PrlrqBHTi6xttU4aF.png) `A: OR TPQ` ![image|439x258](upload://8woHjwXmAdRiaffx6HfKTT4EKlp.png) `B: AND TPQ` ![image|387x101](upload://vHOcl4yca3AITW7OoTnQJ9dUaMh.png) `B: OR TPQ` ![image|382x168](upload://my2ra439NnXHohswgLp72z2RPXy.png) `C: AND TPQ` EMPTY SET `C: OR TPQ` ![image|371x93](upload://1sEqQzxfI1Ns8TOQ9laDJJ05nWQ.png) `D: AND TPQ` ![image|377x70](upload://6QW0cNA77pgHZis8wtMWRrIzfDt.png) `D: OR TPQ` ![image|379x117](upload://5kJtQBgHkDmg1LtQngtKsRoGTxc.png) The permutative behaviour of OR operator is that of UNION: Matches of operand 1 + Matches of operand 2 + Matches of operand 1 & 2 There is very little to discuss about the behaviour of AND, it feels natural to everyone. As I mentioned today, OR is a completely different beast, because it is hard to identify rows with the same semantics from a user perspective. Sometimes there is nothing to eliminate: look at the result of OR query on A. All the rows are different and you can't squash/pack anything there, or throw any rows away. Based on the structure of the input tree (actual data) we may have some rows having the same semantics and we can exclude those from the results, but there is none here. @matijap very interested in your view on that particular result set. I hope I did not mess something up while copying this from paper to screen, I usually use pen and paper to do this. --- ## Post #7 by @thomas.beale I understand the FROM clause as a pattern matcher to filter the *total database* to obtain the *dataset* on which the WHERE, then the SELECT are run. Under this logic, `SELECT o, e FROM composition c CONTAINS (observation o OR evaluation e)` applied to instance structure A would mean the following execution: * FROM processing: -> C1 * WHERE processing: ( nothing ) * SELECT processing: -> ``` ------ O1, E1 O1, E2 O2, E1 O2, E2 ------ ``` This is the same answer as @bna's Alt A - OR1, but only the bottom half only of @Seref's table 'A: OR TPQ'. Accordingly, there are no rows of the form `O1, -`, which seems correct to me, because the structure of A does not look like that. If we imagine a database containing 100 Compositions, including A, B, C and D (call them C1 ..C4), being the only ones with Evals or Obs instances, then the query applied to that database will process as follows: * FROM processing: -> C1, C2, C3, C4 * SELECT processing: -> ``` ------------ C1.O1, C1.E1 C1.O1, C1.E2 C1.O2, C1.E1 C1.O2, C1.E2 C2.O1, C2.E1 C2.O2, C2.E1 C3.O1, - C3.O2, - C4.O1, C4.E1 ------------- ``` The above is the same as @bna's, if you choose a A-OR1, B-OR2, C-OR1, and D-OR2. This approach reduces the permutations by assuming that once OR matching is done, generating a list of *matched top-level container objects*, you just perform normal WHERE and SELECT processing on that list. The use of OR in the first step doesn't create any 'cloned views' containing partial children, which is what some of Bjorn's and (I think) all of Seref's do. This is just one possible AQL semantics which might be useful to think about. --- **Canonical:** https://discourse.openehr.org/t/aql-what-do-you-expect-as-results-for-these-example/1104 **Original content:** https://discourse.openehr.org/t/aql-what-do-you-expect-as-results-for-these-example/1104