Make WordPress Core

Opened 11 years ago

Closed 11 years ago

#22096 closed defect (bug) (fixed)

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

Reported by: batmoo's profile batmoo Owned by: wonderboymusic's profile 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 11 years ago.
22096.diff (518 bytes) - added by DrewAPicture 11 years ago.
notice

Download all attachments as: .zip

Change History (15)

#1 @ethitter
11 years ago

  • Cc erick@… added

#2 @nacin
11 years ago

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

Reproduced in 3.4.

#3 @SergeyBiryukov
11 years ago

  • Version changed from 3.4 to 3.1.3

Caused by line 520 of [17746].

#4 @wonderboymusic
11 years 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 - patch passes Unit Tests

Last edited 11 years ago by wonderboymusic (previous) (diff)

#5 @wonderboymusic
11 years 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)

#6 @batmoo
11 years 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'

#7 @johnjamesjacoby
11 years ago

  • Cc johnjamesjacoby added

#8 @westi
11 years 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

#9 @westi
11 years 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?

#10 @wonderboymusic
11 years ago

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

#11 @markjaquith
11 years 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.

@DrewAPicture
11 years ago

notice

#12 @DrewAPicture
11 years 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.

#13 @SergeyBiryukov
11 years 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.