Wrong equality condition between character varying data type and integer data type

[hahnn]

There is an issue with this scpecific SQL request:

---------------------
[1710086349.154] Error running :
SELECT COUNT(*) FROM wp_comments WHERE comment_author_email = '[email protected]' AND comment_author = 'Un commentateur ou commentatrice WordPress' AND comment_author_url = 'https://fr.wordpress.org/' AND comment_approved = 1
---- converted to ----
SELECT COUNT(*) FROM wp_comments WHERE comment_author_email = '[email protected]' AND comment_author = 'Un commentateur ou commentatrice WordPress' AND comment_author_url = 'https://fr.wordpress.org/' AND comment_approved = 1
----> ERREUR:  l'opérateur n'existe pas : character varying = integer
LINE 1: ...r_url = 'https://fr.wordpress.org/' AND comment_approved = 1
                                                                    ^
HINT:  Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.

The issue is about the column comment_approved in SQL table wp_comments.

This table is like that:

                                                 Table « public.wp_comments »
       Colonne        |            Type             | Collationnement | NULL-able |                 Par défaut
----------------------+-----------------------------+-----------------+-----------+--------------------------------------------
 comment_ID           | bigint                      |                 | not null  | nextval('wp_comments_seq'::text::regclass)
 comment_post_ID      | bigint                      |                 | not null  | '0'::bigint
 comment_author       | text                        |                 | not null  |
 comment_author_email | character varying(100)      |                 | not null  | ''::character varying
 comment_author_url   | character varying(200)      |                 | not null  | ''::character varying
 comment_author_ip    | character varying(100)      |                 | not null  | ''::character varying
 comment_date         | timestamp without time zone |                 | not null  | now()
 comment_date_gmt     | timestamp without time zone |                 | not null  | timezone('gmt'::text, now())
 comment_content      | text                        |                 | not null  |
 comment_karma        | integer                     |                 | not null  | 0
 comment_approved     | character varying(20)       |                 | not null  | '1'::character varying
 comment_agent        | character varying(255)      |                 | not null  | ''::character varying
 comment_type         | character varying(20)       |                 | not null  | 'comment'::character varying
 comment_parent       | bigint                      |                 | not null  | '0'::bigint
 user_id              | bigint                      |                 | not null  | '0'::bigint
Index :
    "wp_comments_pkey" PRIMARY KEY, btree ("comment_ID")
    "wp_comments_comment_approved_date_gmt" btree (comment_approved, comment_date_gmt)
    "wp_comments_comment_author_email" btree (comment_author_email)
    "wp_comments_comment_date_gmt" btree (comment_date_gmt)
    "wp_comments_comment_parent" btree (comment_parent)
    "wp_comments_comment_post_ID" btree ("comment_post_ID")

As you can see, comment_approved is of type character varying(20).

That's why you cannot get an SQL request in which there is this:

AND comment_approved = 1

because it implies comment_approved would be of type integer (or bigint or smallint...)

Because it's a character varying(20), the SQL condition have to be like this instead:

AND comment_approved = '1'

[mattbucci]

Do you know where this query comes from? can you throw an exception and get a stack trace?

All the queries I see from WP core seem to wrap this 1 correctly. To me it seems like a bug in the plugin or WP core which we shouldn't try to fix for them, but instead should report.

https://github.com/WordPress/wordpress-develop/blob/e5a779515cef15166ca05777a7ca2cb212ddce35/src/wp-includes/class-wp-comment-query.php#L568