Sample Header Ad - 728x90

Sqlite comparison of the same operand types behaves differently

3 votes
2 answers
432 views
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
Asked by mvorisek (428 rep)
Oct 27, 2023, 01:14 PM
Last activity: Oct 30, 2023, 03:26 PM