SQLite

View Ticket
Login
Ticket Hash: 533010b8cacebe82533a8cd4e230fbb819565115
Title: Illegal argument to LIKELIHOOD() does not result in error when combined with "IN ()"
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Low
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-11 02:43:44
Version Found In:
User Comments:
mrigger added on 2019-06-10 18:15:31: (text/x-fossil-wiki)
Consider the following test case:

<pre>
CREATE TABLE t1 (c0);
CREATE INDEX i0 ON t1((LIKELIHOOD(c0, 100) IN ())); -- unexpected: no error
ALTER TABLE t1 RENAME COLUMN c0 TO c1; -- error occurs only here: second argument to likelihood() must be a constant between 0.0 and 1.0
</pre>

I would expect that the index cannot be created, since an illegal argument is passed to LIKELIHOOD. However, this is not the case and only when an ALTER TABLE is executed does the illegal parameter result in an error.

Note that this problem also applies to SELECT queries:

<pre>
SELECT * FROM t1 WHERE LIKELIHOOD(c0, 100) IN (); -- unexpected: also no error
SELECT * FROM t1 WHERE LIKELIHOOD(c0, 100) IS 1 -- expected: second argument to likelihood() must be a constant between 0.0 and 1.0
</pre>

dan added on 2019-06-10 19:18:25: (text/x-fossil-wiki)
Fixed by [71643deb].

mrigger added on 2019-06-10 21:58:16: (text/x-fossil-wiki)
I found another, similar bug that was not addressed by the fix:

<pre>
CREATE TABLE t0(c0);
CREATE INDEX i0 ON t0(((LIKELIHOOD(1, 2)) AND ((1 IN ())))); -- unexpected: no error
ALTER TABLE t0 RENAME TO t1; -- -- error occurs only here: second argument to likelihood() must be a constant between 0.0 and 1.0
</pre>