Products' duplicates in the database

[CharlesNepote]

Describe the bug

The database contains few products' duplicates (~30). It can be seen in several places.

It seems to be due to the _id stored as a number at first, and then as string.

To Reproduce

In the JSONL export:

$ zcat openfoodfacts-products.jsonl.gz | jq -c '. | select(.code == "0071923722898")'
# two objects are returned:
# * one containing {"_id":71923722898,"code":"0071923722898" [...]
# * an the other beginning by {"_id":"0071923722898","code":"0071923722898" [...]

In the CSV export:

$ grep "0071923722898" en.openfoodfacts.org.products.csv | cut -c 1-90
0071923722898   http://world-en.openfoodfacts.org/product/0071923722898/frosted-flakes-sweet
0071923722898   http://world-en.openfoodfacts.org/product/0071923722898/frosted-flakes-hospi

In Mirabelle (based on CSV export): http://mirabelle.openfoodfacts.org/products?sql=--+identify+duplicates%0D%0Aselect+rowid%2C+code%2C+url%2C+count%28*%29+as+%22count%22+from+%5Ball%5D+group+by+code+having+count%28*%29+%3E+1%3B This query lists all the products being duplicates as of 2022-11-16 (33).

Expected behavior

No duplicates.

[CharlesNepote]

I'm afraid there is a bug in the current code because the number of duplicates is increasing: 42 as of 2022-12-02 vs 33 as of 2022-11-16.

https://mirabelle.openfoodfacts.org/products?sql=--+identify+duplicates%0D%0Aselect+rowid%2C+code%2C+url%2C+count%28*%29+as+%22count%22+from+%5Ball%5D+group+by+code+having+count%28*%29+%3E+1%3B

[alexgarel]

relates to https://github.com/openfoodfacts/openfoodfacts-server/issues/7248 that could monitor and fix this kind of things.

[github-actions[bot]]

This issue is stale because it has been open 90 days with no activity.

[teolemon]

bumping to p1, as It got a conversation off topic :-/