https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# general
s
This message was deleted.
g
do you have some more details on what you mean by "won't properly group"?
s
Getting 
0
as result of aggregate function of sketch when changing group by from (str, str) -> (str)
v
Could you please share the query (before and after) to illustrate what you mean by "when changing group by from (str, str) -> (str)"
g
seconding the above ☝️ it would be helpful to see the query text (& results) for the one that works and the one that doesn't
s
Copy code
with t1(merge_column, s1) as
    (
      SELECT merge_column,
             DS_THETA(c1)
        FROM table1
    )


      SELECT 
        gp_col1,
        sum(ts2),
        sum(ts1),
        sum(ts3)
      FROM (
               SELECT gp_col1,
                      gp_multiplier,
                      THETA_SKETCH_ESTIMATE((DS_THETA(s2))) as ts2,
                      THETA_SKETCH_ESTIMATE((DS_THETA(s1))) as ts1,
                      gp_multiplier * THETA_SKETCH_ESTIMATE(THETA_SKETCH_INTERSECT(DS_THETA(s2), DS_THETA(s1))) as ts3
                 FROM (
                        SELECT merge_column,
                               gp_col1,
                               gp_multiplier,
                               DS_THETA(c2) as s2
                          FROM table2
                         WHERE gp_multiplier != 0 
                         GROUP BY gp_col1,
                      gp_multiplier
                      ) t2
                 JOIN t1
                   ON t1.merge_column = t2.merge_column
                GROUP BY gp_col1,
                      gp_multiplier
            )
      GROUP BY gp_col1
The above query works fine in v22 and we get get non zero values for sum(ts2), sum(ts1) and sum(ts3) In version 25, only getting non zero value for sum(ts2), others sums are returning 0. Though in version 25, ts2, ts1 and ts3 in subquery are returning correct values.
g
one more question… could you attach the results of 
EXPLAIN PLAN FOR <query>
on the two versions?
hmm, could you double check your query too? When I try to validate it, 
t2
doesn't validate:
Copy code
SELECT merge_column,
                               gp_col1,
                               gp_multiplier,
                               DS_THETA(c2) as s2
                          FROM table2
                         WHERE gp_multiplier != 0 
                         GROUP BY gp_col1,
                      gp_multiplier
