Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4600

CAST(decimal AS SIGNED/UNSIGNED) returns a wrong result

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.6.1, 6.1.1, 5.5.1
    • Fix Version/s: 6.1.1
    • Component/s: PrimProc
    • Labels:
      None
    • Sprint:
      2021-5

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(18,17)) ENGINE=ColumnStore;
      INSERT INTO t1 VALUES (9.49999999999999999);
      SELECT a, CAST(a AS UNSIGNED), CAST(a AS SIGNED) FROM t1;
      

      +---------------------+---------------------+-------------------+
      | a                   | CAST(a AS UNSIGNED) | CAST(a AS SIGNED) |
      +---------------------+---------------------+-------------------+
      | 9.49999999999999999 |                  10 |                10 |
      +---------------------+---------------------+-------------------+
      

      The above looks wrong. If I change to ENGINE=InnoDB, it correctly returns:

      +---------------------+---------------------+-------------------+
      | a                   | CAST(a AS UNSIGNED) | CAST(a AS SIGNED) |
      +---------------------+---------------------+-------------------+
      | 9.49999999999999999 |                   9 |                 9 |
      +---------------------+---------------------+-------------------+
      

      The problem happens due to the precision loss in this code in Func_cast_unsigned::getUintVal():

                  }
                  else
                  {
                      if (d.value < 0)
                      {
                          return 0;
                      }
       
                      double dscale = d.scale;
       
                      uint64_t value = d.value / pow(10.0, dscale);
                      int lefto = (d.value - value * pow(10.0, dscale)) / pow(10.0, dscale - 1);
       
                  if ( utils::is_nonnegative(value) && lefto > 4 )
                  {
                      value++;
                  }
       
                      return value;
                   }
      

        Attachments

          Issue Links

            Activity

              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:

                  Git Integration