I'm seeing non-deterministic behavior when running the same query in SQL Warehouse (Photon) vs. interactive/job clusters (non-Photon), specifically involving a LEFT OUTER JOIN and a DECIMAL comparison in a WHERE clause.

I have two views:

• View A: contains COL1 → DECIMAL(21,5)

• View B: contains COL2 → DECIMAL(38,6)

I’m joining them on two keys and comparing the decimal values in a filter:

 Unexpected Behavior

• When I run this in SQL Warehouse (Photon): I get a consistent and correct result.

• When I run the exact same query in a non-Photon interactive or job cluster:

  • I get inconsistent results across runs

  • The output row count changes on each run

  • When I narrow the filter to a specific key, e.g., EAN_CODE = '1234567890', the result is correct — but on the full dataset, results are wrong or unstable

 What I’ve Tried

1. Casting both columns to the same type:

   sql

    

   WHERE CAST(A.COL1 AS DECIMAL(38,6)) != CAST(B.COL2 AS DECIMAL(38,6))

   → Still inconsistent

2. Comparing as strings:

   sql

    

   WHERE CAST(A.COL1 AS STRING) != CAST(B.COL2 AS STRING)

   → Still inconsistent

3. Checked intermediate output (before WHERE filter):

   • Joins return expected row count consistently

   • The issue starts only when COL1 != COL2 filter is applied

4. Tried limiting to one EAN_CODE:

   • Correct behavior with expected results

   • Problem only occurs with full data volume