Document Type | Troubleshooting
Category | App Development
Applicable Product Version | 7FS02
Document Number | TDETS017
Issue
When performing an INSERT INTO SELECT statement, the decimal point of the NUMBER data type is omitted during insert, causing the inserted data to differ from the selected data.
Example
- SELECT result value: 0.0912721331678228063770232444931240111963
- INSERT result value: 0
Reproduction Scenario
--1. Table DDL CREATE TABLE T1 ( CD VARCHAR(2) NOT NULL, RATE NUMBER(26,17) DEFAULT 0 NOT NULL ); CREATE TABLE T2 ( CD VARCHAR(2) NOT NULL, GUBUN VARCHAR(1) NOT NULL, AMT1 NUMBER(28,8) DEFAULT 0 NOT NULL ); CREATE TABLE T3 ( CD VARCHAR(2) NOT NULL, AMT2 NUMBER(28,8) DEFAULT 0 NOT NULL ); INSERT INTO T2 VALUES (1, 1, 2054250000000); INSERT INTO T3 VALUES (1, 187495779560); commit; --2. INSERT INSERT INTO T1 SELECT A.CD , CASE WHEN C.YN = 'Y' THEN 0 ELSE B.AMT2 / A.AMT1 END AS RATE FROM T2 A JOIN T3 B ON A.CD = B.CD LEFT OUTER JOIN ( SELECT 'Y' AS YN, CD, AMT1 FROM T2 WHERE GUBUN = 2 ) C ON C.CD = A.CD ; --3. Result values --SELECT result #RATE 0.0912721331678228063770232444931240111963 --INSERT result #RATE 0
Cause
This issue occurred because the select and insert operations are applied differently as a side effect of patch 292944a.
- Select operation behavior: Although C.YN used in the CASE function of the select clause is a column derived from the constant value 'Y', it is not treated as a constant because it can have NULL values due to the outer join.
- Insert operation behavior: When hard parsing INSERT INTO SELECT, C.YN is treated as a constant value regardless of the outer join.
NotePatch 292944a: Patch to improve memory usage during hard parsing of multi-insert statements
Solutions
Apply the improved patch to resolve the issue. (Applied patch: 292944b)
CautionApply the patch through technical support provided by Tmax Tibero.
NoteYou can work around this by modifying the query to cache the constant value used in the CASE function of the select clause.INSERT INTO T1 SELECT A.CD , CASE WHEN C.YN = 'Y' THEN 0 ELSE B.AMT2 / A.AMT1 END AS RATE FROM T2 A JOIN T3 B ON A.CD = B.CD LEFT OUTER JOIN ( SELECT (SELECT 'Y' FROM DUAL) AS YN, CD, AMT1 FROM T2 WHERE GUBUN = 2 ) C ON C.CD = A.CD ;