# Re: [HACKERS] Transforming IN (...) to ORs, volatility

```On 11.04.2011 19:06, Kevin Grittner wrote:
```
```Heikki Linnakangas<heikki.linnakan...@enterprisedb.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.linnakan...@enterprisedb.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
```