Query builder queries are not immutable

[jscheel]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• 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

[silentworks]

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.

[jscheel]

@silentworks I'll shoot you a full, reproducible case when I get a moment. Maybe a version mismatch or something.

[silentworks]

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

[silentworks]

@jscheel did you get around looking at the version I created? or creating a reproducible example repo of the behavior you are getting?

[silentworks]

Closing this out as no reply from OP over a month now. Also unable to reproduce the issue.

[jscheel]

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 <#1208>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABTUFRSF3565Y6ULE5NL7L2NCVAPAVCNFSM6AAAAABT5JYC32VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMMRRGI2TMOJYHE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[DDoerner]

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.

[silentworks]

@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.

[jscheel]

@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 😅

[silentworks]

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.