SQLite

Consider the following test case:

CREATE VIRTUAL TABLE stat USING dbstat;
SELECT * FROM stat WHERE stat.aggregate = 1; -- unexpected: fetches one record

Unexpectedly, the query fetches a row, even though the predicate should evaluate to FALSE:

SELECT stat.aggregate = 1 FROM stat; -- FALSE

This bug report was marked as fixed, but I can still reproduce the issue on trunk.

Good point. The ticket is changed from "Fixed" to "Closed" with a resolution of "Works As Designed".

The DBSTAT.AGGREGATE column is a HIDDEN column of the virtual table. That means it can work as an input. "Aggregate" is a setting which can be either 0 or 1. It defaults to 0. But if you have the "aggregate=1" term in the WHERE clause, then the setting is true and the virtual table behaves differently.

In the result, the value of "aggregate" will be whatever it was set to in the WHERE clause, or 0 (the default value) if it is not set in the WHERE clause.

I see, thanks for the explanation! I noticed that also the IN clause with a row value that has a single element has the same effect, which might be surprising:

SELECT * FROM stat WHERE stat.aggregate IN (1);

On the other hand, it might be unlikely that such a query occurs in the wild.