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

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

Another data point on this: DB2 disallow volatile left-operand to BETWEEN

db2 => SELECT * FROM atable WHERE smallint(rand()*10) BETWEEN 4 AND 5
SQL0583N  The use of routine or expression "SYSFUN.RAND" is invalid 
because it
is not deterministic or has an external action.  SQLSTATE=42845

I'd like us to still fix this so that there's no multiple evaluation - 
that would actually make BETWEEN more useful than it is today. I'm 
working on a patch to handle both BETWEEN and IN.

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