The data returned by the query on the first run differs from the data on the second and subsequent runs

Question

Yesterday the new oracle server was installed. DB was migrated to exadata. Query that before was working fine suddenly started to show different results for the first run and for subsequent runs.

SELECT CASE
         WHEN MAX(recommended_unit_retail) != MIN(recommended_unit_retail) THEN
          item
         ELSE
          '-1'
       END item
      ,MIN(recommended_unit_retail) min_ur
      ,MAX(recommended_unit_retail) max_ur
  FROM (SELECT item
              ,loc
              ,recommended_unit_retail
              ,action_date
          FROM (SELECT rps.item
                      ,rps.loc
                      ,rps.recommended_unit_retail
                      ,rps.action_date                      
                      ,row_number() over(PARTITION BY rps.item, rps.loc ORDER BY rps.action_date DESC
                        ) rn
                  FROM uda_item_lov uil
                  JOIN xxlm_recom_prc_storage rps
                    ON rps.item = uil.item
                   AND rps.status IN ('S', 'P')
                   AND rps.action_date <= trunc(SYSDATE + 1)
                  JOIN store s
                    ON s.store = rps.loc
                   AND s.district = 1
                   AND s.store_open_date IS NOT NULL
                   AND s.store_open_date <= trunc(SYSDATE)
                  JOIN item_loc il
                    ON il.item = rps.item
                   AND il.loc = rps.loc
                   AND il.status = 'A'
                 WHERE uil.uda_id = 5
                   AND uil.uda_value != 6
                   AND uil.item = ANY(82584520, 82142283, 82377842))
         WHERE rn = 1)
 GROUP BY item;

The data on the first run is

---------------------
-1 | 548  | 548
-1 | 1708 | 1708
-1 | 1926 | 1926
---------------------

For the second and subsequent runs it's

----------------------------
82584520 | 548  | 710
82142283 | 1708 | 1926
82377842 | 1926 | 1937
----------------------------

The plan for all queries is the same. What could be the problem?

P.S. I've thought it could be related to the different date NLS settings, but we checked and it's the same as it was on the old server.

Answer 1

As Gary Myers said in comments, the problem appears when "the items in the row_number analytic PARTITION / ORDER BY aren't unique, then the different rows could be different orderings depending on the query plan and the rn=1 could be 'picking any of the rows with that item, loc and action_date". According to our case, the problem is that data is wrong.