Campaign Segments which use custom fields causing error 500 with craft 3.7.7

[bliswebagency]

Describe the bug

After creating a segment which references a custom field, then going to the segment listing, no segments are shown and an error 500 is returned. Ajax contains response referencing that custom field:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_newsletter1' in

Issue seems to be related to columnSuffix added to field column names in the content table in craft 3.7 and above. craftcms/cms#6922

Modifying the query to use the column name with the columnSuffix (a unique ID) appended does not produce the column not found error. eg. field_newsletter1_columnSuffix as opposed to field_newsletter1

To reproduce

Steps to reproduce the behaviour:

1. Create segment, using custom field, in craft 3.7.7
2. Go to segment listing
3. No segments shown, only error message in ajax response

Expected behaviour

Created segments appearing in listing

Code

Error is from this query:

SELECT `elements`.`id` FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId` FROM `elements` `elements` INNER JOIN `campaign_contacts` `campaign_contacts` ON `campaign_contacts`.`id` = `elements`.`id` INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id` INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`) WHERE (`field_newsletter1` = '1') AND (`elements_sites`.`siteId`=1) AND (`elements`.`archived`=FALSE) AND ((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) ORDER BY `elements`.`dateCreated` DESC) `subquery` INNER JOIN `campaign_contacts` `campaign_contacts` ON `campaign_contacts`.`id` = `subquery`.`elementsId` INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId` INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId` INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId` ORDER BY `elements`.`dateCreated` DESC

Versions

• Plugin version: 1.20.1
• Craft version: 3.7.7

[bencroker]

Thanks for reporting this! Fixed in fc1e4ed and released in 1.20.2.

FYI you may need to resave any segments that were causing issues.

[bliswebagency]

Thank you! We'll run the update now and see how it goes :)

[bliswebagency]

We can confirm the error 500 is gone now and we can see the segments. Thanks again.