https://linen.dev logo
Join Slack
Powered by
I'm getting null results in a set subtraction when...
# general
j
I'm getting null results in a set subtraction when the NOT IN clause has a lot of results. Example:
Copy code
SELECT *
FROM datasource1
WHERE Id NOT IN (
  SELECT DISTINCT Id
  FROM datasource2
  WHERE <criteria>
)
^ Works when inner query returns 208K results ^ Returns null when inner query returns 400K results Note: I set 
maxSubqueryRows
to 1 million Also note: It is not possible to have no results. I double checked by checking both datasources for specific Ids.
Oh, strange. I got this to work by rewriting the query into a LEFT JOIN:
Copy code
SELECT *
FROM datasource1
LEFT JOIN (
  SELECT DISTINCT Id
  FROM datasource2
  WHERE <criteria>
) ON datasource1.Id = datasource2.Id
WHERE datasource2.Id IS NULL
j
Did you also try removing the DISTINCT from the subquery in the NOT IN () version? It does produce a different native query.
j
Removing DISTINCT does't work.
Explained Query with the NOT IN clause:
Untitled.json
Untitled.json
Explained Query with the LEFT JOIN:
Untitled.json
Untitled.json
The first query returns null The second query returns 694,190
Query 1:
Copy code
SELECT SUM("count")
FROM datasource1
WHERE __time >= '2024-12-01' AND __time < '2025-01-01'
AND Id NOT IN (
  SELECT Id
  FROM datasource2
  WHERE __time >= '2024-12-01' AND __time < '2025-01-04'
  GROUP BY 1
)
Query2:
Copy code
SELECT SUM("count")
FROM datasource1 ds1
LEFT JOIN (
  SELECT Id
  FROM datasource2
  WHERE __time >= '2024-12-01' AND __time < '2025-01-04'
  GROUP BY 1
) ds2 ON ds1.Id = ds2.Id
WHERE __time >= '2024-12-01' AND __time < '2025-01-01'
AND ds2.Id IS NULL
Actually I forgot the:
Copy code
AND ds1.Id IS NOT NULL
I can't edit inline but adding the
Copy code
AND ds1.Id IS NOT NULL
doesn't affect the outcome What I CAN say is that if the inner query returns less than 100k rows than the NOT IN query works. It seems if the inner query returns too many rows than the NOT IN query just returns NULL.
j
Almost forgot .. are you using ANSI NULL handling mode or legacy mode? That could make a difference too.
j
ANSI NULL
b
100k is the default for 
druid.server.http.maxSubqueryRows
, maybe related. This sounds like a bug to me.
If you set maxSubqueryRows to 1000000 in the query context, does it run?
I just verified, if the NOT IN has >= 100k, it fails by default. If it has fewer, or if I set maxSubqueryRows bigger than the number, then it works.
Not sure where you saw null results; in the console, I got an error about the subquery rows.
j
JRob, do you have any of these properties set?
Copy code
#druid.generic.useDefaultValueForNull=true
#druid.expressions.useStrictBooleans=false
#druid.generic.useThreeValueLogicForNativeFilters=false
and what version of Druid are you running?
j
@Ben Krug, yes, I set maxSubqueryRows in the query context. Without it I get an ResourceLimitExceededException: "Subquery generated results beyond maximum[100000] rows"
@John Kowtko We are using default values for those three properties
b
Oh, sorry, I missed earlier that you said that you set maxSubqueryRows. It was working for me though. What version of druid?
j
29.0.1
j
Try toggling 
useThreeValueLogicForNativeFilters
to see if it makes any difference.
j
I can't easily toggle that as I'm doing this on a production system.
I gotta say, the query plan for the LEFT JOIN is significantly simpler than the query plan with the IN clause.
Open in Slack
PreviousNext
Powered by