Bug #24833 QUICK option to DELETE lost in a multiple-table delete
Submitted: 5 Dec 2006 18:34 Modified: 15 Jan 2007 22:02
Reporter: Philip Stoev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.26, 5.1 BK OS:Linux (Linux)
Assigned to: Marc Alff CPU Architecture:Any
Tags: qc
Triage: Triaged: D3 (Medium)

[5 Dec 2006 18:34] Philip Stoev
Description:
Hello,

The documentation says that the QUICK option is available even for multiple-table deletes. Unfortunately, it appears that while the option is properly set in the "opt_delete_option:" portion of sql_yacc.yy, in "single_multi portion:", mysql_init_multi_delete() is called, which in turn calls mysql_init_select() which calls st_select_lex::init_select(), which sets select_lex->options back to zero.

The IGNORE and LOW_PRIORITY options are not affected because they are stored in separate members of the Lex structure.

How to repeat:
Dump the select_lex->option value for various DELETE statements.

Suggested fix:
Avoid calling mysql_init_select() twice, once in the "delete:" portion and one more time in mysql_init_multi_delete().
[15 Jan 2007 21:04] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources. Version 5.1 is affected too.
[15 Jan 2007 21:05] Sveta Smirnova
Test case

Attachment: bug24833.test (application/octet-stream, text), 883 bytes.

[15 Jan 2007 21:06] Sveta Smirnova
Attached file shows that problem is not only with QUICK.
[15 Jan 2007 22:02] Philip Stoev
Whatever the problem with IGNORE is, it is not the same as the problem with QUICK. The problem with QUICK is a purely parser problem, as described in the initial bug report. IGNORE is preserved during parsing, any IGNORE problem must be somewhere else. How to verify:

use DBIx::MyParse;
my $parser = DBIx::MyParse->new( database => 'test' );
my $query = $parser->parse('delete quick ignore from t1, t2 using t1, t2');
print $query->print();

This script parses the query using the MySQL parser and then reconstructs it again in Perl from the parse tree. The QUICK option is lost, however the IGNORE option remains. Single-table DELETE is not affected.