INSTR gives incorrect results for negative index if characters repeat

KKirkpatrick

Version 7.2

 

SELECT INSTR('1+2+3','+',-1,2);

Result (correct): 2

 

SELECT INSTR('1++3','+',-1,2);

Result (incorrect): 0

 

FWIW, INSTRB is not affected by this bug and serves as a viable workaround for (rare) cases that this arises.

 

Sudhakar_B

Kirk,

This exists in 8.0 as well

dbadmin=> SELECT INSTR('1+2+3','+',-1,2);
 INSTR
-------
     2
(1 row)

dbadmin=>
dbadmin=> SELECT INSTR('1++3','+',-1,2);
 INSTR
-------
     0
(1 row)

dbadmin=> select version();
              version               
------------------------------------
 Vertica Analytic Database v8.0.0-0
(1 row)

dbadmin=> SELECT INSTR('1++34567','+',-1,2);
 INSTR
-------
     0
(1 row)