Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-53944

char2real returns inaccurate results on MySQL

    XMLWordPrintable

    Details

      Description

      mysqli_native_moodle_database.php has this:

      public function sql_cast_char2real($fieldname, $text=false) {
          return ' CAST(' . $fieldname . ' AS DECIMAL) ';
      }
      

      This isn't great as when casting to DECIMAL in MySQL we need to specify precision and scale. Where precision represents how many digits there are in total and scale specifies how many fractional digits there are. This is done with DECIMAL(M,D) where M is precision and D is decimal places. When M and D aren't specified, the default is DECIMAL(M, 0) where M refers to how many digits are in the string total

      SELECT CAST('9999.9' as DECIMAL)); //equivalent to DECIMAL(5)
       
      +---------+
      | Decimal |
      +---------+
      |   10000 |
      +---------+
      

      SELECT CAST('9999.9' as DECIMAL(4)));
       
      +---------+
      | Decimal |
      +---------+
      |    9999 |
      +---------+
      

      SELECT CAST('9999.9' as DECIMAL(5,1)) as 'Decimal';
      +---------+
      | Decimal |
      +---------+
      |  9999.9 |
      +---------+
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  9/May/16