SQLite Forum

sqlite3_column_type for a SUM column

sqlite3_column_type for a SUM column

(1) By Gonzalo Diethelm (gonzus) on 2021-01-08 14:54:57 [link]

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

I see `sqlite3_column_type()` returning ` 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?


(2) By ddevienne on 2021-01-08 15:04:06 in reply to 1 [link]

If you control the query, simply `cast`, no?

(3) By Gonzalo Diethelm (gonzus) on 2021-01-08 15:35:54 in reply to 2 [link]

I don't know before hand what the queries will be. All I know is that sometimes they might have `SUM`, `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]

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.

(5) By Gonzalo Diethelm (gonzus) on 2021-01-08 16:18:32 in reply to 4 [link]

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.

(6) By Warren Young (wyoung) on 2021-01-08 16:22:07 in reply to 5 [link]

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]

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.

(8) By Gonzalo Diethelm (gonzus) on 2021-01-08 17:48:23 in reply to 7 [link]

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](https://github.com/JoshuaWise/better-sqlite3). 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.

(9) By Keith Medcalf (kmedcalf) on 2021-01-08 18:58:03 in reply to 8 [link]

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.

(10) By Keith Medcalf (kmedcalf) on 2021-01-08 19:08:23 in reply to 8 [link]

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]

It now seems obvious that the "type" method of the wrapper is calling sqlite3_column_decltype() to retrieve an attribute of the prepared statement.

(12) By Gonzalo Diethelm (gonzus) on 2021-01-11 14:13:27 in reply to 1

Thanks everyone for pointing me in the right direction. As usual, SQLite is working as expected. I opened an [issue on the other project](https://github.com/JoshuaWise/better-sqlite3/issues/535).