Float incorrectly casted to PDO::PARAM_INT in MySQL, should be: PDO::PARAM_STRING

[stemis]

• Laravel Version: 5.5.40
• PHP Version: 7.1
• Database Driver & Version: MariaDB 10.0.24 (MySQL 5.6)

Description:

When issuing the following command:
User::where('rating', '>', 2.5)->get()
The query that actually goes to the database is:
WHERE rating > 2
What should go to the database in order for correct behavior:
WHERE rating > '2.5'

This is caused by the MysqlConnection using PDO::PARAM_INT
According to this wiki article by PHP it should use PDO::PARAM_STRING
https://wiki.php.net/rfc/pdo_float_type

The PDO extension does not have a type to represent floating point values.

The current recommended practice is to use PDO::PARAM_STR

The line that causes the issue is:

framework/src/Illuminate/Database/MySqlConnection.php

Line 80 in 59a0453

is_int($value) || is_float($value) ? PDO::PARAM_INT : PDO::PARAM_STR

It should be edited to:

is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR 

@themsaid this is something you have touched before.
If this is intentional, could you maybe explain why floating point values are not converted to strings, as per the wiki article.

[staudenmeir]

See also #23850.

[stemis]

@staudenmeir Thanks for referencing that. I have a read a couple of more issue topics but am still uncertain about why it is not cast to string.

The one that really matters is: #16063 , which highlights the issues with JSON comparison

However, I think it is safe to say that we should not solve one issue by introducing another.

This should at least be mentioned on the Database/Eloquent documentation page that when using a float in the where query, it should first be casted to string. This should already prevent a lot of people having headaches!

[staudenmeir]

I just tested this on the latest MariaDB 10.3.9 (in Homestead) and it works for me.

[laurencei]

Thanks - I'll close this.

@stemis - feel free to submit a PR to the docs with your idea above.

[ionutantohi]

@stemis do you have PDO::ATTR_EMULATE_PREPARES = true ?

I noticed that this option causes this behaviour. Please check #23850