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

Wrong field type or metadata for COALESCE(signed_int_column, unsigned_int_column)

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.1.8
        • Component/s: OTHER
        • Labels:
        • Sprint:
          10.1.8-3, 10.1.8-4

          Description

          This script:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b INT UNSIGNED);
INSERT INTO t1 VALUES (1,1);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 AS SELECT COALESCE(a,b) AS c FROM t1;
SHOW CREATE TABLE t2;

          returns

          +-------+---------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+---------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `c` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------+

          Now if I start "mysql --column-type-info" and run this query:

          SELECT COALESCE(a,b) AS c FROM t1;

          it returns the following metadata:

          MariaDB [test]> SELECT COALESCE(a,b) AS c FROM t1;
Field   1:  `c`
...
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      BINARY NUM

          Notice, COALESCE(a,b) creates a DECIMAL(10,0) field during CREATE TABLE..SELECT, but at the same time reports itself as LONG in metadata.

          The same problems makes this SQL script return a wrong result set:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
INSERT INTO t1 VALUES (-1,0xFFFFFFFFFFFFFFFF);
SELECT COALESCE(b,a) AS c FROM t1;
SHOW WARNINGS;

          It returns:

          +---------------------+
| c                   |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

          with a warning:

          +---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1916 | Got overflow when converting '18446744073709551615' to INT. Value truncated. |
+---------+------+------------------------------------------------------------------------------+

          The expected result is to return DECIMAL value of 18446744073709551615 with no warnings.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Repeatable in MySQL-5.7.8

                Show
                bar Alexander Barkov added a comment - Repeatable in MySQL-5.7.8

                  People

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

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Agile