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

Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.0, 5.5.28, 5.3.11
    • 10.0.1, 5.5.29, 5.3.12
    • None
    • CentOS 64bit, Ubuntu 11.10 64bit

    Description

      I use the built-in functions "to_days(now())" all the time in my application and queries. To save typing, I shortened this in a user-defined function "tdn()" like this:

      CREATE FUNCTION tdn() RETURNS int(7) DETERMINISTIC RETURN to_days(now());

      When I issue an UPDATE or DELETE statement of a large (300 million rows), fully indexed table like this:

      update <table> set <field>=<value> where daynum=to_days(now()) <plus optional extra criteria>
      or
      delete from <table> where daynum=to_days(now()) <plus optional extra criteria>

      The update uses the index on the "daynum" field to properly update or delete the proper records.

      but if I do the same thing using my UDF:

      update <table> set <field>=<value> where daynum=tdn() <plus optional extra criteria>
      or
      delete from <table> where daynum=tdn() <plus optional extra criteria>

      the query does a full table scan.

      The exact same UPDATE and DELETE queries running on MySQL 5.5.8 both properly use the index using both methods.

      All equivalent SELECT queries also use the index as expected on both platforms.

      The problem only occurs when doing and UPDATE or DELETE and my UDF is in the where clause of an indexed field. But only in MaraiDB and not in MySQL.

      Is there a workaround for this, or is this an optimizer bug?

      I'm actually running version MariaDB 5.5.27.

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            heskin Hank Eskin (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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