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

Hybrid type expressions return wrong format for FLOAT

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.3.16, 10.4.5
    • Component/s: Data types
    • Labels:
      None

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a FLOAT);
      INSERT INTO t1 VALUES (0.671437);
      SELECT a, COALESCE(a), MAX(a), (SELECT a FROM t1) AS c FROM t1;
      

      +----------+--------------------+--------------------+--------------------+
      | a        | COALESCE(a)        | MAX(a)             | c                  |
      +----------+--------------------+--------------------+--------------------+
      | 0.671437 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 |
      +----------+--------------------+--------------------+--------------------+
      

      Notice, the value of the FLOAT type column is limited to 6 significant digits, while values of FLOAT type expressions return more digits.

      The same problem is repeatable in this script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a FLOAT);
      INSERT INTO t1 VALUES (0.671437);
      CREATE OR REPLACE TABLE t2 AS SELECT a, CONCAT(COALESCE(a)) AS b FROM t1;
      

      ERROR 1406 (22001): Data too long for column 'b' at row 1
      

      Notice, returning the error is wrong. It should create and populate t2 without any errors.

      This happens because CONCAT() with a FLOAT type expression returns too long values:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a FLOAT);
      INSERT INTO t1 VALUES (0.671437);
      SELECT CONCAT(a), CONCAT(COALESCE(a)), CONCAT(LEAST(a,a)), CONCAT(MAX(a)), CONCAT((SELECT a FROM t1)) AS c FROM t1;
      

      +-----------+---------------------+--------------------+--------------------+--------------------+
      | CONCAT(a) | CONCAT(COALESCE(a)) | CONCAT(LEAST(a,a)) | CONCAT(MAX(a))     | c                  |
      +-----------+---------------------+--------------------+--------------------+--------------------+
      | 0.671437  | 0.6714370250701904  | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 |
      +-----------+---------------------+--------------------+--------------------+--------------------+
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: