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.