[sqlite3] Converter Not Called with Aggregate Functions

[DLu]

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'> 5

Both 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

Linked PRs

• gh-131002: clarify how to enforce sqlite3 column types for generated fields #131006
• [3.13] gh-131002: clarify how to enforce sqlite3 column types for generated fields (GH-131006) #131385
• [3.12] gh-131002: clarify how to enforce sqlite3 column types for generated fields (GH-131006) #131386

[picnixz]

Is this the same in pure sqlite3 or not?

[erlend-aasland]

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]

Oh! I thought that converters were something from sqlite3 as well. My bad

[erlend-aasland]

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.connect docs for the detect_types parameter:

Types cannot be detected for generated fields (for example max(data)), even when the detect_types parameter is set; str will be returned instead.

[erlend-aasland]

One solution for the query in the OP could be to use PARSE_COLNAMES and enforce "faketype" in the query:

sqlite3.register_converter('faketype', lambda s: int(s.rstrip(b';')))
con = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
cur = con.execute('CREATE TABLE awesome(f faketype)')
cur.execute('INSERT INTO awesome(f) VALUES("5;")')

queries = [
    'SELECT f FROM awesome',
    'SELECT MIN(f) as "f [faketype]" FROM awesome',
]
for query in queries:
    cur.execute(query)
    result = cur.fetchone()[0]
    print(f'{query}\n{type(result)} {result}\n')

[erlend-aasland]

Quoting the sqlite3.connect docs for the detect_types parameter:

Types cannot be detected for generated fields (for example max(data)), even when the detect_types parameter is set; str will be returned instead.

Perhaps we could adjust this particular sentence in the docs. First, I think we should move this sentence to the sqlite3.PARSE_DECLTYPES documentation. Second, I think we should suggest using sqlite3.PARSE_COLNAMES for these cases.

Re-opening and rebranding as a docs issue.