Simple query with multiple where clauses on field keys

#1

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)

returns NO results

Thanks in advance.

#2

@nate Can you share some example writes? Also I would try removing the () from around your different WHERE clauses.

#3

Thank you for the reply here is some results form the singular queries. I tried to take out the ( ) and tried " to ’ but that didn’t help.

Air_Temperature
2017-05-23T21:05:30Z 79.24310211703497
2017-05-23T21:06:18Z 81.03808274086032
2017-05-23T21:06:30Z 79.49482221036084
2017-05-23T21:07:18Z 80.64685157705607
2017-05-23T21:07:30Z 79.11781227928316
2017-05-23T21:08:18Z 81.03808274086032
2017-05-23T21:08:30Z 79.87528867087917
2017-05-23T21:09:17Z 81.56553286488625
2017-05-23T21:09:29Z 80.25927631459997
2017-05-23T21:10:16Z 81.43304000401437
2017-05-23T21:10:29Z 80.64685157705607
2017-05-23T21:11:16Z 81.16931762777398
2017-05-23T21:11:29Z 79.6212571482264
2017-05-23T21:12:16Z 81.43304000401437
2017-05-23T21:12:29Z 79.74807850362363
2017-05-23T21:13:17Z 82.64100062484556
2017-05-23T21:13:28Z 80.51725702236621
2017-05-23T21:14:17Z 82.23442362947851
2017-05-23T21:14:29Z 80.90726184863634
2017-05-23T21:15:18Z 81.83179555137822
2017-05-23T21:15:29Z 80.00289006605101
2017-05-23T21:16:18Z 81.43304000401437
2017-05-23T21:16:29Z 79.87528867087917
2017-05-23T21:17:18Z 81.43304000401437
2017-05-23T21:17:29Z 79.87528867087917
2017-05-23T21:18:18Z 81.16931762777398
2017-05-23T21:18:29Z 79.49482221036084
2017-05-23T21:19:17Z 80.90726184863634
2017-05-23T21:19:29Z 78.99289949398414
2017-05-23T21:20:16Z 80.77685232724556
2017-05-23T21:20:29Z 79.49482221036084
2017-05-23T21:21:16Z 80.90726184863634
2017-05-23T21:21:29Z 78.99289949398414
2017-05-23T21:22:16Z 80.77685232724556

Bulb_Temperature
2017-05-23T20:41:40Z 66.201224
2017-05-23T20:42:40Z 66.140816
2017-05-23T20:43:40Z 66.26163200000002
2017-05-23T20:44:39Z 66.32204000000002
2017-05-23T20:45:39Z 66.26163200000002
2017-05-23T20:46:40Z 66.201224
2017-05-23T20:47:40Z 66.080408
2017-05-23T20:48:41Z 66.442856
2017-05-23T20:49:40Z 66.32204000000002
2017-05-23T20:50:40Z 66.32204000000002
2017-05-23T20:51:40Z 66.26163200000002
2017-05-23T20:52:39Z 66.38244800000001
2017-05-23T20:53:39Z 66.442856
2017-05-23T20:54:40Z 66.56367200000003
2017-05-23T20:55:40Z 66.62408000000002
2017-05-23T20:56:40Z 66.62408000000002
2017-05-23T20:57:41Z 66.56367200000003
2017-05-23T20:58:41Z 66.442856
2017-05-23T20:59:40Z 66.503264
2017-05-23T21:00:39Z 66.32204000000002
2017-05-23T21:01:39Z 66.02000000000001
2017-05-23T21:02:40Z 66.080408
2017-05-23T21:03:40Z 65.89918400000002
2017-05-23T21:04:40Z 65.83877600000002
2017-05-23T21:05:40Z 65.65755200000001
2017-05-23T21:06:40Z 65.59714400000001
2017-05-23T21:07:41Z 65.53673600000002
2017-05-23T21:08:41Z 65.476328
2017-05-23T21:09:39Z 65.476328
2017-05-23T21:10:39Z 65.59714400000001
2017-05-23T21:11:40Z 65.476328
2017-05-23T21:12:40Z 65.476328
2017-05-23T21:13:40Z 65.71796
2017-05-23T21:14:40Z 65.83877600000002
2017-05-23T21:15:40Z 65.71796
2017-05-23T21:16:41Z 65.77836800000003
2017-05-23T21:17:40Z 65.83877600000002
2017-05-23T21:18:40Z 65.77836800000003
2017-05-23T21:19:40Z 65.71796
2017-05-23T21:20:40Z 65.59714400000001
2017-05-23T21:21:40Z 65.71796

#4

Hello Nate,

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.

SELECT Leaving_Evaporator_Dry_Bulb_Temperature, Outdoor_Air_Temperature FROM McDonalds_Cofino_Group WHERE ("Outdoor_Air_Temperature" > 65 AND "Outdoor_Air_Temperature" < 93) LIMIT 10

name: McDonalds_Cofino_Group
time                Leaving_Evaporator_Dry_Bulb_Temperature Outdoor_Air_Temperature
----                --------------------------------------- -----------------------
1494438470000000000                                         79.6212571482264
1494438502000000000                                         76.57044118189583
1494438530000000000                                         79.74807850362363
1494438561000000000                                         76.92492851093579
1494438589000000000                                         79.87528867087917
1494438621000000000                                         76.68826812046248
1494438649000000000                                         79.36877131717426
1494438682000000000                                         76.21895033248457
1494438709000000000                                         79.36877131717426
1494438743000000000                                         76.80642991614772
SELECT Leaving_Evaporator_Dry_Bulb_Temperature, Outdoor_Air_Temperature FROM McDonalds_Cofino_Group WHERE "Leaving_Evaporator_Dry_Bulb_Temperature" <= 80 LIMIT 10
name: McDonalds_Cofino_Group
time                Leaving_Evaporator_Dry_Bulb_Temperature Outdoor_Air_Temperature
----                --------------------------------------- -----------------------
1494438469000000000 70.61100800000003                       
1494438530000000000 70.913048                               
1494438589000000000 71.15468000000001                       
1494438649000000000 71.39631200000002                       
1494438709000000000 71.51712800000001                       
1494438769000000000 71.87957600000001                       
1494438829000000000 71.93998400000001                       
1494438889000000000 71.033864                               
1494438949000000000 67.288568                               
1494439009000000000 64.26816800000003

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.

1 Like
#5

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.

Thank you.

1 Like