$ arguments in non-ascending order always evaluate to FALSE when used after the WHERE statement in queries
MooshiMochi opened this issue · 3 comments
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 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
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
Interesting, I had no idea about that. I assumed all SQL Python libraries supported numeric placeholders. I guess not.
Thanks for letting me know.