Re: Nasty LIMIT/multi-table DELETE bug?
Heikki Tuuri writes: Jon, - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, June 22, 2002 12:51 AM Subject: Nasty LIMIT/multi-table DELETE bug? I just discovered a very scary behavior. We're using MySQL 4.0.1 on Linux, both tables are InnoDB, AUTO_COMMIT is on (default) and these queries are being issues from the MySQL interactive console. If I do a query of the form: SELECT transaction_report.*, confirmed FROM transaction_report, user WHERE user_id = user.id AND DATE_FORMAT(when_reported, '%Y-%m-%d') = '2002-06-21' AND transaction_report.partner_id = 1 AND confirmed != 1 LIMIT 50; This bug was fixed meanwhile ... Now, with multi-table delete you get a syntax error with LIMIT. This has been done so as LIMIT is ambiguous in delete with more then one table involved. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Nasty LIMIT/multi-table DELETE bug?
I just discovered a very scary behavior. We're using MySQL 4.0.1 on Linux, both tables are InnoDB, AUTO_COMMIT is on (default) and these queries are being issues from the MySQL interactive console. If I do a query of the form: SELECT transaction_report.*, confirmed FROM transaction_report, user WHERE user_id = user.id AND DATE_FORMAT(when_reported, '%Y-%m-%d') = '2002-06-21' AND transaction_report.partner_id = 1 AND confirmed != 1 LIMIT 50; And then a query of the form: DELETE transaction_report FROM transaction_report, user WHERE user_id = user.id AND confirmed != 1; Only 50 rows will be deleted. If I reissue the DELETE, it will delete 50 more rows. If I reissue the SELECT with a LIMIT 20, then the DELETE statements will remove 20 rows. This seems to be limited to a single connection handle (issuing the SELECT in one MySQL console doesn't cause problems with the DELETE in a second console...). -JF - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Nasty LIMIT/multi-table DELETE bug?
Jon, - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, June 22, 2002 12:51 AM Subject: Nasty LIMIT/multi-table DELETE bug? I just discovered a very scary behavior. We're using MySQL 4.0.1 on Linux, both tables are InnoDB, AUTO_COMMIT is on (default) and these queries are being issues from the MySQL interactive console. If I do a query of the form: SELECT transaction_report.*, confirmed FROM transaction_report, user WHERE user_id = user.id AND DATE_FORMAT(when_reported, '%Y-%m-%d') = '2002-06-21' AND transaction_report.partner_id = 1 AND confirmed != 1 LIMIT 50; And then a query of the form: DELETE transaction_report FROM transaction_report, user WHERE user_id = user.id AND confirmed != 1; Only 50 rows will be deleted. If I reissue the DELETE, it will delete 50 more rows. If I reissue the SELECT with a LIMIT 20, then the DELETE statements will remove 20 rows. there were several bugs in multi-table delete in 4.0.1. I am not sure if this is one of the bugs which have already been fixed to 4.0.2. I am Cc:ing Sinisa who knows the answer. This seems to be limited to a single connection handle (issuing the SELECT in one MySQL console doesn't cause problems with the DELETE in a second console...). -JF Regards, Heikki Innobase Oy (sql database) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Nasty LIMIT/multi-table DELETE bug?
Hi, AFAIK, this problem has already been fixed by sinisa. (it was due to an uninitialized pointer) ([EMAIL PROTECTED], 2002-05-29 21:20:28+03:00, [EMAIL PROTECTED] A very nasty bug in multi-table deletes updates sql/sql_parse.cc 1.228 02/05/29 21:20:27 [EMAIL PROTECTED] +2 -1 A very nasty bug in multi-table deletes updates) Regards, Jocelyn - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, June 22, 2002 12:16 AM Subject: Re: Nasty LIMIT/multi-table DELETE bug? Jon, - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, June 22, 2002 12:51 AM Subject: Nasty LIMIT/multi-table DELETE bug? I just discovered a very scary behavior. We're using MySQL 4.0.1 on Linux, both tables are InnoDB, AUTO_COMMIT is on (default) and these queries are being issues from the MySQL interactive console. If I do a query of the form: SELECT transaction_report.*, confirmed FROM transaction_report, user WHERE user_id = user.id AND DATE_FORMAT(when_reported, '%Y-%m-%d') = '2002-06-21' AND transaction_report.partner_id = 1 AND confirmed != 1 LIMIT 50; And then a query of the form: DELETE transaction_report FROM transaction_report, user WHERE user_id = user.id AND confirmed != 1; Only 50 rows will be deleted. If I reissue the DELETE, it will delete 50 more rows. If I reissue the SELECT with a LIMIT 20, then the DELETE statements will remove 20 rows. there were several bugs in multi-table delete in 4.0.1. I am not sure if this is one of the bugs which have already been fixed to 4.0.2. I am Cc:ing Sinisa who knows the answer. This seems to be limited to a single connection handle (issuing the SELECT in one MySQL console doesn't cause problems with the DELETE in a second console...). -JF Regards, Heikki Innobase Oy (sql database) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
