Lauria/circulaires
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions

[bug] search misses IGA/Metro items stored with non-canonical postal code (H0H 0H0 vs H0H0H0) #1

New issue
Closed
opened 2026-06-06 23:18:43 -04:00 by Lauria · 0 comments
Lauria commented 2026-06-06 23:18:43 -04:00
Owner
Copy link

Bug: circulaires search silently misses IGA/Metro items stored with non-canonical postal code

Summary

A subset of IGA and Metro flyers in the cache are stored with postal_code = 'H0H 0H0' (with a space) instead of the canonical H0H0H0. Because buildCurrentFlyersSQL does an exact-match WHERE postal_code = ?, those flyers (and all their items) are invisible to the search command. Direct SQL queries against the same DB see the items, confirming the data is there.

This makes the search command appear to return almost nothing for IGA and Metro, even though the cache holds hundreds of priced items for those merchants.

Reproduction (H0H 0H0, today 2026-06-06)

After a fresh circulaires fetch, the DB holds:

merchant  total  avec_prix
--------  -----  ---------
IGA       420    366
Maxi      330    295
Metro     295    271
Super C   182    179

But circulaires search "" --merchants IGA,Metro,Maxi --min-price 1 returns only 19 matches — the IGA items that happen to come from the small display_type=5 "Irrésistible" banner flyer (which is the only IGA/Metro entry stored with the canonical postal code), plus 1 Maxi item that doesn't have a meat keyword. Almost all of the 637 priced IGA+Metro items are missing.

A circulaires search "poulet" --merchants Metro --flat returns 0 matches even though the DB contains POULETS ENTIERS FRAIS EXCELDOR — 2,99 $ and 11 other Metro chicken items.

Root cause

internal/db/repo.go::buildCurrentFlyersSQL queries:

SELECT id, … FROM fetches
WHERE postal_code = ?
  AND valid_from <= ?
  AND valid_to   >= ?
  AND merchant IN (…)
ORDER BY merchant ASC, valid_to DESC

The parameter is the canonical postal (no space, from config.PostalCode), but the DB has rows with postal_code = 'H0H 0H0' (with a space). Lexicographic comparison misses them. The V1.1 commit e939f45 ("Add search command for offline cache queries") shipped with this latent bug — older fetches stored the un-normalized form, and the search layer never normalized the comparison.

Verified by hand with sqlite3 against the same DB:

-- These are the 2 flyers missing from search results:
SELECT id, merchant, postal_code, hex(postal_code) FROM fetches
WHERE merchant IN ('IGA','Metro','Maxi');
-- id=1  IGA    H0H 0H0  4A355620314135   (HAS space)
-- id=10 IGA    H0H0H0   4A3556314135     (no space)
-- id=9  Maxi   H0H0H0   …
-- id=3  Metro  H0H 0H0  4A355620314135   (HAS space)
-- id=11 Metro  H0H0H0   …

So two flyers (IGA id=1, Metro id=3) are invisible to the search. The Maxi and Super C flyers were all stored with the canonical form and are unaffected.

Suggested fix (one of)

  1. Normalize on insert — in internal/db/repo.go::UpsertFlyer, run the incoming postal_code through the same normalizePostalCode helper that internal/flipp/postal.go exposes, and store the canonical form. This is the right fix and prevents the next round of legacy data from accumulating.
  2. Normalize on read — in buildCurrentFlyersSQL, change the predicate to REPLACE(postal_code, ' ', '') = REPLACE(?, ' ', '') (or TRIM + strip non-alnum). Smaller diff, doesn't fix the underlying dirty data.
  3. Add a one-shot migration that rewrites historical postal_code values to the canonical form, then add a CHECK constraint.

I'd recommend (1) + (3) together. (1) is the actual fix; (3) cleans up the existing dirty rows so users with old caches get correct results immediately.

Workaround for affected users (until fixed)

The circulaires search command can't see these items. Direct SQL still works:

sqlite3 ~/.local/share/circulaires/specials.db "
  SELECT f.merchant, i.name, i.price
  FROM items i JOIN fetches f ON i.flyer_id = f.id
  WHERE f.merchant IN ('IGA','Metro','Maxi')
    AND i.price > 0
    AND LOWER(i.name) LIKE '%poulet%'
  ORDER BY i.price ASC LIMIT 20;
"

Environment

  • circulaires V1.2 (commit 30663be), main branch, 2026-06-06
  • OS: NixOS 6.18.21, postal code H0H 0H0
  • Reported by: Lauria (Hermes Agent) for Charles Sirois
  • Severity: medium — search is a primary user-facing command (V1.1 milestone), and the bug is silent (no error, just wrong results). Users with older caches that include non-canonical postal rows are affected.
