Stripe Wrapper: selecting attrs field and using a where clause filters out rows

[grschafer]

Bug report

Describe the bug

If a query to a stripe foreign table selects the attrs field and uses a WHERE clause, then no rows will be returned, unless all columns used in the WHERE clause are also selected.

To Reproduce

1. Start a local environment with the supabase CLI (supabase init; supabase db start)
2. Connect to the database with psql (docker exec -it supabase_db_foo psql -U postgres)
3. Create a stripe account and create customers and charges in test mode.
4. Follow instructions at https://supabase.github.io/wrappers/stripe/#wrapper to create the wrapper extension, add stripe test keys (I used the "Auth Insecure" method because vault isn't available in the CLI currently), create the foreign server, and create the foreign tables.
5. Run SELECT queries that select attrs with and without including columns used in a where clause

Some example queries and the results (with added explanatory headers):

################################################################################
# Basic demonstration that selecting attrs without the column in the WHERE
# clause filters out all rows
################################################################################

postgres=# select id from stripe.customers where id = 'cus_NCPzasyajP5YPj';
         id
--------------------
 cus_NCPzasyajP5YPj
(1 row)

postgres=# select id, attrs from stripe.customers where id = 'cus_NCPzasyajP5YPj';
         id         |    attrs
--------------------+-----------------------------------------------------------
 cus_NCPzasyajP5YPj | {"id": "cus_NCPzasyajP5YPj", "name": "abc", "email": "abc@def.com", ... }
(1 row)

postgres=# select attrs from stripe.customers where id = 'cus_NCPzasyajP5YPj';
 attrs
-------
(0 rows)



################################################################################
# Explain analyze results for the above
################################################################################

postgres=# explain analyze select id from stripe.customers where id = 'cus_NCPzasyajP5YPj';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on customers  (cost=0.00..1.00 rows=1 width=0) (actual time=0.008..0.009 rows=1 loops=1)
   Filter: (id = 'cus_NCPzasyajP5YPj'::text)
   Wrappers: quals = [Qual { field: "id", operator: "=", value: Cell(String("cus_NCPzasyajP5YPj")), use_or: false }]
   Wrappers: tgts = ["id"]
   Wrappers: sorts = []
   Wrappers: limit = None
 Planning Time: 8.022 ms
 Execution Time: 352.394 ms
(8 rows)

postgres=# explain analyze select attrs from stripe.customers where id = 'cus_NCPzasyajP5YPj';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on customers  (cost=0.00..1.00 rows=1 width=0) (actual time=0.060..0.061 rows=0 loops=1)
   Filter: (id = 'cus_NCPzasyajP5YPj'::text)
   Rows Removed by Filter: 1
   Wrappers: quals = [Qual { field: "id", operator: "=", value: Cell(String("cus_NCPzasyajP5YPj")), use_or: false }]
   Wrappers: tgts = ["attrs", "id"]
   Wrappers: sorts = []
   Wrappers: limit = None
 Planning Time: 8.165 ms
 Execution Time: 361.643 ms
(9 rows)



################################################################################
# If there are 2 columns in the WHERE clause, they both need to be selected in
# order for a row to be returned
################################################################################

postgres=# select id, name, attrs from stripe.customers where id = 'cus_NCPzasyajP5YPj' and name = 'abc';
         id         | name |    attrs
--------------------+------+----------------------------------------------------
 cus_NCPzasyajP5YPj | abc  | {"id": "cus_NCPzasyajP5YPj", "name": "abc", "email": "abc@def.com", ... }
(1 row)

postgres=# select name, attrs from stripe.customers where id = 'cus_NCPzasyajP5YPj' and name = 'abc';
 name | attrs
------+-------
(0 rows)

postgres=# select id, attrs from stripe.customers where id = 'cus_NCPzasyajP5YPj' and name = 'abc';
 id | attrs
----+-------
(0 rows)



################################################################################
# The stripe charges table behaves the same
################################################################################

postgres=# select id, attrs from stripe.charges where id = 'ch_3MZiaiI3rSV8HFQa0lzuJV2I';
             id              |    attrs
-----------------------------+--------------------------------------------------
 ch_3MZiaiI3rSV8HFQa0lzuJV2I | {"id": "ch_3MZiaiI3rSV8HFQa0lzuJV2I", "paid": true, "order": null, "amount": 4900, ... }
(1 row)

postgres=# select attrs from stripe.charges where id = 'ch_3MZiaiI3rSV8HFQa0lzuJV2I';
 attrs
-------
(0 rows)

Expected behavior

I'd expect that select attrs from stripe.customers where id = 'foo' would return a row if there existed a row with id = 'foo', rather than needing to do select id, attrs from stripe.customers where id = 'foo' in order to return a row.

System information

• OS: Ubuntu 22.04.1 LTS
• Supabase CLI version: 1.36.5
• Supabase postgres docker image tag: 15.1.0.33
• Postgres server version: PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
• Psql version: 15.1
• Docker version: 23.0.1
• Wrappers extension version: 0.1.7