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

[6 Aug 2006 4:39] TOM DONOVAN
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.
[6 Aug 2006 17:19] TOM DONOVAN
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
[6 Aug 2006 17:20] TOM DONOVAN
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[,...])]
[7 Aug 2006 11:44] Hartmut Holzgraefe
Could you send your changes as a unified diff (diff -u)?
[7 Aug 2006 14:14] TOM DONOVAN
unified diff for sql/sql_yacc.yy

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

[7 Aug 2006 20:37] TOM DONOVAN
to change status back to open
[24 Aug 2006 21:01] TOM DONOVAN
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.
[24 Aug 2006 21:11] MySQL Verification Team
Thank you for the bug report and suggestion patch.
[6 Oct 2006 16:51] 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/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.
[9 Oct 2006 15:10] Konstantin Osipov
Reviewed by email.
[9 Oct 2006 15:59] 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/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.
[9 Oct 2006 23:09] Marc ALFF
Pushed into 5.0-runtime
[15 Nov 2006 18:21] Paul DuBois
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.