SQLite

Consider the following test case:

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

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

<pre>
SELECT stat.aggregate = 1 FROM stat; -- FALSE
</pre>

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:

<pre>
SELECT * FROM stat WHERE stat.aggregate IN (1);
</pre>

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