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

Aggregate function on zerofill column returns padded value from inside a derived table

    XMLWordPrintable

Details

    Description

      MariaDB [test]> SELECT MIN(i) FROM t1;
      +--------+
      | MIN(i) |
      +--------+
      |      1 |
      +--------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM ( SELECT MIN(i) FROM t1 ) cte;
      +------------+
      | MIN(i)     |
      +------------+
      | 0000000001 |
      +------------+
      1 row in set (0.00 sec)
      

      Consequently, CTEs in 10.2 are also affected:

      MariaDB [test]> WITH cte AS ( SELECT MIN(i) FROM t1 ) SELECT * FROM cte;
      +------------+
      | MIN(i)     |
      +------------+
      | 0000000001 |
      +------------+
      1 row in set (0.00 sec)
      

      Test case

      DROP TABLE IF EXISTS t1;
       
      CREATE TABLE t1 (i INT ZEROFILL);
      INSERT INTO t1 VALUES (1);
       
      SELECT MIN(i) FROM t1;
      explain extended
      SELECT MIN(i) FROM t1;
       
      SELECT * FROM ( SELECT MIN(i) FROM t1 ) cte;
      explain extended
      SELECT * FROM ( SELECT MIN(i) FROM t1 ) cte;
       
      /*!100203
      WITH cte AS ( SELECT MIN(i) FROM t1 ) SELECT * FROM cte
      */;
      /*!100203
      explain extended
      WITH cte AS ( SELECT MIN(i) FROM t1 ) SELECT * FROM cte
      */;
       
      DROP TABLE t1;
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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