[MDEV-16117] SP with a single FOR statement creates but further fails to load Created: 2018-05-08  Updated: 2018-09-07  Resolved: 2018-05-10

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
Relates
relates to MDEV-16020 SP variables inside GROUP BY..WITH RO... Closed
relates to MDEV-16095 Oracle-style placeholder inside GROUP... Closed

 Description   

This problem was reported by Peter Gulutzan:

This script creates without problems a stored procedure consisting of a single FOR statement, but an attempt to execute it fails with an error:

DELIMITER $$
 
CREATE OR REPLACE PROCEDURE p1()
 
  FOR i IN 1..10 DO
 
      set @x = 5;
 
  END FOR;
 
$$
 
DELIMITER ;
 
CALL p1;


ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

Querying mysql.proc tells that the body value is wrong for this procedure:

SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1';


+-------------------------------------------+
 
| body                                      |
 
+-------------------------------------------+
 
| i IN 1..10 DO
 
      set @x = 5;
 
  END FOR |
 
+-------------------------------------------+

Notice, the keyword FOR is missing in the beginning.

The same problem is repeatable with a stored function consisting of a single FOR statement:

DELIMITER $$
 
CREATE OR REPLACE FUNCTION f1() RETURNS INT
 
  FOR i IN 1..10 DO
 
    RETURN 1;
 
  END FOR;
 
$$
 
DELIMITER ;
 
SELECT f1();


ERROR 1457 (HY000): Failed to load routine test.f1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)


SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1';


+---------------------------------------+
 
| body                                  |
 
+---------------------------------------+
 
| i IN 1..10 DO
 
    RETURN 1;
 
  END FOR |
 
+---------------------------------------+

It should be fixed to preserve the FOR keyword in body.

 Comments   
Comment by Alexander Barkov [ 2018-05-08 ]

The problem is repeatable with statements starting with any keyword that can be a part of keyword contraction in the function MYSQLlex() in sql_lex.cc:

  switch(token) {
 
  case WITH
 
  ...
 
  case FOR_SYM:
 
  ...
 
  case VALUES:
 
  ...
 
  }

This script demonstrates the same problem with WITH:

DELIMITER $$
 
CREATE OR REPLACE PROCEDURE p1() WITH t1 AS (SELECT 1) SELECT 1;
 
$$
 
DELIMITER ;
 
CALL p1;


ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)


SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1';


+---------------------------+
 
| body                      |
 
+---------------------------+
 
| t1 AS (SELECT 1) SELECT 1 |
 
+---------------------------+

This script demonstrates the same problem with VALUES:

DELIMITER $$
 
CREATE OR REPLACE PROCEDURE p1() VALUES (1);
 
$$
 
DELIMITER ;
 
CALL p1;


ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)


SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1';


+------+
 
| body |
 
+------+
 
| (1)  |
 
+------+

Generated at Thu Feb 08 08:26:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.