Make WordPress Core

Opened 13 months ago

Last modified 7 months ago

#58200 new defect (bug)

Empty result when meta_query is supposed to select date less than certain date

Reported by: oglekler's profile oglekler Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Query Keywords:
Focuses: Cc:

Description

	$args = [
			'post_type'      => 'page',
			'posts_per_page' => '10',
			'paged'          => '1',
		'meta_query'     => [
			'event' => [
				'key'     => 'event_end',
				'value'   => date( 'Y-m-d H:i:s' ),
				'compare' => '<',
				'type'    => 'DATETIME'
			],
		];

Request will be:

			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_postmeta.meta_key = 'event_end' AND CAST(wp_postmeta.meta_value AS DATETIME) < '2023-04-26 17:12:15' )
) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish'))
			GROUP BY wp_posts.ID
			ORDER BY CAST(wp_postmeta.meta_value AS DATETIME) DESC
			LIMIT 0, 10

And as a result, empty values will be among the result.

How to check:

  1. Add to certain post_type post_meta event_end with empty value.
  2. Create new WP_Query with this post_type and meta_query where event_end is less than current date (look above).
  3. Get post and check that selection has empty value.

Expected behaviour is to get all posts, where event_end post_meta has a type of DATETIME and not empty.

Change History (2)

This ticket was mentioned in Slack in #core by oglekler. View the logs.


13 months ago

#2 @domainsupport
7 months ago

This sounds like something we have noticed that has changed in v6.4.

Prior to v6.4 an empty expiry_date meta value would not have been included in the results of the following meta_query ...

<?php
'meta_query' => array(
        array(
                'key' => 'expiry_date',
                'compare' => '<',
                'value' => date('Y-m-d H:i:s'),
                'type' => 'DATETIME'
        )
)

... but now you need to use the following meta_query so as not to include empty meta in the results (we've also added the EXISTS condition for "belt and braces" ...

<?php
'meta_query' => array(
        array(
                'key' => 'expiry_date',
                'compare' => 'EXISTS'
        ),
        array(
                'key' => 'expiry_date',
                'value'   => array(''),
                'compare' => 'NOT IN'
        ),
        array(
                'key' => 'expiry_date',
                'compare' => '<',
                'value' => date('Y-m-d H:i:s'),
                'type' => 'DATETIME'
        )
)
Note: See TracTickets for help on using tickets.