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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your Subscription:
http://mail.postgresql.org/mj/mj_www...tra=pgsql-bugs

"Robert Fritzsching" <robert.fritzsching@gmx.net> writes:
> If I insert data in a table with left outer join, I get no error even though
> the domain type should trigger an error.

This has been discussed before. Most of us think it's a bug in the SQL
spec that the issue even exists. What would you expect to happen with
an outer join that has a not-null-domain output column?

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your Subscription:
http://mail.postgresql.org/mj/mj_www...tra=pgsql-bugs