Subquery is failing on SQL Server

1

SELECT a.orderid,a.orderdate,a.productname,a.companyname,a.region,a.categoryname,a.quantity,a.unitprice,a.sales_revenue,a.discount
FROM Northwind.“Logical Schema”.NORTHWIND_DATA_MODEL a
where
a.categoryname in (‘Beverages’,‘Condiments’,‘Confections’,‘Dairy Products’,‘Produce’,‘Seafood’)
and a.orderid in (select b.orderid from Northwind.“Reports”.“European Orders” b)

Here is error

Node [rel#351726:Subset#43.LOGICAL.ANY([]).[]] could not be implemented; planner state:
Root: rel#351726:Subset#43.LOGICAL.ANY([]).[]

2

If I were you, I would rewrite the logic of that query to use a join clause…

3

True, it can be written as a join. But there are cases where I need subquery to work. Just wondering if Dremio can support subqueries like this…

4

Which version of Dremio is that? And I assume that’s SQL server?

5

It is sql server and dremio 3.0

6

Hi @bharath_natarajan

We certainly support subqueries but just discovered an issue with our planner that generates the above error in a subquery. We have an internal ticket already filed for this to get this sorted out and will have it fixed in one of the upcoming releases

Thanks
@balaji.ramaswamy

7

Hi @balaji.ramaswamy

I am also getting same error in Dremio 3.0C edition as well as with 2.1.6C version with IN sub-query.

Is it known issue for 2.1.6C version as well?

8

Hi . @Monika_Goel

Yes, it is a known issue in 2.1.6 as well

Thanks
@balaji.ramaswamy