sameerpandey17 opened a new pull request, #75635: URL: https://github.com/apache/airflow/pull/75635
closes: #74724 related: #74552 Prefix search filtered with `col >= lower AND col < upper AND substr(col, 1, len(lower)) = lower`, where `upper` is the prefix with its last character bumped. Under a collation with contractions (e.g. Postgres ICU `cs-CZ-x-icu`, where `ch` sorts after `h`), `'chata' < 'd'` is false, so prefix `c` silently misses `chata`. I reproduced this on main (`1b9a32b528`) on Postgres 16: `[]` instead of `['chata']`. I removed the `col < upper` bound from `_build_prefix_range_clauses` and its two callers. `substr(col, 1, len(lower)) = lower` already enforces the exact prefix, so correctness no longer depends on how a collation orders digraphs, and `col >= lower` stays so the scan can still start at the prefix. `_prefix_range_upper` has no callers left, so it is removed with its tests, and the docstrings mentioning an upper bound are updated. The cost is that the scan is no longer cut off at the end of the prefix range, so a short prefix on a very large table may read more of the index (from reading the code, not benchmarked). Testing: a new test checks that no `< upper` clause is generated, and an existing task-display test asserts the same. The new test fails without the change and passes with it. They check the SQL; the collation behaviour I checked on Postgres 16 with `cs-CZ-x-icu` (`[]` before, `['chata']` after). `test_parameters.py` passes on Postgres 16 (64 passed) and SQLite (64 passed). mypy would not run on my machine (missing C toolchain), so I am relying on CI for it. Alternatives I considered, and can switch to if you prefer: - Match the prefix with `LIKE 'prefix%'` and properly escaped wildcards instead of the range. I held back because of escaping and per-backend case-sensitivity differences. - Keep the range bounds but compare them under a binary collation (e.g. `COLLATE "C"` on Postgres). I held back because it is backend-specific and I was not sure it keeps the normal index usable. * * * ##### Was generative AI tooling used to co-author this PR? - Yes (please specify the tool below) Generated-by: Claude Sonnet 5.5 (investigation, planning and drafting the PR text) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
