MDL-53944 dml: make char2real() for mysql to support decimals

- With the patch, 6 positions of scale accuracy are guaranteed
  per individual casted value.
- Backed with unit tests, both for varchar and clob.
- Added 2 missing tests about uses of the method with params
  and values.

Note: 6 was picked because looking to all databases implementation
postgres was found to be casting to real, aka, 6.

diff --git a/lib/dml/mysqli_native_moodle_database.php b/lib/dml/mysqli_native_moodle_database.php
index e8015a2..663bd6a 100644 (file)
--- a/lib/dml/mysqli_native_moodle_database.php
+++ b/lib/dml/mysqli_native_moodle_database.php
@@ -1504,7 +1504,11 @@ class mysqli_native_moodle_database extends moodle_database {
     }
 
     public function sql_cast_char2real($fieldname, $text=false) {
-        return ' CAST(' . $fieldname . ' AS DECIMAL) ';
+        // Set to 65 (max mysql 5.5 precision) with 7 as scale
+        // because we must ensure at least 6 decimal positions
+        // per casting given that postgres is casting to that scale (::real::).
+        // Can be raised easily but that must be done in all DBs and tests.
+        return ' CAST(' . $fieldname . ' AS DECIMAL(65,7)) ';
     }
 
     /**

diff --git a/lib/dml/tests/dml_test.php b/lib/dml/tests/dml_test.php
index b2bb25e..7df8222 100644 (file)
--- a/lib/dml/tests/dml_test.php
+++ b/lib/dml/tests/dml_test.php
@@ -3670,7 +3670,7 @@ class core_dml_testcase extends database_driver_testcase {
 
         $DB->insert_record($tablename, array('name'=>'10.10', 'nametext'=>'10.10', 'res'=>5.1));
         $DB->insert_record($tablename, array('name'=>'91.10', 'nametext'=>'91.10', 'res'=>666));
-        $DB->insert_record($tablename, array('name'=>'011.10', 'nametext'=>'011.10', 'res'=>10.1));
+        $DB->insert_record($tablename, array('name'=>'011.13333333', 'nametext'=>'011.13333333', 'res'=>10.1));
 
         // Casting varchar field.
         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('name')." > res";
@@ -3681,8 +3681,11 @@ class core_dml_testcase extends database_driver_testcase {
         $records = $DB->get_records_sql($sql);
         $this->assertCount(3, $records);
         $this->assertSame('10.10', reset($records)->name);
-        $this->assertSame('011.10', next($records)->name);
+        $this->assertSame('011.13333333', next($records)->name);
         $this->assertSame('91.10', next($records)->name);
+        // And verify we can operate with them without too much problem with at least 6 decimals scale accuracy.
+        $sql = "SELECT AVG(" . $DB->sql_cast_char2real('name') . ") FROM {{$tablename}}";
+        $this->assertEquals(37.44444443333333, (float)$DB->get_field_sql($sql), '', 1.0E-6);
 
         // Casting text field.
         $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_cast_char2real('nametext', true)." > res";
@@ -3693,8 +3696,19 @@ class core_dml_testcase extends database_driver_testcase {
         $records = $DB->get_records_sql($sql);
         $this->assertCount(3, $records);
         $this->assertSame('10.10', reset($records)->nametext);
-        $this->assertSame('011.10', next($records)->nametext);
+        $this->assertSame('011.13333333', next($records)->nametext);
         $this->assertSame('91.10', next($records)->nametext);
+        // And verify we can operate with them without too much problem with at least 6 decimals scale accuracy.
+        $sql = "SELECT AVG(" . $DB->sql_cast_char2real('nametext', true) . ") FROM {{$tablename}}";
+        $this->assertEquals(37.44444443333333, (float)$DB->get_field_sql($sql), '', 1.0E-6);
+
+        // Check it works with values passed as param.
+        $sql = "SELECT name FROM {{$tablename}} WHERE FLOOR(res - " . $DB->sql_cast_char2real(':param') . ") = 0";
+        $this->assertEquals('011.13333333', $DB->get_field_sql($sql, array('param' => '10.09999')));
+
+        // And also, although not recommended, with directly passed values.
+        $sql = "SELECT name FROM {{$tablename}} WHERE FLOOR(res - " . $DB->sql_cast_char2real('10.09999') . ") = 0";
+        $this->assertEquals('011.13333333', $DB->get_field_sql($sql));
     }
 
     public function test_sql_compare_text() {