sqlite3's support for "numeric" paramstyle does not appear to honor the actual numbers with positional parameters #99953

zzzeek · 2022-12-02T16:26:52Z

We're attempting to get some test support for "numeric" paramstyle, which while unnecessary for sqlite3, is similar to the paramstyle used by a very widely used, non-pep-249 library asyncpg.

anyway, I don't think sqlite3 is interpreting "numeric" correctly when the numbers are not ordered. If we consider numbers like ":3, :4, :2, :1" etc. to just be more interesting looking question marks (like "?, ?, ?, ?"), that's certainly easy but it seems to defeat the purpose of "numeric" parameters, where we would assume the number refers to the position of an entry in the parameter list.

if indeed this is wrong and it's a bug (I'm going to ping the DBAPI SIG list with this, to get their notion of intent), I fully expect that sqlite3 probably cant change things at this point, but just want to understand indeed what the intent of "numeric" paramstyle is.

Demo below:

import sqlite3


conn = sqlite3.connect(":memory:")

cursor = conn.cursor()

cursor.execute(
    """
    create table my_table(
        a varchar,
        b varchar,
        c varchar,
        d varchar,
        e varchar
    )
"""
)


cursor.execute(
    """
    insert into my_table(a, b, c, d, e) values ('a', 'b', 'c', 'd', 'e')
"""
)


cursor.execute(
    """
    select count(*) from my_table where a=? and b=? and c=? and d=? and e=?
""",
    ("a", "b", "c", "d", "e"),
)
assert cursor.fetchone() == (1, )


cursor.execute(
    """
    select count(*) from my_table where a=:1 and b=:2 and c=:3 and d=:4 and e=:5
""",
    ("a", "b", "c", "d", "e"),
)
assert cursor.fetchone() == (1, )

cursor.execute(
    """
    select count(*) from my_table where a=:3 and b=:4 and c=:1 and d=:5 and e=:2
""",
    ("c", "e", "a", "b", "d")  # <--- fails

    #("a", "b", "c", "d", "e"),   # <--- succeeds, which is wrong

    # {"3": "a", "4": "b", "1": "c", "2": "e", "5": "d"} # <--- succeeds, but this is not "numeric" paramstyle

)
assert cursor.fetchone() == (1, )

Linked PRs

zzzeek · 2022-12-02T17:26:16Z

Here's the discussion: https://mail.python.org/pipermail/db-sig/2022-December/006363.html

24rr · 2022-12-03T01:48:07Z

The numeric paramstyle in the Python Database API (DBAPI) specifies that parameters in an SQL statement should be represented by numbers. This is in contrast to the other paramstyles, which use a different syntax to specify parameters (such as qmark, which uses ? to represent parameters, or format, which uses %s).

In the numeric paramstyle, the numbers refer to the position of the parameter in the parameter sequence. This means that if you have a statement like this:

SELECT * FROM my_table WHERE a = :1 AND b = :2

then the first parameter in the parameter sequence will be used for the a column, and the second parameter will be used for the b column. The order of the parameters is important, and they should be specified in the same order as they appear in the SQL statement.

In your example, the :3, :4, :1, :5, and :2 parameters in the SELECT statement are out of order. This means that the values in the parameter sequence will not be used in the expected way, and the query will not return the expected results.

If you want to use the numeric paramstyle, you should make sure that the parameters in your SQL statement are in the same order as the values in the parameter sequence. Alternatively, you can use a different paramstyle that does not rely on the order of the parameters, such as the qmark paramstyle.

zzzeek · 2022-12-03T03:24:48Z

The numeric paramstyle in the Python Database API (DBAPI) specifies that parameters in an SQL statement should be represented by numbers. This is in contrast to the other paramstyles, which use a different syntax to specify parameters (such as qmark, which uses ? to represent parameters, or format, which uses %s).

In the numeric paramstyle, the numbers refer to the position of the parameter in the parameter sequence. This means that if you have a statement like this:
SELECT * FROM my_table WHERE a = :1 AND b = :2
then the first parameter in the parameter sequence will be used for the a column, and the second parameter will be used for the b column. The order of the parameters is important, and they should be specified in the same order as they appear in the SQL statement.

In your example, the :3, :4, :1, :5, and :2 parameters in the SELECT statement are out of order. This means that the values in the parameter sequence will not be used in the expected way, and the query will not return the expected results.

