On 11.04.2011 19:33, Heikki Linnakangas wrote:
On 11.04.2011 19:06, Kevin Grittner wrote:
Heikki Linnakangas<heikki.linnakan...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
