SQL and Relational Theory, 2nd Edition

“POSSIBLY NONDETERMINISTIC” EXPRESSIONS

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 ...