Aggregation functions are not working properly when using join feature

[erkdursun]

Aggregation functions are not working properly when using join feature

Affected Version

0.18.0

Description

I have a query to find duration of event which returns 1 record in total

When I try to count the results with query it returns 6 records and if I try to sum all duration for one key it returns some dummy record which should be 86341

[gianm]

Hi @erkdursun,

Thanks for the report.

Could you please include the results of "EXPLAIN PLAN" for this query? i.e. EXPLAIN PLAN FOR SELECT ...

[gianm]

By the way, could you also try running these queries without "Smart query limit" enabled?

[erkdursun]

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

[gianm]

@erkdursun Could you please attach the plan for the basic select * from klm as well? It'd be useful to be able to compare them.

[erkdursun]

@erkdursun Could you please attach the plan for the basic select * from klm as well? It'd be useful to be able to compare them.

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":"'BAC011'","ordering":null},{"type":"expression","name":"p1","expression":"div((\"a0\" - \"d1\"),1000)","ordering":null}],"having":null,"limitSpec":{"type":"NoopLimitSpec"},"context":{"sqlQueryId":"6dfa7834-01c3-44c8-8885-c6bdf7d1f321"},"descending":false}], signature=[{p0:STRING, d1:LONG, d2:LONG, a0:LONG, d3:LONG, p1:LONG}])
  DruidQueryRel(query=[{"queryType":"scan","dataSource":{"type":"table","name":"rich-event-ordered"},"intervals":{"type":"intervals","intervals":["2020-04-23T00:00:00.000Z/2020-04-29T08:08:28.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":"6dfa7834-01c3-44c8-8885-c6bdf7d1f321"},"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-23T00:00:00.000Z/2020-04-29T08:08:28.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":"6dfa7834-01c3-44c8-8885-c6bdf7d1f321"},"descending":false,"granularity":{"type":"all"}}], signature=[{v0:STRING, __time:LONG, value:LONG}])

[suneet-s]

I was able to re-produce this on the sample wikipedia dataset.

with abc as
(
  SELECT page, "__time", sum_added, sum_deleted from wikipedia WHERE "countryName" = 'United States' and "__time" > '2016'
)
, klm AS
(
    SELECT 
      t1.page
      from 
        abc as t1 left join abc as t2 on t1.page = t2.page
      where t1.sum_added < t2.sum_added
      group by 1
)
SELECT count(*) from klm

The query above count(*) returns 306 results. This is the same as the number of results if there is no filter applied (ie remove where t1.sum_added < t2.sum_added)

If we change the query to select *, it returns 18 results, which appears to be correct.

This issue only happens if the filter contains an expression from t2. For example, using a filter t1.sum_added > t1.sum_deleted or t1.sum_added > 1000 returns the expected consistent results, but t2.sum_added > 1999 returns inconsistent results.

My hypothesis is that something in calcite is trying to deciding that we don't need to apply the filters for the aggregate query. I believe this to be the case because I ran the queries with enableJoinFilterPushDown set to false.

I could not reproduce this against a druid datasource

SELECT t1.page from  wikipedia as t1 left join wikipedia as t2 on t1.page = t2.page WHERE t1."countryName" = 'United States' and t1.sum_added < t2.sum_added group by 1

Next steps:

• Look at the calcite plans more closely to find the difference in the generated plans

[suneet-s]

A count(*) query generates a native query that looks like

{
      "queryType": "groupBy",
      "dataSource": {
        "type": "query",
        "query": {
          "queryType": "groupBy",
          "dataSource": {
            "type": "join",
            ...

However a select * query generates a native query that looks like

"queryType": "groupBy",
      "dataSource": {
        "type": "join",
        ...

Too late in the day to actually figure out what's going on here, but hopefully tomorrow it will be obvious what the bug is 🤞

[suneet-s]

@erkdursun I think you can unblock yourself by changing your query from a left join to an inner join. Druid appears to produce consistent results if an inner join query is used.

For your query, since you are joining a table with itself, I believe a left join should be identical to an inner join, thanks again for finding and reporting this bug.