Skip to main content

Search

Welcome to Tibero GTS!

Error Occurrence When Inputting NULL in the offset Parameter of LAG/LEAD Functions

Document Type | Issue Resolution 

Category | App Development

Applicable Product Versions | 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS

Error Code | 11005

Document Number | TDETS004

 

Issue

If a non-numeric NULL value is input into the offset parameter of the LAG and LEAD built-in functions, a TBR-11005 error occurs instead of a query result.
 
TBR-11005: Specified value '-E+125' is invalid for mathematical function argument(s).
at line 1, column 45 of null:
SELECT TABLE_NAME, NUM_ROWS, LAG(NUM_ROWS, NULL, 0)

--Executed Query 1: Executing with an explicit offset parameter NULL value
SQL> SELECT TABLE_NAME, NUM_ROWS, 
LAG(NUM_ROWS, NULL, 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW
FROM DBA_TABLES;


--Executed Query 2: Executing with offset parameter as NULL due to a value not matching DECODE expression
SQL> SELECT TABLE_NAME, NUM_ROWS, 
LAG(NUM_ROWS, DECODE(OWNER, 'SYS', 1, 'TIBERO',2), 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW
FROM DBA_TABLES;

LAG(expr, offset, default)

The LAG function is an analytic function that allows referencing multiple rows within a single table simultaneously without self-joining. It accesses the value of the row located before the current row by the specified offset.

 

LEAD(expr, offset, default)

Similarly, the LEAD function is an analytic function that allows referencing multiple rows within the same table without a separate join. It accesses the value of the row located after the current row by the specified offset.

 

Cause

To prevent negative values from being input into the offset parameter, a logic comparing offset and number_zero was used, but an error occurred due to comparing NULL with number_zero. 

 

Solutions

Modify the query to handle NULL values separately.
--Workaround to output the current ROW value when NULL
SELECT TABLE_NAME, NUM_ROWS, 
LAG(NUM_ROWS, NVL(NULL,0), 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW
FROM DBA_TABLES;

--Query separating data that matches DECODE and data that does not (for data not matching DECODE, the default third parameter is output)
SELECT TABLE_NAME, NUM_ROWS, 
LAG(NUM_ROWS, DECODE(OWNER, 'SYS', 1, 'TIBERO',2), 0) OVER (PARTITION BY OWNER ORDER BY NUM_ROWS) AS LROW
FROM DBA_TABLES
WHERE OWNER IN ('SYS','TIBERO')
UNION ALL
SELECT TABLE_NAME, NUM_ROWS, 
0 AS LROW
FROM DBA_TABLES
WHERE OWNER NOT IN ('SYS','TIBERO')
;
 
 
 

Related to