php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #29064 Exact numeric/decimal/money datatypes lose precision
Submitted: 2004-07-08 17:06 UTC Modified: 2004-07-12 23:07 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:2 (100.0%)
Same OS:2 (100.0%)
From: daniel dot beet at accuratesoftware dot com Assigned: thekid (profile)
Status: Closed Package: Sybase-ct (ctlib) related
PHP Version: 4.3.7 / HEAD OS: Windows 2000 Server
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: daniel dot beet at accuratesoftware dot com
New email:
PHP Version: OS:

 

 [2004-07-08 17:06 UTC] daniel dot beet at accuratesoftware dot com
Description:
------------
PHP's Sybase CT library converts all numeric datatypes to ints of floats, so numbers outside their ranges lose precision.

The following patchs fix these issues in a similar way to mssql and oci8 libs:

Compare: (<)php-4.3.7\ext\sybase_ct\php_sybase_ct.1.14.2.3.h (3780 bytes)
   with: (>)php-4.3.7\ext\sybase_ct\php_sybase_ct.h (3929 bytes)

94c94
< 	int max_length, numeric;
---
> 	int max_length, numeric, precision, scale;

Compare: (<)php-4.3.7\ext\sybase_ct\php_sybase_ct.1.73.2.16.c (65966 bytes)
   with: (>)php-4.3.7\ext\sybase_ct\php_sybase_ct.c (68800 bytes)

1233,1235c1233,1242
< 			case CS_MONEY4_TYPE:
< 				result->datafmt[i].maxlength = 24;
< 				result->numerics[i] = 2;
---
> 				result->datafmt[i].maxlength = 24;
> 				result->numerics[i] = 5;
> 				result->datafmt[i].precision = 19;
> 				result->datafmt[i].scale = 4;
> 				break;
> 			case CS_MONEY4_TYPE:
> 				result->datafmt[i].maxlength = 13;
> 				result->numerics[i] = 2;
> 				result->datafmt[i].precision = 10;
> 				result->datafmt[i].scale = 4;
1244,1246c1251,1254
< 				result->datafmt[i].maxlength = result->datafmt[i].precision + 3;
< 				/* numeric(10) vs numeric(10, 1) */
< 				result->numerics[i] = (result->datafmt[i].scale == 0) ? 3 : 2;
---
> 				/* numerics can overflow real and long types, return as a string */
> 				result->datafmt[i].maxlength = result->datafmt[i].precision + 3;
> 				/* numeric(10) vs numeric(10, 1) */
> 				result->numerics[i] = (result->datafmt[i].scale == 0) ? 4 : 5;
1277c1285,1287
< 		result->fields[i].numeric = result->numerics[i];
---
> 		result->fields[i].numeric = (result->numerics[i] > 0) ? 1 : 0;
> 		result->fields[i].precision = result->datafmt[i].precision;
> 		result->fields[i].scale = result->datafmt[i].scale;
1862,1864c1872,1876
< 		case CS_NUMERIC_TYPE:
< 		case CS_DECIMAL_TYPE:
< 			return "real";
---
> 			return "real";
> 			break;
> 		case CS_NUMERIC_TYPE:
> 		case CS_DECIMAL_TYPE:
> 			return "numeric";
1929a1941,1942
> 	add_property_long(return_value, "precision", result->fields[field_offset].precision);
> 	add_property_long(return_value, "scale", result->fields[field_offset].scale);

Hope that helps! Thanks, Dan.


Reproduce code:
---------------
Create a table test:

create table test (
test_decimal    decimal(38, 6)  null,
test_numeric    numeric(38, 12) null,
test_money  money   null,
test_bigint decimal(38, 0)  null,
test_int    int null,
test_smallmoney smallmoney  null,
test_smallint   smallint    null,
test_tinyint    tinyint null,
test_real   float   null,
test_double float   null
)

execute the following script via php cli exe:

<?php

sybase_min_server_severity(11);
sybase_min_client_severity(11);

$conn = sybase_connect('yourserver', 'sa', 'password');
echo "Connection OK<br />\n";

