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.

[wonderboymusic]

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.

[knutsp]

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.

[ericlewis]

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
		)
	)
) );

[ericlewis]

add support for precision and scale for decimal

[ericlewis]

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

[nacin]

Longstanding issue. Punting to 3.7. Unit tests would be great.

[wonderboymusic]

#19802 was marked as a duplicate.

[wonderboymusic]

#19802 was similar - we also need to fix ORDER logic to CAST properly

[ericlewis]

Replying to wonderboymusic:

we also need to fix ORDER logic to CAST properly

This is already covered by the 'orderby' => 'meta_value_num' flag, no?

[nacin]

Moving to future pending unit tests.

[ericlewis]

attachment:23033.2.diff​ is an update for staleness, and also adds in NUMERIC as a supported meta type, which mimics DECIMAL.

[ericlewis]

attachment:23033.3.diff​ fixes a regex error.

[ericlewis]

In attachment:23033.unit-tests.diff​, test for:

• all comparison operators in a WP_Query object with various meta_query settings
• test_get_cast_for_type() with all the possible meta types

[nacin]

What happens when I do DECIMAL(a), DECIMAL( 10, 5 ), DECIMAL(a, b), etc? Ideally some basic tests beyond "ANYTHING ELSE" can somewhat demonstrate that this regex works as desired.

Finally, the patches can/should be combined — that way src/ and tests/ can be applied and committed together.

[ericlewis]

Replying to nacin:

What happens when I do DECIMAL(a), DECIMAL( 10, 5 ), DECIMAL(a, b), etc?

How loose of a syntax should we allow for spacing here? Any amount of spaces? DECIMAL( 10 , 5 )?

[nacin]

Replying to ericlewis:

Replying to nacin:

What happens when I do DECIMAL(a), DECIMAL( 10, 5 ), DECIMAL(a, b), etc?

How loose of a syntax should we allow for spacing here? Any amount of spaces? DECIMAL( 10 , 5 )?

I don't care if DECIMAL( 10, 5 ) or DECIMAL(10,5) is valid or not, I just wanted a unit test demonstrating them one way or the other. :-)

[ericlewis]

Sure thing. Added more assertions and combined unit tests and core patch in attachment:23033.4.diff​.

[wonderboymusic]

In 26055:

Produce proper CAST for DECIMAL and NUMERIC in Meta Query. Adds a bunch of unit tests.

Props ericlewis.
Fixes #23033.