55-bug1132194 : Code : Percona Server moved to https://jira.percona.com/projects/PS

Get this branch:: bzr branch lp:~sergei.glushchenko/percona-server/55-bug1132194

Only Sergei Glushchenko can upload to this branch. If you are Sergei Glushchenko please log in for upload directions.

Branch merges

No branches dependent on this one.

Related bugs

Link a bug report

Related blueprints

Branch information

Owner:: Sergei Glushchenko

Project:: Percona Server moved to https://jira.percona.com/projects/PS

Status:: Development

Recent revisions

461. By Sergei Glushchenko on 2013-03-08

BUG#1132194: UPDATE/DELETE with LIMIT clause unsafe for SBR
even when ORDER BY PK is present

MYSQL BUGUG#42415 UPDATE/DELETE with LIMIT clause unsafe for SBL
even with ORDER BY PK clause

This is an adoption of http://lists.mysql.com/commits/126382

This is only a partial solution which allows to suppress warning
in many cases when statement with LIMIT isn't really nondeterministic.
Partial means that in mixed mode all statements with LIMIT will be
switched to ROW unconditionally. To determine whether the staments is
really unsafe some analysis of query should be performed. This requires
in particular all tables should be opened and all fields should be
fixed. However decision to switch to ROW mode is made before this
happens.

Original description:
UPDATE/DELETE/INSERT..SELECT with LIMIT clause were considered unsafe
unconditionally, even if there is an ORDER BY PK or WHERE condition
that can guarantee the result to be deterministic.

The problem is fixed by implementing a algorithm to do more elaborate
analyzed on the nature of the query to determine whether the query
will cause uncertainty for replication or not.

The statement will not be considered unsafe for following cases:
- single table UPDATE/DELETE/INSERT..SELECT with ORDER BY
   <non null unique key>
- single table UPDATE/DELETE/INSERT..SELECT with WHERE clause
   that include <non null unique key> = <const value>, if it is
   a multi-part key, then it must be <keypart1>=<const1> AND
   <keypart2>=<const2> ..., and this condition is ANDed with
   other conditions if there is any.
- single table INSERT..SELECT with WHERE clause
   that include some parts of the non null unique key compare to
   const values, and the ORDER BY clause includes all the other
   key pars of the same non null unique key. for example (a,b) is
   a non null unique key, then WHERE a=<const> ORDER b will make
   the query result deterministic.
- for INSERT..SELECT ... JOIN ..., the join condition (ON,
   USING, or WHERE) must include equations between columns of
   non null unique key of tables from both side of the join.
   For example t1 JOIN t2 USING (a,b), if (a,b) is not a non null
   unique key for both t1 and t2, then the result will be non-
   deterministic. otherwise the result can be deterministic with
   appropirate WHERE/ORDER clauses, and in this case, the same
   rule for single table above applys. But there is a difference
   for INNER JOIN with OUTER JOIN, for OUTER JOIN, only one table
   of the two JOIN tables will be used when checking the WHERE/ORDER
   conditions, it's the left table for LEFT JOIN and the right one
   for RIGHT JOIN when checking the keys. On the other hand, for
   INNER JOIN, keys from both tables can be used when checking
   the conditions. For example:
     INSERT..SELECT * FROM t1 INNER JOIN t2 USING(pk) ORDER BY nnuk LIMIT 1;
   This can be safe if nnuk is a non null unique key of either
   t1 or t2. But if we change the INNER JOIN to LEFT JOIN or
   RIGHT JOIN, then nnuk must be a non null unique key key of
   t1 (LEFT JOIN) or t2 (RIGHT JOIN) respectively.
- If JOIN are nested, the will be handled recursively from inner
   outside.
- UNIONs without global LIMIT are unsafe if any of sub-selects
   is unsafe, and safe if all sub-selects are safe. UNIONs with
   global LIMIT are marked unsafe no matter this is ORDER BY or
   not.