SQLite Forum

SQLDIFF not showing true differences
Login
> Could an UPDATE affecting only case on a COLLATE nocase column be validly ignored as an optimization?

Good point, and ... obviously not!

Database content has two primary uses, retrieval and display.  The `collate nocase` should only affect retrieval.  Otherwise, the database engine would be free to convert all text of a `collate nocase` column to either all-lower or all-upper case for storage.  (This would actually provide micro-optimizations in search speed [case-sensitive only comparisons] and required storage [better compression of a smaller char set].)

But, you need to preserve the actual case at least for display purposes. And although I can understand the 'logical' perspective argument, I consider SQLDIFF's failure to update case-only changes of `collate nocase` columns, incorrect behavior from the 'practical' perspective.

And if an option is added to allow both scenarios, the default (IMO) should be to ignore `collate nocase` and give a 'practical' DB difference which is what I believe most users would expect to see.

BTW, from SQLDIFF's help screen:

`Output SQL text that would transform DB1 into DB2.`

Well, not really, as I won't get identical data displays from either db.