Exercise 3: The Dynamic Mapping Gotcha vs. sqlite1-3's Type Affinity Gotcha — Possible Solution ==================================================================== THE DYNAMIC MAPPING GOTCHA, WITH A CONCRETE EXAMPLE ------------------------------ Per this chapter's own warn-box, "since a field's type is inferred from its first-ever value, a product SKU that happens to be all-digits in its first indexed document gets permanently mapped as a numeric type — and a later document with a genuinely alphanumeric SKU value for that same field will fail to index correctly, or behave unexpectedly." Concretely: suppose the very first product indexed has a SKU field with the value "10293" (all digits). Because no mapping was defined explicitly, the engine infers, from that first value, that the sku field should have a numeric mapping. Later, a second product is indexed with a SKU value like "AB-4471" (a genuinely alphanumeric identifier, common in real product catalogs). Because the sku field's mapping was already locked in as numeric from the first document, this second, perfectly valid SKU value either fails to index correctly or produces unexpected behavior, purely because of the accident of which value happened to arrive first. WHY THIS IS CONCEPTUALLY SIMILAR TO SQLITE1-3'S OWN TYPE AFFINITY GOTCHA ------------------------------ Per this chapter, both are fundamentally about "what happens when the engine has to guess a type." Per sqlite1-3, SQLite's own type affinity system tries to coerce a value toward a column's declared affinity, and stores the value as-is when it can't — meaning a column's actual contents can end up genuinely mixed across different rows, with surprising downstream consequences. Both this chapter's dynamic mapping gotcha and sqlite1-3's type affinity gotcha share the same root shape: the ENGINE is inferring/guessing a type on the application's behalf, rather than requiring the type to be explicitly declared and enforced up front, and that guessing process can produce a genuine, real, later surprise. WHY THEY'RE MECHANICALLY DIFFERENT UNDERNEATH ------------------------------ Per this chapter, "the underlying mechanism differs: SQLite stores each value's own actual type flexibly per-row, while here the mapping is inferred once and then effectively locked in for that field." In SQLite (without STRICT), EVERY individual row is free to store whatever storage class its own value actually has — the type "guess" happens per value, and a mismatched value is simply stored as-is without being rejected. In Elasticsearch/OpenSearch's dynamic mapping, the type guess happens only ONCE, on the very first value the engine ever sees for that field — and that single inferred mapping is then treated as fixed and enforced going forward for every subsequent document, rather than remaining flexible per-value the way SQLite's own default typing does. SQLite's gotcha is about ongoing flexibility producing inconsistency; this engine's gotcha is about a single early guess becoming a rigid, hard-to-change constraint. WHY THIS WORKS AS AN ANSWER ------------------------------ It constructs a concrete, plausible SKU example demonstrating the warn-box's own gotcha, and explains both the genuine conceptual similarity (type-guessing by the engine) and the specific mechanical difference (per-value flexibility vs. once-and-locked inference) between this chapter's own gotcha and sqlite1-3's, using both chapters' own stated reasoning.