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

On 11.04.2011 19:06, Kevin Grittner wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> On 05.04.2011 18:42, Heikki Linnakangas wrote:
>>> On 05.04.2011 13:19, Marti Raudsepp wrote:
>>>> On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
>>>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>>>> 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))
>>>>>
>>>>> 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:
>>>>>
>>>>> postgres=# explain SELECT * FROM foo WHERE
>>>>> (random()*2)::integer IN (b, c);
>>>>> QUERY PLAN
>>>>>
>>>>> Seq Scan on foo (cost=0.00..68.20 rows=19 width=12)
>>>>> Filter: ((((random() * 2::double precision))::integer = b) OR
>>>>> (((random()
>>>>> * 2::double precision))::integer = c))
>>>>
>>>> Is there a similar problem with the BETWEEN clause
>>>> transformation into AND expressions?
>>>>
>>>> marti=> explain verbose select random() between 0.25 and 0.75;
>>>> Result (cost=0.00..0.02 rows=1 width=0)
>>>> Output: ((random()>= 0.25::double precision) AND (random()<=
>>>> 0.75::double precision))
>>>
>>> Yes, good point.
>>
>> Hmm, the SQL specification explicitly says that
>>
>> X BETWEEN Y AND Z
>>
>> is equal to
>>
>> X>= Y AND X<= Z
>>
>> It doesn't say anything about side-effects of X. Seems like an
>> oversight in the specification. I would not expect X to be
>> evaluated twice, and I think we should change BETWEEN to not do
>> that.
>
> Does the SQL spec explicitly say anything about how many times X
> should be evaluated if you were to code it as?:
>
> X>= Y AND X<= Z

Not explicitly. However, it does say that:

"
NOTE 258 — Since <between predicate> is an ordering operation, the
Conformance Rules of Subclause 9.12, “Ordering
operations”, also apply.
"

If I'm reading those ordering operation conformance rules correctly, it
only allows the operand to be a simple column or an expression that's
specified in the ORDER BY or similar, not an arbitrary expression. Which
seems quite restrictive, but it would dodge the whole issue..

The spec also has that:

“X BETWEEN SYMMETRIC Y AND Z” is equivalent to “((X BETWEEN ASYMMETRIC Y
AND Z)
OR (X BETWEEN ASYMMETRIC Z AND Y))”.

So if you take that into account too, X is evaluated four times. The SQL
standard can be funny sometimes, but I can't believe that they intended
that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com