If you place query arguments '$1', '$2' in the wrong order after the "WHERE" statement, it returns no results as WHERE will be false.
Example query:
SELECT series_id FROM user_subs WHERE guild_id = $1 AND id = $2 # This will work
SELECT series_id FROM user_subs WHERE id = $2 AND guild_id = $1 # This will NOT work
Additionally, there are no errors whatsoever.
I have tested this using DB Browser for SQLite, and it worked regardless of the position of the $ arguments.
This is the code I used to reproduce the issue.
import asyncio
import aiosqlite
import os
async def main():
db_filename = r"C:\Users\rchir\OneDrive\Desktop\GitProjects\ManhwaUpdatesBot\database.db"
if not os.path.exists(db_filename):
print("Database doesn't exist")
return
async with aiosqlite.connect(db_filename) as db:
working_query = "SELECT series_id FROM user_subs WHERE guild_id = $1 AND id = $2"
broken_query = "SELECT series_id FROM user_subs WHERE id = $2 AND guild_id = $1"
params = (1141105994018078882, 383287544336613385)
queries = [working_query, broken_query]
for query in queries:
print("\nTesting query:", query)
async with db.execute(query, params) as cursor:
result = await cursor.fetchall()
print("Result:", result)
asyncio.run(main())
Details
OS: Windows 11
Python version: 3.11.4
aiosqlite version: 0.19.0
Can you repro on 'main' branch? Yes
Can you repro in a clean virtualenv? Yes
This is the results of me trying to reproduce the error. As you can see, the bug is persistent even in a clean venv
Where did you see that $x placeholders were supported? The documentation specifically states numerical placeholders aren't and the $ syntax is not a thing in python.
Note PEP 249 numeric placeholders are not supported. If used, they will be interpreted as named placeholders.
Description
If you place query arguments '$1', '$2' in the wrong order after the "WHERE" statement, it returns no results as WHERE will be false. Example query:
SELECT series_id FROM user_subs WHERE guild_id = $1 AND id = $2
# This will workSELECT series_id FROM user_subs WHERE id = $2 AND guild_id = $1
# This will NOT work Additionally, there are no errors whatsoever. I have tested this using DB Browser for SQLite, and it worked regardless of the position of the $ arguments.This is the code I used to reproduce the issue.
Details
This is the results of me trying to reproduce the error. As you can see, the bug is persistent even in a clean venv
For those interested in the moment of me finding out about this bug, you can witness it yourself by reading this help thread in discord.py discord server: https://discord.com/channels/336642139381301249/1142201439687553136