You got a similar siltation with NTFS where the file system supports long paths while the userland WinAPI does use a much smaller length limit (unless you jump through "\\?\ hoops, which does not work for relative paths), which renders certain files "unopenable" by certain applications.
[0] Regarding opening the wrong files, anybody remember the Android zip vulnerabilities? https://googlesystem.blogspot.com/2013/07/the-8219321-androi... It's not that hard to imagine that some macOS software does security/sanity checks on files using the low-level non-normalized API but then opens the (wrong and unchecked) file later with normalizing high level API, or vice versa. Having this API discrepancy built into your OS certainly makes these kinds of things more likely.
I've always disliked the practice of messing around with file paths (storing them, concatenating them, etc). I preferred the way that the Classic MacOS typically dealt with filesystem references and aliases instead of file paths. This also meant users can rename or move files and references still work.