Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6571

INFORMATION_SCHEMA.ROUTINE_DEFINITION does not work well with binary values

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.3.12, 5.5.39, 10.0.14
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
          None

          Description

          This script:

          SET NAMES binary;
DROP FUNCTION IF EXISTS f1;
CREATE FUNCTION f1() RETURNS VARBINARY(10) RETURN '😁';
SHOW CREATE FUNCTION f1;
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='f1';

          returns

          +--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| RETURN '😁'      |
+--------------------+
1 row in set (0.00 sec)

          Notice, the original value in RETURN is unreadable.

          If I change the connection character set from "binary" to "utf8"
          and run the same script:

          SET NAMES utf8;
DROP FUNCTION IF EXISTS f1;
CREATE FUNCTION f1() RETURNS VARBINARY(10) RETURN '😁';
SHOW CREATE FUNCTION f1;
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='f1';

          it returns:

          +--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| RETURN '           |
+--------------------+
1 row in set, 1 warning (0.00 sec)

          The value is unreadable again.
          Also, a warning is returned:

          MariaDB [test]> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                              |
+---------+------+--------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x81'' for column 'ROUTINE_DEFINITION' at row 1 |
+---------+------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

          It should be fixed to return a some form of a readable value.
          Also, no warnings should be produced during "SELECT FROM I_S.ROUTINES".

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: