IvorySQL · gaoxueyu · Jan 4, 2026 · Dec 25, 2025 · Dec 25, 2025
diff --git a/src/pl/plisql/src/Makefile b/src/pl/plisql/src/Makefile
@@ -63,7 +63,7 @@ REGRESS = plisql_array plisql_call plisql_control plisql_copy plisql_domain \
 	plisql_record plisql_cache plisql_simple plisql_transaction \
 	plisql_trap plisql_trigger plisql_varprops plisql_nested_subproc \
 	plisql_nested_subproc2 plisql_out_parameter plisql_type_rowtype \
-	plisql_exception plisql_for_loop_implicit plisql_autonomous
+	plisql_exception plisql_for_loop_implicit plisql_autonomous plisql_bugs
 
 # where to find ora_gen_keywordlist.pl and subsidiary files
 TOOLSDIR = $(top_srcdir)/src/tools

diff --git a/src/pl/plisql/src/expected/plisql_bugs.out b/src/pl/plisql/src/expected/plisql_bugs.out
@@ -0,0 +1,128 @@
+--
+-- Test for GitHub Issue #1124: Variable Reset Bug in Nested Functions
+--
+-- Bug: When evaluating expressions like "v_sum := v_sum + nested_func()",
+-- variables show original values instead of modified ones.
+--
+-- Test 1: Variable modification + nested function call in expression
+-- This was the core bug - v_sum's modified value (109) was being lost
+-- during expression evaluation, causing v_sum + get_30(10) to be 0 + 30 = 30
+-- instead of 109 + 30 = 139
+CREATE OR REPLACE FUNCTION test_bug1124_basic() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_result NUMBER;
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    -- Modify v_sum
+    v_sum := v_sum + 10;  -- v_sum = 109
+    -- Expression with nested function - this was buggy
+    v_result := v_sum + get_30(10);  -- Should be 109 + 30 = 139
+    RETURN v_result;
+END;
+/
+SELECT test_bug1124_basic() AS result FROM dual;
+ result 
+--------
+ 139
+(1 row)
+
+DROP FUNCTION test_bug1124_basic;
+-- Test 2: Multiple expressions to ensure consistent behavior
+CREATE OR REPLACE FUNCTION test_bug1124_multiple() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_temp NUMBER;
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    v_sum := v_sum + 10;  -- v_sum = 109
+    -- First expression
+    v_temp := v_sum + get_30(10);  -- Should be 139
+    -- Second expression (same) - should also be 139
+    v_temp := v_sum + get_30(10);
+    -- Third expression
+    v_temp := v_sum + get_30(10);
+    RETURN v_temp;
+END;
+/
+SELECT test_bug1124_multiple() AS result FROM dual;
+ result 
+--------
+ 139
+(1 row)
+
+DROP FUNCTION test_bug1124_multiple;
+-- Test 3: Nested procedure modifying parent variable + nested function in expression
+CREATE OR REPLACE FUNCTION test_bug1124_proc_and_func() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_temp NUMBER;
+    PROCEDURE add_val(p_val NUMBER) IS
+    BEGIN
+        v_sum := v_sum + p_val;
+    END add_val;
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    -- Call nested procedure to modify v_sum
+    add_val(10);  -- v_sum = 109
+    -- Expression with nested function
+    v_temp := v_sum + get_30(10);  -- Should be 109 + 30 = 139
+    RETURN v_temp;
+END;
+/
+SELECT test_bug1124_proc_and_func() AS result FROM dual;
+ result 
+--------
+ 139
+(1 row)
+
+DROP FUNCTION test_bug1124_proc_and_func;
+-- Test 4: Test with integer type (not just NUMBER)
+CREATE OR REPLACE FUNCTION test_bug1124_integer() RETURN integer AS
+    v_sum integer := 99;
+    v_result integer;
+    FUNCTION get_30(p_val integer) RETURN integer IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    v_sum := v_sum + 10;  -- v_sum = 109
+    v_result := v_sum + get_30(10);  -- Should be 109 + 30 = 139
+    RETURN v_result;
+END;
+/
+SELECT test_bug1124_integer() AS result FROM dual;
+ result 
+--------
+    139
+(1 row)
+
+DROP FUNCTION test_bug1124_integer;
+-- Test 5: Nested function on left side of expression
+CREATE OR REPLACE FUNCTION test_bug1124_order() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_temp NUMBER;
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    v_sum := v_sum + 10;  -- v_sum = 109
+    -- Nested function result on left side
+    v_temp := get_30(10) + v_sum;  -- Should be 30 + 109 = 139
+    RETURN v_temp;
+END;
+/
+SELECT test_bug1124_order() AS result FROM dual;
+ result 
+--------
+ 139
+(1 row)
+
+DROP FUNCTION test_bug1124_order;
diff --git a/src/pl/plisql/src/pl_exec.c b/src/pl/plisql/src/pl_exec.c
@@ -7367,10 +7367,38 @@ plisql_param_eval_var_ro(ExprState *state, ExprEvalStep *op,
 	/*
 	 * Inlined version of exec_eval_datum() ... and while we're at it, force
 	 * expanded datums to read-only.
+	 *
+	 * IMPORTANT (Bug #1124 fix): For varlena types that are NOT expanded
+	 * objects, we must make a copy of the value into the eval_mcontext,
+	 * rather than just returning a pointer to the variable's storage. This is
+	 * because nested function calls (a PL/iSQL feature) can happen during
+	 * expression evaluation, and when they return, they copy back their global
+	 * variables to the parent. This copy-back operation frees the parent's old
+	 * variable storage, which would leave us with a dangling pointer if we had
+	 * just returned var->value directly.
+	 *
+	 * For expanded objects (like composite types), we use
+	 * MakeExpandedObjectReadOnly which handles them properly.
+	 *
+	 * By copying to eval_mcontext here, we ensure the value survives for the
+	 * duration of the expression evaluation, even if the original variable
+	 * storage is freed and reallocated.
 	 */
-	*op->resvalue = MakeExpandedObjectReadOnly(var->value,
-											   var->isnull,
-											   -1);
+	if (!var->isnull && var->datatype->typlen == -1 &&
+		!VARATT_IS_EXTERNAL_EXPANDED(DatumGetPointer(var->value)))
+	{
+		/* Non-expanded varlena type - make a copy in eval_mcontext */
+		MemoryContext oldcxt = MemoryContextSwitchTo(get_eval_mcontext(estate));
+		*op->resvalue = datumCopy(var->value, false, -1);
+		MemoryContextSwitchTo(oldcxt);
+	}
+	else
+	{
+		/* Expanded object, pass-by-value, or null - use standard handling */
+		*op->resvalue = MakeExpandedObjectReadOnly(var->value,
+												   var->isnull,
+												   -1);
+	}
 	*op->resnull = var->isnull;
 
 	/* safety check -- an assertion should be sufficient */

diff --git a/src/pl/plisql/src/sql/plisql_bugs.sql b/src/pl/plisql/src/sql/plisql_bugs.sql
@@ -0,0 +1,131 @@
+--
+-- Test for GitHub Issue #1124: Variable Reset Bug in Nested Functions
+--
+-- Bug: When evaluating expressions like "v_sum := v_sum + nested_func()",
+-- variables show original values instead of modified ones.
+--
+
+-- Test 1: Variable modification + nested function call in expression
+-- This was the core bug - v_sum's modified value (109) was being lost
+-- during expression evaluation, causing v_sum + get_30(10) to be 0 + 30 = 30
+-- instead of 109 + 30 = 139
+CREATE OR REPLACE FUNCTION test_bug1124_basic() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_result NUMBER;
+
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    -- Modify v_sum
+    v_sum := v_sum + 10;  -- v_sum = 109
+
+    -- Expression with nested function - this was buggy
+    v_result := v_sum + get_30(10);  -- Should be 109 + 30 = 139
+
+    RETURN v_result;
+END;
+/
+
+SELECT test_bug1124_basic() AS result FROM dual;
+DROP FUNCTION test_bug1124_basic;
+
+-- Test 2: Multiple expressions to ensure consistent behavior
+CREATE OR REPLACE FUNCTION test_bug1124_multiple() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_temp NUMBER;
+
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    v_sum := v_sum + 10;  -- v_sum = 109
+
+    -- First expression
+    v_temp := v_sum + get_30(10);  -- Should be 139
+
+    -- Second expression (same) - should also be 139
+    v_temp := v_sum + get_30(10);
+
+    -- Third expression
+    v_temp := v_sum + get_30(10);
+
+    RETURN v_temp;
+END;
+/
+
+SELECT test_bug1124_multiple() AS result FROM dual;
+DROP FUNCTION test_bug1124_multiple;
+
+-- Test 3: Nested procedure modifying parent variable + nested function in expression
+CREATE OR REPLACE FUNCTION test_bug1124_proc_and_func() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_temp NUMBER;
+
+    PROCEDURE add_val(p_val NUMBER) IS
+    BEGIN
+        v_sum := v_sum + p_val;
+    END add_val;
+
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    -- Call nested procedure to modify v_sum
+    add_val(10);  -- v_sum = 109
+
+    -- Expression with nested function
+    v_temp := v_sum + get_30(10);  -- Should be 109 + 30 = 139
+
+    RETURN v_temp;
+END;
+/
+
+SELECT test_bug1124_proc_and_func() AS result FROM dual;
+DROP FUNCTION test_bug1124_proc_and_func;
+
+-- Test 4: Test with integer type (not just NUMBER)
+CREATE OR REPLACE FUNCTION test_bug1124_integer() RETURN integer AS
+    v_sum integer := 99;
+    v_result integer;
+
+    FUNCTION get_30(p_val integer) RETURN integer IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    v_sum := v_sum + 10;  -- v_sum = 109
+
+    v_result := v_sum + get_30(10);  -- Should be 109 + 30 = 139
+
+    RETURN v_result;
+END;
+/
+
+SELECT test_bug1124_integer() AS result FROM dual;
+DROP FUNCTION test_bug1124_integer;
+
+-- Test 5: Nested function on left side of expression
+CREATE OR REPLACE FUNCTION test_bug1124_order() RETURN NUMBER AS
+    v_sum NUMBER := 99;
+    v_temp NUMBER;
+
+    FUNCTION get_30(p_val NUMBER) RETURN NUMBER IS
+    BEGIN
+        RETURN p_val * 3;
+    END get_30;
+BEGIN
+    v_sum := v_sum + 10;  -- v_sum = 109
+
+    -- Nested function result on left side
+    v_temp := get_30(10) + v_sum;  -- Should be 30 + 109 = 139
+
+    RETURN v_temp;
+END;
+/
+
+SELECT test_bug1124_order() AS result FROM dual;
+DROP FUNCTION test_bug1124_order;