Bug #2171 Delete from two tables using a left join chrashes the server on windows

• Submitted: 19 Dec 2003 5:48
• Modified: 19 Dec 2003 6:04
• Reporter: [ name withheld ]
• Status: Can't repeat
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.0.15-max-nt
• OS: Windows (Win2k)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[19 Dec 2003 5:48] [ name withheld ]

Description:
Doing a delete from two tables, using a normal join works fine, but when the joined table lacks the entry, using left join chrashes the 4.0.15 server on windows 2k and redhat linux.

On linux resulting in a:
ERROR 2013: Lost connection to MySQL server during query
from the client, on windows resulting in a service termination.

How to repeat:
CREATE TABLE a (pk INT);
CREATE TABLE b (pk INT, apk INT);
CREATE INDEX ia ON a ( pk );
CREATE INDEX iba ON b ( apk );
ALTER TABLE b ADD CONSTRAINT fk FOREIGN KEY ( apk ) REFERENCES a ( pk );
INSERT a VALUES(1);
DELETE a, b FROM a LEFT JOIN b ON b.apk=a.pk;

[19 Dec 2003 6:04] MySQL Verification Team

I tested with 4.0.17 and I wasn't able to repeat. Please try 4.0.17:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.17-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE a (pk INT);
Query OK, 0 rows affected (0.26 sec)

mysql> CREATE TABLE b (pk INT, apk INT);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE INDEX ia ON a ( pk );
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX iba ON b ( apk );
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE b ADD CONSTRAINT fk FOREIGN KEY ( apk ) REFERENCES a ( pk );
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT a VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> DELETE a, b FROM a LEFT JOIN b ON b.apk=a.pk;
Query OK, 1 row affected (0.04 sec)