Changeset 53901 for trunk/src/wp-includes/class-wp-meta-query.php

Timestamp:

08/17/2022 01:58:22 PM (16 months ago)

Author:

SergeyBiryukov

Message:

Query: Cast the meta key to BINARY for case-sensitive key comparisons in WP_Meta_Query.

This addresses an error on MySQL 8.0.22 or later:

Character set 'utf8mb4_unicode_520_ci' cannot be used in conjunction with 'binary' in call to regexp_like

From the ​MySQL 8.0.22 changelog:

Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (​Bug #98951, ​Bug #98950)

WordPress meta queries use the BINARY data type for case-sensitive meta key comparisons using regular expressions. By explicitly casting the meta key to BINARY, we can make sure the values being compared use the same character set and produce consistent results.

The change is covered by existing meta query unit tests: three tests which previously failed on MySQL 8.0.22 or later now pass.

References:

• ​MySQL 8.0.22 changelog
• ​Bug #104387 CHARACTER_SET_MISMATCH issue with regex comparisons

Follow-up to [46188].

Fixes #51740.

File:

• trunk/src/wp-includes/class-wp-meta-query.php (modified) (2 diffs)

trunk/src/wp-includes/class-wp-meta-query.php

@@ -680 +680 @@
                         $operator = $meta_compare_key;
                         if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) {
-                            $cast = 'BINARY';
+                            $cast     = 'BINARY';
+                            $meta_key = "CAST($alias.meta_key AS BINARY)";
                         } else {
-                            $cast = '';
+                            $cast     = '';
+                            $meta_key = "$alias.meta_key";
                         }
-                        $where = $wpdb->prepare( "$alias.meta_key $operator $cast %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+                        $where = $wpdb->prepare( "$meta_key $operator $cast %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                         break;
 
@@ -706 +708 @@
                         $operator = $meta_compare_key;
                         if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) {
-                            $cast = 'BINARY';
+                            $cast     = 'BINARY';
+                            $meta_key = "CAST($subquery_alias.meta_key AS BINARY)";
                         } else {
-                            $cast = '';
+                            $cast     = '';
+                            $meta_key = "$subquery_alias.meta_key";
                         }
 
-                        $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key REGEXP $cast %s " . $meta_compare_string_end;
+                        $meta_compare_string = $meta_compare_string_start . "AND $meta_key REGEXP $cast %s " . $meta_compare_string_end;
                         $where               = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                         break;