sybase_select_db('test', $conn);
echo "DB selected OK<br />\n";

$result = sybase_query('select test_decimal, test_numeric, test_money, test_smallmoney, test_bigint, test_int, test_smallint, test_tinyint, test_real, test_double from test', $conn);
echo "Query OK<br />\n";

$f = sybase_num_fields($result);

for ($i = 0; $i < $f; $i++)
{
  $array[] = sybase_fetch_field($result, $i);
}

while ($ar = sybase_fetch_assoc($result))
{
  $array[] = $ar;
}

echo "Results fetched OK<br />\n";

echo '<pre>';
var_export($array);
echo '</pre>';

sybase_close($conn);

?>


Expected result:
----------------
Connection OK<br />
DB selected OK<br />
Query OK<br />
Results fetched OK<br />
<pre>array (
  0 =>
  class stdClass {
    var $name = 'test_decimal';
    var $max_length = 40;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'numeric';
    var $precision = 38;
    var $scale = 6;
  },
  1 =>
  class stdClass {
    var $name = 'test_numeric';
    var $max_length = 40;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'numeric';
    var $precision = 38;
    var $scale = 12;
  },
  2 =>
  class stdClass {
    var $name = 'test_money';
    var $max_length = 21;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'money';
    var $precision = 19;
    var $scale = 4;
  },
  3 =>
  class stdClass {
    var $name = 'test_smallmoney';
    var $max_length = 12;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'money';
    var $precision = 10;
    var $scale = 4;
  },
  4 =>
  class stdClass {
    var $name = 'test_bigint';
    var $max_length = 40;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'numeric';
    var $precision = 38;
    var $scale = 0;
  },
  5 =>
  class stdClass {
    var $name = 'test_int';
    var $max_length = 11;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'int';
    var $precision = 0;
    var $scale = 0;
  },
  6 =>
  class stdClass {
    var $name = 'test_smallint';
    var $max_length = 6;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'int';
    var $precision = 0;
    var $scale = 0;
  },
  7 =>
  class stdClass {
    var $name = 'test_tinyint';
    var $max_length = 3;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'int';
    var $precision = 0;
    var $scale = 0;
  },
  8 =>
  class stdClass {
    var $name = 'test_real';
    var $max_length = 23;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'real';
    var $precision = 0;
    var $scale = 0;
  },
  9 =>
  class stdClass {
    var $name = 'test_double';
    var $max_length = 23;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'real';
    var $precision = 0;
    var $scale = 0;
  },
  10 =>
  array (
    'test_decimal' => '12345678901234567890123456789012.123456',
    'test_numeric' => '12345678901234567890123456.123456789012',
    'test_money' => '123456789012345.1234',
    'test_smallmoney' => 123456.1234,
    'test_bigint' => '12345678901234567890123456789012345678',
    'test_int' => 1234567890,
    'test_smallint' => 12345,
    'test_tinyint' => 123,
    'test_real' => 123456792,
    'test_double' => 123456792,
  ),
  11 =>
  array (
    'test_decimal' => '-12345678901234567890123456789012.123456',
    'test_numeric' => '-12345678901234567890123456.123456789012',
    'test_money' => '-123456789012345.1234',
    'test_smallmoney' => -123456.1234,
    'test_bigint' => '-12345678901234567890123456789012345678',
    'test_int' => -1234567890,
    'test_smallint' => -12345,
    'test_tinyint' => 255,
    'test_real' => -123456792,
    'test_double' => -123456792,
  ),
)</pre>

