Re: DETERMINISTIC as synonym for IMMUTABLE

Troels Arvin <troels@arvin.dk> writes:
> On Sun, 17 Oct 2004 18:08:00 -0400, Tom Lane wrote:
>> These do NOT mean the same thing.

> I'm having a hard time seeing the difference between DETERMINISTIC and
> IMMUTABLE.

Well, the spec is somewhat self-contradictory on the point, but I think
their intention is to model it after their notion of a deterministic
query:
        A <query expression> or <query specification> is possibly non-        deterministic if an SQL-implementation
might,at two different        times where the state of the SQL-data is the same, produce results        that differ by
morethan the order of the rows due to General Rules        that specify implementation-dependent behavior.   [SQL99
4.17]

Notice that it is okay for a deterministic query to produce different
results when the content of the database changes; therefore this is not
IMMUTABLE in our terms.  It is however stronger than our STABLE
condition (for example, "now()" is STABLE but is not deterministic per
the above definition).  It appears to me that they are thinking of
functions like
SELECT value FROM table WHERE pkey = $1

which is deterministic per their definition and also according to (what
I think is) the common meaning of "deterministic".  We could label this
function as STABLE, but not IMMUTABLE; however we have no category that
captures the notion that "it can't change as long as the database
content doesn't change".

What it actually says about deterministic functions in 4.23 is:
        An SQL-invoked routine is either deterministic or possibly non-        deterministic. An SQL-invoked function
thatis deterministic always        returns the same return value for a given list of SQL argument        values. An
SQL-invokedprocedure that is deterministic always        returns the same values in its output and inout SQL parameters
      for a given list of SQL argument values. An SQL-invoked routine        is possibly non-deterministic if, during
invocationof that SQL-        invoked routine, an SQL-implementation might, at two different        times when the
stateof the SQL-data is the same, produce unequal        results due to General Rules that specify
implementation-dependent       behavior.
 

This is clearly bogus as written since it claims that there are only two
possibilities when there are more than two.  Any ordinary function that
selects from the database will satisfy neither their "deterministic" nor
their "possibly non-deterministic" definitions.

I think that they meant to define SQL functions as nondeterministic if
they act like or contain nondeterministic queries; for instance 13.5
says
        3) An <SQL procedure statement> S is possibly non-deterministic if           and only if at least one of the
followingis satisfied:
 
           a) S is a <select statement: single row> that is possibly non-             deterministic.
           b) S contains a <routine invocation> whose subject routine is an             SQL-invoked routine that
possiblymodifies SQL-data.
 
           c) S generally contains a <query specification> or a <query             expression> that is possibly
non-deterministic.
           d) S generally contains a <datetime value function>, CURRENT_             USER, CURRENT_ROLE, SESSION_USER,
orSYSTEM_USER.
 

Anybody know whether the SQL2003 text clarifies the intent at all?

In any case, whether or not you think DETERMINISTIC means IMMUTABLE,
I don't think it's very helpful to identify NOT DETERMINISTIC with
VOLATILE.  As a counterexample, now() is NOT DETERMINISTIC, but it
isn't VOLATILE.
        regards, tom lane