Because 
merge_column
is neither in 
GROUP BY
nor is it an aggregate
s
Actually we took static value 
xyz
as merge_column
Explained query for part working in v25.
Copy code
{
  "queryType": "groupBy",
  "dataSource": {
    "type": "join",
    "left": {
      "type": "query",
      "query": {
        "queryType": "groupBy",
        "dataSource": {
          "type": "table",
          "name": "table2"
        },
        "intervals": {
          "type": "intervals",
          "intervals": [
            "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
          ]
        },
        "filter": {
          "type": "and",
          "fields": [
            {
              "type": "not",
              "field": {
                "type": "bound",
                "dimension": "gp_multiplier",
                "lower": "0",
                "upper": "0",
                "ordering": {
                  "type": "numeric"
                }
              }
            },
            {
              "type": "not",
              "field": {
                "type": "selector",
                "dimension": "gp_col1",
                "value": null
              }
            }
          ]
        },
        "granularity": {
          "type": "all"
        },
        "dimensions": [
          {
            "type": "default",
            "dimension": "gp_col1",
            "outputName": "d0",
            "outputType": "STRING"
          },
          {
            "type": "default",
            "dimension": "gp_multiplier",
            "outputName": "d1",
            "outputType": "STRING"
          }
        ],
        "aggregations": [
          {
            "type": "thetaSketch",
            "name": "a0",
            "fieldName": "c2",
            "size": 16384,
            "shouldFinalize": false
          }
        ],
        "postAggregations": [
          {
            "type": "expression",
            "name": "p0",
            "expression": "0"
          }
        ],
        "limitSpec": {
          "type": "NoopLimitSpec"
        },
        "context": {
          "queryId": "06fae828-88e7-4963-a7e4-8da9061e949e",
          "sqlOuterLimit": 1001,
          "sqlQueryId": "06fae828-88e7-4963-a7e4-8da9061e949e",
          "useNativeQueryExplain": true
        }
      }
    },
    "right": {
      "type": "query",
      "query": {
        "queryType": "timeseries",
        "dataSource": {
          "type": "table",
          "name": "table1"
        },
        "intervals": {
          "type": "intervals",
          "intervals": [
            "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
          ]
        },
        "granularity": {
          "type": "all"
        },
        "aggregations": [
          {
            "type": "thetaSketch",
            "name": "a0",
            "fieldName": "c1",
            "size": 16384,
            "shouldFinalize": false
          }
        ],
        "postAggregations": [
          {
            "type": "expression",
            "name": "p0",
            "expression": "0"
          }
        ],
        "context": {
          "queryId": "06fae828-88e7-4963-a7e4-8da9061e949e",
          "sqlOuterLimit": 1001,
          "sqlQueryId": "06fae828-88e7-4963-a7e4-8da9061e949e",
          "useNativeQueryExplain": true
        }
      }
    },
    "rightPrefix": "j0.",
    "condition": "(\"p0\" == \"j0.p0\")",
    "joinType": "INNER"
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "default",
      "dimension": "d0",
      "outputName": "_d0",
      "outputType": "STRING"
    },
    {
      "type": "default",
      "dimension": "d1",
      "outputName": "_d1",
      "outputType": "STRING"
    }
  ],
  "aggregations": [
    {
      "type": "thetaSketch",
      "name": "_a0",
      "fieldName": "a0",
      "size": 16384,
      "shouldFinalize": false
    },
    {
      "type": "thetaSketch",
      "name": "_a1",
      "fieldName": "j0.a0",
      "size": 16384,
      "shouldFinalize": false
    }
  ],
  "postAggregations": [
    {
      "type": "thetaSketchEstimate",
      "name": "s1",
      "field": {
        "type": "fieldAccess",
        "name": "s0",
        "fieldName": "_a0"
      }
    },
    {
      "type": "thetaSketchEstimate",
      "name": "s3",
      "field": {
        "type": "fieldAccess",
        "name": "s2",
        "fieldName": "_a1"
      }
    },
    {
      "type": "thetaSketchEstimate",
      "name": "s7",
      "field": {
        "type": "thetaSketchSetOp",
        "name": "s6",
        "func": "INTERSECT",
        "size": 16384,
        "fields": [
          {
            "type": "fieldAccess",
            "name": "s4",
            "fieldName": "_a0"
          },
          {
            "type": "fieldAccess",
            "name": "s5",
            "fieldName": "_a1"
          }
        ]
      }
    },
    {
      "type": "expression",
      "name": "s8",
      "expression": "(CAST(\"_d1\", 'DOUBLE') * \"s7\")"
    }
  ],
  "limitSpec": {
    "type": "default",
    "columns": [],
    "limit": 1001
  },
  "context": {
    "queryId": "06fae828-88e7-4963-a7e4-8da9061e949e",
    "sqlOuterLimit": 1001,
    "sqlQueryId": "06fae828-88e7-4963-a7e4-8da9061e949e",
    "useNativeQueryExplain": true
  }
}
Explained query for Query working in v22 and giving issues in v25.
Copy code
{
  "queryType": "groupBy",
  "dataSource": {
    "type": "query",
    "query": {
      "queryType": "groupBy",
      "dataSource": {
        "type": "join",
        "left": {
          "type": "query",
          "query": {
            "queryType": "groupBy",
            "dataSource": {
              "type": "table",
              "name": "table2"
            },
            "intervals": {
              "type": "intervals",
              "intervals": [
                "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
              ]
            },
            "filter": {
              "type": "and",
              "fields": [
                {
                  "type": "not",
                  "field": {
                    "type": "bound",
                    "dimension": "gp_multiplier",
                    "lower": "0",
                    "upper": "0",
                    "ordering": {
                      "type": "numeric"
                    }
                  }
                },
                {
                  "type": "not",
                  "field": {
                    "type": "selector",
                    "dimension": "gp_col1",
                    "value": null
                  }
                }
              ]
            },
            "granularity": {
              "type": "all"
            },
            "dimensions": [
              {
                "type": "default",
                "dimension": "gp_col1",
                "outputName": "d0",
                "outputType": "STRING"
              },
              {
                "type": "default",
                "dimension": "gp_multiplier",
                "outputName": "d1",
                "outputType": "STRING"
              }
            ],
            "aggregations": [
              {
                "type": "thetaSketch",
                "name": "a0",
                "fieldName": "c2",
                "size": 16384,
                "shouldFinalize": false
              }
            ],
            "postAggregations": [
              {
                "type": "expression",
                "name": "p0",
                "expression": "0"
              }
            ],
            "limitSpec": {
              "type": "NoopLimitSpec"
            },
            "context": {
              "queryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
              "sqlOuterLimit": 1001,
              "sqlQueryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
              "useNativeQueryExplain": true
            }
          }
        },
        "right": {
          "type": "query",
          "query": {
            "queryType": "timeseries",
            "dataSource": {
              "type": "table",
              "name": "table1"
            },
            "intervals": {
              "type": "intervals",
              "intervals": [
                "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
              ]
            },
            "granularity": {
              "type": "all"
            },
            "aggregations": [
              {
                "type": "thetaSketch",
                "name": "a0",
                "fieldName": "c1",
                "size": 16384,
                "shouldFinalize": false
              }
            ],
            "postAggregations": [
              {
                "type": "expression",
                "name": "p0",
                "expression": "0"
              }
            ],
            "context": {
              "queryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
              "sqlOuterLimit": 1001,
              "sqlQueryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
              "useNativeQueryExplain": true
            }
          }
        },
        "rightPrefix": "j0.",
        "condition": "(\"p0\" == \"j0.p0\")",
        "joinType": "INNER"
      },
      "intervals": {
        "type": "intervals",
        "intervals": [
          "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
        ]
      },
      "granularity": {
        "type": "all"
      },
      "dimensions": [
        {
          "type": "default",
          "dimension": "d0",
          "outputName": "_d0",
          "outputType": "STRING"
        },
        {
          "type": "default",
          "dimension": "d1",
          "outputName": "_d1",
          "outputType": "STRING"
        }
      ],
      "aggregations": [
        {
          "type": "thetaSketch",
          "name": "_a0:a",
          "fieldName": "a0",
          "size": 16384,
          "shouldFinalize": false
        },
        {
          "type": "thetaSketch",
          "name": "_a1:a",
          "fieldName": "j0.a0",
          "size": 16384,
          "shouldFinalize": false
        }
      ],
      "postAggregations": [
        {
          "type": "thetaSketchEstimate",
          "name": "p1",
          "field": {
            "type": "fieldAccess",
            "name": "p0",
            "fieldName": "_a0:a"
          }
        },
        {
          "type": "thetaSketchEstimate",
          "name": "p3",
          "field": {
            "type": "fieldAccess",
            "name": "p2",
            "fieldName": "_a1:a"
          }
        },
        {
          "type": "thetaSketchEstimate",
          "name": "p7",
          "field": {
            "type": "thetaSketchSetOp",
            "name": "p6",
            "func": "INTERSECT",
            "size": 16384,
            "fields": [
              {
                "type": "fieldAccess",
                "name": "p4",
                "fieldName": "_a0:a"
              },
              {
                "type": "fieldAccess",
                "name": "p5",
                "fieldName": "_a1:a"
              }
            ]
          }
        },
        {
          "type": "expression",
          "name": "p8",
          "expression": "(CAST(\"_d1\", 'DOUBLE') * \"p7\")"
        }
      ],
      "limitSpec": {
        "type": "NoopLimitSpec"
      },
      "context": {
        "queryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
        "sqlOuterLimit": 1001,
        "sqlQueryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
        "useNativeQueryExplain": true
      }
    }
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "default",
      "dimension": "_d0",
      "outputName": "d0",
      "outputType": "STRING"
    }
  ],
  "aggregations": [
    {
      "type": "doubleSum",
      "name": "a0",
      "fieldName": "p1"
    },
    {
      "type": "doubleSum",
      "name": "a1",
      "fieldName": "p3"
    },
    {
      "type": "doubleSum",
      "name": "a2",
      "fieldName": "p8"
    }
  ],
  "limitSpec": {
    "type": "default",
    "columns": [],
    "limit": 1001
  },
  "context": {
    "queryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
    "sqlOuterLimit": 1001,
    "sqlQueryId": "1b05327f-6904-4e6b-8876-225929d81ab9",
    "useNativeQueryExplain": true
  }
}
g
is the native query (from 
EXPLAIN PLAN FOR
) the same in 22 and 25? I'm wondering if the issue is the plan changed, or the plan stayed the same but the behavior of the native query changed
s
Explain Query plan on version 22
Copy code
DruidOuterQueryRel(query=[{"queryType":"topN","dataSource":
  {"type":"table","name":"__subquery__"},
  "virtualColumns":[],
  "dimension":{"type":"default","dimension":"gp_col1","outputName":"d0","outputType":"STRING"},
  "metric":{"type":"dimension","previousStop":null,"ordering":{"type":"lexicographic"}},
  "threshold":101,"intervals":{"type":"intervals",
  "intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},
  "filter":null,
  "granularity":{"type":"all"},
  "aggregations":[{"type":"doubleSum","name":"a0","fieldName":"ts2","expression":null},
    {"type":"doubleSum","name":"a1","fieldName":"ts1","expression":null},
    {"type":"doubleSum","name":"a2","fieldName":"ts3","expression":null}],
  "postAggregations":[],"context":{"sqlOuterLimit":101,"sqlQueryId":"ba9dcb86-9f4f-4c01-ab04-569ffd1cec08"},
"descending":false}],
 signature=[{d0:STRING, a0:DOUBLE, a1:DOUBLE, a2:DOUBLE}]) 
DruidJoinQueryRel(condition=[=($0, $4)], 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":null,"granularity":{"type":"all"},
    "dimensions":
    [{"type":"default","dimension":"gp_col1","outputName":"d0","outputType":"STRING"},
      {"type":"default","dimension":"gp_multiplier","outputName":"d1","outputType":"STRING"}],
    "aggregations":[
      {"type":"thetaSketch","name":"a0","fieldName":"s2","size":16384,"shouldFinalize":true,"isInputThetaSketch":false,"errorBoundsStdDev":null},
  {"type":"thetaSketch","name":"a1","fieldName":"EXPR$1","size":16384,
  "shouldFinalize":true,"isInputThetaSketch":false,"errorBoundsStdDev":null}],
    "postAggregations":[{"type":"thetaSketchEstimate","name":"p1",
      "field":{
      "type":"fieldAccess","name":"p0","fieldName":"a0"},"errorBoundsStdDev":null},
      {"type":"thetaSketchEstimate","name":"p3",
        "field":{"type":"fieldAccess","name":"p2","fieldName":"a1"},"errorBoundsStdDev":null},
        {"type":"thetaSketchEstimate","name":"p7",
          "field":{"type":"thetaSketchSetOp","name":"p6","func":"INTERSECT","size":16384,
            "fields":[{"type":"fieldAccess","name":"p4","fieldName":"a0"},
              {"type":"fieldAccess","name":"p5","fieldName":"a1"}]},"errorBoundsStdDev":null},
            {"type":"expression","name":"p8","expression":"(CAST(\"d1\", 'DOUBLE') * p7)","ordering":null}
          ],"having":null,"limitSpec":{"type":"NoopLimitSpec"},
    "context":{"sqlOuterLimit":101,"sqlQueryId":"ba9dcb86-9f4f-4c01-ab04-569ffd1cec08"},"descending":false}], 
  signature=[{d0:STRING, p1:DOUBLE, p3:DOUBLE, p8:DOUBLE}]) 