Hey there -

sorry, then sqlite3 should raise an error. This is a broken feature and as it's too late to fix it, SQLite should note in the documentation that it's not really working as one would expect.

Refer to the thread here for guidance (that's pep-249's author): https://mail.python.org/pipermail/db-sig/2022-December/006364.html . "numeric" paramstyle is certainly not used by anyone but as sqlite3 mentions it in the docs and has marginal support it, both the docs and the library should be explicit about this unexpected restriction.

If you want to use the numeric paramstyle, you should make sure that the parameters in your SQL statement are in the same order as the values in the parameter sequence. Alternatively, you can use a different paramstyle that does not rely on the order of the parameters, such as the qmark paramstyle.

It should be obvious that "numeric" paramstyle is useless if the numbers have to be in exact numerical order.

24rr · 2022-12-03T03:27:17Z

The numeric paramstyle in the Python Database API (DBAPI) specifies that parameters in an SQL statement should be represented by numbers. This is in contrast to the other paramstyles, which use a different syntax to specify parameters (such as qmark, which uses ? to represent parameters, or format, which uses %s).
In the numeric paramstyle, the numbers refer to the position of the parameter in the parameter sequence. This means that if you have a statement like this:
SELECT * FROM my_table WHERE a = :1 AND b = :2
then the first parameter in the parameter sequence will be used for the a column, and the second parameter will be used for the b column. The order of the parameters is important, and they should be specified in the same order as they appear in the SQL statement.
In your example, the :3, :4, :1, :5, and :2 parameters in the SELECT statement are out of order. This means that the values in the parameter sequence will not be used in the expected way, and the query will not return the expected results.
Hey there -

sorry, then sqlite3 should raise an error. This is a broken feature and as it's too late to fix it, SQLite should note in the documentation that it's not really working as one would expect.

Refer to the thread here for guidance (that's pep-249's author): https://mail.python.org/pipermail/db-sig/2022-December/006364.html . "numeric" paramstyle is certainly not used by anyone but as sqlite3 mentions it in the docs and has marginal support it, both the docs and the library should be explicit about this unexpected restriction.

If you want to use the numeric paramstyle, you should make sure that the parameters in your SQL statement are in the same order as the values in the parameter sequence. Alternatively, you can use a different paramstyle that does not rely on the order of the parameters, such as the qmark paramstyle.

It should be obvious that "numeric" paramstyle is useless if the numbers have to be in exact numerical order.

You are correct - the numeric paramstyle in the Python DBAPI is essentially useless if the parameters have to be in a specific order. This is because most SQL statements will have multiple parameters, and it is not always possible or practical to specify them in a specific order. The qmark and format paramstyles, on the other hand, do not rely on the order of the parameters, and are therefore more flexible and useful in most cases.

erlend-aasland · 2022-12-30T21:42:10Z

Hi Mike, and thanks for the report.

sorry, then sqlite3 should raise an error. This is a broken feature and as it's too late to fix it, SQLite should note in the documentation that it's not really working as one would expect.

+1 to everything you said there. It is too late to fix this; a documentation update is needed.

It should be obvious that "numeric" paramstyle is useless if the numbers have to be in exact numerical order.

Indeed.

erlend-aasland · 2022-12-30T22:01:34Z

Digging a little bit more into this, I think I now see where the confusion comes from. This is the set of SQLite placeholder templates:

qmark: ?
numeric: ?<number>
named: :<name>
named: @<name>
named: $<name>

All of these work as expected in sqlite3. Here's an example using the SQLite numeric style:

import sqlite3
cx = sqlite3.connect(":memory:")
assert cx.execute("select ?1, ?2", ("a", "b")).fetchall() == [('a', 'b')]
assert cx.execute("select ?2, ?1", ("a", "b")).fetchall() == [('b', 'a')]

Now, this is the set of PEP-249 placeholder templates:

qmark: ?
numeric: :<number>
named: :<name>
format: %s
pyformat: %(name)s

If you pass the PEP-249 numeric style to sqlite3, it is silently interpreted as SQLite named style.

The intersection of these two placeholder template sets is ? and :<name>, and those two only. The mention of the numeric style should be purged from the docs :)

erlend-aasland · 2022-12-30T22:16:38Z

Slightly related: #52443

