Recalculate Conversation.CountParticipants results in invalid SQL due to ambigious subquery.

[x00]

Describe the bug
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(uc.ConversationID) from GDN_UserConversation uc where uc.Conversati' at line 2"

Recalculate Conversation.CountParticipants result in invalid SQL
CountParticipants option is using a sub query which refers to c.CountParticipants but c is not defined in that select statement.

vanilla/applications/conversations/models/class.conversationmodel.php

Lines 80 to 88 in cb6f17d

	case 'CountParticipants':
	$innerSQL = <<<SQL
	select count(uc.ConversationID)
	from GDN_UserConversation uc
	where uc.ConversationID = c.ConversationID and uc.Deleted = 0
	SQL;
	$this->SQL->update('Conversation c')
	->set('c.CountParticipants', $innerSQL, false, false)
	->put();

results is a query like so

update GDN_Conversation set c.CountParticipants=(select count(uc.ConversationID) from GDN_UserConversation uc where uc.ConversationID = c.ConversationID and uc.Deleted = 0);

this would result in the following error if run manually

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(uc.ConversationID) from GDN_UserConversation uc where uc.ConversationID = c.ConversationID and uc' at line 1

While you could argue the update statement defines c. c needs to be defined in the subquery to be understood. MySQL Update even supports joins or better still split it up into two statements.

Vanilla info

1. Are you sure this is a core Vanilla problem and not caused by any addon you are using? yes
2. Did you follow our generic troubleshooting steps already? yes
3. What Vanilla version are you using? 2021.012
4. When did the issue start? when run /dba/counts Recalculate Conversation.CountParticipants

To Reproduce
Steps to reproduce the behavior:

1. Go to /dba/counts
2. click on Recalculate Conversation.CountParticipants
3. Start

Expected behavior
for count to update correctly without error

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[Github-Newbie5432]

I built 2023.001 this morning from source and this seems to be fixed in this release. Have uploaded the release here if you'd like to download (it works out-of-the-box on PHP 8.1.12 + PHP 8.2.1 without any workarounds!)

https://open.vanillaforums.com/discussion/39237/vanilla-2023-001-open-source-release-php-8-works-built-from-source