Write consistency with nested subquery in Oracle

I've read many of the gory details of write consistency and I understand how it works in the simple cases. What I'm not clear on is what this means for nested sub-queries.

Here's a concrete example:

A table with PK id, and other columns state, temp and date.

UPDATE table SET state = DECODE(state, 'rainy', 'snowy', 'sunny', 'frosty') WHERE id IN (
  SELECT id FROM (
    SELECT id,state,temp from table WHERE date > 50      
  ) WHERE (state='rainy' OR state='sunny') AND temp < 0
)

The real thing was more convoluted (in the innermost query), but this captures the essence.

If we assume the state column is not nullable, can this update ever fail due to concurrent modification (i.e., the DECODE function doesn't find a match, a value of 'rainy' or 'sunny', and so tries to insert null into a non-nullab开发者_Go百科le column)?

---

Oracle supports "statement level read and write consistency" (as all other serious DBMS)

This means that the statement as a whole will not see any changes to the database that occurred after the statement started.

As your UPDATE is one single statement there shouldn't be a case where the decode returns null.

Btw: the statement can be simplified, you don't need the outer SELECT in the sub-query:

UPDATE table SET state = DECODE(state, 'rainy', 'snowy', 'sunny', 'frosty') 
WHERE id IN (
    SELECT id
    FROM table 
    WHERE date > 50      
    AND (state='rainy' OR state='sunny') 
    AND temp < 0
)

---

I don't see any reason to be concerned. The subquery explicitly retrieves only IDs of rows with state 'rainy' or 'sunny' and that's what outer DECODE is going to get. Thole thing is one statement, and is going to be executed within transaction boundaries.

---

Answering my own question: turns out there is a bug in Oracle which can cause this query to fail. Details confirmed by Tom Kyte, in the discussion starting here.