sqlite3_column_type for a SUM column
I am running a query that looks like:
select country, sum(population) as total_population from cities group by country having total_population >= ? order by total_population desc, country asc
SQLITE_NULL for the
total_population column. I can imagine how this happens, since it would require some data flow analysis to determine the actual type of the
sum() expression; on the other hand, it is a bit of an annoyance because now that column comes back to me (on a NodeJS environment) as a string.
Is there a recommended way of getting the actual numeric value for this type of column? Should I just resign myself to try and manually convert them?
If you control the query, simply
I don't know before hand what the queries will be. All I know is that sometimes they might have
AVG, etc. I'm guessing some other times they might have a function that returns a string for that column.
(4) By Gunter Hick (gunter_hick) on 2021-01-08 16:14:01 in reply to 1 [link] [source]
The sum() function returns NULL if there are no non-NULL values in the column. Total will return 0 in that case.
Note that sqlite3_column_type() inspects the value for the current row, whereas sqlite3_column_decltype() returns the declared type of the table column.
I am not talking about the return value of that
SUM column, I am talking about what
sqlite3_column_type() returns for it: it is always
SQLITE_NULL, even when the column contains only numbers. I wanted to check whether this is as documented (I believe it is), and what is the common way of dealing with this.
Are you certain there are no NULLs in that field? Does adding
WHERE population IS NOT NULL fix it?
(7) By Gunter Hick (gunter_hick) on 2021-01-08 16:24:55 in reply to 5 [link] [source]
The documentstion states:
"These routines return information about a single column of the current result row of a query."
"The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The return value of sqlite3_column_type() can be used to decide which of the first six interface should be used to extract the column value."
If sqlite3_column_type returns SQLITE_NULL, then that column has the value NULL for the current row of the result set.
I was sure I had read that
sqlite3_column_type() can only be used for a "real" table column and does not apply for any computed columns (such as a
SUM aggregation), hence my initial assertion "I can imagine how this happens...". However, now I cannot find this on any docs.
I am starting to wonder whether this is an artifact of the driver I am using: better-sqlite3 for NodeJS. It is on this environment where I see that a
SUM column returns
null for the column type.
The docs for this driver do say:
.type: the name of the declared type, or null if it's an expression or subquery.
so now I think I should direct my questions somewhere else... :-)
Thanks for all the clarifications.
No, sqlite3_column_type returns the datatype of a single value at the intersection of the current row for the column of that row specified in the API call.
It returns no information about the datatype of any value in the same column of any other row preceding or following the current row.
To obtain information about the value in the same column position in some other row, you must retrieve that other row and obtain the datatype of the data via this API at the specified column position in that particular other row.
sqlite3_column_decltype returns the declared type of a column. (sqlite3_column_type returns the ACTUAL type for the data value -- they are quite independent).
sqkite3_column_decltype only works for columns which are table columns and not for expressions -- the declared type for an expression does not exist.
Notwithstanding what sqlite3_column_decltype returns each individual value of the column in each individual row may be of any datatype and that information is queried by the sqlite3_column_type API after each row is retrieved before retrieving each value.
(11) By Gunter Hick (gunter_hick) on 2021-01-11 06:48:45 in reply to 8 [link] [source]
It now seems obvious that the "type" method of the wrapper is calling sqlite3_column_decltype() to retrieve an attribute of the prepared statement.