title: Oracle Select Works, Update Doesn't, Same WHERE Clause
body: <p><strong>FINAL UPDATE:</strong></p>
  
  <p>Turns out, in our case anyway, that this was not a bug. Hidden away deep in the function call was an exception handler that was obfuscating an error message indicating that the function was mutating. The handler hid it away and caused the function to return what is essentially a valid value. So it's on us, this time. :)</p>
  
  <p><strong>Original Post:</strong></p>
  
  <p>First this is really more of a curiosity, now, as I've worked around the issue with a PL/SQL block. However, neither I nor any of my coworkers can figure out this update won't work. Does anyone have any ideas?</p>
  
  <p>I am trying to update a new column with appropriate values. Previously, the status of the record was determined using a function which computed status on demand, and we just converted it to use a dedicated table, allowing status to be stored and improving future flexibility.</p>
  
  <p>The below select works great - it pulls 14 records:</p>
  
  <pre><code>select * 
  from QUERY_TABLE QT1
  where QT1.P_ID in
    (select QT2.P_ID opi
      from QUERY_TABLE QT2
      where F_GET_STATUS(QT2.FUNC_VAL_1, 
        (select RT.FUNC_VAL_2 from RELATED_TABLE RT where RT.RELKEY = QT2.RELKEY)) = 'Value');
  </code></pre>
  
  <p>However, the below update, using the same WHERE clause, updates 0 records:</p>
  
  <pre><code>update QUERY_TABLE QT1
  set QT1.STAT_ID = 1
  where QT1.P_ID in
    (select QT2.P_ID opi
      from QUERY_TABLE QT2
      where F_GET_STATUS(QT2.FUNC_VAL_1, 
        (select RT.FUNC_VAL_2 from RELATED_TABLE RT where RT.RELKEY = QT2.RELKEY)) = 'Value');
  </code></pre>
  
  <p>Not sure if it will help, but the below PL/SQL block works fine to handle the update:</p>
  
  <pre><code>begin
  for x in (
    select QT2.P_ID opi
      from QUERY_TABLE QT2
      where F_GET_STATUS(QT2.FUNC_VAL_1, 
        (select RT.FUNC_VAL_2 from RELATED_TABLE RT where RT.RELKEY = QT2.RELKEY)) = 'Value')
  
  loop
    update QUERY_TABLE QT1
    set QT1.STAT_ID = 1
    where P_ID = x.opi;
    end loop;
  end;
  /
  </code></pre>
  
  <p>I've performed the update using the schema owner and another user with the appropriate privileges. There is not a trigger on the table invalidating the update. The function doesn't do anything weird, and the function and update don't eat each other (it's a brand new column - function is syntactically independent from the column's value). It doesn't give any error messages - it just updates 0 columns.</p>
  
  <p><strong>UPDATE 1</strong>
  For those who may have the same issue, we've contacted Oracle, and it turns out that this is a new bug. It has been logged as Bug 17015253 : UPDATE STATEMENT WITH FUNCTION IN SUBQUERY DOES NOT UPDATE ROWS, though I don't see it in the knowledge base, yet.</p>