[0.9.4.1] current vs. intended behavior of LIMIT and possibly broken SLIMIT

[rkuchan]

LIMIT currently limits the number of results returned per the specified measurement:

> SELECT * FROM feet LIMIT 5
name: feet
----------
time                    location        water_level
2015-08-18T00:00:00Z    santa_monica    2.064
2015-08-18T00:00:00Z    coyote_creek    8.12
2015-08-18T00:06:00Z    coyote_creek    8.005
2015-08-18T00:06:00Z    santa_monica    2.116
2015-08-18T00:12:00Z    coyote_creek    7.887

where location is a tag. Not sure if LIMIT is meant to return N points per series.

SLIMIT currently returns the same thing as SELECT *:

> SELECT * FROM feet SLIMIT 5
name: feet
----------
time                    location        water_level
2015-08-18T00:00:00Z    santa_monica    2.064
2015-08-18T00:00:00Z    coyote_creek    8.12

[...everything...]

2015-09-18T21:36:00Z    santa_monica    5.066
2015-09-18T21:42:00Z    santa_monica    4.938

[pauldix]

This is because you don't have a GROUP BY clause with a tag in it. SLIMIT is intended to limit the number of series you get back. LIMIT is for the number of data points in each series.

[pauldix]

The behavior is a bit odd and we may want to consider revisiting it when we start the development cycle for 1.0. I say that one because we don't want to make a breaking change before then.

[beckettsean]

@pauldix is there a document describing the intended behavior of LIMIT and SLIMIT?

[jacek213]

@beckettsean I'm going to continue here, as #4271 has been closed. Referring to the docs (https://influxdb.com/docs/v0.9/query_language/data_exploration.html#limiting-results-per-measurement) I shouldn't be forced to use a GROUP BY clause to get x last records with SLIMIT. Anyway, even if I include GROUP BY * or any tag, it doesn't change a thing as I'm still not able to get the last point.

> SELECT * FROM response_times where service_id = '4' GROUP BY * ORDER BY time DESC SLIMIT 1
name: response_times
tags: datacenter_id=en.london.dc2, service_id=4
time                    code    result  status  type            value
----                    ----    ------  ------  ----            -----
2015-09-29T14:37:00Z    200     success OK      http_probe      0.356571159
2015-09-29T14:24:01Z    200     success OK      http_probe      0.373094439
2015-09-29T14:19:02Z    200     success OK      http_probe      2.932277486
2015-09-29T14:17:01Z    200     success OK      http_probe      0.603325744
2015-09-29T14:13:00Z    200     success OK      http_probe      0.363605818
2015-09-29T14:08:01Z    200     success OK      http_probe      0.419046664
2015-09-29T14:07:00Z    200     success OK      http_probe      0.364543376
2015-09-29T13:58:00Z    200     success OK      http_probe      0.357237578
2015-09-29T13:54:01Z    200     success OK      http_probe      0.559483503
2015-09-29T13:53:01Z    200     success OK      http_probe      0.599297672
2015-09-29T13:41:01Z    200     success OK      http_probe      0.359249184
2015-09-29T13:39:00Z    200     success OK      http_probe      0.593648828
2015-09-29T13:36:00Z    200     success OK      http_probe      0.379364242
2015-09-29T13:34:00Z    200     success OK      http_probe      0.360531749
2015-09-29T13:23:00Z    200     success OK      http_probe      0.36121976
2015-09-29T13:14:00Z    200     success OK      http_probe      0.385750586
2015-09-29T13:13:00Z    200     success OK      http_probe      0.361899493
2015-09-29T13:06:01Z    200     success OK      http_probe      0.537069489
2015-09-29T12:58:01Z    200     success OK      http_probe      0.547598881
2015-09-29T12:54:01Z    200     success OK      http_probe      0.379697417

[jacek213]

Same thing in 0.9.4.2.

[mpergament]

Is it confirmed to be broken? I do not see SLIMIT working nor with 9.6.1 neither with 10.3.1 (GROUP BY is used in query).

[jsternberg]

Please try this again with the latest version. LIMIT will limit the number of points per output series (such as when GROUP BY host is used) and per measurement. SLIMIT and SOFFSET should be working.