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

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

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

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

pneb · 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.

pneb · 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 added the type-bug An unexpected behavior, bug, or error label Dec 2, 2022

AlexWaygood added the expert-sqlite3 label Dec 2, 2022

erlend-aasland added the docs Documentation in the Doc dir label 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

Open

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.

Nov	DEC	Jan
	31
2021	2022	2023

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

zzzeek commented Dec 2, 2022 •

edited by bedevere-bot

zzzeek commented Dec 2, 2022

pneb commented Dec 3, 2022

zzzeek commented Dec 3, 2022 •

edited

pneb commented Dec 3, 2022

erlend-aasland commented Dec 30, 2022

erlend-aasland commented Dec 30, 2022 •

edited

erlend-aasland commented Dec 30, 2022

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

zzzeek commented Dec 2, 2022 • edited by bedevere-bot

Linked PRs

zzzeek commented Dec 2, 2022

pneb commented Dec 3, 2022

zzzeek commented Dec 3, 2022 • edited

pneb commented Dec 3, 2022

erlend-aasland commented Dec 30, 2022

erlend-aasland commented Dec 30, 2022 • edited

erlend-aasland commented Dec 30, 2022

zzzeek commented Dec 2, 2022 •

edited by bedevere-bot

zzzeek commented Dec 3, 2022 •

edited

erlend-aasland commented Dec 30, 2022 •

edited