1 + 1 = 4 ; how/why?

Welcome & Getting Started
1

Hi.

Can anyone explain what’s going on here please?

select CIP from mqtt_consumer where(topic=‘Live’) and cluster!=‘’ group by cluster order by time desc limit 1
name: mqtt_consumer
tags: cluster=krypton
time CIP

1558001809993918613 1

name: mqtt_consumer
tags: cluster=argon
time CIP

1558001809832442519 1

select sum(CIP) from (select CIP from mqtt_consumer where(topic=‘Live’) and cluster!=‘’ group by cluster order by time desc limit 1) order by time desc
name: mqtt_consumer
time sum

0 4

The first query results in two row, each with CIP=1.

The second query is supposed to add these together, and gives the answer 4.

If either of the values is zero, the sum correctly comes out as 1.

Thanks,

Antony.

2

Hi.

Can anyone explain what’s going on here please?

The first query results in two rows, each with CIP=1.

The second query is supposed to add these together, and gives the answer 4.

If either of the values is zero, the sum correctly comes out as 1.

I’ve investigated this a bit further, and it’s clear that the outer SELECT is
producing results which don’t come from the inner SELECT. I have no idea how
that’s possible.

Here’s the inner SELECT, with all fields selected:

select * from mqtt_consumer where(topic=‘Live’) and cluster!=‘’ and CIP>0
group by cluster order by time desc limit 1

name: mqtt_consumer
tags: cluster=krypton
time CIP callid host server topic

1558008970624610278 1 1558008953 systemy iodine Live

name: mqtt_consumer
tags: cluster=argon
time CIP callid host server topic

1558008183447539177 1 1558008117 systemy copper Live

Now surround this with an outer SELECT, also for all fields, and a new result
pops up:

select * from (select * from mqtt_consumer where(topic=‘Live’) and
cluster!=‘’ and CIP>0 group by cluster order by time desc limit 1) order by
time desc

name: mqtt_consumer
time CIP callid cluster host server topic

1558008970624610278 1 1558008953 krypton systemy iodine Live
1558008183447539177 1 1558008117 argon systemy copper Live
1557667706170163858 1 3 krypton systemy iodine Live

Can someone tell me:

a) what’s going on here - how can an outer SELECT return more results than the
inner one?

b) how can I re-phrase my query so that I can get the sum of the two values
of CIP returned from the inner select?

What I’m aiming for is the sum of the most recent value of “CIP” returned for
each value of “cluster”.

Thanks,

Antony.

3

Can nobody shed any light on what’s going on here?

It seems like a bug to me that an outer SELECT can return more values than are
present in the inner SELECT…

Antony.

4

Hello @Pooh,
Thanks for your question and sorry for the delay! The way that I perform a sum of the last field value across tags is with the following query:
SELECT sum("last_usage_user") FROM (SELECT last("usage_user") as "last_usage_user FROM "telegraf"."autogen"."cpu" WHERE time < '2019-05-12T14:00:00Z' AND "cpu"='cpu-total' OR "cpu"='cpu0' GRPOP BY cpu)

I think the LAST() function might have been the missing part of your query. As for why you’re not getting the values you expect, I’m not sure. It’s hard for me to wrap my mind around your schema.

1 Like
5

Hello Pooh,

I perform a sum of the last field value across tags is with the following
query:
SELECT sum("last_usage_user") FROM (SELECT last("usage_user") as "last_usage_user FROM "telegraf"."autogen"."cpu" WHERE time < '2019-05-12T14:00:00Z' AND "cpu"='cpu-total' OR "cpu"='cpu0' GRPOP BY cpu)

I think the LAST() function might have been the missing part of your query.

Indeed - I didn’t know about that function (it’s not part of MySQL, which is
my normal SQL tool of choice), and it does exactly what I was attempting to
achieve with “order by time desc limit 1”

As for why you’re not getting the values you expect, I’m not sure. It’s
hard for me to wrap my mind around your schema.

My point is simple:

If I do a SELECT() of any sort, and I get two results from it, and I then put
that SELECT() into brackets and perform some operation on the results, I
expect that operation to be performed on the two results.

InfluxDB is capable of returning two results for SELECT * from …something…
and then returning four results from SELECT * from (SELECT * from
…something…) which makes no sense to me.

Thanks for the pointer to the last() function, though - it’s fixed my problem.

Antony.