Exercise 3: Mixed Storage Classes, Surprising Sorts, and How STRICT Prevents It — Possible Solution ==================================================================== HOW A COLUMN ENDS UP WITH MIXED STORAGE CLASSES ------------------------------ Per this chapter's own earlier demonstration, a column declared INTEGER can still end up storing a TEXT value (like 'five') if the inserted value couldn't be coerced. If some rows in that same column genuinely contain numbers (stored as INTEGER or REAL) while other rows accidentally contain text (like 'five' or 'N/A'), the single column now holds a genuine mix of different storage classes, even though it was only ever declared as one type. WHY THIS PRODUCES SURPRISING SORT/COMPARISON RESULTS ------------------------------ Per this chapter's own warn-box, "SQLite orders different storage classes in a fixed sequence (NULL < INTEGER/REAL < TEXT < BLOB) rather than comparing mixed values as if they shared one type." This means an ORDER BY on a mixed column doesn't sort numerically or alphabetically in a single consistent way — instead, EVERY numeric value (INTEGER/REAL) sorts before EVERY text value, regardless of what the actual numbers or text say. For example, the numeric value 100 would sort before the text value '5', even though '5' would normally be considered "smaller" if it were being compared as a number — because the comparison isn't happening within one type at all, it's comparing across two entirely different storage classes according to SQLite's own fixed class ordering, not the values' own natural order. Aggregates (like SUM or AVG) applied to such a column can also behave unexpectedly, since some rows contribute a real number and others don't behave as numbers at all. HOW STRICT TABLES PREVENT THIS ------------------------------ Per this chapter's own tip-box, "a STRICT table can never end up with mixed storage classes in a single column in the first place, since a value that doesn't genuinely match is rejected at insert time — closing the exact gap the warn-box above describes." If the example table from this chapter had been declared with STRICT, the original INSERT INTO example VALUES (1, 'five') attempt would have been rejected outright with an error, rather than silently succeeding and storing 'five' as TEXT inside an INTEGER-declared column. Because every value that's ever accepted into a STRICT column genuinely matches its declared type, that column can never end up containing a mix of storage classes at all — there's simply no path for a non-matching value to get in, so the surprising cross-class sort behavior described above can never arise for that column. WHY THIS WORKS AS AN ANSWER ------------------------------ It explains concretely how mixed storage classes accumulate in a default (non-STRICT) column, explains the specific fixed-class- ordering mechanism that causes surprising sort behavior, and explains precisely why STRICT's own insert-time rejection prevents the root cause (mixed classes) from ever occurring, rather than just fixing the sort behavior after the fact.