Description:
Problem Description:
When executing the REPLACE() function on a VARCHAR(n) column, if the resulting string after character replacement is longer than the original column length n, the output is incorrectly truncated to n characters.
The length limit of a VARCHAR column is only a storage constraint for table data persistence. The return value of a string function in a SELECT query should not be restricted by the source column's defined length. This behavior violates standard SQL semantics.
Furthermore, this silent truncation causes subsequent UNION operations to produce incorrect deduplication results, as two logically different values become identical after being truncated.
How to repeat:
Step 1: Create test table and insert data:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
id TINYINT NOT NULL,
t1 VARCHAR(4) NOT NULL
);
INSERT INTO t0 VALUES
(1, 'aaaa'),
(2, 'abba');
Step 2: Run the test query:
WITH expanded AS (
SELECT
id,
REPLACE(t1, 'a', 'bb') AS expanded_t0
FROM t0
)
SELECT expanded_t0
FROM expanded
WHERE id = 1
UNION
SELECT expanded_t0
FROM expanded
WHERE id = 2;
Observed result:
Only 1 row is returned:
+-------------+
| expanded_t0 |
+-------------+
| bbbb |
+-------------+
Expected result:
2 distinct rows should be returned (order does not matter):
+-------------+
| expanded_t0 |
+-------------+
| bbbbbbbb |
| bbbbbb |
+-------------+
Root cause verification:
Run SELECT id, REPLACE(t1, 'a', 'bb') FROM t0;
Both rows return 'bbbb' (4 characters), confirming that REPLACE results are truncated to the original VARCHAR(4) length.
Suggested fix:
Fix the return type length inference logic for the REPLACE string function.
The maximum possible output length should be dynamically calculated based on the input string length and replacement parameters, rather than directly inheriting the length constraint from the source column definition.
© 2026, Oracle Corporation and/or its affiliates
Description: Problem Description: When executing the REPLACE() function on a VARCHAR(n) column, if the resulting string after character replacement is longer than the original column length n, the output is incorrectly truncated to n characters. The length limit of a VARCHAR column is only a storage constraint for table data persistence. The return value of a string function in a SELECT query should not be restricted by the source column's defined length. This behavior violates standard SQL semantics. Furthermore, this silent truncation causes subsequent UNION operations to produce incorrect deduplication results, as two logically different values become identical after being truncated. How to repeat: Step 1: Create test table and insert data: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( id TINYINT NOT NULL, t1 VARCHAR(4) NOT NULL ); INSERT INTO t0 VALUES (1, 'aaaa'), (2, 'abba'); Step 2: Run the test query: WITH expanded AS ( SELECT id, REPLACE(t1, 'a', 'bb') AS expanded_t0 FROM t0 ) SELECT expanded_t0 FROM expanded WHERE id = 1 UNION SELECT expanded_t0 FROM expanded WHERE id = 2; Observed result: Only 1 row is returned: +-------------+ | expanded_t0 | +-------------+ | bbbb | +-------------+ Expected result: 2 distinct rows should be returned (order does not matter): +-------------+ | expanded_t0 | +-------------+ | bbbbbbbb | | bbbbbb | +-------------+ Root cause verification: Run SELECT id, REPLACE(t1, 'a', 'bb') FROM t0; Both rows return 'bbbb' (4 characters), confirming that REPLACE results are truncated to the original VARCHAR(4) length. Suggested fix: Fix the return type length inference logic for the REPLACE string function. The maximum possible output length should be dynamically calculated based on the input string length and replacement parameters, rather than directly inheriting the length constraint from the source column definition.