Cross-family review of 1b4dd6d6 found that utf8mb4_bin - chosen to keep the
dedupe case-exact - is a PAD SPACE collation, so trailing spaces are
insignificant under it. Verified on MySQL 8.4: '/media/Foo' = '/media/Foo ' is
TRUE, while case correctly compares unequal. Two distinct legal directories
therefore grouped together and the second was DELETED irreversibly, even though
PathUtils.GetPathHash hashes them differently and the unique index about to be
created would have accepted both. The dedupe destroyed data the constraint
never required it to destroy.
Group and join on CONVERT(Path USING binary) instead - NO PAD and byte-exact,
matching the hash. utf8mb4_0900_bin is also NO PAD but carries a server-version
floor. This is the only path comparison in either migration (every other
predicate keys off an integer id), so there is no mix of padded and unpadded
comparisons across the keeper-selection, repoint and delete steps.
SQLite's = on TEXT is byte-exact with no padding, so that migration was already
correct - which is exactly why a SQLite-only test could not see the divergence.
The two providers are now semantically equivalent, and the dedupe fixture is
shared: same rows, same expected survivors (1,4,5,6,7,9,10), asserted by the
SQLite test and reproduced by hand on MySQL 8.4.
Runtime was never affected, and this is now stated and tested rather than
assumed: GetFolder's SQL equality is a superset narrowing (both collation quirks
make it more permissive, never less, so it cannot miss a byte-exact match) and
ResolveExact settles identity with StringComparison.Ordinal, which compares
length first. Added ResolveExact coverage for the trailing-space axis.
Refs #488#308fix#491
timothy
48d41b9235