MySQL Bugs: #23311: Queries within triggers give unexpected results

Bug #23311	Queries within triggers give unexpected results
Submitted:	15 Oct 2006 22:28	Modified:	9 Nov 2006 13:11
Reporter:	Lachlan Mulcahy	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Any (Any)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
3 Tables with a set of AFTER INSERT triggers are created to keep a 4th table updated with an unnormalised  representation of some of the data for searching purposes.

The triggers are designed in such a way that the order with which the tables are INSERTed into should not be relevant, yet results seem to be inconsistent.

If a consistent order of inserting into the tables is maintained, the results in the 4th table are as expected, however, if a random order is adopted they are not.

See the attached files and how to repeat information for further details.

How to repeat:
See internal/developer only comments.

Suggested fix:
unknown

Renamed the bug from "triggers appear to not fire as expected" as further testing indicates all triggers fire as expected, however, the queries within the triggers do not perform as expected.

Adding a more generic, simplified and publically available testcase to demonstrate -- Instructions to follow.

Simplified public testcase

Attachment: bug23311-simplified.tgz (application/x-gzip, text), 2.43 KiB.

To reproduce:

shell> tar xvzf bug23311-simplified.tgz
shell> cd bug23311-simplified

Within MySQL command-line client:

CREATE DATABASE bug23311;
USE bug23311;
SOURCE schema.sql;
SOURCE test.sql;
SELECT * FROM t5;

Proof that all triggers fired:

SELECT * FROM log;

Note: The attached testscript.php file was used to generate test.sql and may be used to perform further testing and experimentation. You will want to redirect it's output to a file.

It seems that the UPDATE t5... query within the t1_bi trigger is not performing it's LEFT JOIN correctly and it is returning NULLs unexpectedly.

another quick addition -- Though the test case only covers BEFORE INSERT triggers, the problem occurs with AFTER INSERT triggers too, so it quite possibly spans many trigger types.

ignore above private comment. i don't get any nulls in t5 when i tried this on 5.0BK today.

This bug is a duplicate of bug#21081, which was already fixed (tested by reverting  21081 patch).