Duplicate Elements in DML Operations on SET Columns

The SET data type does not allow duplicate element values in the same collection. If you attempt to insert duplicate elements into a SET data type, or to update a SET column or variable to a value that includes duplicate elements, the database server issues no error or warning when the INSERT or UPDATE statement executes, but only one of the duplicate elements is stored in the SET column or variable.

For example, suppose you create table t3 with column a of the SET data type, and then you insert four rows, some of which include elements that have identical values:

  > CREATE TABLE t3(a SET(INT NOT NULL));

  Table created.  

> INSERT INTO t3 VALUES( SET{10, 20, 30} );

  1 row(s) inserted.  

> INSERT INTO t3 VALUES( SET{10, 20, 10});

  1 row(s) inserted.  

> INSERT INTO t3 VALUES( SET{10, 10, 10});

  1 row(s) inserted.  

> INSERT INTO t3 VALUES( SET{10,10,10});

  1 row(s) inserted.

When you look at the data values that were inserted into column t3.a, the four inserted rows include no duplicate element values:

> SELECT * FROM t3;

  a  SET{10         ,20         ,30         }  
  a  SET{10         ,20         }
  a  SET{10         }
  a  SET{10         }

  4 row(s) retrieved.

In this example, HCL OneDB™ silently discarded all but one instance of the duplicated elements from what the VALUES clause of the INSERT statement specified for each SET value.

Similar behavior occurs if the SET clause of the UPDATE statement includes duplicate elements within the same SET value. Declare collection columns of the MULTISET data type, rather than of the SET data type, if you want the database to store unordered sets that can include duplicate elements within the same collection