Sqlite spec: 4.2. Type Conversions Prior To Comparison

All we need is an easy explanation of the problem, so here it is.

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 t (id integer primary key, str varchar(20));
INSERT INTO t (id, str) VALUES (1, '5'), (2, '5u');

SELECT id, 5 = str, 5 >= str, CAST(5 AS NUMERIC) >= str, CAST(str AS NUMERIC) FROM t;

to return 5 >= str = 1 for both rows as the left side operand has NUMERIC affinity.

Demo: http://sqlfiddle.com/#!5/e9c19/4

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I think you may be missing this part:

with the CAST operator the conversion always takes place even if the conversion lossy and irreversible, whereas column affinity only changes the data type of a value if the change is lossless and reversible

(emphasis mine).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply