[Qgis-user] Spatialite queries return text instead of numeric values

[Goyo]

2014-12-10 1:50 GMT+01:00 Alexandre Neto <senhor.neto at gmail.com>:
> Hello,
>
> When querying a spatialite database in the database manager, if you create
> some calculations (say add two columns values) the resulting column returns
> as TEXT instead of the expected numeric field.
>
> Is this intended or a bug?
>
> Trying to cast it as real, double or float did not work either.

Calculated columns in SQLite do not have a data type, the actual
values in the column do. If you execute this query:

SELECT "col1" + "col2" AS "thevalue", typeof("col1" + "col2") as
"thetype" FROM "table"

You should have "thetype"='real' in every row (provided "col1" and
"col2" always contain real values) but the column "thevalue" does not
have a data type assigned.

I guess QGIS needs to assign a data type to each column and chooses
the more general type when the column does not have one without
looking at the actual values (which could be expensive and also
misleading if the data change).

Goyo