Skip to main content

Search

Welcome to Tibero GTS!

NUMBER Data Type Decimal Point Omission Issue When Executing INSERT INTO SELECT Statement

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.
Note
Patch 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)
Caution
Apply the patch through technical support provided by Tmax Tibero.
Note
You 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
;
 
 
 

Related to