DruidQueryRel(query=[{"queryType":"groupBy","dataSource":{"type":"table","name":"table2"},
  "intervals":{"type":"intervals","intervals":["2023-03-01T00:00:00.000Z/2023-03-01T00:00:00.001Z"]},
  "virtualColumns":[],"filter":{
    "type":"and","fields":[{
      "type":"not","field":{
        "type":"bound","dimension":"gp_multiplier","lower":"0","upper":"0","lowerStrict":false,"upperStrict":false,"extractionFn":null,"ordering":{
        "type":"numeric"}}},{
          "type":"not","field":{
          "type":"selector","dimension":"gp_col1","value":null,"extractionFn":null}}]},
    "granularity":{"type":"all"},
    "dimensions":[{"type":"default","dimension":"gp_col1","outputName":"d0","outputType":"STRING"},
      {"type":"default","dimension":"gp_multiplier","outputName":"d1","outputType":"STRING"}],
    "aggregations":[{"type":"thetaSketch","name":"a0","fieldName":"c2","size":16384,"shouldFinalize":true,"isInputThetaSketch":false,"errorBoundsStdDev":null}],
    "postAggregations":[{
    "type":"expression","name":"p0","expression":"0","ordering":null},{
    "type":"fieldAccess","name":"p1","fieldName":"a0"}],"having":null,
    "limitSpec":{"type":"NoopLimitSpec"},"context":{
    "sqlOuterLimit":101,"sqlQueryId":"ba9dcb86-9f4f-4c01-ab04-569ffd1cec08"},"descending":false}],
     signature=[{p0:LONG, d0:STRING, d1:STRING, p1:COMPLEX}]) 
