#200 Autocomplete: two suggestions for the same account

I guess it is not because of autocomplete, but i don't really know the origin of this; maybe the fix_duplicates script not removing duplicates of the same server where it is executed?

It works fine for remote accounts (EDIT: no, it doesn't, read below).

I've prepared a query that returns all remote profiles that have duplicates.

PostgreSQL:

SELECT ("profile"."nickname" || '@' || regexp_replace("profile"."profileurl", '^https?://([^/]+)/.*$', '\1')) COLLATE "C" AS "remote_profile",
    MAX("profile"."created") AS "most_recent",
    COUNT(*) AS "count"
  FROM "profile"
  LEFT JOIN "user" ON "profile"."id" = "user"."id"
  LEFT JOIN "user_group" ON "profile"."id" = "user_group"."profile_id"
  WHERE "profile"."nickname" <> ''
  AND "user"."id" IS NULL
  AND "user_group"."profile_id" IS NULL
  GROUP BY "remote_profile"
  HAVING COUNT(*) > 1
  ORDER BY "count" DESC;

MariaDB:

SELECT concat(`profile`.`nickname`, '@', regexp_substr(`profile`.`profileurl`, '(?:(?<=http://)|(?<=https://))([^/]+)')) AS `remote_profile`,
    MAX(`profile`.`created`) AS `most_recent`,
    COUNT(*) AS `count`
  FROM `profile`
  LEFT JOIN `user` ON `profile`.`id` = `user`.`id`
  LEFT JOIN `user_group` ON `profile`.`id` = `user_group`.`profile_id`
  WHERE `profile`.`nickname` <> ''
  AND `user`.`id` IS NULL
  AND `user_group`.`profile_id` IS NULL
  GROUP BY `remote_profile`
  HAVING COUNT(*) > 1
  ORDER BY `count` DESC;

Using that query (thanks!) i've seen that executing some of OStatus scripts and fix_duplicates.php again improves the situation (from 73 to 29 repeated profiles) and the count (from 14 to 3). Anyway, it seems that if the remote node is down (or dead), there's no way to de-duplicate profiles:

FWIW: Unable to connect to tls://social.ingobernable.net:443. Error: Conexi?n rehusada

Ok, deleting from "profile" the ones already dead, and with some more cleaning, fix_duplicates.php returns nothing :)

I'm only seeing this now with local profiles, which kind of makes sense... or not? :)

I'm only seeing this now with local profiles, which kind of makes sense... or not? :)

Duplicates?

I'm not seeing this behaviour anymore, i'd say it is fixed, even if XRevan86 query keeps giving some duplicate remote profiles.