Description:
The pos and occurrence arguments of REGEXP_INSTR(), REGEXP_SUBSTR() and
REGEXP_REPLACE() are read into an int, so only their low 32 bits are used:
occurrence -- 'abcabcabc' has three matches of 'a+':
SELECT REGEXP_INSTR('abcabcabc','a+',1,2); -- 4
SELECT REGEXP_INSTR('abcabcabc','a+',1,4); -- 0 (no 4th match: correct)
SELECT REGEXP_INSTR('abcabcabc','a+',1,4294967298); -- 4 (2^32+2 acts as 2)
SELECT REGEXP_INSTR('abcabcabc','a+',1,4294967296); -- 1 (2^32 acts as 0 -> 1)
SELECT REGEXP_INSTR('abcabcabc','a+',1,2147483648); -- 1 (2^31 is negative -> 1)
SELECT REGEXP_INSTR('abcabcabc','a+',1,9223372036854775809); -- 1
SELECT REGEXP_SUBSTR('ab ac ad','A.',1,4); -- NULL (correct)
SELECT REGEXP_SUBSTR('ab ac ad','A.',1,4294967298); -- 'ac'
SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',1,3); -- 'aaabbccbbddaa' (correct)
SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',1,4294967297); -- 'aaaxccbbddaa'
pos -- a position past the end of the string is an error, but 2^32 + k is
position k:
SELECT REGEXP_INSTR('abcabcabc','b+',10); -- ERROR 3686 Index out of bounds
SELECT REGEXP_INSTR('abcabcabc','b+',2147483648); -- ERROR 3686
SELECT REGEXP_INSTR('abcabcabc','b+',4294967297); -- 2 (acts as pos 1)
SELECT REGEXP_INSTR('abcabcabc','b+',4294967300); -- 5 (acts as pos 4)
SELECT REGEXP_SUBSTR('ab ac ad','A.',4294967299); -- 'ac'
SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',4294967297,0); -- 'aaaxccxddaa'
The same happens when the argument comes from a BIGINT column:
CREATE TABLE t27 (id INT PRIMARY KEY, o BIGINT UNSIGNED);
INSERT INTO t27 VALUES (1, 4), (2, 4294967298);
SELECT id, REGEXP_INSTR('abcabcabc','a+',1,o), REGEXP_SUBSTR('abcabcabc','a+',1,o)
FROM t27 ORDER BY id;
-- 1 0 NULL
-- 2 4 'a'
No warning in any case. The other string functions that take positions and
counts (LEFT, RIGHT, SUBSTRING, LPAD, REPEAT, SPACE, ...) had the same 32-bit
narrowing and were fixed long ago (Bug #10963); REGEXP_* came later.
Where (trunk 3b99be40): sql/item_regexp_func.h
115 std::optional<int> position() const {
118 const int value = args[the_index]->val_int();
...
134 std::optional<int> occurrence() const {
137 const int value = args[the_index]->val_int();
How to repeat:
SELECT REGEXP_INSTR('abcabcabc','a+',1,4), REGEXP_INSTR('abcabcabc','a+',1,4294967298);
SELECT REGEXP_INSTR('abcabcabc','b+',4294967297);
SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',1,4294967297);
-- expected: 0, 0 / ERROR 3686 / 'aaabbccbbddaa'
-- actual: 0, 4 / 2 / 'aaaxccbbddaa'
Suggested fix:
Read pos and occurrence as longlong (taking unsigned_flag into account). A pos
larger than the subject length + 1 raises ER_REGEXP_INDEX_OUTOFBOUNDS_ERROR as it
does today for 10; an occurrence larger than INT_MAX can be clamped to INT_MAX
(there can never be that many matches), which gives "no match".
Description: The pos and occurrence arguments of REGEXP_INSTR(), REGEXP_SUBSTR() and REGEXP_REPLACE() are read into an int, so only their low 32 bits are used: occurrence -- 'abcabcabc' has three matches of 'a+': SELECT REGEXP_INSTR('abcabcabc','a+',1,2); -- 4 SELECT REGEXP_INSTR('abcabcabc','a+',1,4); -- 0 (no 4th match: correct) SELECT REGEXP_INSTR('abcabcabc','a+',1,4294967298); -- 4 (2^32+2 acts as 2) SELECT REGEXP_INSTR('abcabcabc','a+',1,4294967296); -- 1 (2^32 acts as 0 -> 1) SELECT REGEXP_INSTR('abcabcabc','a+',1,2147483648); -- 1 (2^31 is negative -> 1) SELECT REGEXP_INSTR('abcabcabc','a+',1,9223372036854775809); -- 1 SELECT REGEXP_SUBSTR('ab ac ad','A.',1,4); -- NULL (correct) SELECT REGEXP_SUBSTR('ab ac ad','A.',1,4294967298); -- 'ac' SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',1,3); -- 'aaabbccbbddaa' (correct) SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',1,4294967297); -- 'aaaxccbbddaa' pos -- a position past the end of the string is an error, but 2^32 + k is position k: SELECT REGEXP_INSTR('abcabcabc','b+',10); -- ERROR 3686 Index out of bounds SELECT REGEXP_INSTR('abcabcabc','b+',2147483648); -- ERROR 3686 SELECT REGEXP_INSTR('abcabcabc','b+',4294967297); -- 2 (acts as pos 1) SELECT REGEXP_INSTR('abcabcabc','b+',4294967300); -- 5 (acts as pos 4) SELECT REGEXP_SUBSTR('ab ac ad','A.',4294967299); -- 'ac' SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',4294967297,0); -- 'aaaxccxddaa' The same happens when the argument comes from a BIGINT column: CREATE TABLE t27 (id INT PRIMARY KEY, o BIGINT UNSIGNED); INSERT INTO t27 VALUES (1, 4), (2, 4294967298); SELECT id, REGEXP_INSTR('abcabcabc','a+',1,o), REGEXP_SUBSTR('abcabcabc','a+',1,o) FROM t27 ORDER BY id; -- 1 0 NULL -- 2 4 'a' No warning in any case. The other string functions that take positions and counts (LEFT, RIGHT, SUBSTRING, LPAD, REPEAT, SPACE, ...) had the same 32-bit narrowing and were fixed long ago (Bug #10963); REGEXP_* came later. Where (trunk 3b99be40): sql/item_regexp_func.h 115 std::optional<int> position() const { 118 const int value = args[the_index]->val_int(); ... 134 std::optional<int> occurrence() const { 137 const int value = args[the_index]->val_int(); How to repeat: SELECT REGEXP_INSTR('abcabcabc','a+',1,4), REGEXP_INSTR('abcabcabc','a+',1,4294967298); SELECT REGEXP_INSTR('abcabcabc','b+',4294967297); SELECT REGEXP_REPLACE('aaabbccbbddaa','b+','x',1,4294967297); -- expected: 0, 0 / ERROR 3686 / 'aaabbccbbddaa' -- actual: 0, 4 / 2 / 'aaaxccbbddaa' Suggested fix: Read pos and occurrence as longlong (taking unsigned_flag into account). A pos larger than the subject length + 1 raises ER_REGEXP_INDEX_OUTOFBOUNDS_ERROR as it does today for 10; an occurrence larger than INT_MAX can be clamped to INT_MAX (there can never be that many matches), which gives "no match".