RC30 WHERE time filter Regression

[Jhors2]

Hello,
I upgraded from RC29 to RC30 this morning. Since doing so I am hitting the following parser error for a query that worked in RC29, the query is as follows:

select sum(value) from bps_out WHERE time > now() - 1m AND pop =~ /new_york/ GROUP BY time(60s) fill(0)

This is returning the following error:
error: "error parsing query: aggregate functions with GROUP BY time require a WHERE time clause"

I already changed all of my writers to use the new "time" nomenclature away from "timestamp".

[otoolep]

Thanks for the report @Jhors2. This is not a regression. All GROUP BY queries must now be accompanied by a time range. In this case you will need to modify all existing queries.

You can see the change here:

#2540

[Jhors2]

Thanks @otoolep can you clarify how I should change the specific query I listed above to be the proper syntax?

select sum(value) from bps_out WHERE time > now() - 1m AND pop =~ /new_york/ GROUP BY time(60s) fill

[otoolep]

Actually @Jhors2 -- I see you have a WHERE clause in there -- I was examining the very end of your query and missed that.

@corylanou -- should the query as is work? It has the WHERE clause.

[corylanou]

@otoolep yes this should work. Let me see why it is failing this use case. Looks like some more tests are in order.

[corylanou]

I'm unable to replicate this. Using a query exactly like they above (accept to use my current data) this works on RC30 for me.

> select sum(value) from mem WHERE time > now() - 1m AND host =~ /server01/ GROUP BY time(60s) fill(0)
name: mem
---------
time                    sum
2015-05-13T14:38:00Z    0
2015-05-13T14:39:00Z    0

[Jhors2]

After more testing on my side it appears the parser works correctly for some metrics but not all. Specifically the metrics that don't appear to be working properly I am writing with a microsecond epoch timestamp and identifying the precision as such. If there is a way I can provide my measurements and exact data to the Influx team (preferrably offline) I would love to find root cause.

[neonstalwart]

@Jhors2 how are you specifying the precision? is it defined on each point or is it defined for the whole batch? defining it on the batch does not currently work - see #2513

[Jhors2]

The precision is currently declared per point, not batched. The only batching I am doing is Database declaration and Retention policy.

[beckettsean]

@Jhors2 if acceptable you can email your sample data to support@influxdb.com.

[greglook]

I ran into this too and we had to downgrade back to rc29. I found that using the influx shell reordering the query so that the time clause was at the end would sometimes work, but having the time clause at the beginning would not. Obviously the WHERE clause ordering shouldn't have an effect on whether this error is thrown.

However when I updated our Grafana graphs to change the ordering, I found that in practice most of them just hung forever until they timed out. The "bad" queries would return a 400 immediately, but the rewritten queries never even showed up in the InfluxDB log as completing.

[corylanou]

Can you send me specific queries that do not work? The data will have nothing to do with the parser, as this fires before we even send to the query engine.

[Jhors2]

I have sent some sample data that I am seeing the issue with to the influx support email.
select sum(value) from bps_out WHERE time > now() - 1m AND pop =~ /jfk01/ GROUP BY time(60s) fill(0)

Continues to recreate that problem for me, also the below query recreates the issue:
select mean(value) from bps_out WHERE time > now() - 1m AND pop =~ /jfk01/ GROUP BY time(1m),pop fill(0)

[dneuman64]

I am seeing the same issue, re-ordering the where clause to put the time at the end seems to work for me.
For example this works:

SELECT sum(value)*1000/count(value) FROM "kbps" WHERE deliveryservice='steam-dns' and cachegroup = 'total' and time > now() - 120s GROUP BY time(60s), deliveryservice ORDER BY asc

but this does not:

SELECT sum(value)*1000/count(value) FROM "kbps" WHERE time > now() - 120s AND deliveryservice='steam-dns' and cachegroup = 'total' GROUP BY time(60s), deliveryservice ORDER BY asc

FWIW, I found that if I remove one of the 'and' clauses it works for whatever reason (although not helpful cause I need all the ands):

SELECT sum(value)*1000/count(value) FROM "kbps" WHERE time > now() - 120s AND deliveryservice='steam-dns' GROUP BY time(60s), deliveryservice ORDER BY asc

[corylanou]

@dneuman64 thx for the info. I'm able to replicate this now. Looking into it now.