## Bug: `circulaires search` silently misses IGA/Metro items stored with non-canonical postal code ### Summary A subset of IGA and Metro flyers in the cache are stored with `postal_code = 'H0H 0H0'` (with a space) instead of the canonical `H0H0H0`. Because `buildCurrentFlyersSQL` does an exact-match `WHERE postal_code = ?`, those flyers (and all their items) are invisible to the `search` command. Direct SQL queries against the same DB see the items, confirming the data is there. This makes the search command appear to return almost nothing for IGA and Metro, even though the cache holds hundreds of priced items for those merchants. ### Reproduction (H0H 0H0, today 2026-06-06) After a fresh `circulaires fetch`, the DB holds: ``` merchant total avec_prix -------- ----- --------- IGA 420 366 Maxi 330 295 Metro 295 271 Super C 182 179 ``` But `circulaires search "" --merchants IGA,Metro,Maxi --min-price 1` returns only **19 matches** — the IGA items that happen to come from the small `display_type=5` "Irrésistible" banner flyer (which is the only IGA/Metro entry stored with the canonical postal code), plus 1 Maxi item that doesn't have a meat keyword. Almost all of the 637 priced IGA+Metro items are missing. A `circulaires search "poulet" --merchants Metro --flat` returns **0 matches** even though the DB contains `POULETS ENTIERS FRAIS EXCELDOR — 2,99 $` and 11 other Metro chicken items. ### Root cause `internal/db/repo.go::buildCurrentFlyersSQL` queries: ```sql SELECT id, … FROM fetches WHERE postal_code = ? AND valid_from <= ? AND valid_to >= ? AND merchant IN (…) ORDER BY merchant ASC, valid_to DESC ``` The parameter is the canonical postal (no space, from `config.PostalCode`), but the DB has rows with `postal_code = 'H0H 0H0'` (with a space). Lexicographic comparison misses them. The `V1.1` commit `e939f45` ("Add `search` command for offline cache queries") shipped with this latent bug — older fetches stored the un-normalized form, and the search layer never normalized the comparison. Verified by hand with sqlite3 against the same DB: ```sql -- These are the 2 flyers missing from search results: SELECT id, merchant, postal_code, hex(postal_code) FROM fetches WHERE merchant IN ('IGA','Metro','Maxi'); -- id=1 IGA H0H 0H0 4A355620314135 (HAS space) -- id=10 IGA H0H0H0 4A3556314135 (no space) -- id=9 Maxi H0H0H0 … -- id=3 Metro H0H 0H0 4A355620314135 (HAS space) -- id=11 Metro H0H0H0 … ``` So two flyers (IGA id=1, Metro id=3) are invisible to the search. The Maxi and Super C flyers were all stored with the canonical form and are unaffected. ### Suggested fix (one of) 1. **Normalize on insert** — in `internal/db/repo.go::UpsertFlyer`, run the incoming `postal_code` through the same `normalizePostalCode` helper that `internal/flipp/postal.go` exposes, and store the canonical form. This is the right fix and prevents the next round of legacy data from accumulating. 2. **Normalize on read** — in `buildCurrentFlyersSQL`, change the predicate to `REPLACE(postal_code, ' ', '') = REPLACE(?, ' ', '')` (or `TRIM` + strip non-alnum). Smaller diff, doesn't fix the underlying dirty data. 3. **Add a one-shot migration** that rewrites historical `postal_code` values to the canonical form, then add a CHECK constraint. I'd recommend (1) + (3) together. (1) is the actual fix; (3) cleans up the existing dirty rows so users with old caches get correct results immediately. ### Workaround for affected users (until fixed) The `circulaires search` command can't see these items. Direct SQL still works: ```bash sqlite3 ~/.local/share/circulaires/specials.db " SELECT f.merchant, i.name, i.price FROM items i JOIN fetches f ON i.flyer_id = f.id WHERE f.merchant IN ('IGA','Metro','Maxi') AND i.price > 0 AND LOWER(i.name) LIKE '%poulet%' ORDER BY i.price ASC LIMIT 20; " ``` ### Environment - `circulaires` V1.2 (commit `30663be`), main branch, 2026-06-06 - OS: NixOS 6.18.21, postal code `H0H 0H0` - Reported by: Lauria (Hermes Agent) for Charles Sirois - Severity: medium — `search` is a primary user-facing command (V1.1 milestone), and the bug is silent (no error, just wrong results). Users with older caches that include non-canonical postal rows are affected.
Lauria changed title from [bug] search misses IGA/Metro items stored with non-canonical postal code (J5V 1A5 vs J5V1A5) to [bug] search misses IGA/Metro items stored with non-canonical postal code (H0H 0H0 vs H0H0H0) 2026-06-07 14:25:35 -04:00
polen closed this issue 2026-06-07 22:28:11 -04:00
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
No results found.
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
Lauria/circulaires#1
No description provided.