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

Unexpected calculated column lengths

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Not a Bug
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.2.17
    • Fix Version/s: N/A
    • Component/s: Data types, Views
    • Labels:

      Description

      The length of a calculated column in a VIEW is sometimes incorrect, for example:

      MariaDB [private_17_production]> create or replace view test3 as select 2019 as v1, 1 AS v2;
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [private_17_production]> create or replace view test2 as select concat(right(v1,2),'BARRQ',v2) AS v3 from test3;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [private_17_production]> create or replace view test1 as select v3 as v4 from test2;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [private_17_production]> desc test3;
      +-------+--------+------+-----+---------+-------+
      | Field | Type   | Null | Key | Default | Extra |
      +-------+--------+------+-----+---------+-------+
      | v1    | int(4) | NO   |     | 0       |       |
      | v2    | int(1) | NO   |     | 0       |       |
      +-------+--------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
       
      MariaDB [private_17_production]> desc test2;
      +-------+-------------+------+-----+---------+-------+
      | Field | Type        | Null | Key | Default | Extra |
      +-------+-------------+------+-----+---------+-------+
      | v3    | varchar(18) | NO   |     |         |       |
      +-------+-------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [private_17_production]> desc test1;
      +-------+-------------+------+-----+---------+-------+
      | Field | Type        | Null | Key | Default | Extra |
      +-------+-------------+------+-----+---------+-------+
      | v4    | varchar(18) | NO   |     |         |       |
      +-------+-------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
       
      MariaDB [private_17_production]> select v4, length(v4) from test1;
      +----------+------------+
      | v4       | length(v4) |
      +----------+------------+
      | 19BARRQ1 |          8 |
      +----------+------------+
      1 row in set (0.00 sec)
       
      MariaDB [private_17_production]> select v3, length(v3) from test2;
      +----------+------------+
      | v3       | length(v3) |
      +----------+------------+
      | 19BARRQ1 |          8 |
      +----------+------------+
      1 row in set (0.00 sec)
       
      MariaDB [private_17_production]> select v1, length(v1), v2, length(v2) from test3;
      +------+------------+----+------------+
      | v1   | length(v1) | v2 | length(v2) |
      +------+------------+----+------------+
      | 2019 |          4 |  1 |          1 |
      +------+------------+----+------------+
      1 row in set (0.00 sec)
      

      The base values clearly have fixed lengths, and the calculation will clearly always produce a fixed length of 8 characters, but the VIEW description insists that the column is 18 characters wide.

      The character set is 'utf8' on both client and server, but the data contains just single-byte characters, so I don't think it is a confusion between characters and bytes.

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              jonathan.monahan@workbooks.com Jonathan Monahan
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: