Repository navigation
[sqlite3] Converter Not Called with Aggregate Functions #131002
Description
Bug report
Bug description:
The converter functions are correctly called when using the raw field name in a query, but when you use an aggregate function like MIN(fieldname) the value is returned as a string.
Minimal Example
import sqlite3
sqlite3.register_converter('faketype', lambda s: int(s.rstrip(b';')))
con = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.execute('CREATE TABLE awesome(f faketype)')
cur.execute('INSERT INTO awesome(f) VALUES("5;")')
queries = ['SELECT f FROM awesome', 'SELECT MIN(f) FROM awesome']
for query in queries:
cur.execute(query)
result = cur.fetchone()[0]
print(f'{query}\n{type(result)} {result}\n')Expected Output
SELECT f FROM awesome
<class 'int'> 5
SELECT MIN(f) FROM awesome
<class 'int'> 5Both queries return an integer
Actual Output
SELECT f FROM awesome
<class 'int'> 5
SELECT MIN(f) FROM awesome
<class 'str'> 5;The query with just the fieldname f returns the proper type (int). The query using the aggregate function MIN(f) returns a string, indicating the converter function has not been called.
Additional information
A slightly expanded example is available as gist
Python 3.12.3 (main, Feb 4 2025, 14:48:35) [GCC 13.3.0] on linux / Ubuntu 24.04
CPython versions tested on:
3.12
Operating systems tested on:
Linux
Activity
picnixz commentedon Mar 9, 2025
Is this the same in pure sqlite3 or not?
erlend-aasland commentedon Mar 9, 2025
Our sqlite3 library has no control over what happens inside the underlying SQLite library. Similarly, the underlying SQLite library knows nothing at all about Python; it has no concepts of adapters and converters.
picnixz commentedon Mar 9, 2025
Oh! I thought that converters were something from sqlite3 as well. My bad
erlend-aasland commentedon Mar 9, 2025
Converters and adapters are a feature specific to the sqlite3 stdlib module. It has nothing to do with SQLite. We implement it by looking up the declared type (which can be any string; see SQlite's flexible typing) or by bisecting the custom pysqlite "column name" format.
Quoting the
sqlite3.connectdocs for the detect_types parameter:erlend-aasland commentedon Mar 9, 2025
One solution for the query in the OP could be to use
PARSE_COLNAMESand enforce "faketype" in the query:erlend-aasland commentedon Mar 9, 2025
Perhaps we could adjust this particular sentence in the docs. First, I think we should move this sentence to the
sqlite3.PARSE_DECLTYPESdocumentation. Second, I think we should suggest usingsqlite3.PARSE_COLNAMESfor these cases.Re-opening and rebranding as a docs issue.
1 remaining item
pythongh-131002: clarify how to enforce sqlite3 column types for gene…
DLu commentedon Mar 9, 2025
I'm glad to see that this was already covered in the documentation. I missed it partially because I was using the terminology "aggregate", so the referred to bit of docs didn't pop up in my search. My apologies for that.
Would it be possible to recast this as a feature request for some future version? Throw an extra
detect_types=sqlite3.PARSE_AGGREGATESflag in and add the parsing topysqlite_build_row_cast_maperlend-aasland commentedon Mar 9, 2025
Unfortunately, I don't think it is possible to implement such a feature with the SQLite C API :(
I'll have an extra look, though, just to be sure.erlend-aasland commentedon Mar 9, 2025
I wrote that comment too hastily :) A generated expression cannot have a declared type; it does not make sense. The SQLite C API that deals with declared types all return
NULLfor expressions and sub-queries.DLu commentedon Mar 10, 2025
Just to be clear, you mean that
sqlite3_column_namealways returns NULL for expressions/etc?erlend-aasland commentedon Mar 10, 2025
No, this is not the API you want; it only gives you the column query name. That can be anything (
SELECT a as "anything" FROM t). sqlite3_column_decltype provides the declared type:gh-131002: clarify how to enforce sqlite3 column types for generated …
pythongh-131002: clarify how to enforce sqlite3 column types for gene…
pythongh-131002: clarify how to enforce sqlite3 column types for gene…
[3.13] gh-131002: clarify how to enforce sqlite3 column types for gen…
[3.12] gh-131002: clarify how to enforce sqlite3 column types for gen…
pythongh-131002: clarify how to enforce sqlite3 column types for gene…