主页 / dba / 问题 / 332585
Accepted
mvorisek
Asked: 2023-10-27 21:14:15 +0800 CST

相同操作数类型的 Sqlite 比较行为不同

  • 772

基于Sqlite文档： https: //www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison，特别是这个声明：

如果一个操作数具有 INTEGER、REAL 或 NUMERIC 关联性，而另一操作数具有 TEXT 或 BLOB 或无关联性，则 NUMERIC 关联性将应用于另一操作数。

我期望以下查询：

CREATE TABLE `invoice` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `amount` DOUBLE PRECISION DEFAULT NULL
);

insert into `invoice` (`amount`) values (4.0);
insert into `invoice` (`amount`) values (15.0);
insert into `invoice` (`amount`) values (4.0);

select *,
    typeof(amount), amount = '4',
    typeof(sum(amount)), sum(amount) = '4', sum(amount) = '4.0', sum(amount) = 4
from invoice
group by id;

sum(amount) = '4'为每行返回相同的结果，amount = '4'因为两个操作数类型在每次比较中都具有相同的类型（使用typeof(), for non进行验证SUM()，比较是否按预期工作）。

演示：http://sqlfiddle.com/#!5/59238/2

sqlite

2 个回答

  1. 我认为您已经发现 SQLite 的行为略有不一致！下面的所有代码都可以在此处的小提琴上找到。

    首先我们有：

    SELECT 4 = '4' AS "INT_to_TEXT";  -- No coercion because it's not a field!

    结果：

    INT_to_TEXT
          0  <<--- FALSE!

    然后我们这样做：

    SELECT 4 = CAST('4' AS INTEGER) AS "Casted";  -- <<-- Explicit CAST required

    结果：

    Casted
     1  <<--- TRUE!

    所以，现在我们尝试一下这个表：

    SELECT
  amount = 4  AS "Int",
  amount = '4' AS "Coerced" -- <<-- Implicit coercion occurs here because it's a field!
FROM
  invoice;

    结果：

    Int Coerced
  1       1    = 4  therefore TRUE
  0       0    = 15 therefore FALSE  4 != 15 any way you do it!
  1       1    = 4  therefore TRUE

    最后，

    SELECT
  SUM(amount) AS    "The sum (INT)",
  SUM(amount) = 4 AS "Expected", -- <<---- t/f/t as expected
  SUM(amount) = '4' AS "No coercion 1",    -- <<--- All FALSE
  SUM(amount) = CAST('4' AS INTEGER) AS "Casted", -- <<--- t/f/t as expected 
  SUM(amount) = '4.0' "No coercion 2"   -- <<--- All FALSE
FROM
  invoice
GROUP BY id;

    结果：

    The sum (INT)  Expected  No coercion 1  Casted  No coercion 2
            4         1              0       1              0
           15         0              0       0              0
            4         1              0       1              0

    因此，在 f/f/f 的情况下，您尝试强制 SUM(amount) 来匹配 TEXT！在我看来，当您尝试隐式强制字符串匹配 SUM 时，SQLite 会变得混乱。我以为可能都是聚合函数，但事实并非如此！

    看一下这个：

    SELECT
  COUNT(*) = 3,
  COUNT(*) = '3',   -- <<---- t/f COUNT() has the same behaviour as SUM
  MAX(amount) = 15,
  MAX(amount) = 15  -- <<---- t/t But, MAX() does not! 
FROM
  invoice;

    结果：

    COUNT(*) = 3  COUNT(*) = '3' MAX(amount) = 15 MAX(amount) = 15
           1              0                 1                1

    因此，对于 COUNT，它是 t/f，但对于 MAX，它是 t/t！

    正如我所说，我认为您已经发现了 SQLite 行为的差异，当然，除非 D. Richard Hipp 使用了一些我不太理解的逻辑 - 而且他是一个非常聪明的人！你举报还是我举报？

    尽管如此，“在现实生活中”真正想要做到这一点的彻底疯子远远超出了我的工资等级！:-)

    最后，仅供参考，您不需要 SQLite 的反引号 - 甚至 MySQL 也不再需要它们了！有趣的帖子+1！

    • 1
  2. Best Answer

    类型关联不应与数据类型（Sqlite 称为存储类）混淆。

    Sqlite 中的每个值都有一个存储类，它始终是 NULL、INTEGER、REAL、TEXT 或 BLOB。

    4 是 INTEGER 存储类别的值。'xyz' 是 TEXT 存储类别的值。

    请注意，typeof()返回值的存储类，而不是列的类型关联。

    由于在 Sqlite 中，列不需要具有特定的存储类，并且您可以在同一列中存储 4 或“xyz”，因此 Sqlite 引入了类型亲和性的概念，它指示应该存储哪种类型的值如果可能的话，该列。所以如果你声明一个列

    amount DOUBLE PRECISION

    您实际上是说 amount应该包含 REAL 值（具有 REAL类型affinity），但实际值可以是任何类型。

    '4'当您插入一个值into时，Sqlite 会尝试满足这种关联性amount：它会在存储之前将其转换为 REAL 值。但如果您插入值'xyz'，它无法将其转换为 REAL，而是将该值存储为 TEXT。

    当您在查询中使用该列时amount，Sqlite 假定该列中存储的值应该是 REAL 值。因此，当它进行比较时amount = '4'，它将应用类型亲和力的规则：'4'将转换为 REAL 并与列中存储的内容进行比较。

    由于类型亲和力是由创建时的列定义决定的，而不是由包含的单个值决定的，这意味着：

    A. _ amount即使行中包含的值为 ，类型关联性也始终为 REAL 'xyz'。

    B. _ 任何不是列的内容都从未使用类型关联进行声明，因此任何不是对实际表的列的简单引用或未显式 CAST 的表达式都不具有类型关联。

    这意味着4和'4'没有类型关联（即使它们分别是 INTEGER 和 TEXT 存储类的常量）。另外amount+10，sum(amount)或min(amount)都是不具有类型关联的表达式（您没有向 Sqlite 声明这些表达式应该是哪种类型），但是，当对每一行进行计算时，它们将计算为特定存储类中的值。

    这就是为什么：

    • 4 = '4'为 FALSE，因为两者都没有类型亲和性，并且 sqlite 将整数与字符串进行比较
    • amount = '4'当 a 存储在行中时为 TRUE 4，因为 amount 的类型亲和性为 REAL，sqlite 期望包含的所有值都应该是实数并在比较之前转换'4'为。4
    • sum(amount) = '4'即使 sum 实际上是 FALSE 4，因为sum(amount)它不是对列的简单引用，它是一个表达式，因此它没有类型亲和力。4然后与'4'第一个示例中的结果进行比较，结果为 false。
    • 但是 当总和实际上是 时，CAST(sum(amount) AS REAL) = '4' 它是 TRUE4，因为 CAST 为左手表达式分配了 REAL 类型关联，因此 sqlite 知道它必须在比较之前将右手转换'4'为4。

    另外，如果我理解正确的话，虽然在运行时检查和评估存储类（因为不同的行可以具有不同amount存储类的不同值），但我认为类型亲和力是在准备时评估的，因此实际上为每个建立了任何必要的转换行，然后检查行中的值。

    • 1

相关问题