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

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 anyone object to making BETWEEN and IN more strict about the data types? At the moment, you can do this:

postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4;
?column?
----------
t
(1 row)

I'm thinking that it should throw an error. Same with IN, if the values in the IN-list can't be coerced to a common type. That will probably simplify the code a lot, and is what the SQL standard assumes anyway AFAICS.

--
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