MySQL Bugs: #26030: Parsing fails for stored routine w/multi-statement execution enabled

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	CPU Architecture:	Any

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.

spbug.c test program to illustrate the problem

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

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()
$$

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)

Pushed into 6.0.6-alpha

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

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

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

See related bug#37525

pushed to 5.0.66

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

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.

Pushed into 5.0.68  (revid:kpettersson@mysql.com-20080715152926-s5kgnqhtu93b3c8v) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)

Pushed into 5.1.28  (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)