DruidQueryRel(query=[{"queryType":"timeseries","dataSource":{
"type":"table","name":"table1"},
"intervals":{
"type":"intervals",
"intervals":["2023-03-15T00:00:00.000Z/2023-03-16T00:00:00.000Z"]},
"descending":false,"virtualColumns":[],"filter":null,"granularity":{"type":"all"},
"aggregations":[{
"type":"thetaSketch","name":"a0","fieldName":"c1","size":16384,"shouldFinalize":true,"isInputThetaSketch":false,"errorBoundsStdDev":null}],
"postAggregations":[{"type":"expression","name":"p0","expression":"0","ordering":null},
  {"type":"fieldAccess","name":"p1","fieldName":"a0"}],"limit":2147483647,"context":{
"sqlOuterLimit":101,"sqlQueryId":"ba9dcb86-9f4f-4c01-ab04-569ffd1cec08"}}], signature=[{p0:LONG, p1:COMPLEX}])
@Vadim @Gian Merlino We got different query plan in v22 and v25 for same query. We verified the query plan from v25 works in v22 but doesn't work in v25. We are feeling issue comes in v25 when we join and multiplex / demultiplex rows of the sketch values. Any pointers what could be the problem and how to resolve this ?
d
oh… we did see very similar issue after upgrading to Druid 25. The smoking gun seemed to be: 1. The query plan changed. 2. Because of that, some of our historical daemons started to handle a lot more data and thus starts timing out. Join regression seemed to come from v24 and newer.
s
@Didip Kerabat were you able to find any solution for the Join issue ?
Which version are using now ?
d
Unfortunately, because it’s mission critical to stay on Druid 25, we asked the developers and data engineers to rewrite their queries either at Druid level or Spark level (creating new denormalized columns instead of performing JOIN at Druid level).
g
@shubhra gupta Sorry, I did spend some time trying to reproduce this but haven't yet figured it out. If you have a way to repro this that works on a fresh install, that would be extremely helpful! Otherwise I'll keep poking at it trying to come up with a repro @Didip Kerabat if you have any more info about the one you saw, I'd love to learn about it so we can add a planner test case. Hate to see plans get worse when versions change, & the best way to prevent that is having more planner test cases
👍 1
Open in Slack
PreviousNext
Powered by