Matching on null fails to return anything

[arc4randall]

Describe the bug When performing a select, I would like to include the equivalent of "where columnName is null". There are two options for where clause in Supabase flutter:

1. eq: This doesn't accept optional or null values. So it's impossible to do

Supabase.instance.client.from('my_table').select('column_one, column_two').eq('column_three, null);

2. match: This accepts optional and null values but it doesn't actually work.

Supabase.instance.client.from('my_table').select('column_one, column_two').eq('column_three, null);

Returns 0 values even though running the equivalent query in Supabase SQL Editor returns results. Select eq with a value for column_three also returns all of the values. Only null returns no values.

To Reproduce Steps to reproduce the behavior:

1. Setup a table with a column with nullable values
2. Setup a Flutter app
3. Attempt to select from the table
4. See 0 results

Expected behavior I should be able to select where null. The workaround now is that I have to fetch all of the values and filter for null on the client.

Version (please complete the following information): On Linux/macOS Please run dart pub deps | grep -E "supabase|gotrue|postgrest|storage_client|realtime_client|functions_client" in your project directory and paste the output here.

── supabase_flutter 2.5.2 │ ├── supabase 2.1.2 │ │ ├── functions_client 2.1.0 │ │ ├── gotrue 2.6.1 │ │ ├── postgrest 2.1.1 │ │ ├── realtime_client 2.0.4 │ │ ├── storage_client 2.0.1

Additional context Add any other context about the problem here.

[Vinzent03]

You should use the isFilter for that case.

[arc4randall]

You should use the isFilter for that case.

Thanks, this worked, but I don't think we should close this ticket because match accepts null values but they don't actually work.

[dshukertjr]

Yeah, it might make sense to add an assersion for this.

[arc4randall]

Sorry this actually is broken again. isFilter was working but now it is returning this error

code:
"PGRST100"
details:
"unexpected "P" expecting null or trilean value (unknown, true, false)"
hashCode:
738743739
hint:
null
message:
""failed to parse filter (is.Payments)" (line 1, column 4)"

In my case I am passing in a nullable string. The string contains the word "Payments" so that's why the error says that. There is now a bug with match as well as isFilter. I now have to resort to fetching all of the objects and doing a local filter.

[dshukertjr]

@arc4randall

There is now a bug with match as well as isFilter.

Neither one of them is a bug, but how SQL works, and Supabase and PostgREST are just adhering to how Postgres works. If anything, it's just a lack of assertion and documentation for people who are less familiar with how SQL works.

You can learn more about how the is filter works on the Postgres docs here: https://www.postgresql.org/docs/current/functions-comparison.html

In your case, you have to conditionally apply either the eq (or match) filter and the isFilter depending on whether your comparison value is null or not.

[arc4randall]

@arc4randall

There is now a bug with match as well as isFilter.

Neither one of them is a bug, but how SQL works, and Supabase and PostgREST are just adhering to how Postgres works. If anything, it's just a lack of assertion and documentation for people who are less familiar with how SQL works.

You can learn more about how the is filter works on the Postgres docs here: https://www.postgresql.org/docs/current/functions-comparison.html

In your case, you have to conditionally apply either the eq (or match) filter and the isFilter depending on whether your comparison value is null or not.

Right so I understand that SQL you would say column_name is null and column name = "value". This is about improving the API design. The API does not need to be a literal translation of SQL. A good API design will make it easier and less error prone to interact with SQL. So there are a few bugs here that would improve the API design.

1. Match should not be able to accept null since it doesn't work with null.
2. isFilter should only accept an optional bool. Currently isFilter header looks like this: PostgrestFilterBuilder<T> isFilter(String column, Object? value) { So this API design would be cleaner if it was just PostgrestFilterBuilder<T> isFilter(String column, bool? value) {

[dshukertjr]

The match() situation has already been addressed in a PR. The isFilter should've only accepted a nullable boolean as you say.