On Mon, 27 May 2019 at 20:43, Antonin Houska <a...@cybertec.at> wrote: > I've spent some time looking into this.
Thank you for having a look at this. > One problem I see is that SubLink can be in the JOIN/ON clause and thus it's > not necessarily at the top of the join tree. Consider this example: > > CREATE TABLE a(i int); > CREATE TABLE b(j int); > CREATE TABLE c(k int NOT NULL); > CREATE TABLE d(l int); > > SELECT * > FROM > a > JOIN b ON b.j NOT IN > ( SELECT > c.k > FROM > c) > JOIN d ON b.j = d.l; hmm yeah. Since the proofs that are being used in expressions_are_not_nullable assume the join has already taken place, then we'll either need to not use the join conditions are proofs in that case, or just disable the optimisation instead. I think it's fine to just disable the optimisation since it seem rather unlikely that someone would write a join condition like that. Plus it seems quite a bit more complex to validate that the optimisation would even be correct if NULLs were not possible. I've attached a patch which restricts the pullups to FromExpr quals. Anything below a JoinExpr disables the optimisation now. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
not_in_anti_join_v2.patch
Description: Binary data
