SQLite HAVING 比较错误 [英] SQLite HAVING comparison error

问题描述

我有一个用于存储带有值的报告数据的测试 SQLite 表:

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

但是当我尝试使用收入列的聚合 (SUM) 执行 HAVING 子句时，结果不正确.7 不小于 5.

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

如果我用 MySQL 重复完全相同的测试，它会按预期工作，过滤掉 Laffy Taffy 行.对此有什么好的解释吗?

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?

推荐答案

这是 PHP PDO 驱动程序和准备好的语句的问题！如果绑定参数是 PHP 浮点数，则以下准备好的语句不起作用，因为 PHP 只能将其绑定为 INTEGER 或 STRING(并且 string 是建议的小数参数类型).因为我的列可能有 DECIMAL 值，所以我使用的库将它绑定为 STRING.SQLite 对字符串比较有奇怪的行为，即使它们是数字.为了解决这个问题，我将不得不避免准备好的语句...... :-(

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 比较错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！