sqlite select query with decimal/float comparison doesn't work

Question

I need to perform decimal comparison operations using a simple select, 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 do satisfy the comparison. In a few words, "time > 0.0004" always evaluates to true, and "time < 0.0004" always evaluates to false.

I have tried casting and changing the type of the time column from decimal to float without success.

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

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

Table create:

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

Answer 1

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

The "time" column values were being handled as "text". Why?

I was importing data to the table using ".import" and my CSV file contained spaces around the decimal values. Somehow, SQLite's import command allows the inserts to happen even if the types do not match. After I removed the spaces from the CSV file, the data is inserted as "real", which allows for the number comparisons to happen correctly. HOWEVER, it does not explain why the data in the "decimal" column is of type "real" and not "decimal" or "numeric" as explained 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