Bug #26030 Parsing fails for stored routine w/multi-statement execution enabled
Submitted: 1 Feb 2007 20:45 Modified: 14 Jul 2008 19:02
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0 and up OS:Any
Assigned to: Marc Alff
Triage: D3 (Medium)

[1 Feb 2007 20:45] Paul Dubois
Description:
If I enable multi-statement execution for the C API, this statement
string works:

DROP PROCEDURE IF EXISTS p; CREATE PROCEDURE p() BEGIN SELECT 1; END; CALL p()

But this statement string results in a syntax error:

DROP PROCEDURE IF EXISTS p; CREATE PROCEDURE p() SELECT 1; CALL p()

Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1; CALL p()' at line 1

The routine definitions are effectively equivalent. The only difference is that the
second does not use a compound statement.

How to repeat:
Compile the attached test program, spbug.c, to produce an
executable and run it like this:

./spbug db_name

Output should look like this:

0 rows affected
0 rows affected
1 rows returned
0 rows affected
0 rows affected
Could not execute statement
Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1; CALL p()' at line 1
0 rows affected
0 rows affected
1 rows returned
0 rows affected

The rows affected/returned stuff comes from the result processing code. See program
text for details.
[1 Feb 2007 20:46] Paul Dubois
spbug.c test program to illustrate the problem

Attachment: spbug.c (application/octet-stream, text), 5.88 KiB.

[1 Feb 2007 22:18] Marc Alff
Verified with the test driver provided, and with a mysqltest script :

use test;

delimiter $$;

# works
DROP PROCEDURE IF EXISTS p; CREATE PROCEDURE p() BEGIN SELECT 1; END; CALL
p()
$$

# fails when parsing ';' returned as MY_LEX_CHAR, not END_OF_INPUT
-- error ER_PARSE_ERROR
DROP PROCEDURE IF EXISTS p; CREATE PROCEDURE p() SELECT 1; CALL p()
$$

# works
DROP PROCEDURE IF EXISTS p; CREATE PROCEDURE p() BEGIN SELECT 1; END; CALL
p()
$$
[20 Mar 2008 18:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44305

ChangeSet@1.2563, 2008-03-20 11:57:34-06:00, malff@lambda.hsd1.co.comcast.net. +7 -0
  Bug#26030 (Parsing fails for stored routine w/multi-statement execution
  enabled)
  
  Before this fix, the lexer and parser would treat the ';' character as a
  different token (either ';' or END_OF_INPUT), based on convoluted logic,
  which failed in simple cases where a stored procedure is implemented as a
  single statement, and used in a multi query.
  
  With this fix:
  - the character ';' is always parsed as a ';' token in the lexer,
  - parsing multi queries is implemented in the parser, in the 'query:' rules,
  - the value of thd->client_capabilities, which is the capabilities
    negotiated between the client and the server during bootstrap,
    is immutable and not arbitrarily modified during parsing (which was the
    root cause of the bug)
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[7 Jul 2008 16:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/49111
[7 Jul 2008 21:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/49134
[7 Jul 2008 21:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/49135
[8 Jul 2008 18:50] Marc Alff
See related bug#37525
[9 Jul 2008 12:51] Georgi Kodinov
pushed to 5.0.66
[14 Jul 2008 17:09] Marc Alff
Correction:

Pushed into 5.0.68 (not 5.0.66), on July 14, 2008 (after the version bump from July 9, 2008).

Also, pushed into:
- 5.1.28,
- 6.0.6
[14 Jul 2008 19:02] Paul Dubois
Noted in 5.0.68, 5.1.28, 6.0.6 changelogs.

In some cases, the parser interpreted the ; character as the end of
input and misinterpreted stored program definitions.
[28 Jul 2008 13:47] Bugs System
Pushed into 5.0.68  (revid:kpettersson@mysql.com-20080715152926-s5kgnqhtu93b3c8v) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)
[28 Jul 2008 16:46] Bugs System
Pushed into 5.1.28  (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)