I have a very simple query from a MariaDB view:
SELECT c.amount, c.discount
FROM factors_view as c
WHERE c.factor_id = 358
When I run this query in HeidiSQL I get this result: amount = 16000, discount = 1200
But in Laravel 5.7 raw query
$result = \DB::select("
SELECT c.amount,c.discount
FROM factors_view as c
WHERE c.factor_id = 358"
);
result: amount = 16000, discount = 0
and when I put the parameter between quotations:
$result = \DB::select("
SELECT c.amount,c.discount
FROM factors_view as c
WHERE c.factor_id = '358'"
);
result: amount = 16000, discount = 1200
Type of c.factor_id is int(10) unsigned
.
This is very strange to me; because the difference is in the query conditions, not the selections!
The output is the same row with zero value on the specific column!
Does anyone know what happened?
this is my query logs for both queries:
1)
query:"select `c`.`amount`, `c`.`discount` from `factors_view` as `c` where `c`.`factor_id` = ?"
bindings:[0:358]
2)
query:"select `c`.`amount`, `c`.`discount` from `factors_view` as `c` where `c`.`factor_id` = ?"
bindings:[0:"358"]
Finally, I found the reason for this troublesome error. To correct the error, I changed the type of factor_id in the view:
CAST(factor_id AS UNSIGNED) AS factor_id
And it's work correctly.
Although the error has been fixed, I still do not realize this behavior. The where condition must affect the rows, not the values of the cells. This may be a doctrine/dbal bug?!