The following bug has been logged online:
Bug reference: 4008
Logged by: Robert Fritzsching
Email address: robert.fritzsching@gmx.net
PostgreSQL version: 8.0.3, 8.3.0
Operating system: Windows XP, Linux Debian SID
Description: accepts invalid input with domaintypes and outer joins
Details:
Hi developer,
I found a bug:
If I insert data in a table with left outer join, I get no error even though
the domain type should trigger an error.
To reproduce the bug:
<!--
CREATE DOMAIN domname AS integer NOT NULL;
CREATE TABLE tblPrimary (colOtherColumn integer NOT NULL);
INSERT INTO tblPrimary (colOtherColumn) VALUES (10);
INSERT INTO tblPrimary (colOtherColumn) VALUES (20);
INSERT INTO tblPrimary (colOtherColumn) VALUES (30);
INSERT INTO tblPrimary (colOtherColumn) VALUES (40);
CREATE TABLE tblJoin (colWithDomname domname, colOtherColumn integer);
INSERT INTO tblJoin (colWithDomname, colOtherColumn) VALUES (1, 10);
INSERT INTO tblJoin (colWithDomname, colOtherColumn) VALUES (2, 20);
INSERT INTO tblJoin (colWithDomname, colOtherColumn) VALUES (3, 30);
CREATE TABLE tblResult (colWithDomname domname, colOtherColumn integer);
-->
finally this statement should failed:
<!--
INSERT INTO tblResult (SELECT tblJoin.colWithDomname,
tblPrimary.colOtherColumn FROM tblPrimary LEFT JOIN tblJoin
USING(colOtherColumn));
-->
The problem is the outer join - inner join or direct inserts produce an
error.
Thanks, Robert Fritzsching