Skip site navigation (1) Skip section navigation (2)

Transforming IN (...) to ORs, volatility

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Transforming IN (...) to ORs, volatility
Date: 2011-04-01 11:24:53
Message-ID: 4D95B605.2020709@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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:

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))
(2 rows)

I tried to read the SQL spec to see if it has anything to say about 
that, but I couldn't find anything. My common sense says that that 
transformation is not legal.

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

Responses

pgsql-hackers by date

Next:From: Noah MischDate: 2011-04-01 11:51:04
Subject: Re: maximum digits for NUMERIC
Previous:From: Gianni CiolliDate: 2011-04-01 10:44:23
Subject: Re: maximum digits for NUMERIC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group