Subqueries can only perform math on two fields #8848

NotionCommotion · 2017-09-19T15:42:11Z

System info:

InfluxDB shell version 1.3.5
CentOS Linux release 7.3.1611

Steps to reproduce:

Perform the following query from the shell:
SELECT p FROM (SELECT P51 + P55 + P57 AS p FROM L11)

Expected behavior:

The same results as: SELECT P51 + P55 + P57 AS p FROM L11

Actual behavior:

The query runs indefinitely (at least 5 hours) and the shell locks up requiring me to ctrl-z to exit and then kill the query.

Additional info:

My reason to do this is to perform math within an aggregate function using the subquery workaround described by:
https://docs.influxdata.com/influxdb/v1.3/troubleshooting/frequently-asked-questions/#how-do-i-perform-mathematical-operations-within-a-function
https://docs.influxdata.com/influxdb/v1.3/query_language/data_exploration/#subqueries

I am successfully able to perform math on three fields in the main query.
SELECT P51 + P55 + P57 AS p FROM L11

I am successfully able to perform math on two fields in a subquery, and perform an aggregate function on the results.
SELECT p FROM (SELECT P51 + P55 AS p FROM L11)
SELECT mean(p) AS meanp, count(p) AS countp FROM (SELECT P51+P55 AS p FROM L11)

