[bug] Hybrid duplicate prefilter silently drops every multi-author record (untrimmed primary-author key)

[coffbr01]

Describe the bug

In the default (hybrid) duplicate-scan configuration, every book whose record has more than one author is silently excluded from duplicate detection.

The SQL prefilter derives a book's primary author from author_sort by taking the substring before the first &, but does not trim the result — so it keeps the space that preceded the ampersand. Since that value is a GROUP BY key, a two-author record's key ('shelley, mary ') never equals the equivalent single-author record's key ('shelley, mary'), and the two books are never emitted as duplicate candidates.

The prefilter's own docstring describes it as a safe superset:

https://github.com/crocodilestick/Calibre-Web-Automated/blob/main/cps/duplicates.py#L535

Uses only title/author prefiltering to remain a safe superset.

It isn't a superset, because the Python stage derives the primary author a different (and correct) way, so the two stages disagree about what the primary author is.

The code

cps/duplicates.py, find_duplicate_candidate_ids_sql() (~L558):

norm_author_sort = func.lower(func.trim(func.coalesce(db.Books.author_sort, 'unknown')))
primary_author = case(
    (func.instr(norm_author_sort, '&') > 0,
     func.substr(norm_author_sort, 1, func.instr(norm_author_sort, '&') - 1)),   # <-- not trimmed
    else_=norm_author_sort                                                       # <-- trimmed
)
group_by_fields.append(primary_author)

The else_ branch (single-author) is trimmed by func.trim above. The & branch is not.

Meanwhile find_duplicate_books_python() (~L846) does:

book.ordered_authors = calibre_db.order_authors([book])
primary_author = book.ordered_authors[0].name    # later .lower().strip()

'Shelley, Mary' — trimmed, and derived from the author list rather than by string-splitting author_sort.

Because the defaults are duplicate_scan_method: 'hybrid' and duplicate_detection_use_sql: 1, the SQL prefilter runs first and only its surviving candidate ids reach the Python stage. The Python stage would group these books correctly — it just never sees them.

To Reproduce

Add two books with the same title, where one record credits a second author (translator, editor, illustrator, co-author — anything that puts an & in author_sort):

title	authors	resulting author_sort
Frankenstein	Mary Shelley	Shelley, Mary
Frankenstein	Mary Shelley, John Editor	Shelley, Mary & Editor, John

1. Run a duplicate scan with default settings (hybrid / SQL prefilter on) → no duplicate group is reported.
2. Set duplicate_detection_use_sql: 0 (pure-python method) and rescan → the pair is reported.

The grouping-key difference can be confirmed without CWA at all — this is the exact expression the prefilter builds:

$ sqlite3 :memory: "
select '[' || substr(lower(trim('Shelley, Mary & Editor, John')), 1,
                     instr(lower(trim('Shelley, Mary & Editor, John')), '&') - 1) || ']';
select '[' || lower(trim('Shelley, Mary')) || ']';"
[shelley, mary ]
[shelley, mary]

Two different GROUP BY keys for the same primary author.

Expected behavior

A two-author record and a single-author record by the same primary author should land in the same candidate group, so the Python stage can evaluate them — i.e. the prefilter should actually be the safe superset it documents itself as.

Suggested fix

Wrap the & branch in func.trim(...) so both branches produce the same normalised value:

primary_author = case(
    (func.instr(norm_author_sort, '&') > 0,
     func.trim(func.substr(norm_author_sort, 1, func.instr(norm_author_sort, '&') - 1))),
    else_=norm_author_sort
)

Happy to open a PR if that's useful.

It may also be worth asserting the invariant somewhere, since the underlying issue is that two independent implementations of "primary author" have to agree for the prefilter to be sound — a future change to either side can silently reintroduce this class of bug without any test failing.

Impact

Any record that credits a translator, editor, illustrator or co-author is invisible to duplicate detection in the default configuration. That is not a rare shape in an imported library — it is the common way the same work ends up stored twice, because one source credits the translator and another doesn't.

This may be a contributing cause of #1407 ("Duplicates are not detected properly. Detection only catches a small amount of duplicates"), though that report is asking for fuzzier matching generally and this is a distinct, mechanical defect.

Pure-python mode (duplicate_detection_use_sql: 0) is a usable workaround and also confirms the diagnosis.

Version

• CWA v4.0.6 (crocodilestick/calibre-web-automated:v4.0.6), Docker, amd64.
• Verified present on main as of 2026-07-29 (line references above are from main).
• Found while auditing duplicate-detection coverage on a ~5,000-book library.