As we saw in Chapter 2, an SQL table expression is “possibly nondeterministic” if it might give different results on different evaluations, even if the database hasn’t changed in the interim. Here’s the standard’s own definition:
A <query expression> or <query specification> is possibly nondeterministic if an implementation might, at two different times where the state of the SQL-data is the same, produce results that differ by more than the order of the rows due to General Rules that specify implementation dependent behavior.
Actually this definition is a trifle odd, inasmuch as tables—which is what <query expressions>s and <query specifications>s are supposed to produce—aren’t supposed to have a row ordering anyway. But let’s overlook this detail; the important point is that “possibly nondeterministic” expressions aren’t allowed in integrity constraints,[170] a state of affairs that could have serious practical implications if true.
The standard’s rules for labeling a given table expression “possibly nondeterministic” are quite complex, and full details are beyond the scope of the present discussion. However, a table expression tx is certainly considered to be “possibly nondeterministic” if any of the following is true:[171]
tx is a union, intersection, or difference, and the operand tables include a column of type character string.
tx is a SELECT expression, the SELECT item commalist in that SELECT expression includes an item (C say) of type character string, and at least one of the following is true:
The SELECT item commalist is preceded by the keyword DISTINCT.
C involves a MAX or MIN invocation.
tx directly includes a GROUP BY clause and C is one of the grouping columns.l
tx is a SELECT expression that directly includes a HAVING clause and the boolean expression in that HAVING clause includes either (a) a reference to a grouping column of type character string or (b) a MAX or MIN invocation in which the argument is of type character string.
tx is a JOIN expression and either or both of the operand expressions is possibly nondeterministic.
Note, however, that these rules are certainly stronger than they need be. For example, suppose NO PAD applies to the collation in effect and that collation is one in which there are no characters that are “equal but distinguishable”; then, e.g., SELECT MAX(C) FROM T, where column C of table T is of the character string type in question, is surely well defined.
[170] Nor in view definitions, if the CHECK option is specified.
[171] What follows represents my own understanding and paraphrasing of the pertinent text from SQL:1992 (except that I’ve taken into account certain minor revisions made in subsequent versions of the standard). More important, I follow SQL:1992 here in talking about character string types only. The rules have since been extended to include as possibly nondeterministic (a) expressions involving data of certain user defined types and (b) expressions involving invocations of certain user defined operators (routines, to use the standard’s term). Further details are beyond the scope of this book.