Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We鈥檒l occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

DeleteDuplicateUids fails on postgres with ambigous column #9785

Closed
kesselb opened this issue Jul 1, 2024 · 1 comment 路 Fixed by #9793
Closed

DeleteDuplicateUids fails on postgres with ambigous column #9785

kesselb opened this issue Jul 1, 2024 · 1 comment 路 Fixed by #9793
Assignees
@st3iny
Labels
3. to review bug
Milestone
v4.0.0

Comments

@kesselb
Copy link
Contributor

kesselb commented Jul 1, 2024

Steps to reproduce

  1. Use postgres and mail 3.8.x
  2. Run occ upgrade or occ maintenance:repair
  3. 馃挜

Expected behavior

No 馃挜

Actual behavior

- Delete duplicated cached messages
     - ERROR: An exception occurred while executing a query: SQLSTATE[42702]: Ambiguous column: 7 ERROR:  column reference "mailbox_id" is ambiguous
LINE 1: ..."t2"."uid") AND ("t1"."id" <> "t2"."id") GROUP BY "mailbox_i...

Mail app version

No response

Mailserver or service

No response

Operating system

No response

PHP engine version

None

Web server

None

Database

None

Additional info

No response
@kesselb kesselb added this to the v3.8.0 milestone Jul 1, 2024
@kesselb
Copy link
Contributor Author

kesselb commented Jul 1, 2024 

Index: lib/Db/MessageMapper.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/lib/Db/MessageMapper.php b/lib/Db/MessageMapper.php
--- a/lib/Db/MessageMapper.php	(revision 4ee7334fb200ce2d729f4d0ea1a4d669dae7782b)
+++ b/lib/Db/MessageMapper.php	(date 1719825056620)
@@ -1672,7 +1672,7 @@
 				$qb->expr()->eq('t1.uid', 't2.uid', IQueryBuilder::PARAM_INT),
 				$qb->expr()->neq('t1.id', 't2.id', IQueryBuilder::PARAM_INT),
 			))
-			->groupBy('mailbox_id', 'uid')
+			->groupBy('t1.mailbox_id', 't1.uid', 't1.id')
 			->executeQuery();
 		$rows = $result->fetchAll();
 		$result->closeCursor();

Note: t1.id for groupBy is needed to avoid Grouping error: 7 ERROR: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function

@st3iny st3iny self-assigned this Jul 1, 2024
@st3iny st3iny mentioned this issue Jul 2, 2024
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@st3iny st3iny

Labels
3. to review bug
Projects
馃拰 馃搮 馃懃 Groupware team
Status: 鈽戯笍 Done
Milestone
v4.0.0
Development

Successfully merging a pull request may close this issue.

fix: duplicate uid repair job failing on postgres nextcloud/mail
2 participants
@st3iny @kesselb