IN meta_query with empty array as meta_value results in invalid database query

[batmoo]

If you do an IN meta_query and pass in an empty array to the value, the INNER JOIN clause for the postmeta table isn't added, which results in an invalid query:

new WP_Query( array(
       'meta_query' => array( array( 'key' => 'abc', 'value' => array(), 'compare' => 'IN' ) )
) );

This results in an error like so:

WordPress database error: [Unknown column 'wp_postmeta.meta_key' in 'where clause']
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 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 = 'abc' ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5

[nacin]

Reproduced in 3.4.

[SergeyBiryukov]

Caused by line 520 of [17746].

[wonderboymusic]

We need to add WP_Meta_Query::clean_query maybe - patch passes Unit Tests

[wonderboymusic]

This patch still applies cleanly, works, etc (I have no recollection of actually writing this patch)

[batmoo]

Another example, this time when you combine a 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' with an empty value and another valid IN:

new WP_Query( array(
       'meta_query' => array(
              'relation' => 'AND',
              array( 'key' => 'key1', 'value' => array( 'value1', 'value1b' ), 'compare' => 'IN' ),
              array( 'key' => 'key2', 'value' => array(), 'compare' => 'NOT IN' )
       )
) );

results in:

SELECT SQL_CALC_FOUND_ROWS  wp_15797879_posts.ID FROM wp_15797879_posts  INNER JOIN wp_15797879_postmeta ON (wp_15797879_posts.ID = wp_15797879_postmeta.post_id) WHERE 1=1  AND wp_15797879_posts.post_type = \'post\' AND (wp_15797879_posts.post_status = \'publish\') AND ( (wp_15797879_postmeta.meta_key = 'key1' AND CAST(wp_15797879_postmeta.meta_value AS CHAR) IN (\'value1\',\'value1b\'))
AND mt1.meta_key = 'key2' ) GROUP BY wp_15797879_posts.ID ORDER BY wp_15797879_posts.post_date DESC LIMIT 0, 10

The JOIN for mt1 is unset and the meta_value ignored (​http://core.trac.wordpress.org/browser/trunk/wp-includes/meta.php?rev=23961#L789) resulting in an invalid query and an SQL error:

Unknown column 'mt1.meta_key' in 'where clause'

[westi]

In 1276/tests:

Meta Queries: Add a test case to validate that when generating the meta query SQL for an empty value array we don't generate invalid SQL.

See #22096

[westi]

@wonderboymusic I just came across an incident of this again and wrote the above unit test.

Is this ready to commit or are there more test cases we should add here?

[wonderboymusic]

the patch still applies cleanly, someone else can decide whether to punt

[markjaquith]

In 24563:

Prevent invalid queries in certain empty-array-passing meta_query cases.

fixes #22096. props wonderboymusic.

[DrewAPicture]

notice

[DrewAPicture]

22096.diff​ fixes an undefined index notice for value following [24563]. This is at r24579.

[SergeyBiryukov]

In 24580:

Avoid an undefined index notice. props DrewAPicture. fixes #22096.