[DB-SIG] intent of "numeric" paramstyle, wrt actual numbers not numerically ordered?

[Mike Bayer]

Does numeric paramstyle intend to support statements where the numbers are not numerically ordered within the statement?   For example:

select count(*) from my_table where a=:3 and b=:4 and c=:1 and d=:5 and e=:2


if so, what is the expected form of the positional tuple?    Consider this data:

insert into my_table(a, b, c, d, e) values ('a', 'b', 'c', 'd', 'e')

to match this row, if we assume the positional tuple's contents should correspond to the numbers in the statement assuming 1-based ordering, we would expect this statement to match the row:

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")
)

OTOH, if we did not expect the numbers to be significant, and they are basically more interesting looking question marks where we dont care about the number, we'd expect this to match:

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

Apparently Python sqlite3 module, which has been in production for decades at this point in billions of computers, seems to honor the second form, and an issue search has not shown anyone ever noticing.  I've raised an issue at https://github.com/python/cpython/issues/99953 

It would appear this might speak to the relative un-popularity of "numeric" paramstyle, though that doesn't change my own process here, which is to try to support it.




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.python.org/pipermail/db-sig/attachments/20221202/9b82a291/attachment.html>