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:
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.24a OS:Any (Any)
Assigned to: Assigned Account CPU Architecture:Any

[15 Oct 2006 22:28] Lachlan Mulcahy
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:
[17 Oct 2006 7:00] Lachlan Mulcahy
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.
[17 Oct 2006 7:15] Lachlan Mulcahy
Simplified public testcase

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

[17 Oct 2006 7:19] Lachlan Mulcahy
To reproduce:

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

Within MySQL command-line client:

USE bug23311;
SOURCE schema.sql;
SOURCE test.sql;

Proof that all triggers fired:


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.
[17 Oct 2006 7:25] Lachlan Mulcahy
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.
[18 Oct 2006 7:30] MySQL Verification Team
ignore above private comment. i don't get any nulls in t5 when i tried this on 5.0BK today.
[9 Nov 2006 13:11] Tomash Brechko
This bug is a duplicate of bug#21081, which was already fixed (tested by reverting  21081 patch).