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

Wrong field type for CAST(temporal_column AS [UN]SIGNED)

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2(EOL), 10.3(EOL)
    • 10.4(EOL)
    • Data types
    • None

    Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT
        CAST(TIME'10:10:10.123456' AS SIGNED) AS c1,
        CAST(TIME'10:10:10.123456' AS UNSIGNED) AS c2;
      DESC t1;
      

      +-------+---------------------+------+-----+---------+-------+
      | Field | Type                | Null | Key | Default | Extra |
      +-------+---------------------+------+-----+---------+-------+
      | c1    | bigint(17)          | NO   |     | NULL    |       |
      | c2    | bigint(20) unsigned | NO   |     | NULL    |       |
      +-------+---------------------+------+-----+---------+-------+
      

      Looks excessive. TIME can have only 7 digits in the integer part: hhhmmss.

      Attachments

        Issue Links

          Activity

            A similar problem is repeatable when casting from signed int to signed int:

            DROP TABLE IF EXISTS t1,t2;
            CREATE TABLE t1 (a INT(1));
            CREATE TABLE t2 AS SELECT
              CAST(a AS SIGNED) AS c1,
              COALESCE(a) AS c2
            FROM t1;
            DESC t2;
            

            +-------+------------+------+-----+---------+-------+
            | Field | Type       | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | c1    | bigint(11) | YES  |     | NULL    |       |
            | c2    | int(11)    | YES  |     | NULL    |       |
            +-------+------------+------+-----+---------+-------+
            

            Notice, the data type for the column c1 was erroneously detected as bigint(11). We're casting from SIGNED INT to SIGNED INT, so nothing should change. The expected data type should be int, not bigint. Notice, COALESCE() does preserve int.

            bar Alexander Barkov added a comment - A similar problem is repeatable when casting from signed int to signed int: DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (a INT (1)); CREATE TABLE t2 AS SELECT CAST (a AS SIGNED) AS c1, COALESCE (a) AS c2 FROM t1; DESC t2; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c1 | bigint(11) | YES | | NULL | | | c2 | int(11) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ Notice, the data type for the column c1 was erroneously detected as bigint(11) . We're casting from SIGNED INT to SIGNED INT, so nothing should change. The expected data type should be int , not bigint . Notice, COALESCE() does preserve int .

            A similar problem is repeatable when casting from unsigned int to unsigned int:
            Notice, the data type for the column c1 was erroneously detected as bigint(11). We're casting from UNSIGNED INT to UNSIGNED INT, so nothing should change. The expected data type should be int unsigned, not bigint unsigned. Notice, COALESCE() does preserve int unsigned.

            DROP TABLE IF EXISTS t1,t2;
            CREATE TABLE t1 (a INT(11) UNSIGNED);
            CREATE TABLE t2 AS SELECT
              CAST(a AS UNSIGNED) AS c1,
              COALESCE(a) AS c2
            FROM t1;
            DESC t2;
            

            +-------+---------------------+------+-----+---------+-------+
            | Field | Type                | Null | Key | Default | Extra |
            +-------+---------------------+------+-----+---------+-------+
            | c1    | bigint(11) unsigned | YES  |     | NULL    |       |
            | c2    | int(11) unsigned    | YES  |     | NULL    |       |
            +-------+---------------------+------+-----+---------+-------+
            

            bar Alexander Barkov added a comment - A similar problem is repeatable when casting from unsigned int to unsigned int: Notice, the data type for the column c1 was erroneously detected as bigint(11) . We're casting from UNSIGNED INT to UNSIGNED INT, so nothing should change. The expected data type should be int unsigned , not bigint unsigned . Notice, COALESCE() does preserve int unsigned . DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (a INT (11) UNSIGNED); CREATE TABLE t2 AS SELECT CAST (a AS UNSIGNED) AS c1, COALESCE (a) AS c2 FROM t1; DESC t2; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | c1 | bigint(11) unsigned | YES | | NULL | | | c2 | int(11) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+

            People

              bar Alexander Barkov
              bar Alexander Barkov
              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.