Column name needs to be quoted in the where query param.

[TWith2Sugars]

This took a while to figure out but essentially I have the following table structure:

CREATE TABLE public.users_electric (
    project_id uuid NOT NULL,
    user_id uuid NOT NULL,
    first_name text,
    last_name text,
    created_on timestamp without time zone NOT NULL,
    modified_on timestamp without time zone NOT NULL,
    deleted_on timestamp without time zone,
    is_active boolean NOT NULL,
    primary key (user_id, project_id)
);

and attempt to curl the shape api with the where param set to project_id = '00000000-0000-0000-0000-000000000000' it responds with unknown reference.

But if set the where param to "project_id" = '00000000-0000-0000-0000-000000000000' it all works fine.

Unsure if this is intended behaviour (in which case. would be worth mentioning in some docs/openapi) or a bug?

[KyleAMathews]

Yeah we should support unquoted column names so I'll make this as a bug to fix. Thanks for reporting!

[msfstef]

I can't seem to reproduce this with the same table and filter - could you please paste the exact curl request made that is problematic?

Examples that worked for me:

curl --get http://localhost:3000/v1/shape/users_electric --data-urlencode "offset=-1" --data-urlencode "where=project_id = '00000000-0000-0000-0000-000000000000'" 

fetch(`http://localhost:3000/v1/shape/users_electric?offset=-1&where=${encodeURIComponent("project_id='00000000-0000-0000-0000-000000000000'")}`)

[TWith2Sugars]

@msfstef

curl -X 'GET' \
  'http://localhost:3000/v1/shape/users_electric?offset=-1&where=%22project_id%20%3D%20%2700000000-0000-0000-0000-000000000000%27%22' \
  -H 'accept: application/json'

response I got:

{"root_table":["At location 0: unknown reference \"project_id = '00000000-0000-0000-0000-000000000000'\""]}

[alco]

@TWith2Sugars In your example, the whole WHERE clause is enclosed in quotes, and the error message indicates that: it cannot find the column named project_id = '00000000-0000-0000-0000-000000000000'.

[TWith2Sugars]

@alco I also had this example that failed as well:

curl -i 'http://localhost:3000/v1/shape/users_electric?offset=-1&where=project_id%3D00000000-0000-0000-0000-000000000000'

response:

{"root_table":["At location 0: unknown reference project_id"]}

[alco]

Please provide a reproduction repo. It's working fine here when I use your schema:

$ curl -i http://localhost:3000/v1/shape/users_electric\?offset=-1&where=project_id%3D%2700000000-0000-0000-0000-000000000000%27
HTTP/1.1 200 OK
transfer-encoding: chunked
date: Wed, 28 Aug 2024 10:04:47 GMT
cache-control: max-age=1, stale-while-revalidate=3
x-request-id: F-_cYEHRPQB-VdsAAAfF
server: ElectricSQL/0.3.1
access-control-allow-origin: *
access-control-expose-headers: *
access-control-allow-methods: GET, POST, OPTIONS
content-type: application/json; charset=utf-8
x-electric-shape-id: 56441451-1724839488316
x-electric-chunk-last-offset: 0_0
x-electric-schema: {"created_on":{"type":"timestamp","not_null":true},"deleted_on":{"type":"timestamp"},"first_name":{"type":"text"},"is_active":{"type":"bool","not_null":true},"last_name":{"type":"text"},"modified_on":{"type":"timestamp","not_null":true},"project_id":{"type":"uuid","not_null":true,"pk_index":1},"user_id":{"type":"uuid","not_null":true,"pk_index":0}}
etag: 56441451-1724839488316:-1:0_0

[{"headers":{"control":"up-to-date"}}]

[localhost] postgres:electric=# insert into users_electric (project_id, user_id, created_on, modified_on, is_active) values ('00000000-0000-0000-0000-000000000000', gen_random_uuid(), now(), now(), false);
INSERT 0 1

$ curl -i http://localhost:3000/v1/shape/users_electric\?offset=-1&where=project_id%3D%2700000000-0000-0000-0000-000000000000%27
HTTP/1.1 200 OK
transfer-encoding: chunked
date: Wed, 28 Aug 2024 10:07:26 GMT
cache-control: max-age=1, stale-while-revalidate=3
x-request-id: F-_chThzPHI236UAAAmF
server: ElectricSQL/0.3.1
access-control-allow-origin: *
access-control-expose-headers: *
access-control-allow-methods: GET, POST, OPTIONS
content-type: application/json; charset=utf-8
x-electric-shape-id: 56441451-1724839488316
x-electric-chunk-last-offset: 321928784_0
x-electric-schema: {"created_on":{"type":"timestamp","not_null":true},"deleted_on":{"type":"timestamp"},"first_name":{"type":"text"},"is_active":{"type":"bool","not_null":true},"last_name":{"type":"text"},"modified_on":{"type":"timestamp","not_null":true},"project_id":{"type":"uuid","not_null":true,"pk_index":1},"user_id":{"type":"uuid","not_null":true,"pk_index":0}}
etag: 56441451-1724839488316:-1:321928784_0

[{"offset":"321928784_0","value":{"created_on":"2024-08-28 10:07:14.013601","deleted_on":null,"first_name":null,"is_active":"f","last_name":null,"modified_on":"2024-08-28 10:07:14.013601","project_id":"00000000-0000-0000-0000-000000000000","user_id":"22545c6f-f7df-4a53-80e8-249ce0936fa9"},"key":"\"public\".\"users_electric\"/\"22545c6f-f7df-4a53-80e8-249ce0936fa9\"/\"00000000-0000-0000-0000-000000000000\"","headers":{"relation":["public","users_electric"],"operation":"insert","txid":1287}},{"headers":{"control":"up-to-date"}}]

[alco]

Hey @TWith2Sugars. Any updates?

[TWith2Sugars]

@alco hey sorry for the delay, I've not had time to recreate this issue. I'll close it and if I manage to bump in to again I'll reopen with a repo.