Repair two constraint-exclusion corner cases triggered by proving that an

[postgresql] / src / test / regress / sql / rules.sql

diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index 908c9804628944c1a1010f0d691c41734edc37cf..e898336d92a27df3bdb70cd8b3391022abd5ea8d 100644 (file)
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -218,17 +218,19 @@ select * from rtest_v1;
 update rtest_v1 set b = 88 where b < 50;
 select * from rtest_v1;
 delete from rtest_v1;
-insert into rtest_v1 select rtest_t2.a, rtest_t3.b where rtest_t2.a = rtest_t3.a;
+insert into rtest_v1 select rtest_t2.a, rtest_t3.b
+    from rtest_t2, rtest_t3
+    where rtest_t2.a = rtest_t3.a;
 select * from rtest_v1;
 
 -- updates in a mergejoin
-update rtest_v1 set b = rtest_t2.b where a = rtest_t2.a;
+update rtest_v1 set b = rtest_t2.b from rtest_t2 where rtest_v1.a = rtest_t2.a;
 select * from rtest_v1;
 insert into rtest_v1 select * from rtest_t3;
 select * from rtest_v1;
 update rtest_t1 set a = a + 10 where b > 30;
 select * from rtest_v1;
-update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b;
+update rtest_v1 set a = rtest_t3.a + 20 from rtest_t3 where rtest_v1.b = rtest_t3.b;
 select * from rtest_v1;
 
 --
@@ -285,9 +287,9 @@ insert into rtest_empmass values ('mayr', '6000.00');
 insert into rtest_emp select * from rtest_empmass;
 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 update rtest_empmass set salary = salary + '1000.00';
-update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename;
+update rtest_emp set salary = rtest_empmass.salary from rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
-delete from rtest_emp where ename = rtest_empmass.ename;
+delete from rtest_emp using rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 
 --
@@ -429,7 +431,7 @@ create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount
        group by X.a, X.b;
 create function rtest_viewfunc1(int4) returns int4 as
        'select count(*)::int4 from rtest_view2 where a = $1'
-       language 'sql';
+       language sql;
 create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount
        from rtest_view1;
 
@@ -679,7 +681,7 @@ SELECT * FROM shoelace_log ORDER BY sl_name;
 insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
 insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
 
-SELECT * FROM shoelace_obsolete;
+SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm;
 SELECT * FROM shoelace_candelete;
 
 DELETE FROM shoelace WHERE EXISTS
@@ -848,3 +850,72 @@ create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
 
 insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
 insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
+
+--
+-- check for planner problems with complex inherited UPDATES
+--
+
+create table id (id serial primary key, name text);
+-- currently, must respecify PKEY for each inherited subtable
+create table test_1 (id integer primary key) inherits (id);
+create table test_2 (id integer primary key) inherits (id);
+create table test_3 (id integer primary key) inherits (id);
+
+insert into test_1 (name) values ('Test 1');
+insert into test_1 (name) values ('Test 2');
+insert into test_2 (name) values ('Test 3');
+insert into test_2 (name) values ('Test 4');
+insert into test_3 (name) values ('Test 5');
+insert into test_3 (name) values ('Test 6');
+
+create view id_ordered as select * from id order by id;
+
+create rule update_id_ordered as on update to id_ordered
+       do instead update id set name = new.name where id = old.id;
+
+select * from id_ordered;
+update id_ordered set name = 'update 2' where id = 2;
+update id_ordered set name = 'update 4' where id = 4;
+update id_ordered set name = 'update 5' where id = 5;
+select * from id_ordered;
+
+set client_min_messages to warning; -- suppress cascade notices
+drop table id cascade;
+reset client_min_messages;
+
+--
+-- check corner case where an entirely-dummy subplan is created by
+-- constraint exclusion
+--
+
+create temp table t1 (a integer primary key);
+
+create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1);
+create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1);
+
+create rule t1_ins_1 as on insert to t1 
+       where new.a >= 0 and new.a < 10
+       do instead
+       insert into t1_1 values (new.a);
+create rule t1_ins_2 as on insert to t1 
+       where new.a >= 10 and new.a < 20
+       do instead
+       insert into t1_2 values (new.a);
+
+create rule t1_upd_1 as on update to t1
+       where old.a >= 0 and old.a < 10
+       do instead
+       update t1_1 set a = new.a where a = old.a;
+create rule t1_upd_2 as on update to t1
+       where old.a >= 10 and old.a < 20
+       do instead
+       update t1_2 set a = new.a where a = old.a;
+
+set constraint_exclusion = on;
+
+insert into t1 select * from generate_series(5,19,1) g;
+update t1 set a = 4 where a = 5;
+
+select * from only t1;
+select * from only t1_1;
+select * from only t1_2;