[Bug]: Exact filter on COUNT() column doesn't work

[midoragh]

What did you do?

I created a view
CREATE VIEW "ViewProductsPurchased" AS
SELECT (COUNT(Purchases.Product)-COUNT(Purchases.Removed)) AS Count, Products.Manufacturer
FROM Products LEFT JOIN Purchases ON Products.Key = Purchases.Product
GROUP BY Products.Key
to count how often a product has been purchased.
Selected 'Exact filter' on a value of the Count column

What did you expect to see?

I expected that the list shows all rows containing the exact filter value

What did you see instead?

The result list is empty

DB4S Version

3.13.0-rc1

What OS are you seeing the problem on?

Windows

OS version

Windows 10

Relevant log output

Filter 2: 
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Count" = '2'  ORDER BY "Manufacturer" ASC LIMIT 49999 OFFSET 0;

I also tried
Filter 2:
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Count" LIKE '%2%' ESCAPE '\' ORDER BY "Manufacturer" ASC LIMIT 49999 OFFSET 0;
Filter >2:
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Count" > 2  ORDER BY "Manufacturer" ASC LIMIT 49999 OFFSET 0;
Both showed the expected result set.

Maybe the exact filter should produce
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Count" = 2  ORDER BY "Manufacturer" ASC LIMIT 49999 OFFSET 0;
or
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Count" LIKE '2' ESCAPE '\' ORDER BY "Manufacturer" ASC LIMIT 49999 OFFSET 0;

Prevention against duplicate issues

• I have searched for similar issues

[chrisjlocke]

Can you share your schema (not the data) of the two tables.
My initial thought was that the view doesn't quite make sense - the view contains more columns than the group by, but that may just be me using SQL server too much...

[chrisjlocke]

COUNT(Purchases.Product)

Isn't that just a count of the number of your purchases? (sales). So if you had ten sales (regardless of the number of products sold in those purchases) it would just be ten? I don't think this is the cause of your problem, but either way, its not going to give you the figure you're after. I'm trying to recreate this issue, but can't fathom how you're getting your values.

Also, count just gives you the number of rows. Does the field 'removed' contain how many items were purchased? So if fred made a purchase of ten items, and bob made a purchase of ten items, that would be two rows of ten in the 'removed' field? If so, count would be two, so you'd need the 'sum' keyword to get the true value of twenty.

[chrisjlocke]

Ignoring my comments above, the exact filter is working for me on a view.

Edit: OK, it works on normal columns, but not calculated columns.
Its treating it as text, and the where clause is failing as a value of 2 doesn't equal a textual where of ='2' (which surprises me...)
Looking at the schema, db4s isn't correctly reporting the field type of the calculated column. I don't know if this is an issue with db4s or not.
The easiest solution is to override the filter and specifically tell it to filter on a numeric value of 2 not string value of 2.

[midoragh]

CREATE TABLE "Products" (
"Key" TEXT UNIQUE,
"Manufacturer" TEXT,
"ProductId" TEXT,
"ProductName" TEXT,
"Category" TEXT,
"Description" TEXT,
"Color" TEXT,
"Width" REAL,
"Depth" REAL,
"Height" REAL,
"PriceCurrency" TEXT DEFAULT 'CHF',
"PriceValue" REAL,
"PriceDate" TEXT,
"Photo" BLOB,
"Remark" TEXT
)
CREATE TABLE "Purchases" (
"Added" TEXT,
"Removed" TEXT,
"Dealer" TEXT,
"DealerLocation" TEXT,
"Product" TEXT,
"Currency" TEXT,
"Price" REAL,
"Location" TEXT,
"Remarks" TEXT,
FOREIGN KEY("Product") REFERENCES "Products"("Key")
)

The Purchases table contains the Products I bought. Sometimes one of these items get destroyed of gifted. In this case there will be a date in the removed column.
You may simplify
SELECT (COUNT(Purchases.Product)-COUNT(Purchases.Removed)) AS Count,
to
SELECT COUNT(Purchases.Product) AS Count,
but the exact filter is the same.

[midoragh]

=2 is working

but =2 doesn't work

[chrisjlocke]

I don't know the internal logic of DB4S, but I'm guessing its seeing the calculated column and not knowing what it is .. text, integer, etc, so assuming text.

Try creating a view, forcing the type.

create view vwProducts2
as
select *, cast(key + product as INT) as derived
from products

This may then work.

Edit:
So for your use case, this would be

SELECT cast(COUNT(Purchases.Product) - COUNT(Purchases.Removed) as int) AS Count, Products.Manufacturer
from products
LEFT JOIN Purchases ON Products.Key = Purchases.Product
GROUP BY Products.Key

[midoragh]

It works, thanks for the support. This is a little bit strange because COUNT() should be already of type int.

Do you think that this is an sqlite3 issue? Still wondering why the log shows
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Manufacturer" LIKE '%Ugears%' ESCAPE '' AND "Count" = '2' ORDER BY "Count" DESC LIMIT 49999 OFFSET 0;
and not
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Manufacturer" LIKE '%Ugears%' ESCAPE '' AND "Count" = 2 ORDER BY "Count" DESC LIMIT 49999 OFFSET 0;
as with the other compare operators like >=.

[chrisjlocke]

Do you think that this is an sqlite3 issue?

More of a Db4S issue. The filter probably (this is an assumption) looks at the field type and says, 'is this an integer? If so, I don't need to add quotes, otherwise I do', doesn't see anything, so based on the 'otherwise', adds quotes.
There are a couple of open issues around the filters, eg setting explicit values, which are kind of related.

Just found another 'workaround', rather than setting the cast type. Change the filter to just 2, and not =2. This then uses a 'like' operator which finds the record. However, this fails if you also have a count value of 22. Hmm. So not much of a workaround. Not foolproof, anyway. :(

[chrisjlocke]

Setting this as a bug as someone more intelligent than me might be able to spot an easy fix.

[midoragh]

The cast works fine. Typically I'm only filtering for "=0" or ">0". But because "=0" didn't work I just used "0" as workaround ;-)
Just thought that I should raise the issue/bug here. Maybe it's really just an issue that evaluating COUNT() or the arithmetic operators doesn't forward the correct type. So COUNT() is always int and int+int is also int.