#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 / HEAD Assigned To: thekid New Comment: On the money datatype: http://groups.google.com/groups?selm=37445202.6643%40sybase.com (link might be wrapped) Previous Comments: [2004-07-12 21:51:02] [EMAIL PROTECTED] 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 12:48:54] 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-11 18:40:53] [EMAIL PROTECTED] 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-11 15:35:00] [EMAIL PROTECTED] 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 15:29:26] [EMAIL PROTECTED] 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 The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/29064 -- Edit this bug report at http://bugs.php.net/?id=29064&edit=1
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 / HEAD Assigned To: thekid New Comment: 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:) Previous Comments: [2004-07-12 12:48:54] 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-11 18:40:53] [EMAIL PROTECTED] 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-11 15:35:00] [EMAIL PROTECTED] 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 15:29:26] [EMAIL PROTECTED] 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 13:56:26] [EMAIL PROTECTED] 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". The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/29064 -- Edit this bug report at http://bugs.php.net/?id=29064&edit=1
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 User updated by: daniel dot beet at accuratesoftware dot com Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 / HEAD Assigned To: thekid New Comment: 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 Previous Comments: [2004-07-11 18:40:53] [EMAIL PROTECTED] 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-11 15:35:00] [EMAIL PROTECTED] 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 15:29:26] [EMAIL PROTECTED] 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 13:56:26] [EMAIL PROTECTED] 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 13:26:01] [EMAIL PROTECTED] 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) The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/29064 -- Edit this bug report at http://bugs.php.net/?id=29064&edit=1
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 / HEAD Assigned To: thekid New Comment: 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) Previous Comments: [2004-07-11 15:35:00] [EMAIL PROTECTED] 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 15:29:26] [EMAIL PROTECTED] 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 13:56:26] [EMAIL PROTECTED] 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 13:26:01] [EMAIL PROTECTED] 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:19:19] [EMAIL PROTECTED] 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? The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/29064 -- Edit this bug report at http://bugs.php.net/?id=29064&edit=1
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server -PHP Version: 4.3.7 +PHP Version: 4.3.7 / HEAD Assigned To: thekid New Comment: 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) Previous Comments: [2004-07-11 15:29:26] [EMAIL PROTECTED] 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 13:56:26] [EMAIL PROTECTED] 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 13:26:01] [EMAIL PROTECTED] 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:19:19] [EMAIL PROTECTED] 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-08 17:08:17] 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 ) The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/29064 -- Edit this bug report at http://bugs.php.net/?id=29064&edit=1
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 Assigned To: thekid New Comment: 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 Previous Comments: [2004-07-11 13:56:26] [EMAIL PROTECTED] 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 13:26:01] [EMAIL PROTECTED] 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:19:19] [EMAIL PROTECTED] 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-08 17:08:17] 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-08 17:06:18] 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; --- > /* num
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 Assigned To: thekid New Comment: 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". Previous Comments: [2004-07-11 13:26:01] [EMAIL PROTECTED] 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:19:19] [EMAIL PROTECTED] 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-08 17:08:17] 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-08 17:06:18] 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->num
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 Assigned To: thekid New Comment: 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) Previous Comments: [2004-07-11 13:19:19] [EMAIL PROTECTED] 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-08 17:08:17] 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-08 17:06:18] 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_NUM
#29064 [Asn]: Exact numeric/decimal/money datatypes lose precision
ID: 29064 Updated by: [EMAIL PROTECTED] Reported By: daniel dot beet at accuratesoftware dot com Status: Assigned Bug Type: Sybase-ct (ctlib) related Operating System: Windows 2000 Server PHP Version: 4.3.7 Assigned To: thekid New Comment: 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? Previous Comments: [2004-07-08 17:08:17] 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-08 17:06:18] 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->
