Sqlite select query with decimal / floating comparison not working

Question

I need to perform decimal comparison operations with a simple selection, for example:

select * from table_a, where time> 0.0004

id time

---

0 0.000502
1 0.000745
2 0.000725
4 0.000197
5 0.000176
6 0.000833
7 0.000269
8 0.000307
9 0.000302

The result contains values ​​that satisfy the comparison. In a few words, "time> 0.0004" is always evaluated as true, and "time <0.0004" is always evaluated as false.

I tried casting and changed the type of time column from decimal to swim without success.

If I use a subquery, it correctly executes the query:

select * from table_a, where time> (select time from table_a, where id = 8);

id time

---

0 0.000502
1 0.000745
2 0.000725
6 0.000833

Create table:

CREATE TABLE "table_a" ("id" integer NOT NULL PRIMARY KEY, "time" decimal NOT NULL);

Answer 1

SQLite has an unorthodox dynamic typing system, where the column type does not determine the actual data type in the column, it simply encourages SQLite to convert the data to a specific type, if possible.

When trying to compare a string and a number, if the string is not a well-formed number, it is considered larger than the number.

Thus, it is obvious that for some reason these time values ​​are not actually numbers, but strings. This is puzzling for two reasons. (1) Since the time column is of type decimal , it must have a "numerical" affinity, which should receive something in it that looks like a number converted to a number. (2) Even if the values ​​were stored as strings, they still had to be converted to numbers for comparison with 0.0004.

Why can't they be converted? Opportunity number 1: perhaps they contain extra spaces or something like that. Opportunity number 2: perhaps your language wants to use something different from . as a decimal point. (Perhaps there may be other possibilities that I did not think about.)

If you insert a record into a table that actually contains a number - insert into table_a (id,time) values (999,0.0001) or something like that, is this record included in your choice?

Answer 2

Thanks to all your suggestions, I found the root of the problem:

The values ​​of the time columns were treated as text. Why?

I imported the data into a table using ".import", and my CSV file contained spaces around decimal values. One way or another, the SQLite import command allows you to insert inserts, even if the types do not match. After I removed the spaces from the CSV file, the data is inserted as "real", which allows you to correctly compare the numbers. HOWEVER, this does not explain why the data in the "decimal" column is of the type "real" and not "decimal" or "numeric", as described in their docs: http://www.sqlite.org/datatype3.html

 select id, time, typeof(time) from table_a; id time type ---- ------------- ---- 0 0.000502 text 1 0.000745 text 2 0.000725 text 4 0.000197 text 5 0.000176 text 6 0.000833 text 7 0.000269 text 8 0.000307 text 9 0.000302 text select id, time, typeof(time) from table_b; id time type ---- ------------- ---- 0 0.000502 real 1 0.000745 real 2 0.000725 real 4 0.000197 real 5 0.000176 real 6 0.000833 real 7 0.000269 real 8 0.000307 real 9 0.000302 real 

Answer 3

Old question, but I want to give a different answer.

Because SQLite tends to see everything as text, the following happens:

If you request ... where a < 1000 , it will never find anything if a is processed as text

if you ask ... where a < '1000' , it will compare one thousand as some text and will have problems if the numbers have different character lengths. 1000 - 4 characters, and 100 - 3, etc.

But there is a CAST expression http://www.sqlite.org/lang_expr.html#castexpr

therefore ... where cast(a as Integer) < 1000 will lead to what you want (can be done with REAL for floats)

Answer 4

Obviously, this is not your case, but keep in mind that sometimes some administration tools (for example, SQLite Administrator or SQLite Expert ) can show you real values ​​as equal, although they are not. Here is an example:

The difference is not significant, but it is probably enough to give you a severe headache.

Answer 5

I can not reproduce the behavior that you see. I get the right results even if I

• define a time column as varchar,
• single quote value,
• double quote insertion value,
• define the time column as decimal and insert text literals first ("test text")

But, like Samuel Neff, I would still like to see the result of SELECT id, time, typeof(time) FROM table_a; in your database. Do you know what else might be interesting? Result

 select * from table_a order by time; 

These results are taken from version 3.7.4, which may not be the current version.

 sqlite> .dump table_a PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE "table_a" ("id" integer NOT NULL PRIMARY KEY, "time" decimal NOT NULL); INSERT INTO "table_a" VALUES(1,0.4); INSERT INTO "table_a" VALUES(2,0.6); INSERT INTO "table_a" VALUES(3,0.0005); INSERT INTO "table_a" VALUES(4,0.0006); INSERT INTO "table_a" VALUES(5,0.0004); COMMIT; sqlite> select * from table_a where time < 0.0005; 5|0.0004 sqlite> SELECT id, time, typeof(time) FROM table_a; 1|0.4|real 2|0.6|real 3|0.0005|real 4|0.0006|real 5|0.0004|real