[wp-trac] [WordPress Trac] #36652: Use meta_value in a meta query to decide type format in SQL clause

[WordPress Trac]

#36652: Use meta_value in a meta query to decide type format in SQL clause
--------------------------------------------------+------------------
 Reporter:  ericlewis                             |       Owner:
     Type:  enhancement                           |      Status:  new
 Priority:  normal                                |   Milestone:  4.6
Component:  Query                                 |     Version:
 Severity:  normal                                |  Resolution:
 Keywords:  has-patch has-unit-tests 2nd-opinion  |     Focuses:
--------------------------------------------------+------------------
Changes (by boonebgorges):

 * keywords:   => has-patch has-unit-tests 2nd-opinion
 * milestone:  Awaiting Review => 4.6


Comment:

 [attachment:36652.diff] is a first pass at how this might work. High-level
 overview:

 * Takes a conservative approach to determining when 'type' can be
 inferred:
   * `is_int( $value ) || is_float( $value )`, and 'compare' is of the type
 that makes sense with numeric casts (`=`, `!=`, `<`, `<=`, `>`, `>=`)
   * `$value` is an array, and *each* member of the array is a float or an
 integer, and 'compare' is either `BETWEEN` or `NOT BETWEEN`
 * Inferring 'type' is not enough. We also have to infer the proper type
 selectors for `$wpdb->prepare()` - `%d`, `%f`, `%s`

 The tests in [attachment:36652.diff] should spell this out pretty clearly.

 A few questions for consideration.

 a. [attachment:36652.diff] opts to infer `SIGNED` when 'compare' is `=` or
 `!=`. This means that  certain kinds of meta queries common in core are
 `CAST()` to `SIGNED` when they probably don't have to be. This is
 especially true in places where you're storing IDs in meta (see eg
 `wp_get_associated_nav_menu_items()`) - `meta_value = '3'` is the same as
 `CAST(meta_value AS SIGNED) = 3` for these purposes. The `CAST` may be
 slightly slower, and breaks the use of any index that might exist. On the
 flip side, the `CAST` is important if you want mathematical matches (`3 ==
 3.00`). So I'm a bit torn on the expected behavior.
 b. I'm using the term "type selector" for `%s` etc because that's what the
 PHP docs use http://php.net/manual/en/function.sprintf.php. If anyone has
 a better idea ("placeholder"?), shoot.
 c. Possible compat breaks are cases where one is passing numeric values
 but is expecting string behavior. Eg: `"10" < "1"` (alpha sort) but `10 >
 1` (numerical sort). It's only in certain corner cases that this problem
 will ever arise. My guess is that the type-inferring suggested in this
 ticket will cause a number of bugs like this that is far smaller than the
 number of type-related bugs it'll fix. But a second opinion would be
 helpful.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/36652#comment:2>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform