WP_Meta_Query can give wrong results when comparing large numbers

[gitlost]

This is a follow on from #27272.

Using quoted numbers can trigger MySQL to do floating-point type conversion (last two points in the first bullet list in ​http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html):

• If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
• In all other cases, the arguments are compared as floating-point (real) numbers.

Currently number literals are quoted in WP_Meta_Query, whereas the LONGTEXT meta_value column is cast to the passed-in meta type. Thus when a number literal (quoted string) is compared to the meta_value column (cast to SIGNED/UNSIGNED meta type), floating-point type conversion is evoked. If the numbers involved are larger than the floating-point precision (53 bits), then incorrect comparisons can occur.

You can see this happening in the mysql client by using the 54 bit numbers 9007199254740992 (0x20 0000 0000 0000) and 9007199254740993 (0x20 0000 0000 0001):

select '9007199254740992'+0.0, '9007199254740993'+0.0, '9007199254740992'+0.0 < '9007199254740993'+0.0;

which will return:

+------------------------+------------------------+-------------------------------------------------+
| '9007199254740992'+0.0 | '9007199254740993'+0.0 | '9007199254740992'+0.0 < '9007199254740993'+0.0 |
+------------------------+------------------------+-------------------------------------------------+
|   9.007199254740992e15 |   9.007199254740992e15 |                                               0 |
+------------------------+------------------------+-------------------------------------------------+

(Note the results are system dependent.)

A way around this is to not quote number literals, as in the proposed patch, based on @wonderboymusic's 27272.2.diff. It's been switched (ahem) to an if-elseif-else statement to easily allow cases like DECIMAL(10,2) etc to be added. (Note the patch attached here also fixes a regression I introduced in previous proposed patches on the #27272 ticket re casting on LIKE/NOT LIKE.)

This issue relates to #36652 (using the PHP type of the meta value to determine cast), and the proposed patch should facilitate it.

This issue also relates to #36625 (don't bother CHAR casting meta_value column), and the proposed patch shouldn't impact on a fix for that.

[gitlost]

Patch based on wonderboy's 27272.2.diff.

[gitlost]

Unit tests.