[0.10.0] Alias doesn't work when used with GROUP BY *

[ivanscattergood]

Hi,

I am trying to alias a column name and it doesn't work unless I use a function.

Select "max-disk-usage" as "value" from "SYSTEM_MSG-SPOOL" WHERE time >= '2016-01-13 15:07:35.326' and time < '2016-02-07 15:07:35.326' GROUP BY * limit 1
name: SYSTEM_MSG-SPOOL
tags: ROUTER_NAME=sol-ldn-qagc2
time max-disk-usage

---

1454666402940000000 50000

But if I use mean then it works

Select mean("max-disk-usage") as "value" from "SYSTEM_MSG-SPOOL" WHERE time >= '2016-01-13 15:07:35.326' and time < '2016-02-07 15:07:35.326' GROUP BY * limit 1
name: SYSTEM_MSG-SPOOL
tags: ROUTER_NAME=sol-ldn-qagc2
time value

---

1452697655326000000 50000

[rossmcdonald]

@ivanscattergood What version of InfluxDB and operating system/distribution are you using?

[ivanscattergood]

[root@influx ~]# uname -a
Linux influx.bdit 2.6.32-504.el6.x86_64 #1 SMP Wed Oct 15 04:27:16 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

[root@influx ~]# influx
Visit https://enterprise.influxdata.com to register for updates, InfluxDB server management, and monitoring.
Connected to http://localhost:8086 version 0.10.0
InfluxDB shell 0.10.0

[rossmcdonald]

@ivanscattergood I'm unable to reproduce this issue with some sample Telegraf data on 0.10.0:

> select usage_user, usage_user as "value" from telegraf."default".cpu where time > now() - 30s
name: cpu
---------
time            usage_user      value
1454971690000000000 0           0
1454971690000000000 0           0
1454971700000000000 0.10030090270812196 0.10030090270812196
1454971700000000000 0.10030090270812196 0.10030090270812196
1454971710000000000 0.1004016064257003  0.1004016064257003
1454971710000000000 0.1004016064257003  0.1004016064257003

So it may have something to do with your data or your environment. Is this a clustered setup, or single node? Would it be possible to provide us with sample data to reproduce on?

[ivanscattergood]

Hi,
The Data was originally in 0.9.6 format, and it was an in-place upgrade, so that data might still be in BZ1 format.

Let me investigate further

[rossmcdonald]

Ah, okay. You may want to try converting the shards using the influx_tsm conversion tool and see if the problem persists.

[ivanscattergood]

Cool, thanks for the help @rossmcdonald

[zstyblik]

@rossmcdonald please, can you give it another look? I'm able to reproduce this with v0.10.0 stable. The GROUP BY * is important part of the query.

select "load1" as "value" from "system" WHERE time >= '2016-01-13 15:07:35.326' and time < '2016-02-07 15:07:35.326' GROUP BY * limit 1

system
dc:docker, host:XXX
time     load1
2016-02-05T16:32:20Z    0.08

system
dc:docker, host:YYY
time     load1
2016-02-05T16:13:00Z    0.25

[rossmcdonald]

@zstyblik Good catch. It does seem that the GROUP BY is the cause:

> select usage_user, usage_user as "value" from telegraf."default".cpu where time > now() - 30s group by *
name: cpu
tags: cpu=cpu-total, host=kapacitor1
time            usage_user
----            ----------
1455024376000000000 0
1455024386000000000 0
1455024396000000000 0.10020040080160546


name: cpu
tags: cpu=cpu0, host=kapacitor1
time            usage_user
----            ----------
1455024376000000000 0
1455024386000000000 0
1455024396000000000 0.10020040080160546

[rossmcdonald]

It's important to note that this issue occurs only when being used with GROUP BY *. I'm also not 100% sure if GROUP BY * is supposed to support aliasing, but it should be documented either way.

[brettdh]

I can reproduce this (or something very much like it) without GROUP BY *:

# telegraf & influxd running in docker container
root@d236bf739cec $ influx -database telegraf
Visit https://enterprise.influxdata.com to register for updates, InfluxDB server management, and monitoring.
Connected to http://localhost:8086 version 0.10.0
InfluxDB shell 0.10.0
> select "com.docker.compose.service" as service, rx_bytes from docker_net where "com.docker.compose.service" = 'fluentd' limit 5
name: docker_net
----------------
time                    service rx_bytes
1455030607000000000     fluentd 2457430
1455030609000000000     fluentd 2458390
1455030610000000000     fluentd 2460557
1455030612000000000     fluentd 2460557
1455030614000000000     fluentd 2460759

> select "com.docker.compose.service" as service, rx_bytes from docker_net where service = 'fluentd' limit 5                      
>

Expected same data when filtering using the alias; got none. This db has been using tsm1 from the start.

[zstyblik]

@brettdh @rossmcdonald so, it might be LIMIT X then. I haven't really tried that hard, but I know that the part after GROUP BY made it reproducible.

[brettdh]

I believe the no-results behavior also occurs if I remove the limit. I will
double check in a little bit.
On Tue, Feb 9, 2016 at 11:10 AM Zdenek Styblik notifications@github.com
wrote:

@brettdh https://github.com/brettdh @rossmcdonald
https://github.com/rossmcdonald so, it might be LIMIT X then. I haven't
really tried that hard, but I know that the part after GROUP BY made it
reproducible.

—
Reply to this email directly or view it on GitHub
#5567 (comment)
.

[jsternberg]

This appears to work in the new query engine that was released as part of 0.11. Please try again using the latest version of InfluxDB and reopen if this is still an issue.

Thanks!