Search code examples
phpmysqllaravelmariadbdbal

Laravel returns zero instead of the actual cell value when a parameter passed as an integer


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"]

Solution

  • 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?!