Wrong results with OR condition in WHERE clause

1

Hi.

I came across a situation where the results from a SELECT query are not the same depending on the order of conditions around “OR”.

I’m running the latest free Docker version(version 25.0.0-202404051521110861-ed9515a8).

Here is a simple way to replicate:

CREATE VIEW "@dremio".trades AS SELECT * from (values
    (cast(1 as bigint), 'p1', cast('2021-07-19' as date), cast(10 as double), 'ec-1', 'ms-7'),
    (2	,'p1',	cast('2021-07-19' as date)	,40	,'ec-1', 	'az-2'),
    (3	,'p1',	cast('2021-07-20' as date)	, 3	  ,'ec-1', 	'az-2'),
    (4	,'p1',	cast('2021-07-20' as date)	, 14	,'ec-1', 	'az-4'),
    (5	,'p2',	cast('2021-07-20' as date)	, 42	,'ms-4', 	'ms-7'),
    (6	,'p3',	cast('2021-07-21' as date)	, 33	,'ms-4', 	'ms-7')) AS t(id, product, "date", quantity, seller, buyer);

Then the query
SELECT * FROM "@dremio".trades WHERE ((product = 'p1' AND id = 3) OR buyer = 'ms-7')
returns 2 rows (which is wrong).

While the query
SELECT * FROM "@dremio".trades WHERE (buyer = 'ms-7' OR (product = 'p1' AND id = 3))
returns 4 rows (which is correct).

2

Would you share profiles for both?

3

Thanks @dch for having a look. :slight_smile:

Weirdly enough, when restarting a blank server, my toy example above didn’t replicate …

In any case, I could replicate the actual issue. The initial view is created via the REST API with the following query:

SELECT * from (values
    (cast(1 as bigint), 'p1', cast('2021-07-19' as date), cast(10 as double), 'ec-1', 'ms-7', 'lon-1'),
    (2	,'p1',	cast('2021-07-19' as date)	,40	,'ec-1', 	'az-2',	'lon-1'),
    (3	,'p1',	cast('2021-07-20' as date)	, 3	  ,'ec-1', 	'az-2',	'lon-1'),
    (4	,'p1',	cast('2021-07-20' as date)	, 14	,'ec-1', 	'az-4',	'bir-2'),
    (5	,'p2',	cast('2021-07-20' as date)	, 42	,'ms-4', 	'ms-7',	'chi-3'),
    (6	,'p2',	cast('2021-07-20' as date)	, 74	,'az-2', 	'ec-1',	'mon-2'),
    (7	,'p2',	cast('2021-07-21' as date)	, 10	,'az-2', 	'az-4',	'mon-2'),
    (8	,'p2',	cast('2021-07-21' as date)	, 35	,'az-2', 	'ms-4',	'tor-1'),
    (9	,'p3',	cast('2021-07-21' as date)	, 33	,'ms-4', 	'ms-7',	'nyo-1'),
    (10	,'p3',	cast('2021-07-21' as date)	, 44	,'ms-7', 	'ec-1',	'mar-2'))
AS t(id, product, "date", quantity, seller, buyer, warehouse);

And here are the 2 profiles which return different results.
CorrectResult.zip (20.8 KB)
WrongResult.zip (20.7 KB)

4

Thanks for the repo steps, will review this and get back to you