title: INSERT INTO / SELECT DISTINCT results in primary key violation for a SYS_GUID
body: <p>I had some problems bulk inserting data from another table into a new one. The target looked somewhat like this:</p>
  
  <pre><code>CREATE TABLE TEST_T (
        T_GUID RAW(16) DEFAULT SYS_GUID() NOT NULL,
        T_VAL1 NUMBER(10) NOT NULL,
        T_VAL2 VARCHAR2(10) NOT NULL,
        PRIMARY KEY (T_GUID)
  )
  </code></pre>
  
  <p>The simplified version of the statement I intended to fill it with data:</p>
  
  <pre><code>INSERT INTO TEST_T (T_VAL1, T_VAL2)
  SELECT DISTINCT
       CAST(SUBSTR(zip_code, 1,1) AS NUMBER) as  t_val1,
       zip_code as t_val2
  FROM OTHER_TABLE_T
  WHERE ...
  ORDER BY t_val1
  </code></pre>
  
  <p>As I'm not providing a <code>T_GUID</code> valued, I would've assumed that I get one provided by the <code>SYS_GUID</code> function for each new line. But something is going wrong, and I get a uniqueness constraint violation for the primary key.</p>
  
  <p>If I remove the <code>DISTINCT</code>, the statement succeeds, but I get lots of duplicate entries. And, of course, if I explicitly provide a <code>SYS_GUID()</code> call in my <code>SELECT</code>, that has exactly the same result.</p>
  
  <p>Now I found that if I simply put another <code>SELECT</code> around mine, it works out alright, no constraint violations and the distinct rows get inserted:</p>
  
  <pre><code>INSERT INTO ...
  SELECT x.* FROM (
      SELECT DISTINCT ...
  ) x
  </code></pre>
  
  <p>So where do the duplicate guids come from? If the full set of rows doesn't have problems, why would removing rows via distinct cause trouble? As SYS_GUID creates a unique identifier for each call, I could only imagine that in the distinct case it only gets called once for the whole clause, which is solved by the surrounding wrapping <code>SELECT</code>. I'd be very happy if someone could explain how the execution differs in that case.</p>