SQLite浮点亲和性疑问：复杂WHERE子句数值比较技术问询

Hey there, let’s break down the floating point comparison problems you’re hitting in SQLite—since you’ve already reviewed the official docs, I’ll zero in on the nuanced type affinity and dynamic typing quirks that often trip up even experienced users.

Common Pitfalls & Fixes #

• Implicit Conversions Between Text and Numeric Types
  SQLite’s type affinity rules mean when you compare values of different types (e.g., a TEXT column storing numeric strings vs. a REAL literal), it automatically converts one to match the other. This can lead to unexpected misses. For example:

  CREATE TABLE measurements (reading TEXT);
  INSERT INTO measurements VALUES ('0.1');
  -- This might NOT return the row, even though it looks like it should
  SELECT * FROM measurements WHERE reading = 0.1;
  

  The issue here is converting the text "0.1" to a REAL introduces the same binary floating point imprecision as the literal 0.1. While they look identical as decimals, their binary representations are slightly off.

• Mismatched Affinities Causing Unexpected Type Casts
  If you’re comparing a TEXT column with numeric content to an INTEGER, SQLite will try to convert the TEXT to an INTEGER first (not a REAL). This truncates decimal values, leading to false matches:

  -- This will return the row with '1.9' because it gets cast to INTEGER 1
  SELECT * FROM measurements WHERE reading = 1;
  

  To avoid this, explicitly cast the text to a REAL if you need decimal precision:

  SELECT * FROM measurements WHERE CAST(reading AS REAL) = 1.9;
  

• Exact Equality Checks with REAL Values Are Risky
  Floating point numbers (REAL in SQLite) can’t represent all decimal values exactly (like 0.1 or 0.3). Direct equality checks will fail for values that should be logically equal:

  CREATE TABLE calculations (result REAL);
  INSERT INTO calculations VALUES (0.1 + 0.2);
  -- This won't return the row, since 0.1+0.2 = ~0.30000000000000004
  SELECT * FROM calculations WHERE result = 0.3;
  

  Instead, use a tolerance-based comparison to account for precision:

  SELECT * FROM calculations WHERE ABS(result - 0.3) < 1e-9;
  

• Column Affinity ≠ Actual Stored Type
  SQLite lets you store any data type in any column, regardless of the defined affinity. A column marked as REAL might still hold TEXT values if you inserted them as strings. To debug this, use the typeof() function to check the actual type of stored values:

  SELECT reading, typeof(reading) FROM measurements;
  

  If you see TEXT where you expected REAL, you’ll know to adjust your inserts or add explicit casts in your queries.

Quick Recap #

SQLite’s flexible typing is powerful, but it requires you to be intentional about how values are stored and compared. Always verify actual data types with typeof(), avoid exact equality for REALs, and use explicit casts when mixing text and numeric types.

内容的提问来源于stack exchange，提问作者James Mapledoram