#3064 (ok 3.3.6) It fails to edit rows with positive DECIMAL(16,6)
I have a table with two DECIMAL(16,6) field.
When I browse my table and click on the "edit" icon, PHPMyAdmin fails to load this row because its criterias are wrong : it wraps the DECIMAL field name with CONCAT(). Without CONCAT() it's working perfectly. And if the field has a value of 0.000000 it works too.
Here is the SELECT request generated when I want to edit a row :
SELECT *
FROM `mydb`.`accounts`
WHERE `accounts`.`id` =0
AND `accounts`.`admin_login` = 'dd'
AND `accounts`.`admin_password` = 'dd'
AND CONCAT( `accounts`.`SYNC_LMT` ) = 0.000000
AND `accounts`.`SYNC_LMB` =0
AND CONCAT( `accounts`.`SYNC_DBMT` ) = 1279557821.443215;
=> MySQL returned an empty result set (i.e. zero rows).
Here is my table structure and content :
CREATE TABLE IF NOT EXISTS `accounts` (
`id` int(11) NOT NULL,
`admin_login` varchar(100) CHARACTER SET utf8 NOT NULL,
`admin_password` varchar(100) CHARACTER SET utf8 NOT NULL,
`SYNC_LMT` decimal(16,6) unsigned NOT NULL,
`SYNC_LMB` int(2) unsigned NOT NULL,
`SYNC_DBMT` decimal(16,6) unsigned NOT NULL,
KEY `id` (`id`),
KEY `SYNC_LMT` (`SYNC_LMT`),
KEY `SYNC_LMB` (`SYNC_LMB`),
KEY `SYNC_DBMT` (`SYNC_DBMT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `accounts`
--
INSERT INTO `accounts` (`id`, `admin_login`, `admin_password`, `SYNC_LMT`, `SYNC_LMB`, `SYNC_DBMT`) VALUES
(0, 'dd', 'dd', 0.000000, 0, 1279557821.443215);
What phpMyAdmin and MySQL versions do you use? I can not reproduce the failure (though the CONCAT is indeed there).
I just fixed using CONCAT for decimal fields.
Server version: 5.1.41-3ubuntu12.3
PMA Version information: 3.3.3
When will the fixed release be available ?
Thanks
It will take about month, currently we're just in freeze for 3.3.4, so this fix could not go into it.
Michal meant we are in freeze for 3.3.5.
The patch is available at
http://phpmyadmin.git.sourceforge.net/git/gitweb.cgi?p=phpmyadmin/phpmyadmin;a=commitdiff;h=ef500cd23fd863e62bf810537776b45a4ed76c34
Your patch seems to work with one non-zero DECIMAL column, but not with two.
Can you check ?
Here is my updated table structure and data, the delete icon still fails with this.
--
-- Table structure for table `accounts`
--
CREATE TABLE IF NOT EXISTS `accounts` (
`id` int(11) NOT NULL,
`admin_login` varchar(100) CHARACTER SET utf8 NOT NULL,
`admin_password` varchar(100) CHARACTER SET utf8 NOT NULL,
`SYNC_LMT` decimal(16,6) unsigned NOT NULL,
`SYNC_LMB` int(2) unsigned NOT NULL,
`SYNC_DBMT` decimal(16,6) unsigned NOT NULL,
KEY `id` (`id`),
KEY `SYNC_LMT` (`SYNC_LMT`),
KEY `SYNC_LMB` (`SYNC_LMB`),
KEY `SYNC_DBMT` (`SYNC_DBMT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `accounts`
--
INSERT INTO `accounts` (`id`, `admin_login`, `admin_password`, `SYNC_LMT`, `SYNC_LMB`, `SYNC_DBMT`) VALUES
(3, 'zzz', 'zzz', 1280056069.763544, 1, 1280056069.763560),
(0, 'xxx', 'xxx', 1280056100.672505, 1, 1280056100.672521);
The problem may be at MySQL level.
I made some tests :
SELECT concat('1280056069.763560')=1280056069.763560
=> 0
SELECT '1280056069.763560'=1280056069.763560
=> 0
SELECT concat('1280056069.76356')=1280056069.76356
=> 0
SELECT concat('1280056069.76')=1280056069.76
=> 1
SELECT concat('1280056069')=1280056069
=> 1
SELECT concat('1280056069.763560')='1280056069.763560'
=> 1
I just read chapter 11.2.2 of MySQL manual, it explains the limits of MySQL comparisons when using data of different types.
You should avoid using CONCAT(), and just compare column_name=column_value in the request made by your script.
Here is what the doc said :
"Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:
mysql> SELECT '18015376320243458' = 18015376320243458;
-> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
-> 0
Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding."
In general, for float columns we had to use CONCAT() because without it, comparison failed.
P.S. if you had defined a primary key, it would be used for edit or delete, instead of generating a big condition.
If you want to use CONCAT() you must wrap the row value with quotes (see the last line of my tests), to have string on both sides of the = operator.
Or you use directly the column name and the row value (both DECIMAL).
You're right, fixed generating of condition to treat float/decimal numbers as strings.
The patch is here: http://phpmyadmin.git.sourceforge.net/git/gitweb.cgi?p=phpmyadmin/phpmyadmin;a=commitdiff;h=049fc7fef7548c2ba603196937c6dcaf9ff9bf00
The patch is working great !
Thanks !