Incorrect result from LOCATE with CLOB [CR #648637]

The LOCATE string function has an optional third parameter, which may be supplied to provide a starting offset for the LOCATE string search.The syntax for LOCATE is:

LOCATE(string-expression1, string-expression2 [, numeric-expression])

If string-expression1, the string to be searched, is a CLOB data type and the numeric-expression provided for the offset is evaluated as a BIGINT data type, the offset may be interpreted incorrectly.

Workaround: To ensure the offset is evaluated correctly, either use an integer constant or CAST the numeric-expression as an integer.

Example 1

This example illustrates both workarounds.

CREATE TABLE t1(c_clob CLOB, c_bigint BIGINT);
INSERT INTO t1 VALUES ('abc abc def', 4);

CAST numeric-expression as an integer:

SELECT LOCATE( c_clob, 'abc', 
    CAST(LOCATE(c_clob,'abc def') as INT)) FROM t1;
SELECT LOCATE( c_clob, 'abc', 
    CAST(c_bigint as INT)) FROM t1;

Or use an integer constant:

SELECT LOCATE( c_clob, 'abc', 4) FROM t1;

For more information on the LOCATE function, refer to Reference: Building Blocks, Tables, and Procedures.