Hi Druids, I am new to Druid and just getting into joins. I am on version 0.22.1 and have two datasources i wish to join using native query. But i am getting error while trying to use JOINS which i believe should work in this version? Below is the query and error i am getting. Any help much appreciated on this

QUERY:
{
  "queryType": "groupBy",
  "dataSource": {
    "type": "join",
    "left": "transactions",
    "right": {
      "type": "table",
      "name": "prices"
    },
    "rightPrefix": "p.",
    "condition": "ac_id == \"p.ac_id\"",
    "joinType": "INNER"
  },
  "intervals": ["2024-04-01T00:00:00+00:00\/2024-06-26T23:59:59+00:00"],
  "granularity": "all",
  "dimensions": ["ac_id"],
  "aggregations": [
    {
      "type": "count",
      "name": "transaction_count"
    }
  ]
}

ERROR:

{
  "error": "Unknown exception",
  "errorMessage": "Cannot handle subquery structure for dataSource: JoinDataSource{left=transactions, right=prices, rightPrefix='p.', condition=ac_id == \"p.ac_id\", joinType=INNER, leftFilter=null}",
  "errorClass": "org.apache.druid.java.util.common.ISE",
  "host": null
}

I typically use SQL but try:

"condition": "transactions.ac_id == prices.ac_id"

@JRob: I tried that as well, but got same error

@Bhavok - For native queries, the right-hand data source must be

lookup

,

query

, or

inline

. Could you convert the right hand to a subquery? https://docs.imply.io/latest/druid/querying/datasource/#joins-in-native-queries

@Benjamin Hopp: Thanks Benjamin. How ever i am not getting query error now, but i get Subquery generated results beyond maximum[100000] error. I have nearly 1-2 million records to be queried at a time, Do you think increasing subQuery limit is sufficient enough to resolve it?

Rather than increasing the rows, I would recommend switching to a byte based subquery limit. https://druid.apache.org/docs/latest/release-info/migr-subquery-limit/#enable-a-byte-based-subquery-limit

You could also try writing this in SQL first and then "Explain Plan" to see the native that it generates ... that should provide some helpful hints ...

@Bhavok Maybe add some intervals and conditions to your subquery