(text/x-fossil-wiki)
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.
<blockquote><verbatim>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;
</verbatim></blockquote>
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.
|