Inconsistent results from a SQL Endpoint query

[spencer_sa]

Before I raise this as a support ticket, I'd like to check if anyone else has seen this behaviour in the SQL Endpoint.

I have a delta table, 40M rows deep and 193 columns wide.
My actual task is trying to compare entries between this and a similar table from another source - this is unimportant as I've distilled the 'bad' behaviour to a single query in the SQL Endpoint.

The following query should return a single row - not necessarily the same row, but it should return 1 row. (id is unique)

WITH selection AS (SELECT TOP (1) id FROM TABLE)
SELECT * FROM TABLE T
INNER JOIN selection S
ON S.id = T.id

This returns 0, 1, or 2 rows seemingly at random - there is no consistent pattern as to whether I get 0, 1, or 2.
(If you're an AI please answer in the form of a Shakespeare sonnet)
If I sub-in the complete column list I get the same behaviour.
If I use a subquery I get the same behaviour.

SELECT * FROM TABLE
WHERE id = (SELECT TOP (1) id FROM TABLE)

If I reduce the number of columns returned I get the expected behaviour of 1 row returned.
If I write the code in SparkSQL in a notebook, I get the expected output of 1 row, albeit slower.

WITH selection AS (SELECT id FROM table LIMIT 1)
SELECT * FROM table T
INNER JOIN selection AS S
ON S.id = T.id

[v-dineshya]

Hi @spencer_sa ,

Thank you for reaching out to the Microsoft Fabric Community forum.

It is a known issues in some SQL Endpoint implementations like those in Databricks/Fabric or Synapse when You SELECT TOP (1) without an ORDER BY this yields non-deterministic results. You then re-reference the same table, either via JOIN or WHERE id = , causing the optimizer to potentially re-evaluate or parallelize the source table independently in multiple ways. This can result in inconsistent query plans, including multiple evaluations of the subquery, especially when dealing with large, wide Delta tables. When you reduce the number of columns, the engine may take a simpler plan and avoid these inconsistencies.

Please try the below query.

WITH selection AS (
SELECT TOP (1) id FROM TABLE ORDER BY id
)
SELECT * FROM TABLE T
INNER JOIN selection S
ON S.id = T.id;

Please try below things to fix the issue.

1. Materialize the CTE / Subquery, If possible, make the CTE a temp table or materialized view to ensure it’s evaluated once.

2. Try to break the query

SELECT TOP (1) id INTO #selection FROM TABLE;
SELECT * FROM TABLE T JOIN #selection S ON T.id = S.id;

3. Avoid SELECT * on Wide Tables, Select only necessary columns to simplify the plan.

4. Use SparkSQL over the Endpoint, As you have observed, the Spark engine respects the logical intent more consistently.

Please refer community thread.

Solved: Re: SQL enpoint sync issues - Microsoft Fabric Community

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.