MySQL Bugs: #14272: Federated Storage Engine does not use indexes on UPDATE

Bug #14272	Federated Storage Engine does not use indexes on UPDATE
Submitted:	24 Oct 2005 20:39	Modified:	31 Oct 2005 19:39
Reporter:	Patrick Galbraith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Any (all)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
Federated test breaks because it does not use indexes in UPDATE, though the flags are set to do so. Logic needs to be added to update_row to use create_where_from key in the event the table has an index that is used in the initial query.

How to repeat:
create a table (myisam) with both primary key and regular keys on a foreign db, then a federated table with the same definition (except it being federated) to use the foreign table, insert rows of data, then update that table using a regular key in the where clause, note the failure "table storage engine for 'tablename' doesn't have this option. If you use a primary key in the update clause, notice in the foreign server logs that it doesn't use the primary key in the where clause, but all the columns in the where clause.

Suggested fix:
Implement calling of create_where_from key in update_row

The fix for this isn't that it needs to call create where_from_key (that would be long term). The way this has to work for now is this - you call the select that gets the rows that are to be updated. If the WHERE clause is for any columns that are indexed, you call read_range_first, which gets those records, or if no index, full table scan (select * from). 

The problem seems to be much higher up. Some change, to the optimiser (?) now makes it fail if the where clause contains a regular index (not a primary key) that is also going to be changed in the SET part of the statement.

Here's the scoop:

CREATE TABLE `t1` (
  `id` int(20) NOT NULL auto_increment,
  `name` varchar(32) NOT NULL default '',
  `other` int(20) NOT NULL default '0',
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `other` (`other`),
  KEY `created` (`created`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@localhost/test/t1' |

mysql> select * from t1;
+----+--------------+--------+---------------------+
| id | name         | other  | created             |
+----+--------------+--------+---------------------+
|  1 | First Name   |  11111 | 2004-01-01 01:01:01 |
|  2 | Second Name  |  22222 | 2004-01-23 02:43:00 |
|  3 | Third name   |  33333 | 2004-02-14 02:14:00 |
|  4 | Fourth Name  |  44444 | 2003-04-05 00:00:00 |
|  5 | Fifth Name   |  55555 | 2001-02-02 02:02:02 |
|  6 | Sixth Name   |  66666 | 2005-06-06 15:30:00 |
|  7 | Seventh Name |  77777 | 2003-12-12 18:32:00 |
|  8 | Eigth Name   |  88888 | 2005-03-12 11:00:00 |
|  9 | Ninth Name   |  99999 | 2005-03-12 11:00:01 |
| 10 | Tenth Name   | 101010 | 2005-03-12 12:00:01 |
+----+--------------+--------+---------------------+

mysql> update t1 set name = '4th Name' where id = 4;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

remote queries: 

SELECT `id`, `name`, `other`, `created` FROM `t1` WHERE  ( (`id` = 4) ) AND ( (`id` <= 4) )
 `t1` SET id = 4, name = '4th Name', other = 44444, created = '2003-04-05 00:00:00' WHERE id = 4 AND name = 'Fourth Name' AND other = 44444 AND created = '2003-04-05 00:00:00'

mysql> update t1 set other = 44445 where name = '4th Name';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

remote queries:

SELECT `id`, `name`, `other`, `created` FROM `t1` WHERE  ( (`name` = '4th Name') ) AND ( (`name` <= '4th Name') ) 
UPDATE `t1` SET id = 4, name = '4th Name', other = 44445, created = '2003-04-05 00:00:00' WHERE id = 4 AND name = '4th Name' AND other = 44444 AND created = '2003-04-05 00:00:00'

mysql> update t1 set name = 'Fourth Name' where other = 44445;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

remote queries:
SELECT `id`, `name`, `other`, `created` FROM `t1` WHERE  ( (`other` = 44445) ) AND ( (`other` <= 44445) )
UPDATE `t1` SET id = 4, name = 'Fourth Name', other = 44445, created = '2003-04-05 00:00:00' WHERE id = 4 AND name = '4th Name' AND other = 44445 AND created = '2003-04-05 00:00:00'

mysql> update t1 set name = '4th Name' where name = 'Fourth Name';
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
mysql> update t1 set name = '4th Name' where other = 44445 and name = 'Fourth Name';
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

mysql> update t1 set other= 44444 where other = 44445 and name = 'Fourth Name';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

remote queries:

SELECT `id`, `name`, `other`, `created` FROM `t1` WHERE  ( (`name` = 'Fourth Name') ) AND ( (`name` <= 'Fourth Name') )
 UPDATE `t1` SET id = 4, name = 'Fourth Name', other = 44444, created = '2003-04-05 00:00:00' WHERE id = 4 AND name = 'Fourth Name' AND other = 44445 AND created = '2003-04-05 00:00:00'

mysql> update t1 set other= 44444 where other = 44445;
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

mysql> update t1 set other= 44445 where other = 44444 and id = 4;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

remote queries:

SELECT `id`, `name`, `other`, `created` FROM `t1` WHERE  ( (`id` = 4) ) AND ( (`id` <= 4) )
 UPDATE `t1` SET id = 4, name = 'Fourth Name', other = 44445, created = '2003-04-05 00:00:00' WHERE id = 4 AND name = 'Fourth Name' AND other = 44444 AND created = '2003-04-05 00:00:00'

Primary key doesn't have a problem:

SELECT `id`, `name`, `other`, `created` FROM `t1` WHERE  ( (`id` = 10) ) AND ( (`id` <= 10) )
UPDATE `t1` SET id = 11, name = 'Tenth Name', other = 101010, created = '2005-03-12 12:00:01' WHERE id = 10 AND name = 'Tenth Name' AND other = 101010 AND created = '2005-03-12 12:00:01'

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31451

Patch approved by Monty on irc.

Fix pushed into 5.0.16 tree.
(no need for documentation as it is does not appear in released versions)

No changelog entry needed.