MySQL Bugs: #21462: Stored procedures with no arguments require parenthesis

Bug #21462	Stored procedures with no arguments require parenthesis
Submitted:	6 Aug 2006 4:39	Modified:	15 Nov 2006 18:21
Reporter:	TOM DONOVAN (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.21-community-nt/5.1	OS:	Windows (Windows/Linux)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	CALL, stored procedure

Description:
A call to a stored procedure which takes no arguments requires an empty set of parentheses, e.g.  CALL MyProc();  instead of  CALL MyProc;

This fact is described in the MySQL docs http://mysql.com/doc/refman/5.0/en/call.html

The syntax is not clearly specified by SQL92.

Both JDBC and ODBC specify that parentheses are omitted when there are no arguments:
http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/JDBC20.html#JDBC2013_4
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcprocedure_calls.asp

This causes syntax errors when MySQL stored procedures are called, unless the SQL is altered to supply empty parentheses.  

In many applications the user cannot easily change the JDBC or ODBC SQL syntax to accomodate MySQL.

Note that most (possibly all) other databases process a no-argument procedure call without parentheses.

How to repeat:
EXAMPLE PROCEDURE:
    delimiter $                 
    create procedure MyProc()    
    begin                       
    select 123;                 
    end                         
    $                           
    delimiter ;                 

JDBC calling example:
    import java.sql.*;
    ...
    Class c = Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://{server}:3306/{database}";		
    Connection con = DriverManager.getConnection(url, "{user}", "{password}");
    CallableStatement cs = con.prepareCall("{call MyProc}");
    cs.execute();
    ...

EXAMPLE Connector/J 5.0.3 error :

  com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 '' at line 1

EXAMPLE Connector/ODBC 3.51 error 
(ODBC called from DataDirect Sequelink JDBC driver):

   [SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver] 
   [mysqld-5.0.21-community-nt]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 '{call MyProc}' at line 1 

Suggested fix:
This seems simpler to fix in MySQL server.

Since only the JDBC and ODBC specs say that parentheses are omitted when there are no arguments, it would be acceptable to fix this in both drivers instead.  This could be done by supplying an empty set of parentheses if they are (correctly) omitted, before the driver forwards the SQL to the server for execution.

This change to sql/sql_yacc.yy seems to allow a no-parentheses procedure CALL.  
It still allows an empty pair of parentheses to work as well.
================================================
call:
          CALL_SYM sp_name
          {
            LEX *lex = Lex;
            lex->sql_command= SQLCOM_CALL;
            lex->spname= $2;
            lex->value_list.empty();
            sp_add_used_routine(lex, YYTHD, $2, TYPE_ENUM_PROCEDURE);
          }
         sp_cparam_list {}
        ;

/* CALL parameters */
sp_cparam_list:
          '(' sp_cparams ')'
        | /* Empty */
        ;
sp_cparams:
          sp_cparams ',' expr
          {
            Lex->value_list.push_back($3);
          }
        | expr
          {
            Lex->value_list.push_back($1);
          }
        | /* Empty */ {}
        ;
================================================
SOME TEST EXAMPLES:   
   Procedure myproc takes no arguments.  
   Procedure myproc2 takes one integer argument.

call myproc;            WORKS

call myproc();          WORKS

call myproc(      );    WORKS

call myproc(
);                      WORKS       (parentheses split across multiple lines)

call myproc(101);       FAILS   
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.myproc; expected 0, got 1

call myproc2;           FAILS   
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.myproc2; expected 1, got 0

call myproc2();         FAILS   
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.myproc2; expected 1, got 0

call myproc2(103);      WORKS

call myproc2(  103 );   WORKS

If this is changed, the doc at http://mysql.com/doc/refman/5.0/en/call.html
should be changed:

    from:   CALL sp_name([parameter[,...]])
    to:     CALL sp_name[(parameter[,...])]

Could you send your changes as a unified diff (diff -u)?

unified diff for sql/sql_yacc.yy

Attachment: sql_yacc.yy.patch (application/octet-stream, text), 741 bytes.

to change status back to open

Do you need anything more for this bug?

Also, please be sure to apply the patch with the -l switch (loose whitespace matching).  Whitespace is not significant to this change to sql/sql_yacc.yy.

Thank you for the bug report and suggestion patch.

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/13277

ChangeSet@1.2275, 2006-10-06 10:50:58-07:00, malff@weblab.(none) +3 -0
  Bug#21462 (Stored procedures with no arguments require parenthesis)
  
  The syntax of the CALL statement, to invoke a stored procedure, has been
  changed to make the use of parenthesis optional in the argument list.
  With this change, "CALL p;" is equivalent to "CALL p();".
  
  While the SQL spec does not explicitely mandate this syntax, supporting it
  is needed for practical reasons, for integration with JDBC / ODBC connectors.
  
  Also, warnings in the sql/sql_yacc.yy file, which were not reported by Bison 2.1
  but are now reported by Bison 2.2, have been fixed.

Reviewed by email.

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/13341

ChangeSet@1.2275, 2006-10-09 09:59:02-07:00, malff@weblab.(none) +3 -0
  Bug#21462 (Stored procedures with no arguments require parenthesis)
  
  The syntax of the CALL statement, to invoke a stored procedure, has been
  changed to make the use of parenthesis optional in the argument list.
  With this change, "CALL p;" is equivalent to "CALL p();".
  
  While the SQL spec does not explicitely mandate this syntax, supporting it
  is needed for practical reasons, for integration with JDBC / ODBC connectors.
  
  Also, warnings in the sql/sql_yacc.yy file, which were not reported by Bison 2.1
  but are now reported by Bison 2.2, have been fixed.
  
  The warning found were:
  bison -y -p MYSQL  -d --debug --verbose sql_yacc.yy
  sql_yacc.yy:653.9-18: warning: symbol UNLOCK_SYM redeclared
  sql_yacc.yy:656.9-17: warning: symbol UNTIL_SYM redeclared
  sql_yacc.yy:658.9-18: warning: symbol UPDATE_SYM redeclared
  sql_yacc.yy:5169.11-5174.11: warning: unused value: $2
  sql_yacc.yy:5208.11-5220.11: warning: unused value: $5
  sql_yacc.yy:5221.11-5234.11: warning: unused value: $5
  conflicts: 249 shift/reduce
  
  "unused value: $2" correspond to the $$=$1 assignment in the 1st {} block
  in table_ref -> join_table {} {},
  which does not procude a result ($$) for the rule but an intermediate $2
  value for the action instead.
  "unused value: $5" are similar, with $$ assignments in {} actions blocks
  which are not for the final reduce.

Pushed into 5.0-runtime

Noted in 5.0.30 (not 5.0.29), 5.1.13 changelogs.

For the CALL statement, stored procedures that take no arguments now
can be invoked without parentheses. That is, CALL p() and CALL p are
equivalent.

Also updated the section that describes CALL syntax.