Actual result:
--------------
Connection OK<br />
DB selected OK<br />
Query OK<br />
Results fetched OK<br />
<pre>array (
  0 =>
  class stdClass {
    var $name = 'test_decimal';
    var $max_length = 40;
    var $column_source = '';
    var $numeric = 2;
    var $type = 'real';
  },
  1 =>
  class stdClass {
    var $name = 'test_numeric';
    var $max_length = 40;
    var $column_source = '';
    var $numeric = 2;
    var $type = 'real';
  },
  2 =>
  class stdClass {
    var $name = 'test_money';
    var $max_length = 23;
    var $column_source = '';
    var $numeric = 2;
    var $type = 'money';
  },
  3 =>
  class stdClass {
    var $name = 'test_smallmoney';
    var $max_length = 23;
    var $column_source = '';
    var $numeric = 2;
    var $type = 'money';
  },
  4 =>
  class stdClass {
    var $name = 'test_bigint';
    var $max_length = 40;
    var $column_source = '';
    var $numeric = 3;
    var $type = 'real';
  },
  5 =>
  class stdClass {
    var $name = 'test_int';
    var $max_length = 11;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'int';
  },
  6 =>
  class stdClass {
    var $name = 'test_smallint';
    var $max_length = 6;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'int';
  },
  7 =>
  class stdClass {
    var $name = 'test_tinyint';
    var $max_length = 3;
    var $column_source = '';
    var $numeric = 1;
    var $type = 'int';
  },
  8 =>
  class stdClass {
    var $name = 'test_real';
    var $max_length = 23;
    var $column_source = '';
    var $numeric = 2;
    var $type = 'real';
  },
  9 =>
  class stdClass {
    var $name = 'test_double';
    var $max_length = 23;
    var $column_source = '';
    var $numeric = 2;
    var $type = 'real';
  },
  10 =>
  array (
    'test_decimal' => 1.2345678901235E+31,
    'test_numeric' => 1.2345678901235E+25,
    'test_money' => 123456789012350,
    'test_smallmoney' => 123456.12,
    'test_bigint' => 1.2345678901235E+37,
    'test_int' => 1234567890,
    'test_smallint' => 12345,
    'test_tinyint' => 123,
    'test_real' => 123456792,
    'test_double' => 123456792,
  ),
  11 =>
  array (
    'test_decimal' => -1.2345678901235E+31,
    'test_numeric' => -1.2345678901235E+25,
    'test_money' => -123456789012350,
    'test_smallmoney' => -123456.12,
    'test_bigint' => -1.2345678901235E+37,
    'test_int' => -1234567890,
    'test_smallint' => -12345,
    'test_tinyint' => 255,
    'test_real' => -123456792,
    'test_double' => -123456792,
  ),
)</pre>

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-07-08 17:08 UTC] daniel dot beet at accuratesoftware dot com
Sorry, forgot the SQL to insert test data:

insert into test 
(test_decimal, test_numeric, test_money, test_bigint, test_int, test_smallmoney, test_smallint, test_tinyint, test_real, test_double) 
values 
(12345678901234567890123456789012.123456,
12345678901234567890123456.123456789012,
123456789012345.1234,
12345678901234567890123456789012345678,
1234567890,
123456.1234,
12345,
123,
123456789.12345679,
123456789.12345679
)

insert into test 
(test_decimal, test_numeric, test_money, test_bigint, test_int, test_smallmoney, test_smallint, test_tinyint, test_real, test_double) 
values 
(-12345678901234567890123456789012.123456,
-12345678901234567890123456.123456789012,
-123456789012345.1234,
-12345678901234567890123456789012345678,
-1234567890,
-123456.1234,
-12345,
255,
-123456789.12345679,
-123456789.12345679
)
 [2004-07-11 13:19 UTC] thekid@php.net
Thank you for your bug report, first of all. 

