Oracle SQL: Understanding the behavior of SYS_GUID() when present in an inline view?

Question

Here is the example SQL in question; The SQL should run on any Oracle DBMS (I'm running 11.2.0.2.0).

Note how the UUID values are different (one has 898 the other has 899) in the resultset despite being built from within the inline view/with clause. Further below you can see how DBMS_RANDOM.RANDOM() does not have this side effect.

SQL:

WITH data AS (SELECT SYS_GUID () uuid FROM DUAL)
    SELECT uuid, uuid
      FROM data

Output:

UUID                                      UUID_1
F8FCA4B4D8982B55E0440000BEA88F11      F8FCA4B4D8992B55E0440000BEA88F11

In Contrast DBMS_RANDOM the results are the same

SQL:

WITH data AS (SELECT DBMS_RANDOM.RANDOM() rand FROM DUAL)
SELECT rand, rand
  FROM data

Output:

RAND    RAND_1
92518726    92518726

Even more interesting is I can change the behavior / stabilize sys_guid by including calls to DBMS_RANDOM.RANDOM:

WITH data AS (
        SELECT SYS_GUID () uuid, 
        DBMS_RANDOM.random () rand 
        FROM DUAL)
SELECT uuid a,
       uuid b,
       rand c,
       rand d
  FROM data

SQL Fiddle That Stabilizes SYS_GUID: http://sqlfiddle.com/#!4/d41d8/29409

SQL Fiddle That shows the odd SYS_GUID behavior: http://sqlfiddle.com/#!4/d41d8/29411

Answer 1

The documentation gives a reason as to why you may see a discrepancy (emphasis mine):

Caution:

Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language. If your application requires that a function be executed a certain number of times, do not invoke that function from a SQL statement. Use a cursor instead.

For example, if your application requires that a function be called for each selected row, then open a cursor, select rows from the cursor, and call the function for each row. This technique guarantees that the number of calls to the function is the number of rows fetched from the cursor.

Basically, Oracle doesn't specify how many times a function will be called inside a sql statement: it may be dependent upon the release, the environment, the access path among other factors.

However, there are ways to limit query rewrite as explained in the chapter Unnesting of Nested Subqueries:

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.

As explained above, you can use ROWNUM pseudo-column to prevent Oracle from unnesting a subquery:

SQL> WITH data AS (SELECT SYS_GUID() uuid FROM DUAL WHERE ROWNUM >= 1)
  2  SELECT uuid, uuid FROM data;

UUID                             UUID
-------------------------------- --------------------------------
1ADF387E847F472494A869B033C2661A 1ADF387E847F472494A869B033C2661A

Answer 2

The NO_MERGE hint "fixes" it. Prevents Oracle from re-writing the inline view.

WITH data AS (SELECT /*+ NO_MERGE */
                    SYS_GUID () uuid FROM DUAL)
SELECT uuid, uuid
  FROM data

From the docs:

The NO_MERGE hint instructs the optimizer not to combine the outer query and any inline view queries into a single query.This hint lets you have more influence over the way in which the view is accessed.

SQL Fiddle with the NO_MERGE hint applied:

I'm still struggling to understand/articulate how the query is being re-written in such a way that sys_guid() would be called twice. Perhaps it is a bug; but I tend to assume it is a bug in my own thoughts/code.