Sqlite spec: 4.2. Type Conversions Prior To Comparison



  • Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison , especially this statement:

    If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.

    I would expect the following query:

    CREATE TABLE t (id integer primary key, str varchar(20));
    INSERT INTO t (id, str) VALUES (1, '5'), (2, '5u');
    

    SELECT id, 5 = str, 5 >= str, CAST(5 AS NUMERIC) >= str, CAST(str AS NUMERIC) FROM t;

    to return 5 >= str = 1 for both rows as the left side operand has NUMERIC affinity.

    Demo: http://sqlfiddle.com/#!5/e9c19/4



  • I think you may be missing https://www.sqlite.org/lang_expr.html#castexpr :

    with the CAST operator the conversion always takes place even if the conversion lossy and irreversible, whereas column affinity only changes the data type of a value if the change is lossless and reversible

    (emphasis mine).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2