Sqlite comparison of the same operand types behaves differently

Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison, especially this statement:

If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.

I would expect the following query:

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;

to return the same result for sum(amount) = '4' as for amount = '4' for each row as both operand types have the same type in each comparison (verified using typeof(), for non-SUM() the comparison is working as expected).

Demo: http://sqlfiddle.com/#!5/59238/2

I think you've spotted a slight inconsistency in the behaviour of SQLite! All of the code below is available on the fiddle here.

First we have:

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

Result:

INT_to_TEXT
          0  <<--- FALSE!

Then we do this:

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

Result:

Casted
     1  <<--- TRUE!

So, now we try the table:

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

Result:

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

Finally,

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;

Result:

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

So, the cases where it's f/f/f are the ones where you try and coerce SUM(amount) to match TEXT! It seems to me that what's happening is that SQLite becomes confused when you try and implicitly coerce a string to match a SUM. I thought it might be all aggregate functions, but it's not!

Check this out:

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;

Result:

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

So, for COUNT, it's t/f but for MAX, it's t/t!

As I said, I think you've discovered a discrepancy in the behaviour of SQLite, unless of course D. Richard Hipp is using some logic which I haven't quite understood - and he's a pretty smart cookie! Shall you report it or shall I?

Although, quite what utter lunatic would actually want to do this "in real life" is way way beyond my pay grade! :-)

And finally, just an FYI, you don't need backticks for SQLite - even MySQL doesn't require them any longer! +1 for an interesting post!

StackExchange Database Administrators Q#332585, answer score: 2