SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select sqlite_version();


sqlite_version()


3.39.1


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


✓
INSERT INTO invoice (amount) 
VALUES (4.0), (15.0), (4.0);

✓
SELECT * FROM invoice;


id
amount


1
4

2
15

3
4


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

-- Result FALSE!


INT_to_TEXT


0


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

-- Result TRUE!


Casted


1


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


id
amount
SUM(amount)
typeof(amount)
amount = '4'
typeof(sum(amount))
sum(amount) = '4'
sum(amount) = '4.0'
sum(amount) = 4


1
4
4
real
1
real
0
0
1

2
15
15
real
0
real
0
0
0

3
4
4
real
1
real
0
0
1


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


Int
Coerced


1
1

0
0

1
1


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


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



COUNT(*) = 3
COUNT(*) = '3'
MAX(amount) = 15
MAX(amount) = 15  -- <<---- But, MAX() does not!


1
0
1
1