SQLite HAVING comparison error

Question

I have a test SQLite table for storing reporting 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'm verifying that the revenue column is understood to be numeric/integer and that a comparison using the revenue 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 attempt to do a HAVING clause using an aggregate (SUM) of the revenue column, the result is incorrect. 7 is not less than 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 exact same test with MySQL, it works as expected, filtering out the Laffy Taffy row. Is there a good explanation for this?

Your query looks right to me -- I cannot replicate your issue: http://sqlfiddle.com/#!5/c1426/1 — sgeddes, Jun 29 '16 at 17:08
I'm using PHP 5.6 PDO 'sqlite' engine with a ':memory:' database file. Perhaps it's a version issue. — Dan Blaisdell, Jun 29 '16 at 17:17
Also, if I change the HAVING clause to be `SUM(test_fact_daily_revenue.revenue) > 5`, I get no rows back. — Dan Blaisdell, Jun 29 '16 at 17:35
@sgeddes, thank you for your help. If I alter your example to use '5' instead of 5, it replicates my issue. It is a PHP library issue. — Dan Blaisdell, Jun 29 '16 at 18:13

score 2 · Answer 1 · answered Jun 29 '16 at 18:11

It's an issue with the PHP PDO driver and prepared statements! The following prepared statement doesn't work if the bound parameter is a PHP float, because PHP only can bind it as INTEGER or STRING (and string is the recommended param type for decimals). Because my column could have DECIMAL values, the library I'm using bound it as STRING. SQLite has weird behaviors for string comparisons, even if they are numeric. To get around it, I'm going to 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) < ?

SQLite HAVING comparison error

1 Answers1

Linked