lp:~sergei.glushchenko/percona-server/5.5-ST29724_bug1132194

Created by Sergei Glushchenko on 2013-03-11 and last modified on 2013-06-26
Get this branch:
bzr branch lp:~sergei.glushchenko/percona-server/5.5-ST29724_bug1132194
Only Sergei Glushchenko can upload to this branch. If you are Sergei Glushchenko please log in for upload directions.

Branch merges

Related bugs

Related blueprints

Branch information

Recent revisions

517. By Sergei Glushchenko on 2013-06-26

BUG#1132194: UPDATE/DELETE with LIMIT clause unsafe for SBR
even when ORDER BY PK is present
MYSQL BUG#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
all tables to be opened and all fields to 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.
If the key field length is longer than MAX_SORT_LENGTH, then it
will be ignored and not treated as a key field.

Branch metadata

Branch format:
Branch format 7
Repository format:
Bazaar repository format 2a (needs bzr 1.16 or later)
Stacked on:
lp:percona-server/5.6
This branch contains Public information 
Everyone can see this information.