Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0, 5.5.28, 5.3.11
-
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
Thank you for creating this test case and validating the bug (so I don't have to).
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%';
Output:
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:
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