Doctrine 1
  1. Doctrine 1
  2. DC-62

decimal fields are rounded to 6 decimal places on save()

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.13, 1.1.4, 1.2.0-BETA2
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Record
    • Labels:
      None
    • Environment:
      db engine: MySQL 5.0.75 and SQLite
      php ini: precision = 12

      Description

      Decimal fields with high precision are rounded down to 6 decimal places prior to inserting into database on Doctrine_Record->save()

      For example,
      if you define a column to represent decimal numbers with 12 digits following the decimal:

      MySet:
      columns:
      p_value:

      { type: decimal(18), scale: 12 }

      Then create a new doctrine record object and save:

      $record = new MySet();
      $record['p_value'] = 5.012345678912;
      $record->save();

      The value in the database at this point has been rounded down to 6 decimal places:
      value = 5.012346000000

      print_r($record->toArray()) shows the correct value to 12 decimal places

        Activity

        Hide
        brady added a comment -

        I found that retrieving from the database gives the correct precision, so the rounding problem only occurs on saving. I've tested doctrine versions 1.0.13 (through symfony), 1.1.4, and 1.2 alpha2 - all have this problem.

        //require_once('lib/vendor/Doctrine-1.2.0/lib/Doctrine.php');
        require_once('lib/vendor/Doctrine-1.1.4/Doctrine.php');
        spl_autoload_register(array('Doctrine', 'autoload'));

        class TestRecord extends Doctrine_Record
        {
        public function setTableDefinition()

        { $this->setTableName('test'); $this->hasColumn('id', 'integer', null, array( 'type' => 'integer', 'notnull' => false, 'primary' => true, 'autoincrement' => true, )); $this->hasColumn('test_field', 'decimal', 18, array( 'scale' => 12, )); }

        }

        $pdo = new PDO('sqlite:testdb.db');
        //$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'root', 'pass');
        $conn = Doctrine_Manager::connection($pdo);

        // test retreival of value (first row has 12.891841075016 inserted manually)
        $q = Doctrine_Query::create()->from('TestRecord')
        ->where('id = ?', 1);
        $record = $q->fetchOne();
        echo 'retrieved value = ', $record['test_field'], "\n";
        // outputs 12.891841075016 (OK)

        // test save of high precision decimal
        $test = new TestRecord();
        $test['test_field'] = 12.891841075016;
        $test->save();
        // database shows 12.891841000000 (rounded incorrectly)

        Show
        brady added a comment - I found that retrieving from the database gives the correct precision, so the rounding problem only occurs on saving. I've tested doctrine versions 1.0.13 (through symfony), 1.1.4, and 1.2 alpha2 - all have this problem. //require_once('lib/vendor/Doctrine-1.2.0/lib/Doctrine.php'); require_once('lib/vendor/Doctrine-1.1.4/Doctrine.php'); spl_autoload_register(array('Doctrine', 'autoload')); class TestRecord extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('test'); $this->hasColumn('id', 'integer', null, array( 'type' => 'integer', 'notnull' => false, 'primary' => true, 'autoincrement' => true, )); $this->hasColumn('test_field', 'decimal', 18, array( 'scale' => 12, )); } } $pdo = new PDO('sqlite:testdb.db'); //$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'root', 'pass'); $conn = Doctrine_Manager::connection($pdo); // test retreival of value (first row has 12.891841075016 inserted manually) $q = Doctrine_Query::create()->from('TestRecord') ->where('id = ?', 1); $record = $q->fetchOne(); echo 'retrieved value = ', $record ['test_field'] , "\n"; // outputs 12.891841075016 (OK) // test save of high precision decimal $test = new TestRecord(); $test ['test_field'] = 12.891841075016; $test->save(); // database shows 12.891841000000 (rounded incorrectly)
        Hide
        Jonathan H. Wage added a comment -

        This is a problem with mysql. In Doctrine decimal won't store that value properly. If you change it to float, it works properly. I had to make a change in Doctrine so that Doctrine float maps to mysql float, double to double and decimal to decimal. Update Doctrine 1.2 from SVN and change your schema type to be 'float' instead of 'decimal'. It should work properly then.

        Show
        Jonathan H. Wage added a comment - This is a problem with mysql. In Doctrine decimal won't store that value properly. If you change it to float, it works properly. I had to make a change in Doctrine so that Doctrine float maps to mysql float, double to double and decimal to decimal. Update Doctrine 1.2 from SVN and change your schema type to be 'float' instead of 'decimal'. It should work properly then.
        Hide
        Jörg Eichhorn added a comment -

        Seems another issue is generated by the fix to this ticket. For a schema like:

        Data:
        columns:
        id:
        type: integer(5)
        primary: true
        value:
        type: double

        and a MySQL database the generated column type for value is DOUBLE(18,2), which obviously isn't what was intended. Actually it means that storing values like "1.234567" or "12 345 678 901 234 567 890" doesn't work like one expects when working with double precision floats.

        Maybe I oversee something but I think there isn't a way to just use plain doubles.

        A possible fix could be to check for a explicitly set scale or length (line 233 and 237 of DataDict/Mysql.php):

        if (!empty($field['length']) || !empty($field['scale']))

        { $length = !empty($field['length']) ? $field['length'] : 18; $scale = !empty($field['scale']) ? $field['scale'] : $this->conn->getAttribute(Doctrine_Core::ATTR_DECIMAL_PLACES); return 'DOUBLE('.$length.', '.$scale.')'; }

        else

        { return 'DOUBLE'; }

        This way there is a fallback to the version before rev 6750.

        Show
        Jörg Eichhorn added a comment - Seems another issue is generated by the fix to this ticket. For a schema like: Data: columns: id: type: integer(5) primary: true value: type: double and a MySQL database the generated column type for value is DOUBLE(18,2), which obviously isn't what was intended. Actually it means that storing values like "1.234567" or "12 345 678 901 234 567 890" doesn't work like one expects when working with double precision floats. Maybe I oversee something but I think there isn't a way to just use plain doubles. A possible fix could be to check for a explicitly set scale or length (line 233 and 237 of DataDict/Mysql.php): if (!empty($field ['length'] ) || !empty($field ['scale'] )) { $length = !empty($field['length']) ? $field['length'] : 18; $scale = !empty($field['scale']) ? $field['scale'] : $this->conn->getAttribute(Doctrine_Core::ATTR_DECIMAL_PLACES); return 'DOUBLE('.$length.', '.$scale.')'; } else { return 'DOUBLE'; } This way there is a fallback to the version before rev 6750.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            brady
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: