[MDEV-3900] Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements Created: 2012-12-01  Updated: 2013-01-17  Resolved: 2013-01-17

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0, 5.5.28, 5.3.11
Fix Version/s: 10.0.1, 5.5.29, 5.3.12

Type: Bug Priority: Major
Reporter: Hank Eskin (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: optimizer
Environment:

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.

 Comments   
Comment by Elena Stepanova [ 2012-12-01 ]

Reproducible on current maria/5.3, maria/5.5, maria/10.0. Not reproducible on maria/5.2, mysql/5.6, mysql-5.1.
Reproducible with the default optimizer_switch as well as with all OFF values.

Test case:

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

CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, daynum INT, a CHAR(1), INDEX(daynum), INDEX(a)) ENGINE=MyISAM;
INSERT INTO t1 (daynum) VALUES (1),(2),(3),(4),(5),(TO_DAYS(NOW())),(7),(8);
INSERT INTO t1 (daynum) SELECT a1.daynum FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;

FLUSH TABLES;
FLUSH STATUS;

--echo # Status before UPDATE
SHOW STATUS LIKE '%Handler_read%';

UPDATE t1 SET a = '+' WHERE daynum=tdn();

--echo # Status after UPDATE
SHOW STATUS LIKE '%Handler_read%';

  1. End of test case

Output:

  1. Status after UPDATE
    SHOW STATUS LIKE '%Handler_read%';
    Variable_name Value
    Handler_read_first 0
    Handler_read_key 1
    Handler_read_last 0
    Handler_read_next 0
    Handler_read_prev 0
    Handler_read_rnd 0
    Handler_read_rnd_deleted 0
    Handler_read_rnd_next 32777

Output with TO_DAYS instead of tdn:

  1. Status after UPDATE
    SHOW STATUS LIKE '%Handler_read%';
    Variable_name Value
    Handler_read_first 0
    Handler_read_key 1
    Handler_read_last 0
    Handler_read_next 4097
    Handler_read_prev 0
    Handler_read_rnd 0
    Handler_read_rnd_deleted 0
    Handler_read_rnd_next 0
Comment by Hank Eskin (Inactive) [ 2012-12-01 ]

Thank you for creating this test case and validating the bug (so I don't have to).

Comment by Timour Katchaounov (Inactive) [ 2013-01-17 ]

Pushed to 5.3.

Generated at Thu Feb 08 06:52:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.