Description
I found a bug that was introduced in v2.1.8. I walked back through the version history and found that the bug does not exist on v2.1.7, but does on v2.1.8 and all the way up through the latest release.
I have a complex query that uses GROUP BY to remove rows that have duplicate ids, caused by a LEFT JOIN on a different table. I have tested my query with the sqlite3 command line tool and a Python client and both return the correct results. However, node-sqlite3 after version v2.1.8 returns wrong results.
To reproduce the bug, you can initialize your database with this code:
CREATE TABLE Category (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT,
name TEXT,
UNIQUE(username, name)
);
CREATE TABLE FlexCategory (
id INTEGER PRIMARY KEY AUTOINCREMENT,
categoryId INTEGER,
month INTEGER
);
INSERT INTO Category (username, name) VALUES ('foo', 'cat1');
INSERT INTO Category (username, name) VALUES ('foo', 'cat2');
INSERT INTO Category (username, name) VALUES ('foo', 'cat3');
INSERT INTO Category (username, name) VALUES ('foo', 'cat4');
INSERT INTO FlexCategory (categoryId, month) VALUES (4, 10);
INSERT INTO FlexCategory (categoryId, month) VALUES (4, 11);Now run this query:
SELECT "Category".*, "FlexCategory"."id" AS "flexId"
FROM "Category"
LEFT JOIN "FlexCategory" ON ("Category"."id" = "FlexCategory"."categoryId")
WHERE ((Category.username = 'foo') AND
(("FlexCategory"."id" IS NULL) OR
(("FlexCategory"."month" >= 0) AND
("FlexCategory"."month" <= 5))))
GROUP BY Category.idBasically, the query gets categories from the Category table, and also joins on FlexCategory and only returns categories that either aren't referenced by FlexCategory, or are and are within the correct month range. The GROUP BY removes duplicate categories if there are multiple entries in FlexCategory within your range, and shown in the initial data I gave.
The above query should only return the "cat1", "cat2", and "cat3" categories, like so:
[ { id: 1, username: 'foo', name: 'cat1', flexId: null },
{ id: 2, username: 'foo', name: 'cat2', flexId: null },
{ id: 3, username: 'foo', name: 'cat3', flexId: null } ]The command line sqlite3 tool and a Python client both correctly return this. However, with node-sqlite from v2.1.8 and on, it returns:
[ { id: 1, username: 'foo', name: 'cat1', flexId: null },
{ id: 2, username: 'foo', name: 'cat2', flexId: null },
{ id: 3, username: 'foo', name: 'cat3', flexId: null },
{ id: 4, username: 'foo', name: 'cat4', flexId: null } ]"cat4" is included in the results, which is wrong. If I remove GROUP BY it works, but then I get duplicate results in other cases. Maybe it's processing GROUP BY in the wrong order?
Again, this works on 2.1.7 so I am forced to revert to that version. It'd be great to get this fixed.