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

Question

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

Can you share the raw queries produced by each of the above? (As described at https://stackoverflow.com/questions/41140975/laravel-eloquent-display-query-log) — apokryfos, May 07 '20 at 10:18
You are selecting the factor_id from the view, Can you select it from the source table without surrounding it with single quote? Does it give you the same results? — Mohammad.Kaab, May 07 '20 at 10:20
I don't know why but despite the fact you've shared a raw unparameterised query, the query logs show a parameterised query. This might explain the discrepancy since there might be an unexpected cast happening somewhere — apokryfos, May 07 '20 at 14:16

score 0 · Accepted Answer · answered May 07 '20 at 20:40

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

score 0 · Answer 2 · answered May 24 '20 at 20:53

Please provide SHOW CREATE TABLE.

I guess that factor_id is VARCHAR, when it should be INT.

int_col = 123     -- can use index
int_col = '123'   -- can use index
char_col = 123    -- 123 dominates; must check all rows, converting char_col to int
char_col = '123'  -- can use index

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

2 Answers2