Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Matching on null fails to return anything #917

Closed
arc4randall opened this issue May 6, 2024 · 7 comments · Fixed by #919 or #920
Closed

Matching on null fails to return anything #917

arc4randall opened this issue May 6, 2024 · 7 comments · Fixed by #919 or #920
Labels
bug Something isn't working postgrest This issue or pull request is related to postgrest

Comments

@arc4randall
Copy link

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);

  1. 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.
@arc4randall arc4randall added the bug Something isn't working label May 6, 2024
@Vinzent03
Copy link
Collaborator

You should use the isFilter for that case.

@arc4randall
Copy link
Author

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 dshukertjr added the postgrest This issue or pull request is related to postgrest label May 7, 2024
@dshukertjr
Copy link
Member

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

@dshukertjr dshukertjr mentioned this issue May 7, 2024
Merged
@arc4randall
Copy link
Author

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
Copy link
Member

dshukertjr commented May 8, 2024 •
edited

@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
Copy link
Author

arc4randall commented May 8, 2024 •
edited

@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
Copy link
Member

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

@dshukertjr dshukertjr mentioned this issue May 8, 2024
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working postgrest This issue or pull request is related to postgrest
Projects
None yet
Milestone
No milestone
3 participants
@arc4randall @dshukertjr @Vinzent03