Unexpected results when using `REPLACE` function

(1) By Suyang Zhong (suyang) on 2024-01-20 07:00:05 [source]

Considering the test case below:

CREATE TABLE t0(c0 VARCHAR, PRIMARY KEY(c0));
INSERT INTO t0 (c0) VALUES ( true);

SELECT * FROM t0; -- 1
SELECT * FROM t0 WHERE (t0.c0)=(REPLACE(true, '', false)); -- 1
SELECT * FROM t0 WHERE NOT ((t0.c0)=(REPLACE(true, '', false))); -- 1

It is unexpected that both the second and third SELECT return 1, since the expression and its negation could not be evaluated to 1 at the same time. Thus, at least one of the second and third SELECT should return an empty result.

I ran on the latest trunk version b06ab46a

(2) By Chris Locke (chrisjlocke1) on 2024-01-21 08:23:51 in reply to 1 [link] [source]

I remember a similar query 'recently' (unsure how recent...) but its to do with the comparison not looking at the same type of fields. Replace normally works with strings, so returns a string. Comparing a string to an integer isn't going to work great.

Replacing the replace with the constant true in the third statement works as expected.

Note that 'varchar' in the table creation is nonsense .. sqlite doesn't understand varchar and uses text instead.

Also the replace is also weird. Replace normally replaces strings in strings, so searching true for a string is always going to fail.

(3) By Suyang Zhong (suyang) on 2024-01-21 09:09:35 in reply to 2 [link] [source]

Hi,

Thanks for your explanation. Yes, the statements are somewhat nonsense because it was generated by a random generator, but I think anyway it's an unexpected result.

Seems it has been already fixed by check-in

(4) By Isokaze on 2024-01-21 11:22:14 in reply to 2 [link] [source]

replace converts arg0 to string, which is '1'.

replace(true, '1', 'whatever') can be done, if you want to.

(5) By Richard Hipp (drh) on 2024-01-21 11:46:30 in reply to 4 [link] [source]

Close - the replace() function was returning its first argument not as a string but as a weird intermediate value that is both a string and an integer at the same time, and that intermediate value was causing confusion further down the line when the OP_Eq operator in the byte code engine tried to compare it against another string using text affinity.

The replace() function was fixed by this check-in: https://sqlite.org/src/info/01868ebcd25fadb2.

The OP_Eq operator in the byte-code engine was fixed so that it still gets the correct answer even when passed one of those values that is both a string and an integer at the same time by this check-in: https://sqlite.org/src/info/709841f88c77276f

Please retry your test case in fiddle and verify that it now gets the correct answer.