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.

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

Maybe_ generate wrong (non intuitive) sql query #524

Open
sashasashasasha151 opened this issue Sep 28, 2020 · 2 comments
Open

Maybe_ generate wrong (non intuitive) sql query #524

sashasashasasha151 opened this issue Sep 28, 2020 · 2 comments
Labels

Comments

@sashasashasasha151
Copy link

sashasashasasha151 commented Sep 28, 2020

I have left and right table, where right table contains nullable fields
I join them with leftJoin_ and try to create filter_ which has such condition:

maybe_ nothing_ nullableFieldFromRightTable rt /=. just_ (val_ 1)

So, if rt is null just return nothing and if rt is not null return nullableFieldFromRightTable which will return nothing or just value
I am using it as beam doen't support to check nested maybes
Then I just check if it is different from just 1
This code compiles but I get invalid result, so, here is why:

CASE
  WHEN ((((("t1"."someField1") IS NOT NULL) AND (("t1"."someField2") IS NOT NULL)) AND
    (("t1"."nullableFieldFromRightTable") IS NOT NULL)) AND <all_other_fields_from_table> IS NOT NULL) 
    THEN "t1"."nullableFieldFromRightTable"
  ELSE null END) IS DISTINCT FROM 1)

Beam generates strange query which doesn't the meaning of the beam query

@kmicklas
Copy link
Member

Can you explain how this differs from what you expected? Is it that maybe_ takes the "just" case only if all columns from rt are non-null? This does seem to cause confusion, for example see #442.

@sashasashasasha151
Copy link
Author

Is it that maybe_ takes the "just" case only if all columns from rt are non-null?

Yes, I already checked that the root of the problem is in SQL but not in beam. But it will be useful to see it in documentation. I just catched this issue easily because it quickly ruined my project and I knew where to search. But it may cause really big problems for somebody.
Or maybe it is possible to create a new method like strictMaybe of something like this that will return True if at least one element in joined table is not null. Yes it will not work for tables with all nullable columns and I am not sure that it is possible to solve it somehow but it is still better than nothing.

@kmicklas kmicklas added the docs label Oct 21, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants