ehddnr301 commented on PR #74552: URL: https://github.com/apache/airflow/pull/74552#issuecomment-6148017764
> This one feels a bit odd to be fixing client site -- I would have expected the fix to this to be to change the database's collation? I didn't go with changing the collation because it would require existing installations to change their database setup, and it could also affect sorting and comparisons elsewhere. That said, I think the shared filter you suggested on Slack is the better place for this. Fixing it there means we don't need to add `COLLATE "C"` to each affected query, and the filter returns correct results regardless of the database's collation. > Can you explain that a bit more? I don't quite understand what it means. The filter builds the upper bound by bumping the last character to the next Unicode code point. For `카페`, that gives: ```sql name >= '카페' AND name < '카펙' ``` That works if strings are compared by code point, but Postgres compares them using the collation, and the two orders don't always match. On PostgreSQL 16.15: ```sql SELECT '카페매출집계' < '카펙' COLLATE "en_US.utf8"; -- false SELECT '카페매출집계' < '카펙' COLLATE "C"; -- true ``` So with `en_US.utf8`, `카페매출집계` falls outside the range even though it starts with `카페`. Since the conditions are ANDed, the substring check can't bring it back. The same thing happens in a few other filters too: https://github.com/apache/airflow/issues/74551#issuecomment-6142148108 I'll update the PR to move the fix into the shared filter, and would appreciate a look once it's up. Dropping the bounds can lose the range scan on indexed columns, so I'll include the performance numbers too. Let me know if you think the collation approach would fit better. Drafted-by: Codex (GPT-6); reviewed by ehddnr301 before posting -- 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]
