SQLite

In the following SQL, the query with the duplicate "user.active=1" constraint returns an incorrect result. The one with a single "user.active=1" constraint works correctly.

CREATE TABLE user ( id INTEGER NOT NULL, active INTEGER NOT NULL, account INTEGER, PRIMARY KEY(id) );
CREATE TABLE account ( id INTEGER NOT NULL, name TEXT, PRIMARY KEY(id) );
INSERT INTO user VALUES (1, 0, 1);
INSERT INTO account VALUES (1, 'test');
SELECT user.id FROM user INNER JOIN account ON user.account = account.id WHERE user.active = 1 AND user.active = 1;
SELECT user.id FROM user INNER JOIN account ON user.account = account.id WHERE user.active = 1;

Bisecting shows that the problem was introduced by the WHERE-clause constraint propagation optimization added by check-in [f4229707ac08d66c5b0f5348] on 2018-07-27.

This problem was reported on the mailing list by Sebastian Zwack.