Continuation…
Additionally, I went back and found an old testing issue #773 (one of a number around search – but this one ran a while and got to the root with, I think, a reasonable explanation:
Found in UAT whilst testing
Issue Raised
Query:
Clinical_Note.Clinical_Note_Text != “YYYY” AND (Clinical_Note.Clinical_Note_Text = “brother ” OR Clinical_Note.Clinical_Note_Text = “sister”)
returns results including:
Attempted to visit, saw her sister YYYYY who reported that she had been taken into AAAAA Community hospital to have her leg assessed as she has an ulcer. Will call later in the week to re arrange
It also returns results not including either “sister” or “brother”
Comment 1 From Tester
Alternative inappropriate result mentioned above:
Hi Please could you send an appt out to this lady to say I will visit her at home on 29/01/14 at 10.00 I have been unable to get hold of her daughter Thank you C
Comment 2 From Tester
I did try numerous alternatives such as:
((Clinical_Note.Clinical_Note_Text != “YYYYY”) AND (Clinical_Note.Clinical_Note_Text = “brother” OR Clinical_Note.Clinical_Note_Text = “sister”))
that did not work.
When using brother or sister individually, such as:
Clinical_Note.Clinical_Note_Text != “YYYYY” AND Clinical_Note.Clinical_Note_Text = “brother”
the query works as expected only returning results the do contain ‘brother’ and do not contain ‘YYYYY’
Comment 3 From Developer
I think I’ve finally managed to get my head around this and some of the related issues it presents.
Here’s the problem query (converted to Rio structure because that’s what our code tests use for search):
progress_notes.Text != “YYYYY” AND (progress_notes.Text = “brother” OR progress_notes.Text = “sister”)
The problem is based on the assumption that this query should mean: find all patients who have a single progress_notes.Text value that does not contain YYYYY but does contain at least one of “brother” or “sister”. (a)
The code is currently interpreting it as: find all patients who have a progress_notes.Text value without YYYYY and also have a (possibly different) progress_notes.Text value that contains at least one of “brother” or “sister”. (b)
I think I’ve finally worked out a way to change the code so that it interprets the query as (a), based on assuming that AND clauses outside of brackets should always be merged into sub-queries within brackets if related to the same table.
However this now raises the question…
How do you ask the query explained in (b) above? I can’t think of a query structure that would represent it. A possibly better need for it:
Diagnosis.Diagnosis = “f4” AND ((Diagnosis.Diagnosis = “f3” AND Diagnosis.Diagnosis_Start_Date <= “2015-01-01” AND Diagnosis.Diagnosis_Start_Date >= “2012-01-01”) OR (Diagnosis.Diagnosis = “f3” AND Diagnosis.Diagnosis_Start_Date <= “2015-01-01”))
Currently it means to find patients who have an “f3” diagnosis that matches the given dates and has a (possibly separate) “f4” diagnosis. This sounds like quite a sensible thing to want.
If we change our logic to fix this bug, then this will change to finding a patient that has a *single* diagnosis that matches “f4” and “f3 within given dates”, which is probably wrong.
However, looking at the problem the other way around – ie can I re-work the problem query here into a different form so that it already means (a), I think I can:
(progress_notes.Text != “YYYYY” AND progress_notes.Text = “brother”) OR (progress_notes.Text != “YYYYY” AND progress_notes.Text = “sister”)
Hence, after far too long trying to understand things and work out how to adapt the code, I’m now thinking we may not want to!?
Comment 4 From Tester
Just to say that I feel the above explanation is excellent and I support the decision not to change code to enable the initial interpretation of the query to act as we all initially expected.
To offer an alternative explanation- if the same field is referenced within brackets then that is the same individual instance of the field, once outside that bracket then a different instance of the field will be considered by the query