Decimal and numeric options in meta_query do not produce correct MYSQL for floating point numbers comparisons

[ericlewis]

If you have a custom post type (shoes) that has floating point numbers (shoe size) as post meta, querying against this post meta with a specific decimal value ( >10.5 ) does not work properly because of the way the values are cast out of the database, and will produce surprising results.

Our support for CAST needs work. Here is what you have to do:

$stuff = new WP_Query( array(
	'meta_query' => array(
		array(
			'key' => '_edit_last',
			'type' => 'decimal',
			'compare' => '=',
			'value' => '10.5'
		)
	)
) );

echo $stuff->request;
// SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  
// INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_type = 'post' 
// AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND 
// ( (wp_postmeta.meta_key = '_edit_last' AND CAST(wp_postmeta.meta_value AS DECIMAL) = '10.5') ) 
// GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
exit();

We should probably be casting the value (10.5) so the query doesn't end up = (string) '10.5'. We should also support FLOAT.

Replying to wonderboymusic:

We should probably be casting the value (10.5) so the query doesn't end up = (string) '10.5'. We should also support FLOAT.

Cast 10.5 to 10.5 is meaningless. The value is already a float. The problem seems to be that the value is converted to a string when it shouldn't.

We'll also need to be able to specify the precision and scale for fixed-point data types. I suggest that we use the convention of defining the precision and scale in the 'type' field, and explode the string for parsing when we need to.

$stuff = new WP_Query( array(
	'meta_query' => array(
		array(
			'key' => '_edit_last',
			'type' => 'decimal(3, 1)',
			'compare' => '=',
			'value' => 10.5
		)
	)
) );

add support for precision and scale for decimal

Pervious patch was created in the subdirectory, here's a proper one.