DUPLICATE ROW PROBLEM WHEN INSERT SELECT INTO A SET TABLE WITH UPI

IT SEEMS THAT ,WHEN DEAL WITH DUPLICATE ROW INTO A SET TABLE WITH UPI,
TERADATA GOT SOME DIFFERENCE BETWEEN MANUAL AND THE ACTUAL.

WE KNOW THAT WHEN DEFINE A SET TABLE WITH UPI, TERADATA WILL DO A UPI
CHECK AND IGNORE SET TABLE CHECK(DUPLICATE ROW CHECK).


AND TERADATA ALSO DID IT WHEN WE INSERT INTO A SET-UPI TABLE.

BUT THING GOES DIFFERENT WHEN WE DO A INSERT SELECT


TERADATA WILL NOT SHOW ANY ERROR MESSAGE BUT SIMPLY GET A SUCCESS.
OF COURSE, THE DUPLICATE ROW WILL NOT APPEAR IN THE TARGET TABLE

THAT REALLY CONFUSED ME.

I SEARCHED THE INTERNET AND FOUND SOME SIMILAR PROBLEM

http://forums.teradata.com/forum/database/set-with-upi

http://carlosal.wordpress.com/2009/02/16/tablas-set-y-multiset-en-teradata/



按照教科书的说法，TERADATA在处理定义有UPI的SET表时，会用UPI的唯一性检查替换掉SET表的重复行检查。
(参见Teradata Factory)
但是事实上并非完全如此，至少在某些情况下，比如说INSERT SELECT
在直接执行INSERT时，对于UPI的SET表，如果insert重复行，会提示有UPI重复错误。
但是如果在INSERT SELECT中，有时候不会有任何错误发生，Teradata会默默地去掉重复行，只插入非重复的部分
而不会返回任何错误
即使有教科书，有时候也挺让人迷惑的


UPDATE 2012/6/26-------------------------------------------------------
i found the answer from the Teradata ApplicationDesignand Development manual
they said that an insert-select automatically discards duplicate rows with out any notification. even if the target table has a UPI on it
To trap this we create a multi-set table with a UPI on it. 

Tweet