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

ZEROFILL on a stored function return type is not applied to the function's own result

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      SELECT f_zf() on a function declared RETURNS INT(5) ZEROFILL returns 7, while CONCAT(f_zf()), CAST(f_zf() AS CHAR) and a view over the same call all return 00007

      CREATE TABLE t_zf (c INT(5) ZEROFILL);
      INSERT INTO t_zf VALUES (7);
      SELECT c, CONCAT(c), CAST(c AS CHAR) FROM t_zf;
      CREATE FUNCTION f_zf() RETURNS INT(5) ZEROFILL DETERMINISTIC RETURN 7;
      SELECT f_zf(), CONCAT(f_zf()), CAST(f_zf() AS CHAR);
      CREATE VIEW v_zf AS SELECT f_zf() AS c;
      SELECT c FROM v_zf;
      CREATE TABLE t_zf3 AS SELECT f_zf() AS c;
      SHOW CREATE TABLE t_zf3;
      

      CLI output

      10.11.19-opt>CREATE TABLE t_zf (c INT(5) ZEROFILL);
      Query OK, 0 rows affected (0.010 sec)
       
      10.11.19-opt>INSERT INTO t_zf VALUES (7);
      Query OK, 1 row affected (0.001 sec)
       
      10.11.19-opt>SELECT c, CONCAT(c), CAST(c AS CHAR) FROM t_zf;
      +-------+-----------+-----------------+
      | c     | CONCAT(c) | CAST(c AS CHAR) |
      +-------+-----------+-----------------+
      | 00007 | 00007     | 00007           |
      +-------+-----------+-----------------+
      1 row in set (0.000 sec)
       
      10.11.19-opt>CREATE FUNCTION f_zf() RETURNS INT(5) ZEROFILL DETERMINISTIC RETURN 7;
      Query OK, 0 rows affected (0.002 sec)
       
      10.11.19-opt>SELECT f_zf(), CONCAT(f_zf()), CAST(f_zf() AS CHAR);
      +--------+----------------+----------------------+
      | f_zf() | CONCAT(f_zf()) | CAST(f_zf() AS CHAR) |
      +--------+----------------+----------------------+
      |      7 | 00007          | 00007                |
      +--------+----------------+----------------------+
      1 row in set (0.000 sec)
       
      10.11.19-opt>CREATE VIEW v_zf AS SELECT f_zf() AS c;
      Query OK, 0 rows affected (0.003 sec)
       
      10.11.19-opt>SELECT c FROM v_zf;
      +-------+
      | c     |
      +-------+
      | 00007 |
      +-------+
      1 row in set (0.000 sec)
       
      10.11.19-opt>CREATE TABLE t_zf3 AS SELECT f_zf() AS c;
      Query OK, 1 row affected (0.005 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      10.11.19-opt>SHOW CREATE TABLE t_zf3;
      +-------+-------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                        |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------+
      | t_zf3 | CREATE TABLE `t_zf3` (
        `c` int(5) unsigned zerofill DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      10.11.19-opt>
      

      Attachments

        Activity

          People

            shulga Dmitry Shulga
            ramesh Ramesh Sivaraman
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0d
                0d
                Logged:
                Time Spent - 0.5h
                0.5h

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.