Discrepancy in behaviour (possible bug?)

(1.1) Originally by Pól (dragam) with edits by Richard Hipp (drh) on 2023-10-27 16:36:29 from 1.0 [source]

Hi all,

Today (2023-10-27), there was a question on dba.stackexchange.com. The scenario is a bit contrived, but nonetheless, it's a bit of a "niggle" if not a bug?

https://dba.stackexchange.com/questions/332585/sqlite-comparison-of-the-same-operand-types-behaves-differently

All of the following code is available on the fiddle here (much clearer).

https://dbfiddle.uk/NYtHzJvz

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
  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


Now, this is consistent - SQLite doens't like to coerce a SUM of something to an INTEGER, which kinda makes sense, unless:

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

Result:

COUNT(amount) = 3 COUNT(amount) = '3' MAX(amount) = 15 	MAX(amount) = 15 

                1 	           0 	             1 	               1


So, SUM() and COUNT() behave one way and MAX() behaves in another - surely all of the aggregate functions should behave in the same way?

(2) By ddevienne on 2023-10-27 16:30:08 in reply to 1.0 [link] [source]

Please lookup the forum's markup doc so your post does not look so garbled.
Just surrounding code blocks with triple-backticks would go a long way already.

PS: You can edit your own post in place too (to add backticks), in case you haven't noticed.

(3) By David Raymond (dvdraymond) on 2023-10-27 16:31:00 in reply to 1.0 [link] [source]

COUNT(amount) = 3 COUNT(amount) = '3' MAX(amount) = 15 MAX(amount) = 15

You didn't put the final 15 in quotes.

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

(5) By Pól (dragam) on 2023-10-28 15:30:45 in reply to 3 [link] [source]

Doh... <slaps forehead a la Homer Simpson> - thanks for that. It'll teach more not to assume bugs too quickly in future...

(4.1) By David Raymond (dvdraymond) on 2023-10-27 17:24:14 edited from 4.0 in reply to 1.1 [link] [source]

Datatypes In SQLite

Section 3.2 Affinity of Expressions
If you follow the rules there, "sum(amount)" falls into the final bullet point "Otherwise, an expression has no affinity." It does have a type of real, but it does not have an affinity.

So the part quoted in the original question from 4.2 Type Conversion Prior To Comparison: "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." doesn't come into play since "sum(amount)" does not have an affinity.
And the text literal '4.0' also does not have an affinity, so the second bullet point of section 4.2 doesn't take effect either.
So it goes to the third bullet point: "Otherwise, no affinity is applied and both operands are compared as is."

(Edited to add these examples)

sqlite> select 4.0 = '4.0';
4.0 = '4.0'
-----------
0

sqlite> select cast(4.0 as real) = '4.0';
cast(4.0 as real) = '4.0'
-------------------------
1

sqlite> select 4.0 = cast('4.0' as text);
4.0 = cast('4.0' as text)
-------------------------
1

sqlite> select cast(4.0 as real) = cast('4.0' as text);
cast(4.0 as real) = cast('4.0' as text)
---------------------------------------
1

(6) By Pól (dragam) on 2023-10-28 15:35:54 in reply to 4.1 [link] [source]

Thanks for your input.

I think I prefer PostgreSQL's way of doing it!

https://dbfiddle.uk/jj9bvwmN

Rgs,

Pól...