SQLite

The query in the SQL shown below gives an incorrect answer after
ANALYZE has been run:

<blockquote><verbatim>
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
CREATE INDEX t1b ON t1(b);
CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
CREATE INDEX t2y ON t2(y);
WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
  INSERT INTO t1(b) SELECT x/10 - 1 FROM c;
WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
  INSERT INTO t2(x,y) SELECT x, 1 FROM c;
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
ANALYZE;
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
</verbatim></blockquote>

This problem seems to have been introduced by the skip-ahead-distinct
optimization of check-in [f489b5bb6b35665], first included in SQLite
version 3.19.0.  The problem was reported on the public mailing list
by David Raymond.

Incorrect result due to the skip-ahead-distinct optimization