My desired query, however, displays the same behaviour as the query listed under Steps to Reproduce.
SELECT mean(p) AS meanp, count(p) AS countp FROM (SELECT P51 + P55 + P57 AS p FROM L11)

Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z opened service service=subscriber
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Starting monitor system service=monitor
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z 'build' registered for diagnostics monitoring service=monitor
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z 'runtime' registered for diagnostics monitoring service=monitor
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z 'network' registered for diagnostics monitoring service=monitor
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z 'system' registered for diagnostics monitoring service=monitor
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Starting precreation service with check interval of 10m0s, advance period of 30m0s service=shard-precreation
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Starting snapshot service service=snapshot
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Starting continuous query service service=continuous_querier
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Starting HTTP service service=httpd
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Authentication enabled:false service=httpd
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Listening on HTTP:[::]:8086 service=httpd
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Starting retention policy enforcement service with check interval of 30m0s service=retention
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Listening for signals
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Storing statistics in database '_internal' retention policy 'monitor', at interval 10s service=monitor
Sep 19 15:08:49 devserver influxd[45661]: [I] 2017-09-19T15:08:49Z Sending usage statistics to usage.influxdata.com
Sep 19 15:08:52 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:08:52 +0000] "GET /ping HTTP/1.1" 204 0 "-" "InfluxDBShell/1.3.5" 72ac73a0-9d4c-11e7-8001-000000000000 112
Sep 19 15:08:59 devserver influxd[45661]: [I] 2017-09-19T15:08:59Z SHOW DATABASES service=query
Sep 19 15:08:59 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:08:59 +0000] "POST /query?db=&epoch=ns&q=SHOW+DATABASES HTTP/1.1" 200 170 "-" "InfluxDBShell/1.3.5" 76e86642-9d4c-11e7-8002-000000000000 731
Sep 19 15:09:10 devserver influxd[45661]: [I] 2017-09-19T15:09:10Z SELECT P51 + P55 + P57 AS p FROM greenbean_dl.autogen.L11 service=query
Sep 19 15:09:10 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:09:10 +0000] "POST /query?chunked=true&db=greenbean_dl&epoch=ns&q=SELECT+P51+%2B+P55+%2B+P57+AS+p+FROM+L11 HTTP/1.1" 200 401085 "-" "InfluxDBShell/1.
Sep 19 15:09:19 devserver influxd[45661]: [I] 2017-09-19T15:09:19Z SELECT p FROM (SELECT P51 + P55 AS p FROM greenbean_dl.autogen.L11) service=query
Sep 19 15:09:19 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:09:19 +0000] "POST /query?chunked=true&db=greenbean_dl&epoch=ns&q=SELECT+p+FROM+%28SELECT+P51+%2B+P55+AS+p+FROM+L11%29 HTTP/1.1" 200 130435 "-" "Infl
Sep 19 15:09:27 devserver influxd[45661]: [I] 2017-09-19T15:09:27Z SELECT mean(p) AS meanp, count(p) AS countp FROM (SELECT P51 + P55 AS p FROM greenbean_dl.autogen.L11) service=query
Sep 19 15:09:28 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:09:27 +0000] "POST /query?chunked=true&db=greenbean_dl&epoch=ns&q=SELECT+mean%28p%29+AS+meanp%2C+count%28p%29+AS+countp+FROM+%28SELECT+P51%2BP55+AS+p
Sep 19 15:09:38 devserver influxd[45661]: [I] 2017-09-19T15:09:38Z SELECT p FROM (SELECT P51 + P55 + P57 AS p FROM greenbean_dl.autogen.L11) service=query
Sep 19 15:10:12 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:10:12 +0000] "GET /ping HTTP/1.1" 204 0 "-" "InfluxDBShell/1.3.5" a282518e-9d4c-11e7-8007-000000000000 21
Sep 19 15:10:15 devserver influxd[45661]: [I] 2017-09-19T15:10:15Z SHOW QUERIES service=query
Sep 19 15:10:15 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:10:15 +0000] "POST /query?chunked=true&db=&epoch=ns&q=show+queries HTTP/1.1" 200 211 "-" "InfluxDBShell/1.3.5" a40b0b4c-9d4c-11e7-8008-000000000000 7
Sep 19 15:10:22 devserver influxd[45661]: [I] 2017-09-19T15:10:22Z KILL QUERY 5 service=query
Sep 19 15:10:22 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:10:22 +0000] "POST /query?chunked=true&db=&epoch=ns&q=kill+query+5 HTTP/1.1" 200 67 "-" "InfluxDBShell/1.3.5" a81a9319-9d4c-11e7-8009-000000000000 57
Sep 19 15:10:34 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:10:34 +0000] "POST /query?chunked=true&db=&epoch=ns&q=SELECT+mean%28p%29+AS+meanp%2C+count%28p%29+AS+countp+FROM+%28SELECT+P51+%2B+P55+%2B+P57+AS+p+F
Sep 19 15:10:48 devserver influxd[45661]: [I] 2017-09-19T15:10:48Z SHOW DATABASES service=query
Sep 19 15:10:48 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:10:48 +0000] "POST /query?db=&epoch=ns&q=SHOW+DATABASES HTTP/1.1" 200 170 "-" "InfluxDBShell/1.3.5" b7bfb677-9d4c-11e7-800b-000000000000 545
Sep 19 15:10:50 devserver influxd[45661]: [I] 2017-09-19T15:10:50Z SELECT mean(p) AS meanp, count(p) AS countp FROM (SELECT P51 + P55 + P57 AS p FROM greenbean_dl.autogen.L11) service=query
Sep 19 15:11:14 devserver influxd[45661]: [httpd] 127.0.0.1 - - [19/Sep/2017:15:11:14 +000

jsternberg · 2017-09-19T18:21:12Z

When you kill the query, does the influx client return control or does it continue to stall? Basically, can you take two terminals. In one terminal, run the query that's a problem. In the other, kill the query. Does control return to the first terminal or do you still have to kill the program?

NotionCommotion · 2017-09-19T18:39:49Z

After killing the query, the influx client does not return control but continues to stall.

jsternberg · 2017-09-19T18:58:11Z

Meh, I thought that would happen. The query is still using resources, but I never fixed the task manager to actually say it was a zombie task. There are two separate tangential issues that I'll also see if I can fix. Fixing them won't help you get a running query, but having them broken makes it worse.

I'm trying to reproduce this but haven't been able to yet. It works for me on my local machine. Do you have any missing values within the data? Can you run SHOW FIELD KEYS and SHOW SHARDS on your data? Feel free to anonymize anything you want if needed. I'm mostly just interested in shard durations and field types.

This more accurately shows whether or not a query has been killed. Instead of automatically removing it from the query table when it's killed even though goroutines and iterators may still be open, it now marks the process as killed. This should allow us to more accurately determine if a query has been stalled and is still using resources on the server. This is related to #8848, but not directly connected.

NotionCommotion · 2017-09-19T21:52:00Z

Yes, there is missing data. My data arrives asynchronously and often I don't have data for all fields for a given timestamp. Is that an issue? If necessary, I can give you access to the server.


[michael@devserver ~]$ /usr/bin/influx
Connected to http://localhost:8086 version 1.3.5
InfluxDB shell version: 1.3.5
> use devserver_dl
Using database devserver_dl
> show field keys
name: L11
fieldKey fieldType
-------- ---------
P16      float
P32      float
P49      float
P6       float
P53      float
P69      float
P70      float
P45      float
P72      float
P47      float
P75      float
P77      float
P51      float
P110     float
P55      float
P57      float
P58      float
P59      float
P60      float
P65      float
P66      float

name: L12
fieldKey fieldType
-------- ---------
P158     float
P159     float
P160     float
P161     float

name: L5
fieldKey fieldType
-------- ---------
P141     float
P111     float
P31      float
P134     float
P52      float
P54      float
P18      float
P68      float
P73      float
P44      float
P95      float
P46      float
P98      float
P56      float
P61      float
P63      float
P64      float
P67      float

name: L6
fieldKey fieldType
-------- ---------
P17      float
P33      float
P34      float
P38      float
P41      float
P42      float
P48      float
P50      float
P62      float
P71      float
P74      float
P76      float
> show shards
name: _internal
id  database  retention_policy shard_group start_time           end_time             expiry_time          owners
--  --------  ---------------- ----------- ----------           --------             -----------          ------
168 _internal monitor          168         2017-09-12T00:00:00Z 2017-09-13T00:00:00Z 2017-09-20T00:00:00Z
169 _internal monitor          169         2017-09-13T00:00:00Z 2017-09-14T00:00:00Z 2017-09-21T00:00:00Z
170 _internal monitor          170         2017-09-14T00:00:00Z 2017-09-15T00:00:00Z 2017-09-22T00:00:00Z
171 _internal monitor          171         2017-09-15T00:00:00Z 2017-09-16T00:00:00Z 2017-09-23T00:00:00Z
172 _internal monitor          172         2017-09-16T00:00:00Z 2017-09-17T00:00:00Z 2017-09-24T00:00:00Z
173 _internal monitor          173         2017-09-17T00:00:00Z 2017-09-18T00:00:00Z 2017-09-25T00:00:00Z
174 _internal monitor          174         2017-09-18T00:00:00Z 2017-09-19T00:00:00Z 2017-09-26T00:00:00Z
178 _internal monitor          178         2017-09-19T00:00:00Z 2017-09-20T00:00:00Z 2017-09-27T00:00:00Z

name: devtest_1865094168
id database retention_policy shard_group start_time end_time expiry_time owners
-- -------- ---------------- ----------- ---------- -------- ----------- ------

name: devtest_1586701055
id database retention_policy shard_group start_time end_time expiry_time owners
-- -------- ---------------- ----------- ---------- -------- ----------- ------

name: devtest_1104665423
id  database              retention_policy shard_group start_time           end_time             expiry_time          owners
--  --------              ---------------- ----------- ----------           --------             -----------          ------
19  devtest_1104665423 autogen          19          2017-06-05T00:00:00Z 2017-06-12T00:00:00Z 2017-06-12T00:00:00Z
23  devtest_1104665423 autogen          23          2017-06-12T00:00:00Z 2017-06-19T00:00:00Z 2017-06-19T00:00:00Z
31  devtest_1104665423 autogen          31          2017-06-19T00:00:00Z 2017-06-26T00:00:00Z 2017-06-26T00:00:00Z
39  devtest_1104665423 autogen          39          2017-06-26T00:00:00Z 2017-07-03T00:00:00Z 2017-07-03T00:00:00Z
47  devtest_1104665423 autogen          47          2017-07-03T00:00:00Z 2017-07-10T00:00:00Z 2017-07-10T00:00:00Z
55  devtest_1104665423 autogen          55          2017-07-10T00:00:00Z 2017-07-17T00:00:00Z 2017-07-17T00:00:00Z
63  devtest_1104665423 autogen          63          2017-07-17T00:00:00Z 2017-07-24T00:00:00Z 2017-07-24T00:00:00Z
71  devtest_1104665423 autogen          71          2017-07-24T00:00:00Z 2017-07-31T00:00:00Z 2017-07-31T00:00:00Z
79  devtest_1104665423 autogen          79          2017-07-31T00:00:00Z 2017-08-07T00:00:00Z 2017-08-07T00:00:00Z
87  devtest_1104665423 autogen          87          2017-08-07T00:00:00Z 2017-08-14T00:00:00Z 2017-08-14T00:00:00Z
95  devtest_1104665423 autogen          95          2017-08-14T00:00:00Z 2017-08-21T00:00:00Z 2017-08-21T00:00:00Z
103 devtest_1104665423 autogen          103         2017-08-21T00:00:00Z 2017-08-28T00:00:00Z 2017-08-28T00:00:00Z
111 devtest_1104665423 autogen          111         2017-08-28T00:00:00Z 2017-09-04T00:00:00Z 2017-09-04T00:00:00Z
119 devtest_1104665423 autogen          119         2017-09-04T00:00:00Z 2017-09-11T00:00:00Z 2017-09-11T00:00:00Z
165 devtest_1104665423 autogen          165         2017-09-11T00:00:00Z 2017-09-18T00:00:00Z 2017-09-18T00:00:00Z
175 devtest_1104665423 autogen          175         2017-09-18T00:00:00Z 2017-09-25T00:00:00Z 2017-09-25T00:00:00Z

name: devtest_1974750116
id  database              retention_policy shard_group start_time           end_time             expiry_time          owners
--  --------              ---------------- ----------- ----------           --------             -----------          ------
152 devtest_1974750116 autogen          152         2017-09-04T00:00:00Z 2017-09-11T00:00:00Z 2017-09-11T00:00:00Z
166 devtest_1974750116 autogen          166         2017-09-11T00:00:00Z 2017-09-18T00:00:00Z 2017-09-18T00:00:00Z
176 devtest_1974750116 autogen          176         2017-09-18T00:00:00Z 2017-09-25T00:00:00Z 2017-09-25T00:00:00Z

name: devserver_dl
id  database     retention_policy shard_group start_time           end_time             expiry_time          owners
--  --------     ---------------- ----------- ----------           --------             -----------          ------
163 devserver_dl autogen          163         2017-06-19T00:00:00Z 2017-06-26T00:00:00Z 2017-06-26T00:00:00Z
162 devserver_dl autogen          162         2017-07-03T00:00:00Z 2017-07-10T00:00:00Z 2017-07-10T00:00:00Z
161 devserver_dl autogen          161         2017-07-10T00:00:00Z 2017-07-17T00:00:00Z 2017-07-17T00:00:00Z
160 devserver_dl autogen          160         2017-07-17T00:00:00Z 2017-07-24T00:00:00Z 2017-07-24T00:00:00Z
159 devserver_dl autogen          159         2017-07-24T00:00:00Z 2017-07-31T00:00:00Z 2017-07-31T00:00:00Z
158 devserver_dl autogen          158         2017-07-31T00:00:00Z 2017-08-07T00:00:00Z 2017-08-07T00:00:00Z
157 devserver_dl autogen          157         2017-08-07T00:00:00Z 2017-08-14T00:00:00Z 2017-08-14T00:00:00Z
156 devserver_dl autogen          156         2017-08-14T00:00:00Z 2017-08-21T00:00:00Z 2017-08-21T00:00:00Z
155 devserver_dl autogen          155         2017-08-21T00:00:00Z 2017-08-28T00:00:00Z 2017-08-28T00:00:00Z
154 devserver_dl autogen          154         2017-08-28T00:00:00Z 2017-09-04T00:00:00Z 2017-09-04T00:00:00Z
153 devserver_dl autogen          153         2017-09-04T00:00:00Z 2017-09-11T00:00:00Z 2017-09-11T00:00:00Z
167 devserver_dl autogen          167         2017-09-11T00:00:00Z 2017-09-18T00:00:00Z 2017-09-18T00:00:00Z
177 devserver_dl autogen          177         2017-09-18T00:00:00Z 2017-09-25T00:00:00Z 2017-09-25T00:00:00Z
>

NotionCommotion · 2017-09-19T22:35:30Z

Sorry, accidentally closed and didn't realize until now.

jsternberg · 2017-09-20T18:46:24Z

The missing data can be an issue, but I thought that had been fixed. There was an issue where fill(none) would cause a deadlock because the fields have to be read with each other or it causes a deadlock. The original issue is here. Is there any other part to your query? We automatically set the subquery to use fill(none). If you are querying multiple fields in the subquery, those two might conflict and it's my best guess where the error is right now.

I'll investigate a bit, but the current query doesn't seem to deadlock for me when I remove a value from the inputs. Is there something more to the query like this?

SELECT * FROM (SELECT v1, v1 + v2 FROM m0)

NotionCommotion · 2017-09-21T11:56:45Z

I created the following from scratch to simulate the data. I used your query "SELECT * FROM (SELECT f1, f1 + f2 FROM m1)", and it stalls similar to "SELECT sq FROM (SELECT f1+f2+f3 AS sq FROM m1)". What do you whitness when you use the following?

[michael@devserver ~]$ /usr/bin/influx
Connected to http://localhost:8086 version 1.3.5
InfluxDB shell version: 1.3.5
> DROP DATABASE test_subquery
> CREATE DATABASE test_subquery
> USE test_subquery
Using database test_subquery
> INSERT m1,t1=t2 f3=33.9,f1=18.5,f2=26 1505994014000000000
> INSERT m1,t1=t2 f5=95,f4=50.4,f3=54,f1=17.3,f2=22.2 1505994018000000000
> INSERT m1,t1=t2 f1=10.8,f2=34.4,f4=48.8,f5=64 1505994022000000000
> INSERT m1,t1=t1 f5=96.5,f4=62.4,f3=39.9,f1=15.9 1505994023000000000
> INSERT m1,t1=t4 f5=89,f3=31.8,f2=26.6 1505994024000000000
> INSERT m1,t1=t2 f5=77,f4=65.2,f1=17.1,f2=27 1505994029000000000
> INSERT m1,t1=t1 f3=45.9,f1=17.5,f5=87.5 1505994033000000000
> INSERT m1,t1=t5 f5=79.5,f2=25 1505994038000000000
> INSERT m1,t1=t5 f3=41.7,f1=15.7,f5=65 1505994039000000000
> INSERT m1,t1=t1 f1=17.5,f2=29,f4=70 1505994041000000000
> INSERT m1,t1=t1 f1=12.3,f2=23.8,f3=58.5 1505994044000000000
> INSERT m1,t1=t4 f3=43.8,f1=15.3,f4=44.4 1505994048000000000
> INSERT m1,t1=t5 f1=17.7,f3=34.8,f5=95.5 1505994050000000000
> INSERT m1,t1=t2 f1=13.6,f3=41.7,f4=58.8,f2=29.8 1505994051000000000
> INSERT m1,t1=t4 f4=54.4,f2=21,f1=12.1,f3=52.5 1505994053000000000
> INSERT m1,t1=t1 f5=96,f3=36,f1=17.2,f4=66,f2=28.2 1505994058000000000
> INSERT m1,t1=t5 f3=47.7,f5=92,f2=37.4,f4=57.6 1505994060000000000
> INSERT m1,t1=t1 f4=48.4,f1=15.4,f2=33.8 1505994061000000000
> INSERT m1,t1=t1 f4=78,f5=88.5,f3=35.7 1505994064000000000
> INSERT m1,t1=t3 f3=50.7,f1=16.8 1505994065000000000
> SELECT * FROM m1
name: m1
time                f1   f2   f3   f4   f5   t1
----                --   --   --   --   --   --
1505994014000000000 18.5 26   33.9           t2
1505994018000000000 17.3 22.2 54   50.4 95   t2
1505994022000000000 10.8 34.4      48.8 64   t2
1505994023000000000 15.9      39.9 62.4 96.5 t1
1505994024000000000      26.6 31.8      89   t4
1505994029000000000 17.1 27        65.2 77   t2
1505994033000000000 17.5      45.9      87.5 t1
1505994038000000000      25             79.5 t5
1505994039000000000 15.7      41.7      65   t5
1505994041000000000 17.5 29        70        t1
1505994044000000000 12.3 23.8 58.5           t1
1505994048000000000 15.3      43.8 44.4      t4
1505994050000000000 17.7      34.8      95.5 t5
1505994051000000000 13.6 29.8 41.7 58.8      t2
1505994053000000000 12.1 21   52.5 54.4      t4
1505994058000000000 17.2 28.2 36   66   96   t1
1505994060000000000      37.4 47.7 57.6 92   t5
1505994061000000000 15.4 33.8      48.4      t1
1505994064000000000           35.7 78   88.5 t1
1505994065000000000 16.8      50.7           t3
> SELECT sq FROM (SELECT f1+f2 AS sq FROM m1)
name: m1
time                sq
----                --
1505994014000000000 44.5
1505994018000000000 39.5
1505994022000000000 45.2
1505994029000000000 44.1
1505994041000000000 46.5
1505994044000000000 36.1
1505994051000000000 43.4
1505994053000000000 33.1
1505994058000000000 45.4
1505994061000000000 49.199999999999996
> SELECT sq FROM (SELECT f1+f3 AS sq FROM m1)
name: m1
time                sq
----                --
1505994014000000000 52.4
1505994018000000000 71.3
1505994023000000000 55.8
1505994033000000000 63.4
1505994039000000000 57.400000000000006
1505994044000000000 70.8
1505994048000000000 59.099999999999994
1505994050000000000 52.5
1505994051000000000 55.300000000000004
1505994053000000000 64.6
1505994058000000000 53.2
1505994065000000000 67.5
> SELECT sq FROM (SELECT f2+f3 AS sq FROM m1)
name: m1
time                sq
----                --
1505994014000000000 59.9
1505994018000000000 76.2
1505994024000000000 58.400000000000006
1505994044000000000 82.3
1505994051000000000 71.5
1505994053000000000 73.5
1505994058000000000 64.2
1505994060000000000 85.1
> SELECT * FROM (SELECT f1, f1 + f2 FROM m1)

[michael@devserver ~]$ /usr/bin/influx
Connected to http://localhost:8086 version 1.3.5
InfluxDB shell version: 1.3.5
> show queries
qid query                                      database      duration
--- -----                                      --------      --------
113 SELECT * FROM (SELECT f1, f1 + f2 FROM m1) test_subquery 3m38s
115 SHOW QUERIES                                             64µs
>

NotionCommotion · 2017-09-22T11:41:58Z

Note that this behavior is not experienced should all fields have a value for each timestamp. Furthermore, it is not experienced if some fields do not have a value provided this does not occur for two subsequent timestamps.

This query works:

> SELECT * FROM m1
name: m1
time                f1   f2   f3   f4   f5   t1
----                --   --   --   --   --   --
1506078186000000000      39.4 46.5 42.8 73   t1
1506078191000000000 16.8 36.6 54.6 71.6 99.5 t5
1506078196000000000 11.2      42   41.6 60   t1
1506078199000000000 14.2 35.2 49.8 73.2 71.5 t2
1506078202000000000 10.3 38.8      75.6 52.5 t2
1506078204000000000 17.9 33.4 48.3 68.8 96   t4
1506078209000000000 18.9 29.4 35.4      96.5 t1
1506078210000000000 14   24.6 51.3 61.6 71.5 t2
1506078213000000000 13.2 24.2 50.1 61.6      t2
> SELECT MEAN(sq),SUM(f1),MAX(f2),MIN(f3),COUNT(f4),INTEGRAL(f5) FROM (SELECT f1,f2,f3,f4,f5,1*f1+2*f2+3*f3+4*f4+5*f5 AS sq FROM m1)
name: m1
time mean   sum                max  min  count integral
---- ----   ---                ---  ---  ----- --------
0    931.95 116.50000000000001 39.4 35.4 8     1927
>

This query crashes the system:

> SELECT * FROM m1
name: m1
time                f1   f2   f3   f4   f5   t1
----                --   --   --   --   --   --
1506078186000000000 11.2 39.4 46.5 42.8 73   t1
1506078191000000000      36.6 54.6 71.6 99.5 t5
1506078196000000000 11.2 20.4 42   41.6      t1
1506078199000000000 14.2 35.2 49.8 73.2 71.5 t2
1506078202000000000 10.3 38.8 41.4 75.6 52.5 t2
1506078204000000000 17.9 33.4 48.3 68.8 96   t4
1506078209000000000 18.9 29.4 35.4 63.2 96.5 t1
1506078210000000000 14   24.6 51.3 61.6 71.5 t2
1506078213000000000 13.2 24.2 50.1 61.6 90.5 t2
> SELECT MEAN(sq),SUM(f1),MAX(f2),MIN(f3),COUNT(f4),INTEGRAL(f5) FROM (SELECT f1,f2,f3,f4,f5,1*f1+2*f2+3*f3+4*f4+5*f5 AS sq FROM m1)

jsternberg · 2017-09-22T14:57:25Z

The first one is a good reproducer. I'll try out some fixes.

rbetts assigned jsternberg Sep 19, 2017

rbetts added backlog/storage proposed labels Sep 19, 2017

jsternberg mentioned this issue Sep 19, 2017

Report the task status for a query #8854

Merged

4 tasks

NotionCommotion closed this as completed Sep 19, 2017

ghost removed the proposed label Sep 19, 2017

NotionCommotion reopened this Sep 19, 2017

rbetts added the proposed label Sep 21, 2017

jsternberg mentioned this issue Sep 22, 2017

Prevent deadlock when doing math on the result of a subquery #8864

Merged

3 tasks

ghost added review and removed proposed labels Sep 22, 2017

jsternberg closed this as completed in #8864 Sep 25, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Subqueries can only perform math on two fields #8848

Subqueries can only perform math on two fields #8848

NotionCommotion commented Sep 19, 2017

jsternberg commented Sep 19, 2017

NotionCommotion commented Sep 19, 2017

jsternberg commented Sep 19, 2017

NotionCommotion commented Sep 19, 2017

NotionCommotion commented Sep 19, 2017

jsternberg commented Sep 20, 2017

NotionCommotion commented Sep 21, 2017

NotionCommotion commented Sep 22, 2017

jsternberg commented Sep 22, 2017

Subqueries can only perform math on two fields #8848

Subqueries can only perform math on two fields #8848

Comments

NotionCommotion commented Sep 19, 2017

jsternberg commented Sep 19, 2017

NotionCommotion commented Sep 19, 2017

jsternberg commented Sep 19, 2017

NotionCommotion commented Sep 19, 2017

NotionCommotion commented Sep 19, 2017

jsternberg commented Sep 20, 2017

NotionCommotion commented Sep 21, 2017

NotionCommotion commented Sep 22, 2017

jsternberg commented Sep 22, 2017