You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by GitBox <gi...@apache.org> on 2020/04/29 21:56:04 UTC
[GitHub] [druid] erkdursun commented on issue #9792: Aggregation functions are not working properly when using join feature
erkdursun commented on issue #9792:
URL: https://github.com/apache/druid/issues/9792#issuecomment-621486273
> Hi @erkdursun,
>
> Thanks for the report.
>
> Could you please include the results of "EXPLAIN PLAN" for this query? i.e. `EXPLAIN PLAN FOR SELECT ...`
Hello these are the EXPLAIN PLAN results
~~~
----SUM----
DruidOuterQueryRel(query=[{"queryType":"timeseries","dataSource":{"type":"table","name":"__subquery__"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"descending":false,"virtualColumns":[],"filter":null,"granularity":{"type":"all"},"aggregations":[{"type":"longSum","name":"a0","fieldName":"duration","expression":null}],"postAggregations":[],"limit":2147483647,"context":{"skipEmptyBuckets":true,"sqlQueryId":"f79085fd-5296-4899-aa77-de389875b939"}}], signature=[{a0:LONG}])
DruidJoinQueryRel(condition=[=($0, $3)], joinType=[left], query=[{"queryType":"groupBy","dataSource":{"type":"table","name":"__join__"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"filter":{"type":"and","fields":[{"type":"expression","expression":"(\"__time\" < \"__time0\")"},{"type":"expression","expression":"(\"value\" != \"value0\")"},{"type":"not","field":{"type":"selector","dimension":"value","value":"0","extractionFn":null}}]},"granularity":{"type":"all"},"dimensions":[{"type":"default","dimension":"__time","outputName":"d1","outputType":"LONG"},{"type":"default","dimension":"value","outputName":"d2","outputType":"LONG"},{"type":"default","dimension":"value0","outputName":"d3","outputType":"LONG"}],"aggregations":[{"type":"longMin","name":"a0","fieldName":"__time0","expression":null}],"postAggregations":[{"type":"expression","name":"p0","expression":"div((\"a0\" - \"d1\"),1000)","ordering":null}],"having":null,"limitSpec":{"type":"NoopLimitSpec"},"context":{"sqlQueryId":"f79085fd-5296-4899-aa77-de389875b939"},"descending":false}], signature=[{p0:LONG}])
DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-22T00:00:00.000Z/2020-04-28T21:33:13.001Z"]},"virtualColumns":[{"type":"expression","name":"v0","expression":"'BAC011'","outputType":"STRING"}],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"order":"none","filter":{"type":"and","fields":[{"type":"selector","dimension":"ELEMENT_NAME","value":"TcLruSta","extractionFn":null},{"type":"selector","dimension":"B1_NAME","value":"BAC011","extractionFn":null}]},"columns":["__time","v0","value"],"legacy":false,"context":{"sqlQueryId":"f79085fd-5296-4899-aa77-de389875b939"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}])
DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-22T00:00:00.000Z/2020-04-28T21:33:13.001Z"]},"virtualColumns":[{"type":"expression","name":"v0","expression":"'BAC011'","outputType":"STRING"}],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"order":"none","filter":{"type":"and","fields":[{"type":"selector","dimension":"ELEMENT_NAME","value":"TcLruSta","extractionFn":null},{"type":"selector","dimension":"B1_NAME","value":"BAC011","extractionFn":null}]},"columns":["__time","v0","value"],"legacy":false,"context":{"sqlQueryId":"f79085fd-5296-4899-aa77-de389875b939"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}])
~~~
~~~
----COUNT----
DruidOuterQueryRel(query=[{"queryType":"timeseries","dataSource":{"type":"table","name":"__subquery__"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"descending":false,"virtualColumns":[],"filter":null,"granularity":{"type":"all"},"aggregations":[{"type":"count","name":"a0"}],"postAggregations":[],"limit":2147483647,"context":{"skipEmptyBuckets":true,"sqlQueryId":"f22dc038-f377-47b4-8802-40abe3a68bf1"}}], signature=[{a0:LONG}])
DruidJoinQueryRel(condition=[=($0, $3)], joinType=[left], query=[{"queryType":"groupBy","dataSource":{"type":"table","name":"__join__"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"filter":{"type":"and","fields":[{"type":"expression","expression":"(\"__time\" < \"__time0\")"},{"type":"expression","expression":"(\"value\" != \"value0\")"},{"type":"not","field":{"type":"selector","dimension":"value","value":"0","extractionFn":null}}]},"granularity":{"type":"all"},"dimensions":[{"type":"default","dimension":"__time","outputName":"d1","outputType":"LONG"},{"type":"default","dimension":"value","outputName":"d2","outputType":"LONG"},{"type":"default","dimension":"value0","outputName":"d3","outputType":"LONG"}],"aggregations":[],"postAggregations":[{"type":"expression","name":"p0","expression":"0","ordering":null}],"having":null,"limitSpec":{"type":"NoopLimitSpec"},"context":{"sqlQueryId":"f22dc038-f377-47b4-8802-40abe3a68bf1"},"descending":false}], signature=[{p0:LONG}])
DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-22T00:00:00.000Z/2020-04-28T21:40:00.001Z"]},"virtualColumns":[{"type":"expression","name":"v0","expression":"'BAC011'","outputType":"STRING"}],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"order":"none","filter":{"type":"and","fields":[{"type":"selector","dimension":"ELEMENT_NAME","value":"TcLruSta","extractionFn":null},{"type":"selector","dimension":"B1_NAME","value":"BAC011","extractionFn":null}]},"columns":["__time","v0","value"],"legacy":false,"context":{"sqlQueryId":"f22dc038-f377-47b4-8802-40abe3a68bf1"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}])
DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-22T00:00:00.000Z/2020-04-28T21:40:00.001Z"]},"virtualColumns":[{"type":"expression","name":"v0","expression":"'BAC011'","outputType":"STRING"}],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"order":"none","filter":{"type":"and","fields":[{"type":"selector","dimension":"ELEMENT_NAME","value":"TcLruSta","extractionFn":null},{"type":"selector","dimension":"B1_NAME","value":"BAC011","extractionFn":null}]},"columns":["__time","v0","value"],"legacy":false,"context":{"sqlQueryId":"f22dc038-f377-47b4-8802-40abe3a68bf1"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}])
~~~
> By the way, could you also try running these queries _without_ "Smart query limit" enabled?
Disabling smart query didn't work. Same results again
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org