Re: Transforming IN (...) to ORs, volatility

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> We sometimes transform IN-clauses to a list of ORs:
> postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
> QUERY PLAN
> ------------------------------------------------------
> Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
> Filter: ((a = b) OR (a = c))
> (2 rows)

> But what if you replace "a" with a volatile function? It doesn't seem
> legal to do that transformation in that case, but we do it:

This is the fault of transformAExprIn(). But please let's *not* fix
this by adding volatility to the set of heuristics used there. Looking
at this again, it seems to me that most of the problem with this code
is that we're trying to make optimization decisions in the parser.

I think what we ought to do is have the parser emit a full-fledged
InExpr node type (with semantics rather like CaseExpr) and then teach
the planner to optimize that to something else when it seems
safe/prudent to do so. One nontrivial advantage of that is that
rules/views containing IN constructs would start to reverse-parse
in the same fashion, instead of introducing weird substitute
expressions.

regards, tom lane