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

              Assignee:
              stronk7 Eloy Lafuente (stronk7)
              Reporter:
              cameron1729 cameron1729
              Peer reviewer:
              cameron1729
              Integrator:
              David Monllaó
              Tester:
              CiBoT
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

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