[#MDEV-30662] SQL/PL package body does not appear in I_S.ROUTINES.ROUTINE

Comment by Alexander Barkov [ 2023-02-27 ]

Repeatable with this script:

SET sql_mode=ORACLE;

DELIMITER $$

CREATE OR REPLACE PACKAGE pkg1 AS

  FUNCTION f1() RETURN INT;

END;

$$

CREATE PACKAGE BODY pkg1 AS

  FUNCTION f1() RETURN INT AS

  BEGIN

    RETURN 1;

  END;

BEGIN

END;

$$

DELIMITER ;

SELECT routine_name, routine_type, routine_body, routine_definition FROM information_schema.routines WHERE routine_type LIKE 'PACKAGE%'\G

*************************** 1. row ***************************

      routine_name: pkg1

      routine_type: PACKAGE

      routine_body: SQL

routine_definition:

*************************** 2. row ***************************

      routine_name: pkg1

      routine_type: PACKAGE BODY

      routine_body: SQL

routine_definition:

2 rows in set (0.005 sec)

Notice, the column routine_definition is empty.

An equivalent query to mysql.proc returns definitions without problems:

SELECT name, type, `body` FROM mysql.proc WHERE type LIKE 'PACKAGE%'\G

*************************** 1. row ***************************

name: pkg1

type: PACKAGE

body: AS

  FUNCTION f1() RETURN INT;

END

*************************** 2. row ***************************

name: pkg1

type: PACKAGE BODY

body: AS

  FUNCTION f1() RETURN INT AS

  BEGIN

    RETURN 1;

  END;

BEGIN

END

2 rows in set (0.002 sec)

Comment by Alexander Barkov [ 2023-07-13 ]

The problem happens because mysql.proc.body_utf8 is empty for PACKAGE and PACKAGE BODY records for some reasons:

MariaDB [test]> select name, type, body_utf8 from mysql.proc where name='pkg1';

+------+--------------+-----------+

| name | type         | body_utf8 |

+------+--------------+-----------+

| pkg1 | PACKAGE      |           |

| pkg1 | PACKAGE BODY |           |

+------+--------------+-----------+

[MDEV-30662] SQL/PL package body does not appear in I_S.ROUTINES.ROUTINE_DEFINITION Created: 2023-02-16 Updated: 2023-09-16 Resolved: 2023-07-14
Status:	Closed
Project:	MariaDB Server
Component/s:	Configuration, Information Schema
Affects Version/s:	10.6.8, 10.5, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s:	10.8.8, 10.5.22, 10.6.15, 10.9.8, 10.11.5, 11.0.3, 11.1.2, 11.2.1