|Title:||DBSTAT query computes incorrect result for stat.aggregate = 1 condition|
|Last Modified:||2020-01-04 20:07:58|
|Version Found In:||3.30.0|
mrigger added on 2020-01-04 18:51:14:
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>
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 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.
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: <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.