Mysql deterministic function, scope

determinismfunctionsMySQL

The documentation for MySQL isn't explicit on this point, but I'm wondering whether the scope for a function with respect to DETERMINISTIC is database global, or by some other scope?

Consider this definition:

CREATE
FUNCTION test() 
RETURNS VARCHAR(255) DETERMINISTIC
RETURN USER();

This function is determinstic for all calls in the same connection, as user() will always return the same value. However, does DETERMINISTIC mean that "computed" values are shared across connections?

Defensively I would treat this function as NOT DETERMINISTIC but the question came up and I'm curious about the answer.

Best Answer

Formally, since the function USER() is not deterministic according to that term's definition:

A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.

your function that simply returns its result is also not deterministic.

However, the manual further states that

Assessment of the nature of a routine is based on the “honesty” of the creator

What it means is that you define the scope where it is deterministic (or not). Considering that this property is used for query optimization, and that, as you say, within a single session USER() will always return the same result, you can treat it, and the functions that call it, deterministic within the scope of a single query.

This changes, however, if the query cache is enabled, as the cache is shared among sessions and it is possible that sessions other than yours might get results from the cache that would be invalid for them.

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Best Answer

Related Solutions

MySQL Deterministic Procedures

PostgreSQL – Is There a Type-Safe first() Aggregate Function?

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`