I can’t for the life of me figure out what is going on here, it seems that the PDO where clause is rounding down numbers!!???
I have this:
$gasql = 'SELECT * FROM modx_inventory_gage_long WHERE id = '.$ga.' ;';
$gas = $this->modx->query($gasql);
while($ga = $gas->fetch(PDO::FETCH_ASSOC)){
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga = ' . print_r($ga, TRUE));
$min = $ga['min'];
$max = $ga['max'];
}
$arrayWhere[] = array('ga:>=' => $min);
$arrayWhere[] = array('ga:<=' => $max);
it logs this:
(
[id] => 73
[ga] => 0
[min] => 2.250
[max] => 2.490
[display] => 2.250
)
Which is correct… a little later on:
$criteria->where($arrayWhere);
$criteria->limit($limit, $offset);
$criteria->leftJoin('InventoryReserve','InventoryReserve');
$criteria->prepare();
$this->modx->log(modX::LOG_LEVEL_ERROR, 'search SQL = ' . $criteria->toSQL());
$tags = $this->modx->getCollection('InventoryData', $criteria);
Pretty normal, right? Well this is what I get in the logs:
SELECT <<a bunch of stuff>> FROM `modx_gssi_inventory_data` AS `InventoryData`
LEFT JOIN `modx_gssi_inventory_reserve` `InventoryReserve` ON `InventoryData`.`id` = `InventoryReserve`.`tag_id`
WHERE
( `InventoryData`.`id` >= 1
AND ( `InventoryData`.`product_category` IN ('STAINLESS') AND `InventoryData`.`material_type` IN ('BAR','PIPE','TUBE','SEAMLESS-TUBE','WIRE') )
AND `InventoryData`.`ga` >= 2 AND `InventoryData`.`ga` <= 2
)
ORDER BY gage ASC, width ASC LIMIT 33
!!! notice the inventorydata.ga both have the same value?
am I just staring right at this or is something done wrong?
Seems to have been an issue before:
modxcms:master
← Fi1osof:patch-1
opened 02:21AM - 28 Nov 13 UTC
If datatype float or double, on xPDOQuery->set(array $fields), we got rounded va… lue.
https://github.com/modxcms/xpdo/blob/d4bd6d617b8b755fe7c4784c12ba3e2c1be2a4a6/xpdo/om/xpdoquery.class.php#L236
For example (shopModx Extra required):
print '<pre>';
$q = $this->modx->newQuery('ShopmodxProduct');
$q->command('update');
$q->set(array(
'sm_price' => 154.99,
));
if($q->prepare()){
print $q->toSQL();
}
Maybe a wrong dbtype
/ phptype
combination in your schema.
I tried some combinations but couldn’t reproduce your problem.
I know bizarre right?? The schema looks fine and is nearly identical to another table that does not do this:
<object class="InventoryGageLong" table="inventory_gage_long" extends="xPDOSimpleObject">
<field key="ga" dbtype="int" precision="4" phptype="integer" null="false" />
<field key="min" dbtype="decimal" precision="4,3" phptype="float" null="true" />
<field key="max" dbtype="decimal" precision="4,3" phptype="float" null="true" />
<field key="display" dbtype="decimal" precision="4,3" phptype="float" null="true" />
</object>
As for the database - it looks fine as well, I believe I actually just copied the other (working) table and added the one extra field.
CREATE TABLE `modx_gssi_inventory_gage_long` (
`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`ga` INT(4) NOT NULL,
`min` DECIMAL(4,3) UNSIGNED ZEROFILL NULL DEFAULT NULL,
`max` DECIMAL(4,3) UNSIGNED ZEROFILL NULL DEFAULT NULL,
`display` DECIMAL(4,3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=90
;
Quite mystified at this point!
If I dump my criteria array just before I prepare it and getCollection the ga values are correct.
arrayWhere = Array
(
[0] => Array
(
[id:>=] => 1
)
[1] => Array
(
[product_category:IN] => Array
(
[0] => STAINLESS
)
)
[2] => Array
(
[ga:>=] => 0.017
)
[3] => Array
(
[ga:<=] => 0.021
)
)
try to use phptype string
It was worth a try - but no dice
$gage = $this->modx->getObject('InventoryGageLong',$ga);
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/min-val = ' . $ga .' ' . gettype($gage->get('min')).' '.$gage->get('min'));
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/max-val = ' . $ga .' ' . gettype($gage->get('max')).' '.$gage->get('max'));
settype($gage->get('min'),'double');
settype($gage->get('max'),'double');
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/min-val = ' . $ga .' ' . gettype($gage->get('min')).' '.$gage->get('min'));
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/max-val = ' . $ga .' ' . gettype($gage->get('max')).' '.$gage->get('max'));
$arrayWhere[] = array('ga:>=' => $gage->get('min'));
$arrayWhere[] = array('ga:<=' => $gage->get('max'));
/model/liveinventory.class.php : 299) ga/type/min-val = 54 double 2.25
/model/liveinventory.class.php : 301) ga/type/max-val = 54 double 2.49
/model/liveinventory.class.php : 307) ga/type/min-val = 54 double 2.25
/model/liveinventory.class.php : 309) ga/type/max-val = 54 double 2.49
`AND `InventoryData`.`ga` >= 2 AND `InventoryData`.`ga` <= 2 )
I could reproduce the behavior on my webserver.
It seems the binding
in the xPDOQueryCondition
gets assigned the wrong type. PDO::PARAM_INT
instead of PDO::PARAM_STR
.
The query works if I add 'float'
to this array .
protected $_quotable= array ('string', 'password', 'date', 'datetime', 'timestamp', 'time', 'json', 'array', 'float');
But it is probably not a good idea to change the MODX core code.
This post solved my problem, though as I understand it is a PDO issue and not modx, it will get fixed when PDO gets updated. from the sounds of it PDO will be a dependency for modx 3 and installed via composer…
opened 12:42AM - 24 Oct 17 UTC
closed 10:47AM - 24 Oct 17 UTC
### Summary
Using floats in a 'andCondition' call would transform them as integ… er
### Step to reproduce
create an XPDO query calling andCondtion as this following exemple
```
$query->andCondition(
array(
'location_latitude:>=' => 48.858201356788,
'AND:location_latitude:<=' => 48.861798643212,
)
);
```
### Observed behavior
result is like this one :
` SELECT `id` FROM `match_request` AS `MatchRequest` WHERE ( `MatchRequest`.`location_latitude` >= 48 AND `MatchRequest`.`location_latitude` <= 48)`
### Expected behavior
`SELECT `id` FROM `match_request` AS `MatchRequest` WHERE ( `MatchRequest`.`location_latitude` >= 48.858201356788 AND `MatchRequest`.`location_latitude` <= 48.861798643212)`
### Suggestion for solving the problem
Perhaps I missed something but...
The problem was detected in the parseBindings function using the 'quote' function... called line 2704 in core/xpdo/xpdo.class.php
```
if ($type > 0) {
$v= $this->quote($v, $type);
```
Whatever type (int, float, ...) , you call 'quote'...
The problem sounds to be solved by changing line 2560 in core/xpdo/xpdo.class.php in function quote
```
case PDO::PARAM_INT:
$quoted = trim($quoted);
//$quoted = (integer) trim($quoted, "'");//removed
$quoted = trim($quoted, "'");//added
break;
```
Actually, it seems like float are seen like "param_int" (versus param_str) all along the processing...
so it should not be cast here as values in float could arrive here...
halftrainedharry:
I could reproduce the behavior on my webserver.
It seems the binding
in the xPDOQueryCondition
gets assigned the wrong type. PDO::PARAM_INT
instead of PDO::PARAM_STR
.
The query works if I add 'float'
to this array .
Copy to clipboard
protected $_quotable= array ('string', 'password', 'date', 'datetime', 'timestamp', 'time', 'json', 'array', 'float');
But it is probably not a good idea to change the MODX core code.
This IS a change that needs to be made to the xPDO core code. See recent discussion at Round decimal by Fi1osof · Pull Request #25 · modxcms/xpdo · GitHub for more information. It seems this issue has sat unresolved and unintentionally ignored for some time.
1 Like
By the way, setting the phptype to string in the xpdo schema can temporarily work around the problem, but may prevent the use of other float-specific features in xpdo for that field.
1 Like