FK equals check

[hwai...@netflix.com]

Howdy!

I recently ran into a nasty bug where I had a query that was checking if two fields were equal, though the two did not have a FK constraint. I'm curious if there is a feature of jOOQ (or better yet SQL) that will fail equality checks on fields without FK constraints? Of course, not all equality checks have FK constraints, but it would be nice to specify in the query that the two fields should have a FK constraint AND be equal. 

Thanks in advance!

Hayden

[Lukas Eder]

Hi Hayden,

Thank you very much for your message. I'm assuming you mean that you compared two columns in a JOIN .. ON clause that were not meant to be compared, because they were unrelated. There are a number of tools in jOOQ already, that would prevent this for some cases. For example:

- You could use onKey(), which works in simple join graphs, where it automatically finds the right columns that need to be used when joining between two tables. It breaks as soon as the join graph yields ambiguities, i.e. as soon as there are several possible ways to join two tables (note that by "table", I mean join sub-graph)

- You could use implicit joins for to-one joins, which have been introduced in jOOQ 3.11: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/implicit-join/

We could definitely implement an introspection that would allow for logging a warning / throwing an exception, if a join is not an equi join. Of course, this introspection would not be turned on by default, because a join is not necessarily an equi join. It is just an ordinary relational operator, based on the cross product operation. Also, a lot of people do not use foreign key constraints at all, or at least not everywhere in their schema. But it's a useful introspection nonetheless, just like the one where we check if update / delete statements have a where clause.

I have created a feature request for this:

https://github.com/jOOQ/jOOQ/issues/8849 

You could implement your own validation using a VisitListener (which you could turn off in production, for performance reasons). It is not trivial, but definitely possible. 

Thanks,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/c6f00f29-3db9-4b80-97a6-23e3959eb4e1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Hayden Waisanen]

Thanks so much for the thorough response, Lukas! All seem like great ideas. I think that the `onKey()` is exactly what we need. It'll also simplify our codebase. 

If introspection does eventually come available, we'd also consider enabling that too. 

Thanks again!

Hayden

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/uyG3LlyppaQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6Axoh0SxFUu6MOGLXMJhCu8SDzhqVLCh%2BdBWMXddwE3A%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Hayden