On 12/3/21 12:05, Hywel Carver wrote: > I've built and tested this, and it seems to function correctly to me. One question I have is whether the added "IS NOT NULL" filters can be omitted when they're unnecessary. Some of the resulting plans included an "IS NOT NULL" filter on a non-nullable column. To be clear, this is still an improvement (to me) without that.
I think, here we could ask more general question: do we want to remove a 'IS NOT NULL' clause from the clause list if the rest of the list implicitly implies it?
My suggestion was not to remove it, but to avoid adding it in the first place. When your optimisation has found a join on a group of columns under a uniqueness constraint, you would do something like this (forgive the pseudo-code)
foreach(column, join_clause) {
if(column.nullable) { // This condition is what I'm suggesting is added
add_null_test(column, IS_NOT_NULL);
}
}
But it may be that that's not possible or practical at this point in the code.