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
Last activity: Oct 30, 2023, 03:26 PM