/ View Ticket
Ticket Hash: a3713a5fca066b00781958519fc49796d87ca6f7
Title: DBSTAT query computes incorrect result for stat.aggregate = 1 condition
Status: Closed Type: Code_Defect
Severity: Minor Priority: Low
Subsystem: Unknown Resolution: Works_As_Designed
Last Modified: 2020-01-04 20:07:58
Version Found In: 3.30.0
User Comments:
mrigger added on 2020-01-04 18:51:14: (text/x-fossil-wiki)
Consider the following test case:

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

mrigger added on 2020-01-04 19:26:51: (text/x-fossil-wiki)
This bug report was marked as fixed, but I can still reproduce the issue on trunk.

drh added on 2020-01-04 19:57:13: (text/x-fossil-wiki)
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

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

mrigger added on 2020-01-04 20:07:58: (text/x-fossil-wiki)
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.