I am trying to run a simple query of all records (no time span) that contains multiple where clauses on field keys . Can someone help me on what I am doing wrong?
SELECT “Air_Temperature” FROM test WHERE (“Air_Temperature” > 65 AND “Air_Temperature” < 93)
returns many results
SELECT “Bulb_Temperature” FROM test WHERE (“Bulb_Temperature” > 60 AND “Bulb_Temperature” < 85)
returns many results
SELECT “Air_Temperature”,“Bulb_Temperature” FROM test WHERE (“Bulb_Temperature” > 60 AND “Bulb_Temperature” < 85) AND (“Air_Temperature” > 65 AND “Air_Temperature” < 93)
returns NO results
SELECT “Air_Temperature”,“Bulb_Temperature” FROM test WHERE (“Bulb_Temperature” > 60 AND “Bulb_Temperature” < 85) OR (“Air_Temperature” > 65 OR “Air_Temperature” < 93)
Thanks for your question and it was very helpful to have access to the data so we could help you out, so thank you for setting that up. This is a confusing issue and at first I have to admit I too was a stumped about why this wouldn’t work; However running each query separately without the OR statement gave me some clues. Let me try explain why this doesn’t work.
The issue you’re experiencing lies in the fact that a query won’t compute unless a point contains all the fields which you’re performing the WHERE statement against, otherwise the query silently fails.
If you take a look at the output from both queries you will see that each query returns points which only contain either the Outdoor_Air_Temperature field or the Leaving_Evaporator_Dry_Bulb_Temperature field. This provided me with a few clues to what may be the issue.
So the reason why the following statement doesn’t work is because all the points have only one of the fields you’re querying for.
SELECT Leaving_Evaporator_Dry_Bulb_Temperature, Outdoor_Air_Temperature FROM McDonalds_Cofino_Group WHERE ("Outdoor_Air_Temperature" > 65 AND "Outdoor_Air_Temperature" < 93) OR ("Leaving_Evaporator_Dry_Bulb_Temperature" <= 80) LIMIT 10
I understand this doesn’t seem very logical and it would be helpful if you were provided with some kind of warning when this happens, so I will raise an issue in Github for this and provide you with a link.
In this situation I would run both queries separately and then merge the output in my code base after the fact, is that a possibility ?
I hope this answers your initial question and feel free to ask us if you need any more assistance.
I really appreciate the time you took to help me understand this condition. It would be great if in the future the database could handle these type of queries. I can do what you recommend and run them all separately and combine them in the application logic, that is an option.
I am curious if it would be possible to run these types of queries, where the data is in this condition, if the data was down sampled to an increment greater than milliseconds, perhaps 1 to 2 second, etc. Do you think that would be a viable strategy? Or would I just end up in the sample problem I have here?
And if so any examples or guidance that you might have would be greatly appreciated, as I am new to influx.
