Skip to content

Commit 91a6541

Browse files
NotHimmelNotHimmel
committed
Add test case for crash when nested sub-functions access NEW after DECLARE uses NEW.id.
1 parent 42b974c commit 91a6541

File tree

2 files changed

+139
-0
lines changed

2 files changed

+139
-0
lines changed

‎src/pl/plisql/src/expected/plisql_nested_subproc.out‎

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4078,3 +4078,68 @@ SELECT test_func_1005_multi(10) FROM dual;
40784078
(1 row)
40794079

40804080
DROP FUNCTION test_func_1005_multi;
4081+
CREATE TABLE test_subproc_recfield(id integer, name varchar2(256));
4082+
CREATE OR REPLACE FUNCTION trig_subproc_recfield1() RETURNS TRIGGER AS $$
4083+
DECLARE
4084+
v_id integer := new.id;
4085+
FUNCTION get_id() RETURN integer IS
4086+
BEGIN
4087+
RETURN v_id;
4088+
END;
4089+
BEGIN
4090+
RAISE INFO 'id: %', get_id();
4091+
RETURN new;
4092+
END;
4093+
$$ LANGUAGE plisql;
4094+
/
4095+
CREATE TRIGGER trig_subproc_recfield1_trig
4096+
AFTER INSERT ON test_subproc_recfield
4097+
FOR EACH ROW EXECUTE PROCEDURE trig_subproc_recfield1();
4098+
INSERT INTO test_subproc_recfield VALUES(42, 'hello');
4099+
INFO: id: 42
4100+
DROP TRIGGER trig_subproc_recfield1_trig ON test_subproc_recfield;
4101+
DROP FUNCTION trig_subproc_recfield1();
4102+
CREATE OR REPLACE FUNCTION trig_subproc_recfield2() RETURNS TRIGGER AS $$
4103+
DECLARE
4104+
v_id integer := new.id;
4105+
v_result integer := 0;
4106+
PROCEDURE accumulate(p_val integer) IS
4107+
BEGIN
4108+
v_result := v_result + p_val;
4109+
END;
4110+
BEGIN
4111+
accumulate(v_id);
4112+
RAISE INFO 'result: %', v_result;
4113+
RETURN new;
4114+
END;
4115+
$$ LANGUAGE plisql;
4116+
/
4117+
CREATE TRIGGER trig_subproc_recfield2_trig
4118+
AFTER INSERT ON test_subproc_recfield
4119+
FOR EACH ROW EXECUTE PROCEDURE trig_subproc_recfield2();
4120+
INSERT INTO test_subproc_recfield VALUES(10, 'world');
4121+
INFO: result: 10
4122+
DROP TRIGGER trig_subproc_recfield2_trig ON test_subproc_recfield;
4123+
DROP FUNCTION trig_subproc_recfield2();
4124+
CREATE OR REPLACE FUNCTION trig_subproc_recfield3() RETURNS TRIGGER AS $$
4125+
DECLARE
4126+
v_id integer := new.id;
4127+
v_name varchar2(256) := new.name;
4128+
FUNCTION summary() RETURN text IS
4129+
BEGIN
4130+
RETURN v_id::text || ':' || v_name;
4131+
END;
4132+
BEGIN
4133+
RAISE INFO '%', summary();
4134+
RETURN new;
4135+
END;
4136+
$$ LANGUAGE plisql;
4137+
/
4138+
CREATE TRIGGER trig_subproc_recfield3_trig
4139+
AFTER INSERT ON test_subproc_recfield
4140+
FOR EACH ROW EXECUTE PROCEDURE trig_subproc_recfield3();
4141+
INSERT INTO test_subproc_recfield VALUES(5, 'test');
4142+
INFO: 5:test
4143+
DROP TRIGGER trig_subproc_recfield3_trig ON test_subproc_recfield;
4144+
DROP FUNCTION trig_subproc_recfield3();
4145+
DROP TABLE test_subproc_recfield;

‎src/pl/plisql/src/sql/plisql_nested_subproc.sql‎

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3890,3 +3890,77 @@ END;
38903890
SELECT test_func_1005_multi(10) FROM dual;
38913891

38923892
DROP FUNCTION test_func_1005_multi;
3893+
CREATE TABLE test_subproc_recfield(id integer, name varchar2(256));
3894+
3895+
CREATE OR REPLACE FUNCTION trig_subproc_recfield1() RETURNS TRIGGER AS $$
3896+
DECLARE
3897+
v_id integer := new.id;
3898+
FUNCTION get_id() RETURN integer IS
3899+
BEGIN
3900+
RETURN v_id;
3901+
END;
3902+
BEGIN
3903+
RAISE INFO 'id: %', get_id();
3904+
RETURN new;
3905+
END;
3906+
$$ LANGUAGE plisql;
3907+
/
3908+
3909+
CREATE TRIGGER trig_subproc_recfield1_trig
3910+
AFTER INSERT ON test_subproc_recfield
3911+
FOR EACH ROW EXECUTE PROCEDURE trig_subproc_recfield1();
3912+
3913+
INSERT INTO test_subproc_recfield VALUES(42, 'hello');
3914+
3915+
DROP TRIGGER trig_subproc_recfield1_trig ON test_subproc_recfield;
3916+
DROP FUNCTION trig_subproc_recfield1();
3917+
3918+
CREATE OR REPLACE FUNCTION trig_subproc_recfield2() RETURNS TRIGGER AS $$
3919+
DECLARE
3920+
v_id integer := new.id;
3921+
v_result integer := 0;
3922+
PROCEDURE accumulate(p_val integer) IS
3923+
BEGIN
3924+
v_result := v_result + p_val;
3925+
END;
3926+
BEGIN
3927+
accumulate(v_id);
3928+
RAISE INFO 'result: %', v_result;
3929+
RETURN new;
3930+
END;
3931+
$$ LANGUAGE plisql;
3932+
/
3933+
3934+
CREATE TRIGGER trig_subproc_recfield2_trig
3935+
AFTER INSERT ON test_subproc_recfield
3936+
FOR EACH ROW EXECUTE PROCEDURE trig_subproc_recfield2();
3937+
3938+
INSERT INTO test_subproc_recfield VALUES(10, 'world');
3939+
3940+
DROP TRIGGER trig_subproc_recfield2_trig ON test_subproc_recfield;
3941+
DROP FUNCTION trig_subproc_recfield2();
3942+
3943+
CREATE OR REPLACE FUNCTION trig_subproc_recfield3() RETURNS TRIGGER AS $$
3944+
DECLARE
3945+
v_id integer := new.id;
3946+
v_name varchar2(256) := new.name;
3947+
FUNCTION summary() RETURN text IS
3948+
BEGIN
3949+
RETURN v_id::text || ':' || v_name;
3950+
END;
3951+
BEGIN
3952+
RAISE INFO '%', summary();
3953+
RETURN new;
3954+
END;
3955+
$$ LANGUAGE plisql;
3956+
/
3957+
3958+
CREATE TRIGGER trig_subproc_recfield3_trig
3959+
AFTER INSERT ON test_subproc_recfield
3960+
FOR EACH ROW EXECUTE PROCEDURE trig_subproc_recfield3();
3961+
3962+
INSERT INTO test_subproc_recfield VALUES(5, 'test');
3963+
3964+
DROP TRIGGER trig_subproc_recfield3_trig ON test_subproc_recfield;
3965+
DROP FUNCTION trig_subproc_recfield3();
3966+
DROP TABLE test_subproc_recfield;

0 commit comments

Comments
 (0)