Incorrect result may caused by window function

(1) By syang (ysy111) on 2024-03-08 11:52:05 [source]

Description

I excute the following stamtements in SQLite3 Fiddle(2024-03-06). Since sql1 returns NULL value,sql2 should return NULL too. Instead ,sql2 returns nothing.

CREATE TABLE v0 ( c1 INTEGER PRIMARY KEY, c2 TEXT);
CREATE VIEW v5 AS SELECT c1, COUNT ( * ) AS y, sum ( c2 ) OVER ( PARTITION BY c1) FROM v0;

SELECT c1 from v5; --sql1
c1
NULL

SELECT c1 FROM v5 WHERE c1 IS NULL; --sql2
--nothing

(2) By Richard Hipp (drh) on 2024-03-09 00:43:19 in reply to 1 [link] [source]

This problem goes back about three years to check-in de9c86c9e4cdb34f - the NOT NULL strength reduction optimization, that was added to try to improve efficiency of some SQL queries, in response to a forum post by Jinho Jung. The idea is that if X is a column marked as NOT NULL, then the expression "X IS NULL" will always be false and "x IS NOT NULL" will always be true. And nobody has had any trouble with that, across millions of applications, for over three years.

But as demonstrated by ysy111, the optimization not always correct. Here is a simple example of where the optimization fails:

CREATE TABLE t1(X INT NOT NULL);
SELECT X IS NULL, count(*) FROM t1;

Since the t1 table is empty, the bare X column in the aggregation is NULL even though the t1.X column is marked as not null. The optimization was causing the query above to return "0 0" instead of the correct answer of "1 0".

So, in other words, this bug is a classic case of over-optimization.

The solution is to only convert X IS NULL to false and X IS NOT NULL to true if they occur inside the WHERE clause.

The fix is on trunk, on branch-3.45, and on fiddle, and will appear in the next release.