erkdursun commented on issue #9792: URL: https://github.com/apache/druid/issues/9792#issuecomment-624314268
Hello @suneet-s First of all thanks for your and @gianm efforts. I had already tried inner join but unfortunately it showed same behavior in my query.    PLANS for the queries #### select * from klm ~~~ DruidJoinQueryRel(condition=[=($0, $3)], joinType=[inner], 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":[{"type":"expression","name":"v0","expression":"'BAC011'","outputType":"STRING"}],"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":"v0","outputName":"d0","outputType":"STRING"},{"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":"6c957e82-c860-4c27-aa24-db664bacba2c"},"descending":false}], signature=[{d0:STRING, d1:LONG, d2:LONG, a0:LONG, d3:LONG, p0:LONG}]) DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-21T00:00:00.000Z/2020-04-28T21:04:42.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":"6c957e82-c860-4c27-aa24-db664bacba2c"},"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-21T00:00:00.000Z/2020-04-28T21:04:42.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":"6c957e82-c860-4c27-aa24-db664bacba2c"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}]) ~~~ #### select count(*) from klm ~~~ 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":"6c2eda1c-df9b-40d2-8218-bbcb72181825"}}], signature=[{a0:LONG}]) DruidJoinQueryRel(condition=[=($0, $3)], joinType=[inner], 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":"6c2eda1c-df9b-40d2-8218-bbcb72181825"},"descending":false}], signature=[{p0:LONG}]) DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-21T00:00:00.000Z/2020-04-28T21:05:57.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":"6c2eda1c-df9b-40d2-8218-bbcb72181825"},"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-21T00:00:00.000Z/2020-04-28T21:05:57.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":"6c2eda1c-df9b-40d2-8218-bbcb72181825"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}]) ~~~ #### select sum(duration) from klm ~~~ 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":"9dbd9333-701c-4c3c-a409-5d50a3bb7386"}}], signature=[{a0:LONG}]) DruidJoinQueryRel(condition=[=($0, $3)], joinType=[inner], 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":"9dbd9333-701c-4c3c-a409-5d50a3bb7386"},"descending":false}], signature=[{p0:LONG}]) DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-21T00:00:00.000Z/2020-04-28T21:06:21.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":"9dbd9333-701c-4c3c-a409-5d50a3bb7386"},"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-21T00:00:00.000Z/2020-04-28T21:06:21.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":"9dbd9333-701c-4c3c-a409-5d50a3bb7386"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}]) ~~~ ---------------------------------------------------------------- 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: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
