SQLite User Forum

Unexpected Result by WHERE Again
Login

Unexpected Result by WHERE Again

(1) By Jinsheng Ba (bajinsheng) on 2022-06-02 13:46:59 [link] [source]

See this inconsistent results. ('1')IS(v0.c1) IS FALSE is evaluated true at the third SELECT, but the second SELECT does not return any row.

CREATE TABLE t0 (c0);
CREATE VIEW v0(c0, c1) AS SELECT CUME_DIST() OVER (PARTITION BY t0.c0), TRUE FROM t0;
INSERT INTO t0 VALUES ('x');

SELECT ALL * FROM v0, t0;
-- 1.0|1|x
SELECT * FROM v0, t0 WHERE ('1')IS(v0.c1) IS FALSE;
-- Null
SELECT ('1')IS(v0.c1) IS FALSE FROM v0, t0;
-- 1

Environment:

Version: 3.39.0

Commit ID: 8eb9a7dd

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Richard Hipp (drh) on 2022-06-02 21:08:43 in reply to 1 [source]

Thanks for the bug report.

This problem goes back to version 3.35.0. A fix has been checked in on trunk. Even though this is a bug in a released version, the bug is highly obscure, and it is not a security threat, so we do not plan to do a patch release for it. The change will be in the next regular release (3.39.0) which should be out before too much longer.

(3) By Jinsheng Ba (bajinsheng) on 2022-06-03 09:10:19 in reply to 2 [link] [source]

Thanks!