WordPress.org

Make WordPress Core

#22096 closed defect (bug) (fixed)

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

Reported by: batmoo Owned by: wonderboymusic
Milestone: 3.6 Priority: normal
Severity: normal Version: 3.1.3
Component: Query Keywords: has-patch
Focuses: Cc:

Description

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

Attachments (2)

meta-value-empty-array.diff (1.0 KB) - added by wonderboymusic 21 months ago.
22096.diff (518 bytes) - added by DrewAPicture 12 months ago.
notice

Download all attachments as: .zip

Change History (15)

comment:1 ethitter22 months ago

  • Cc erick@… added

comment:2 nacin22 months ago

  • Component changed from General to Query
  • Version set to 3.4

Reproduced in 3.4.

comment:3 SergeyBiryukov21 months ago

  • Version changed from 3.4 to 3.1.3

Caused by line 520 of [17746].

comment:4 wonderboymusic21 months ago

  • Keywords has-patch added
  • Milestone changed from Awaiting Review to Future Release
  • Owner set to wonderboymusic
  • Status changed from new to accepted

We need to add WP_Meta_query::clean_query maybe

Version 0, edited 21 months ago by wonderboymusic (next)

comment:5 wonderboymusic18 months ago

  • Milestone changed from Future Release to 3.6

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

comment:6 batmoo15 months ago

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'

comment:7 johnjamesjacoby15 months ago

  • Cc johnjamesjacoby added

comment:8 westi15 months ago

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

comment:9 westi15 months ago

@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?

comment:10 wonderboymusic13 months ago

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

comment:11 markjaquith12 months ago

  • Resolution set to fixed
  • Status changed from accepted to closed

In 24563:

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

fixes #22096. props wonderboymusic.

DrewAPicture12 months ago

notice

comment:12 DrewAPicture12 months ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

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

comment:13 SergeyBiryukov12 months ago

  • Resolution set to fixed
  • Status changed from reopened to closed

In 24580:

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

Note: See TracTickets for help on using tickets.