supabase-community
Query builder queries are not immutable
Bug report
- [x] I confirm this is a bug with Supabase, not with my own application.
- [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
Query builder objects are not immutable, which means that you cannot share a query builder and add separate conditions on each execution.
query = (
sb.table("items")
.select("*")
.eq("account_id", "abc")
)
query.in_("id", ["1", "2", "3"]).execute()
query.in_("id", ["4", "5", "6"]).execute()
This will execute one query with ids = 1, 2, 3 and then the second query will be 1, 2, 3, 4, 5, 6.
Expected behavior
One query with ids = 1, 2, 3 and another with 4, 5, 6.
Discord Discussion
https://discord.com/channels/839993398554656828/1319331194902413384
I've tested this and I'm unable to reproduce it. I get an issue of when one query runs the other doesn't run at all and not the query appending to each other.
@silentworks I'll shoot you a full, reproducible case when I get a moment. Maybe a version mismatch or something.
I created a reproducible with the results I'm getting. You can modify it to see if you can get the results you got and create a PR to the repo https://github.com/silentworks/postgres_py_issue_550
@jscheel did you get around looking at the version I created? or creating a reproducible example repo of the behavior you are getting?
Closing this out as no reply from OP over a month now. Also unable to reproduce the issue.
Sorry, I’ve been incredibly slammed lately. I’ll get to a repro case as soon as I can. On Wed, Jan 29, 2025 at 4:33 AM Andrew Smith @.***> wrote:
Closing this out as no reply from OP over a month now. Also unable to reproduce the issue.
— Reply to this email directly, view it on GitHub https://github.com/supabase/supabase-py/issues/1208, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABTUFRSF3565Y6ULE5NL7L2NCVAPAVCNFSM6AAAAABT5JYC32VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMMRRGI2TMOJYHE . You are receiving this because you were mentioned.Message ID: @.***>
The issue is very noticable when trying to do pagination using a single base query.
We initially built pagination like this:
query = client.table(table_name).select("*")
items = []
for offset in range(0, MAX_ROWS, PAGE_SIZE):
next_page = query.range(offset, offset + PAGE_SIZE).execute()
if next_page.data:
items.extend(next_page.data)
...
But we noticed this actually attempts to repeatedly add the offset and limit parameters to the query object, producing HTTP calls such as ?select=*&offset=0&offset=250&offset=500&offset=750&offset=1000&offset=1250&offset=1500&offset=1750&offset=2000&offset=2250&limit=251&limit=251&limit=251&limit=251&limit=251&limit=251&limit=251&limit=251&limit=251&limit=251.
@DDoerner I've left a repo above showing that I cannot reproduce what the OP posted. Can you clone it and update it to show your issue and then I'll have a look.
@silentworks The bug still appears to exist. I've created a reproducible repo for you here: https://github.com/jscheel/supabase-query-builder-bug
I enabled logging so that you can see the two requests, with the second one getting both conditions. Sorry I didn't fork your repo, had this example before I saw your repo 😅
The example your provided doesn't have any data to go with it. This is why I created the example repo as it has data and everything. I'm going to update your example to use data similar to what you originally posted.
Ok tested this and can now replicate the issue. I will reopen this and start looking into fixing this now.
@silentworks yeah, sorry, like I said, I didn't realize. Was just trying to bang something out quickly (deep in some other work atm), but any table with a id::uuid column should work. Lemme know if I can give you any more details!
I see what the issue is but fixing this is going to create a breaking change which I don't think we will want to do. I tested out some other options which would work for the chaining from the .table as .select being chained means that all filters are appended.
query = sb.table("items")
query.select("*").eq("account_id", "abc").in_("id", ["1", "2", "3"]).execute()
query.select("*").eq("account_id", "abc").in_("id", ["4", "5", "6"]).execute()
or if you use the copy module
from copy import copy
query = (
sb.table("items")
.select("*")
.eq("account_id", "abc")
)
copy(query).in_("id", ["1", "2", "3"]).execute()
copy(query).in_("id", ["4", "5", "6"]).execute()
If you are concerned about backwards compatibility, then probably best just to show a warning if execute is called a second time but the resolved query does not match the first request. If select is what actually resolves the query, it's possible that could be tacked on the end if someone needs to create a new query from the chain (not sure if that's actually true, not at my computer to test). Otherwise, this probably just a warning and documentation solution. I believe the documented options would be:
- Write new query
- Use a query factory (just a cleaner number 1)
- Use copy
correct?
Yes those are the options. I am going to leave this issue open as you have made good suggestions here. I will close this when we have decided on which step to take and update you here.
@silentworks just thought of one non-bc-breaking approach that could also work. The library could introduce some kind of checkpoint chaining function. This would act as a marker to reset the query chain to after an execute. It would look something like this:
query = (
sb.table("items")
.select("*")
.eq("account_id", "abc")
.checkpoint()
)
query.in_("id", ["1", "2", "3"]).execute()
query.in_("id", ["4", "5", "6"]).execute()
"checkpoint" is a terrible name, and there's some edge cases for sure. Not sure if it's worth pursuing at all, but figured I'd throw out the idea all the same 😅
@jscheel I don't think that would be an option. This library would be further deviating away from the JS library which is something we try to keep to a minimum. This would also mean a change in the API in order to introduce something like this and I'm not just talking the external API, the internal API would have to break in order to make this change.
I do think there's a way to fix this without too much trouble. It would be a non backwards compatible change in the sense that the queries would no longer be mutable, but at the same time I hardly believe anyone is relying on them being mutable. I'll investigate later and see what I can find.
@o-santi that change would be welcome as the queries shouldn't have been mutable to begin with, so making them immutable would be fixing a long standing bug rather than breaking the library as such.