Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20541

Implement a flag to show if an expression is non-deterministic

Details

    • Task
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    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.

      Attachments

        Issue Links

          is part of

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11588 Support for ONLY_FULL_GROUP_BY functional dependency

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment - - edited

            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?

            serg Sergei Golubchik added a comment - - edited 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?
            serg Sergei Golubchik added a comment -

            igor FYI

            serg Sergei Golubchik added a comment - igor FYI
            f_razzoli Federico Razzoli added a comment -

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

            f_razzoli Federico Razzoli added a comment - WIll you consider deterministic stored functions that are declared as DETERMINISTIC? Currently that flag is ignored in all contexts as far as I know.

            People

              igor Igor Babaev
              shagalla Galina Shalygina (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.