[wp-trac] [WordPress Trac] #36169: Incorrect results when combining a NOT EXISTS meta query with a comparison.
WordPress Trac
noreply at wordpress.org
Tue Mar 8 11:05:08 UTC 2016
#36169: Incorrect results when combining a NOT EXISTS meta query with a comparison.
--------------------------+------------------------------
Reporter: mikejolley | Owner:
Type: defect (bug) | Status: new
Priority: normal | Milestone: Awaiting Review
Component: Query | Version: 4.4.2
Severity: normal | Resolution:
Keywords: | Focuses:
--------------------------+------------------------------
Changes (by dd32):
* component: General => Query
Old description:
> This is the meta_query being used in my code:
>
> {{{
> ["meta_query"]=>
> array(3) {
> [0]=>
> array(3) {
> ["relation"]=>
> string(2) "OR"
> [0]=>
> array(3) {
> ["key"]=>
> string(27) "attribute_calligraphy-style"
> ["value"]=>
> array(2) {
> [0]=>
> string(0) ""
> [1]=>
> string(5) "Julia"
> }
> ["compare"]=>
> string(2) "IN"
> }
> [1]=>
> array(3) {
> ["key"]=>
> string(27) "attribute_calligraphy-style"
> ["compare"]=>
> string(10) "NOT EXISTS"
> }
> }
> [1]=>
> array(3) {
> ["relation"]=>
> string(2) "OR"
> [0]=>
> array(3) {
> ["key"]=>
> string(17) "attribute_product"
> ["value"]=>
> array(2) {
> [0]=>
> string(0) ""
> [1]=>
> string(33) "Outer + Inner + Return + Response"
> }
> ["compare"]=>
> string(2) "IN"
> }
> [1]=>
> array(3) {
> ["key"]=>
> string(17) "attribute_product"
> ["compare"]=>
> string(10) "NOT EXISTS"
> }
> }
> [2]=>
> array(3) {
> ["relation"]=>
> string(2) "OR"
> [0]=>
> array(3) {
> ["key"]=>
> string(13) "attribute_ink"
> ["value"]=>
> array(2) {
> [0]=>
> string(0) ""
> [1]=>
> string(38) "Custom (Including all metallic colors)"
> }
> ["compare"]=>
> string(2) "IN"
> }
> [1]=>
> array(3) {
> ["key"]=>
> string(13) "attribute_ink"
> ["compare"]=>
> string(10) "NOT EXISTS"
> }
> }
> }}}
>
> So for each meta key, e.g. `attribute_calligraphy-style`, we want to
> allow '', some value, OR where the meta key does not exist.
>
> Looking at the SQL I had this:
>
> {{{
> SELECT wp_oiwx_posts.ID FROM wp_oiwx_posts LEFT JOIN wp_oiwx_postmeta
> ON ( wp_oiwx_posts.ID = wp_oiwx_postmeta.post_id ) LEFT JOIN
> wp_oiwx_postmeta AS mt1 ON (wp_oiwx_posts.ID = mt1.post_id AND
> mt1.meta_key = 'attribute_calligraphy-style' ) LEFT JOIN
> wp_oiwx_postmeta AS mt2 ON ( wp_oiwx_posts.ID = mt2.post_id ) LEFT JOIN
> wp_oiwx_postmeta AS mt3 ON (wp_oiwx_posts.ID = mt3.post_id AND
> mt3.meta_key = 'attribute_product' ) LEFT JOIN wp_oiwx_postmeta AS mt4
> ON ( wp_oiwx_posts.ID = mt4.post_id ) LEFT JOIN wp_oiwx_postmeta AS mt5
> ON (wp_oiwx_posts.ID = mt5.post_id AND mt5.meta_key = 'attribute_ink' )
> WHERE 1=1 AND wp_oiwx_posts.post_parent = 4790 AND (
> (
> ( wp_oiwx_postmeta.meta_key = 'attribute_calligraphy-style' AND
> CAST(wp_oiwx_postmeta.meta_value AS CHAR) IN ('','Julia') )
> OR
> mt1.post_id IS NULL
> )
> AND
> (
> ( mt2.meta_key = 'attribute_product' AND CAST(mt2.meta_value AS CHAR)
> IN ('','Outer + Inner + Return + Response') )
> OR
> mt3.post_id IS NULL
> )
> AND
> (
> ( mt4.meta_key = 'attribute_ink' AND CAST(mt4.meta_value AS CHAR) IN
> ('','Custom (Including all metallic colors)') )
> OR
> mt5.post_id IS NULL
> )
> ) AND wp_oiwx_posts.post_type = 'product_variation' AND
> ((wp_oiwx_posts.post_status = 'publish')) GROUP BY wp_oiwx_posts.ID ORDER
> BY wp_oiwx_posts.post_date DESC LIMIT 0, 5
> }}}
>
> Notice that the 2nd meta query uses mt3.post_id instead of mt2, and the
> 3rd query uses mt5.post_id instead of mt3.
>
> I struggled to see what breaks this, but sufficed to say, no results come
> back for the above query. If I remove the 'not exists' clause, my query
> returns results without problem.
>
> I'm going to find a different workaround for the above, but thought this
> should be logged in case the generated query is wrong.
New description:
This is the meta_query being used in my code:
{{{
["meta_query"]=>
array(3) {
[0]=>
array(3) {
["relation"]=>
string(2) "OR"
[0]=>
array(3) {
["key"]=>
string(27) "attribute_calligraphy-style"
["value"]=>
array(2) {
[0]=>
string(0) ""
[1]=>
string(5) "Julia"
}
["compare"]=>
string(2) "IN"
}
[1]=>
array(3) {
["key"]=>
string(27) "attribute_calligraphy-style"
["compare"]=>
string(10) "NOT EXISTS"
}
}
[1]=>
array(3) {
["relation"]=>
string(2) "OR"
[0]=>
array(3) {
["key"]=>
string(17) "attribute_product"
["value"]=>
array(2) {
[0]=>
string(0) ""
[1]=>
string(33) "Outer + Inner + Return + Response"
}
["compare"]=>
string(2) "IN"
}
[1]=>
array(3) {
["key"]=>
string(17) "attribute_product"
["compare"]=>
string(10) "NOT EXISTS"
}
}
[2]=>
array(3) {
["relation"]=>
string(2) "OR"
[0]=>
array(3) {
["key"]=>
string(13) "attribute_ink"
["value"]=>
array(2) {
[0]=>
string(0) ""
[1]=>
string(38) "Custom (Including all metallic colors)"
}
["compare"]=>
string(2) "IN"
}
[1]=>
array(3) {
["key"]=>
string(13) "attribute_ink"
["compare"]=>
string(10) "NOT EXISTS"
}
}
}}}
So for each meta key, e.g. `attribute_calligraphy-style`, we want to allow
'', some value, OR where the meta key does not exist.
Looking at the SQL I had this:
{{{
SELECT wp_oiwx_posts.ID FROM wp_oiwx_posts
LEFT JOIN wp_oiwx_postmeta ON ( wp_oiwx_posts.ID =
wp_oiwx_postmeta.post_id )
LEFT JOIN wp_oiwx_postmeta AS mt1 ON (wp_oiwx_posts.ID = mt1.post_id AND
mt1.meta_key = 'attribute_calligraphy-style' )
LEFT JOIN wp_oiwx_postmeta AS mt2 ON ( wp_oiwx_posts.ID = mt2.post_id )
LEFT JOIN wp_oiwx_postmeta AS mt3 ON (wp_oiwx_posts.ID = mt3.post_id AND
mt3.meta_key = 'attribute_product' )
LEFT JOIN wp_oiwx_postmeta AS mt4 ON ( wp_oiwx_posts.ID = mt4.post_id )
LEFT JOIN wp_oiwx_postmeta AS mt5 ON (wp_oiwx_posts.ID = mt5.post_id AND
mt5.meta_key = 'attribute_ink' )
WHERE 1=1 AND wp_oiwx_posts.post_parent = 4790 AND (
(
( wp_oiwx_postmeta.meta_key = 'attribute_calligraphy-style' AND
CAST(wp_oiwx_postmeta.meta_value AS CHAR) IN ('','Julia') )
OR
mt1.post_id IS NULL
)
AND
(
( mt2.meta_key = 'attribute_product' AND CAST(mt2.meta_value AS CHAR)
IN ('','Outer + Inner + Return + Response') )
OR
mt3.post_id IS NULL
)
AND
(
( mt4.meta_key = 'attribute_ink' AND CAST(mt4.meta_value AS CHAR) IN
('','Custom (Including all metallic colors)') )
OR
mt5.post_id IS NULL
)
) AND wp_oiwx_posts.post_type = 'product_variation' AND
((wp_oiwx_posts.post_status = 'publish')) GROUP BY wp_oiwx_posts.ID ORDER
BY wp_oiwx_posts.post_date DESC LIMIT 0, 5
}}}
Notice that the 2nd meta query uses mt3.post_id instead of mt2, and the
3rd query uses mt5.post_id instead of mt3.
I struggled to see what breaks this, but sufficed to say, no results come
back for the above query. If I remove the 'not exists' clause, my query
returns results without problem.
I'm going to find a different workaround for the above, but thought this
should be logged in case the generated query is wrong.
--
Comment:
Just broke up the JOIN syntax into multiple lines for readability.
--
Ticket URL: <https://core.trac.wordpress.org/ticket/36169#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
More information about the wp-trac
mailing list