[wp-trac] [WordPress Trac] #36696: WP_Meta_Query can give wrong results when comparing large numbers

[WordPress Trac]

#36696: WP_Meta_Query can give wrong results when comparing large numbers
--------------------------+-----------------------------
 Reporter:  gitlost       |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  3.8.1
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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
 [https://core.trac.wordpress.org/ticket/27272#comment:5 "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.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/36696>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform