[MDEV-20541] Implement a flag to show if an expression is non-deterministic Created: 2019-09-09  Updated: 2023-08-24

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: Task Priority: Major
Reporter: Galina Shalygina (Inactive) Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-11588 Support for ONLY_FULL_GROUP_BY functi... Stalled

 Description   

Deterministic expression is an expression that doesn't use non-deterministic functions.
Non-deterministic function is a function that for equal input sets returns the same result set.

E.g.: LENGTH() function of VARCHAR fields.
'a' = 'a '
But
LENGTH('a') = 1 != 2 = LENGTH('a ')

So, for equal input variables LENGTH() function returns different results.
Therefore, LENGTH() of VARCHARs is non-deterministic.

This task is a subtask for MDEV-11588.
Non-deterministic expressions of GROUP BY fields are not functionally dependent on GROUP BY fields. Therefore, can't be used in SELECT list, HAVING and ORDER BY clauses.

 Comments   
Comment by Sergei Golubchik [ 2019-09-10 ]

I don't think that's what "non-deterministic" usually means.
According to the SQL standard (2016, part 2, section 4.22 Determinism):

In general, an operation is deterministic if that operation assuredly computes identical results when repeated with identical input values.

"Identical" is stronger than "equal", in particular "a" and "a " are not identical, even if they compare as equal.

Being deterministic is an important property, used in generated columns (only deterministic expressions can be used, if the result is stored persistently) and in the optimizer (an index can be used only if the lookup value is deterministic).

Could you please find some other term for the property you're interested in?

Comment by Sergei Golubchik [ 2019-09-11 ]

igor FYI

Comment by Federico Razzoli [ 2023-08-24 ]

WIll you consider deterministic stored functions that are declared as DETERMINISTIC? Currently that flag is ignored in all contexts as far as I know.

Generated at Thu Feb 08 09:00:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.