> /* numerics can overflow real and long types, return as a string */
I would not like to do that in general. I use the convert_scalar_to_number() function (defined in Zend/zend_operators.c) on numerics to achieve the "best" result, which is:
* If the scale is 0 and if it fits into an int, make it an int, make it a float otherwise (this simulates PHP's behaviour when overflowing LONG_MAX)
* If the scale is not 0, make it a float

Now what I forgot was that a float may not be able to hold the number returned, therefore my suggestion would be to check for float overflow and thus add this to the above list:

* If a float overflow is detected, make it a string

How's that?
 [2004-07-11 13:26 UTC] thekid@php.net
Btw: Sybase-CT behaves like PHP itself (due to the afforementioned reason):

$ php -r 'var_dump(12345678901234567890123456789012.123456);'
float(1.23456789012E+31)

(the above result depends on the precision set:

$ php -dprecision=20 -r 'var_dump((float)"12345678901234567890123456789012.123456");'
float(1.234567890123457E+31)

 [2004-07-11 13:56 UTC] thekid@php.net
Have a look at the following:

  http://sitten-polizei.de/php/float_precision.diff

This accomplishes the "best-result"-semantics described in my above comments. I use EG(precision) to check where we overflow - as I understand, this value is the precision to which floats are still "correct".
 [2004-07-11 15:29 UTC] thekid@php.net
Seeing that the PHP version in the Bug report was 4.3.7, I also made a patch against the PHP4 branch.

  http://sitten-polizei.de/php/float_precision-php4.diff
 [2004-07-11 15:35 UTC] thekid@php.net
Testcase now available at:
  http://sitten-polizei.de/php/bug29064.phpt

(place in ext/sybase_ct/tests and run make test TESTS=ext/sybase_ct, be sure to edit ext/sybase_ct/tests/test.inc and supply correct DS_QUERY and credentials there)
 [2004-07-11 18:40 UTC] thekid@php.net
I fixed this bug locally but still have quite an amount of other bugfixes / improvements lying around, which I don't want to separate from this. Maybe you'd like to test the complete patchset available at

  http://sitten-polizei.de/php/sybase_ct.diff (HEAD)
  http://sitten-polizei.de/php/sybase_ct4.diff (PHP_4 branch)

As soon as I've tested the other changes enough, I'll commit everything to CVS (should be in a couple of days)
 [2004-07-12 12:48 UTC] daniel dot beet at accuratesoftware dot com
I have tested the complete patch set against PHP 4, and have found no major issues.

I did notice that MONEY columns only have 2 decimal places fetched back rather than the stored 4 places (this is also the case currently), but (not sure why, something to do with the default formating within Sybase for Money columns maybe?) the patch I did brought back 4 decimal places.

This is not an issue for me, but does not seem quite right.

Just wondering about the consistency of the different database drivers in PHP. MSSQL and OCI8 return numeric/decimals as numeric strings all the time. I would hope that where possible, all the database extensions would return data in a similar way.

Thanks for your help!

Dan
 [2004-07-12 21:51 UTC] thekid@php.net
Hello,

> I have tested the complete patch set against PHP 4, 
> and have found no major issues.

Thanks for testing. I did the same, but with PHP5:)

> I did notice that MONEY columns only have 2 decimal 
> places fetched back rather than the stored 4 places
> (this is also the case currently), but (not sure why, 
> something to do with the default formating within 
> Sybase for Money columns maybe?) 

This has to do with ct_bind() asd CS_CHARTYPE - one would have to use CS_MONEYTYPE and do quite a bit of refactoring (atm, it can be safely assumed every value returned is a char*...)

> the patch I did brought back 4 decimal places.

Are you sure? I wasn't able to do so...

> This is not an issue for me, but does not seem quite 
> right.

You're right. Try to search Google for this, though. There is some discussion on this topic, but they all simply suggest to use convert(numeric(19, 4), ...) on the value if you actually need the two last digits.

That works fine in PHP, too.

> Just wondering about the consistency of the different 
> database drivers in PHP. MSSQL and OCI8 return 
> numeric/decimals as numeric strings all the time. I 
> would hope that where possible, all the database 
> extensions would return data in a similar way.

I think it's wrong to get string(1)"1" when issuing this:

  select 1

All in all, you selected a numeric value. IMHO, PHP should to its best and try to provide you with a numeric value from that (being int(1) in this situation).

> Thanks for your help!

Thanks for yours:)
 [2004-07-12 21:58 UTC] thekid@php.net
On the money datatype:

http://groups.google.com/groups?selm=37445202.6643%40sybase.com
(link might be wrapped)
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Jan 21 21:01:24 2021 UTC