Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6767

The execution of a stored function in a WHERE clause was skipped.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.16-7, 8.0.17-8, 8.0.18-9
    • Fix Version/s: 8.0.19-10
    • Component/s: None
    • Labels:

      Description

      This is a mainline bug that has been fixed in 8.0.19. I'm not sure whether Percona would consider this important enough to fix as a patch to their 8.0.18, but wanted to raise it in case it is. 

      Here is the bug report: https://bugs.mysql.com/bug.php?id=98160
      And the release note:

      When optimizer extracts conditions on constant tables for early evaluation, it does not include WHERE conditions that are expensive to evaluate, including conditions involving stored functions. When the extracted condition evaluated to true because it involved only const tables, the entire WHERE condition was incorrectly removed. Now in such cases, a check for expensive conditions is performed prior to any removal of the WHERE condition. (Bug #30520714)

      Since this bug leads to incorrect results from a query, it seems like an important bugfix.

      Here are the simplest versions of queries we found that returned incorrect results:
      select 1 from t1 where t1.id = 1234 and 1=returnzero();

      Should return no rows, and with this bug, returns a row. returnzero() can be any kind of stored function of any complexity. 

        Attachments

          Activity

            People

            Assignee:
            pschwaller Peter Schwaller (Inactive)
            Reporter:
            jlesueur John LeSueur
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist