Re: Bug in 8.0.0rc3 query planner: constant column in view changes execution plan

=?ISO-8859-15?Q?J=F6rg_Hoppe?= <hoppe(at)geoinformationsdienst(dot)de> writes:
> --- SELECTing expressions, which do not access any table data,
> --- should not influence the execution plan.

Unfortunately, that assertion is dead wrong.

> SELECT R.a_ID, R.b_ID, v.constcol
> FROM t_b R LEFT JOIN v_test_bad V on R.a_id = V.a_id
> WHERE r.b_id between 900000 and 900999
> AND v.a_id = v.a_id
> ;

The reason this behaves differently from the others is that a
constant-one column from v_test_bad won't automatically go to NULL
when the underlying table row is expanded to NULLs by the left join.
That prevents flattening of the view. See has_nullable_targetlist()
in prepjointree.c.

has_nullable_targetlist could be smarter than it is, but no improvement
in its intelligence would change the behavior in the case you give.
The only way this could be made to work is a fairly fundamental change
in the handling of variables in an execution tree, such that expressions
emitted by a view get evaluated below the point of the outer join rather
than above it. I've looked at this a bit and concluded that it probably
would not be a win overall ... indeed, it arguably might cause runtime
failures that do not occur now (eg, division by zero in a row that would
never have been evaluated otherwise).

regards, tom lane