topic Re: Databricks SQL Exists does not work correct? in Data Engineering

Databricks SQL Exists does not work correct?

sergecom — Thu, 20 Mar 2025 09:08:37 GMT

Can someone explain to me how this is possible?

Re: Databricks SQL Exists does not work correct?

Shua42 — Thu, 20 Mar 2025 21:11:57 GMT

I replicated the query exactly but I couldn't produce the same results, and got back the expected result of the single row with 'aaa'. My initial thought is that it could be a bug in the runtime. Can you share the runtime of the cluster you are using?

Re: Databricks SQL Exists does not work correct?

sergecom — Thu, 20 Mar 2025 21:30:38 GMT

Thanks for your reply @Shua42 !
I use 15.4 LTS
Here is the summary of cluster configuration:

Re: Databricks SQL Exists does not work correct?

Shua42 — Thu, 20 Mar 2025 22:35:08 GMT

@sergecom This looks to be a possible bug, thank you for flagging. It looks like the EXISTS function works as intended with Serverless Compute and Serverless SQL Warehouses, but exhibits a different behavior when run on all-purpose compute. I've raised a ticket so that they can look into this.

Re: Databricks SQL Exists does not work correct?

sergecom — Fri, 21 Mar 2025 05:11:00 GMT

Thanks for the update! Let me know if you need any more details from my side—happy to help.
Is there any way to follow this ticket @Shua42 ?

Re: Databricks SQL Exists does not work correct?

marco1995 — Fri, 21 Mar 2025 07:55:31 GMT

We were able to reproduce the problem, but it only occurs in Databricks SQL version 2025.10. With version 2024.05, the correct result is obtained.

Re: Databricks SQL Exists does not work correct?

sergecom — Fri, 21 Mar 2025 09:14:43 GMT

Thanks for feedback @MarcoI've also tested with an older All-purpose Runtime 12.2 LTS and it works.
But its alarming that the latest runtime does not work as expected.

I will post SQL code so it will be easier for other to test. Both these queries give wrong results:

%sql

WITH a AS (

SELECT 'aaa' as produktkod

UNION ALL

SELECT 'bbb' as produktkod

b AS (

SELECT 'aaa' as produktkod

UNION ALL

SELECT 'ccc' as produktkod

)

SELECT a.*

FROM a

WHERE exists (

select 1 from b where a.produktkod = b.produktkod

);

%sql
WITH a AS (
SELECT 'aaa' as produktkod
UNION ALL
SELECT 'bbb' as produktkod
),
b AS (
SELECT 'aaa' as produktkod
UNION ALL
SELECT 'ccc' as produktkod
)
SELECT a.*,
(SELECT COUNT(1)
FROM b
WHERE b.produktkod = a.produktkod) as match_count
FROM a;

Re: Databricks SQL Exists does not work correct?

Shua42 — Fri, 21 Mar 2025 22:02:51 GMT

@sergecom The engineering team worked on this and I believe it is now resolved for the newer runtimes. I confirmed it nows works as expected on 16.2. Can you confirm if it works on your end?

Re: Databricks SQL Exists does not work correct?

sergecom — Mon, 24 Mar 2025 14:52:44 GMT

Hi @Shua42
I can confirm that the EXISTS/NOT EXISTS query is working as expected now in the 15.4 LTS . Thank you for fixing it so quickly!

But the subquery issue still NOT fixed. Before I was getting two rows (that is incorrect). Now I'm getting an error:

Re: Databricks SQL Exists does not work correct?

sergecom — Tue, 25 Mar 2025 09:46:42 GMT

@Shua42 I've realized that I didn’t mention the subquery issue in my first post, so I guess this can be handled as a separate ticket.