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

    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

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

                Dates

                • Created:
                  Updated: