Re: [BUGS] INSTEAD rule bug?

Tom Lane wrote:

>Dmitry Tkach <dmitry@openratings.com> writes:
>
>
>>Something like
>>insert into test
>>select null,null union select 1,2 where false
>>has the same problem... and it doesn't refer to any relations.
>>
>>
>
>But that's parsed as
>
>insert into test
>(select null,null) union (select 1,2 where false)
>
>so I'd expect it to bomb if test has NOT NULL constraints.
>
>
Sure, but it is inside the rule that has 'where x is not null and y is
not null' on it as a qualifier, so
with my test example it should just never get executed in the first place.

>
>
>
>>Not just 7.2... I was testing this in 7.3 - it has the same problem
>>
>>
>
>Yeah, the change is post-7.3.
>
>
>
>>insert into test select * from (select null,null union select 1,2 where
>>false) as dummy
>>... that works fine.
>>
>>
>
>I get
>ERROR:  ExecInsert: Fail to add null value in not null attribute x
>which is what I'd expect.
>
>
Really? In 7.3?
That's weird...
Here is what I am getting exactly:

testdb=# drop table test cascade;
NOTICE:  Drop cascades to rule insert_test on view test_view
NOTICE:  Drop cascades to rule _RETURN on view test_view
NOTICE:  Drop cascades to view test_view
DROP TABLE
testdb=# drop table test_reject cascade;
DROP TABLE
testdb=#
testdb=# create table test (x int not null, y int not null);
CREATE TABLE
testdb=# create table test_reject (x int, y int, reason text);
CREATE TABLE
testdb=#
testdb=# create view test_view as select * from test;
CREATE VIEW
testdb=#
testdb=# create rule reject_x as on insert to test_view where new.x is
null do instead insert into test_reject values (new.*, 'NULL x');
CREATE RULE
testdb=#
testdb=# create rule reject_y as on insert to test_view where new.y is
null do instead insert into test_reject values (new.*, 'NULL y');
CREATE RULE
testdb=#
testdb=# create rule insert_test as on insert to test_view where new.x
is not null and new.y is not null do instead
testdb-# (
testdb(#    insert into test select * from
testdb(#    (select null,null union select 1,2 where false) as dummy
testdb(# );
CREATE RULE
testdb=#
testdb=# create rule dummy_insert as on insert to test_view do instead
nothing;
CREATE RULE
testdb=#
testdb=#
testdb=# insert into test_view values (null, null);
INSERT 17648 1
testdb=# select * from test;
 x | y
---+---
(0 rows)

testdb=# select * from test_reject;
 x | y | reason
---+---+--------
   |   | NULL x
   |   | NULL y
(2 rows)