We're updating the issue view to help you get more done. 

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

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.

Environment

CentOS 64bit, Ubuntu 11.10 64bit

Status

Assignee

Timour Katchaounov

Reporter

Hank Eskin

Labels

Fix versions

Affects versions

10.0.0
5.5.28
5.3.11

Priority

Major