INSERT INTO / SELECT DISTINCT results in primary key violation for a SYS_GUID

Question

I had some problems bulk inserting data from another table into a new one. The target looked somewhat like this:

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)
)

The simplified version of the statement I intended to fill it with data:

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

As I'm not providing a T_GUID valued, I would've assumed that I get one provided by the SYS_GUID function for each new line. But something is going wrong, and I get a uniqueness constraint violation for the primary key.

If I remove the DISTINCT, the statement succeeds, but I get lots of duplicate entries. And, of course, if I explicitly provide a SYS_GUID() call in my SELECT, that has exactly the same result.

Now I found that if I simply put another SELECT around mine, it works out alright, no constraint violations and the distinct rows get inserted:

INSERT INTO ...
SELECT x.* FROM (
    SELECT DISTINCT ...
) x

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 SELECT. I'd be very happy if someone could explain how the execution differs in that case.

Answer 1

Try something like this:

insert into test_t(t_guid, t_val1, t_val2)
  select sys_guid(), t_val1, t_val2
    from (select distinct to_number(substr(zip_code, 1, 1)) as t_val1, zip_code as t_val2
            from other_table_t
           where ...)