SQLite HAVING comparison error

Question

I have a SQLite test pattern for storing report data with values:

CREATE TABLE IF NOT EXISTS "test_fact_daily_revenue" ( "date" TEXT, "revenue" NUMERIC, "product" TEXT ); INSERT INTO "test_fact_daily_revenue" ("date", "revenue", "product") VALUES ('2014-01-01', 3, 'Nerds'), ('2014-01-01', 2, 'Laffy Taffy'), ('2014-01-02', 1, 'Smarties'), ('2014-01-02', 5, 'Laffy Taffy'), ('2014-01-03', 0.5, 'Smarties'), ('2014-01-03', 1, 'Skittles'); 

I verify that the income column is understood as numeric / integer and that comparing using the income column works correctly:

 SELECT typeof(SUM(revenue)) AS revenue, typeof(product) AS product FROM test_fact_daily_revenue WHERE revenue > 1 GROUP BY product; integer|text integer|text 

But when I try to make a HAVING clause using the aggregate (SUM) of the revenue column, the result is incorrect. 7 is at least 5.

 SELECT test_fact_daily_revenue.product AS "product", SUM(test_fact_daily_revenue.revenue) AS "revenue" FROM "test_fact_daily_revenue" WHERE "test_fact_daily_revenue"."product" IS NOT NULL GROUP BY "test_fact_daily_revenue"."product" HAVING SUM(test_fact_daily_revenue.revenue) < 5 Laffy Taffy|7 Nerds|3 Skittles|1 Smarties|1.5 

If I repeat the same test with MySQL, it works as expected, filtering out the Laffy Taffy string. Is there a good explanation for this?

Answer 1

This is a problem with the PHP PDO driver and prepared operations! The following prepared statement does not work if the associated parameter is a PHP flag, because PHP can only bind it as INTEGER or STRING (and the string is the recommended parameter type for decimal places). Since there may be DECIMAL values ​​in my column, the library I use binds it as STRING. SQLite has strange behavior for comparing strings, even if they are numeric. To get around this, I will have to avoid prepared statements ...: - (

 SELECT test_fact_daily_revenue.product AS "product", SUM(test_fact_daily_revenue.revenue) AS "revenue" FROM "test_fact_daily_revenue" WHERE "test_fact_daily_revenue"."product" IS NOT NULL GROUP BY "test_fact_daily_revenue"."product" HAVING SUM(test_fact_daily_revenue.revenue) < ?