"IN" and "NOT IN" with subquery in SQL queries

[abossert]

Affected Version

0.16.0 (and likely earlier too)

Description

I noticed that the same issue was reported here: #8455, but was closed out. I am not sure why.

When I run the following query, it succeeds:

SELECT *
FROM myTable
WHERE
  query IN(
    SELECT
      DISTINCT query
    FROM
      myTable
    LIMIT 100
  )
LIMIT 100

However, running the exact same query with NOT IN, rather than IN:

SELECT *
FROM myTable
WHERE
  query NOT IN(
    SELECT
      DISTINCT query
    FROM
      myTable
    LIMIT 100
  )
LIMIT 100

I get the following error:

Unknown exception / Cannot build plan for query: SELECT * FROM (SELECT * FROM myTable WHERE query NOT IN( SELECT DISTINCT query FROM myTable LIMIT 100 ) LIMIT 100 ) LIMIT 100 / org.apache.druid.java.util.common.ISE

I have not been able to find any other error in the logs for the Broker. The error above shows up in the query UI.

[ntkawasaki]

Similar issue, but not in a subquery context just in a list of values:

Works:

SELECT 
	COUNT(*) AS "orders.count"
FROM druid.orders  AS orders

WHERE 
	orders.id in (1, 3, 5, 6, 7, 10, 12, 14, 15)  
LIMIT 20

SELECT 
	COUNT(*) AS "orders.count"
FROM druid.orders  AS orders

WHERE 
	NOT COALESCE(orders.id in (1, 3, 5, 6, 7, 10, 12, 14, 15)  , FALSE)
LIMIT 20

Throws:

Error -1 (00000) : Error while executing SQL "-- Looker Query Context '{"user_id":866,"history_id":5907381,"instance_slug":"b6ff28049851f1954156526c66ca9912"}' SELECT COUNT(*) AS "orders.count" FROM druid.orders AS orders WHERE NOT COALESCE(orders.id in (1, 3, 5, 6, 7, 10, 12, 14, 15) , FALSE) LIMIT 20": Remote driver error: ClassCastException: org.apache.calcite.sql.fun.SqlCase cannot be cast to org.apache.calcite.sql.SqlBasicCall