WHERE EXISTS fails

The following query fails for me on OmniSci 5.5.2-20210222-aefdf7ece2.

SELECT COUNT(*)
FROM max_dim_date t
WHERE EXISTS
(
  SELECT NULL
  FROM w_8aaa_sales as s
  WHERE s.saledate = t.the_date
)

I get the following error in DBeaver, which usually indicates an internal error in OmniSci.

[TIOStreamTransport.java:read:130:org.apache.thrift.transport.TTransportException: Socket is closed by peer.]

There’s a one-to-many relationship from max_dim_date to w_8aaa_sales, so I cannot do a join because the count will be incorrect.

Are you able to repro?

Is there another query that I can use to work around the problem?

Hi @Larry_Parker,

Do the query works if you use another values beside the null on exists subquery?

SELECT COUNT(*)
FROM max_dim_date t
WHERE EXISTS
(
  SELECT 1
  FROM w_8aaa_sales as s
  WHERE s.saledate = t.the_date
)

No, same (internal) error doing SELECT 1.

Hi Larry,

I ran this query

select count(*) from max_date_test where exists (select null from test_date where test_date.O_ORDERDATE=max_date_test.O_ORDERDATE);

On both CPU and GPU mode without issue.

The max_date_test has the same 2406 distinct values of the test_date table with 100M records; tried adding different dates, running against a table of 3 Billion records with other fields, with no luck; everything is working.

CREATE TABLE max_date_test (
  O_ORDERDATE DATE ENCODING DAYS(16))
WITH (MAX_CHUNK_SIZE=2147483648, MAX_ROLLBACK_EPOCHS=3);

I’m using a date datatype (with an optimized encoding).

Then I tried to change the datatype from date to timestamp and I got a crash with the same query

as a workaround, you can try to change the query this way

select count(*) from max_date_test where o_orderdate in (select distinct o_orderdate from test_date );

it’s a semi-join so you won’t have a duplication of records, but performance-wise, if you aren’t using the time part, would be better to change the datatype to DATE (16 and 32-bit both works).

Regards
Candido