Case-insensitive collation mode for SQL and typed queries

[chrisrichards]

Context

The dialect's string comparisons are ordinal and case-sensitive (agreed in the SQL plan; it matches DBF MACHINE collation exactly, which is what makes index-backed and scan-backed execution provably equivalent — see #288/#291). Users coming from other databases often expect case-insensitive matching, and FoxPro applications commonly index UPPER(col) for exactly this reason.

Proposed approach

• An opt-in Collation/CaseInsensitive setting on DbfDataReaderOptions (+ connection-string keyword) and a builder method, applied consistently in SqlValueComparer (comparisons and ORDER BY) and the LIKE regex (RegexOptions.IgnoreCase).
• Index interaction is the subtle part: a case-insensitive comparison cannot use a MACHINE-collation index directly. Two options, not mutually exclusive:
  1. With case-insensitivity on, the planner falls back to scans (simple, correct).
  2. Teach QueryPlanner.FindEligibleTags to recognise UPPER(<column>) key expressions (currently rejected by the plain-identifier rule) and, for case-insensitive equality/prefix predicates, search with the upper-cased key — this is how VFP itself optimises these queries.
• The differential harness must run in both collation modes.

🤖 Generated with Claude Code