The PEP-249 numeric style has never been supported.

zzzeek · 2022-12-31T14:46:29Z

If you pass the PEP-249 numeric style to sqlite3, it is silently interpreted as SQLite named style.

yup, this is what it looked like was happening :) however, at the same time when you pass parameters to execute as a sequence, they somehow were being lined up with those named parameters. I'm not sure to what degree sqlite itself or the python driver is doing this, that is, silently adapting a sequence of non-named parameters to a statement using named style, but it would be nice if that didn't pass silently. But if it means having to newly parse SQL to figure it out, as I also suspect, then definitely dont get into that. I would not mind an implementation detail like this being noted in a sidebox however that is optional.

CAM-Gerlach · 2022-12-31T23:45:21Z

Just FYI, this comment sounds like it was generated by ChatGPT...

…0630) The PEP-249 numeric style has never been supported by sqlite3.

pythonGH-100630) The PEP-249 numeric style has never been supported by sqlite3. (cherry picked from commit b7a68ab) Co-authored-by: Erlend E. Aasland <erlend.aasland@protonmail.com>

erlend-aasland · 2023-01-01T22:34:00Z

however, at the same time when you pass parameters to execute as a sequence, they somehow were being lined up with those named parameters. I'm not sure to what degree sqlite itself or the python driver is doing this, that is, silently adapting a sequence of non-named parameters to a statement using named style, but it would be nice if that didn't pass silently.

Yep, it is unfortunate that this particular case is silently allowed. And by the way, it is the sqlite3 extension module, not SQLite, that has created that particular mess.

But if it means having to newly parse SQL to figure it out, as I also suspect, then definitely dont get into that. I would not mind an implementation detail like this being noted in a sidebox however that is optional.

Definitely! See #100668. I suggest we fix that doc issue first, and then consider how we can mitigate the case where the named style is used and a tuple, list, or non-dict-sequence is supplied.

…100630) The PEP-249 numeric style has never been supported by sqlite3. (cherry picked from commit b7a68ab) Co-authored-by: Erlend E. Aasland <erlend.aasland@protonmail.com>

zzzeek added the type-bug An unexpected behavior, bug, or error label Dec 2, 2022

AlexWaygood added the topic-sqlite3 label Dec 2, 2022

erlend-aasland added the docs Documentation in the Doc dir label Dec 30, 2022

erlend-aasland added this to sqlite3 issues Dec 30, 2022

erlend-aasland moved this to TODO: Docs in sqlite3 issues Dec 30, 2022

erlend-aasland self-assigned this Dec 30, 2022

bedevere-bot mentioned this issue Dec 30, 2022

gh-99953: Purge mention of numeric param style from sqlite3 docs #100630

Merged

erlend-aasland added a commit to erlend-aasland/cpython that referenced this issue Dec 30, 2022

pythongh-99953: Purge mention of numeric param style from sqlite3 docs

48ac1bf

The PEP-249 numeric style has never been supported.

moonsikpark mentioned this issue Dec 31, 2022

gh-99951: Warn if there is an OpenSSL major version mismatch #100641

Closed

erlend-aasland added a commit that referenced this issue Jan 1, 2023

gh-99953: Purge mention of numeric param style from sqlite3 docs (#10…

b7a68ab

…0630) The PEP-249 numeric style has never been supported by sqlite3.

This was referenced Jan 1, 2023

[3.11] gh-99953: Purge mention of numeric param style from sqlite3 docs (GH-100630) #100669

Merged

[3.10] gh-99953: Purge mention of numeric param style from sqlite3 docs (GH-100630) #100670

Merged

erlend-aasland closed this as completed Jan 1, 2023

github-project-automation bot moved this from TODO: Docs to Done in sqlite3 issues Jan 1, 2023

erlend-aasland mentioned this issue Jan 1, 2023

Clarify how sqlite3 maps parameters onto placeholders #100668

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sqlite3's support for "numeric" paramstyle does not appear to honor the actual numbers with positional parameters #99953

sqlite3's support for "numeric" paramstyle does not appear to honor the actual numbers with positional parameters #99953

sqlite3's support for "numeric" paramstyle does not appear to honor the actual numbers with positional parameters #99953

sqlite3's support for "numeric" paramstyle does not appear to honor the actual numbers with positional parameters #99953

Comments

Linked PRs