I've experienced some weirdness with the results of a comparison
between values of different data types. I'm not sure if this is due
to type affinity, implicit conversion, or something else, but it
doesn't behave as I expect it to as indicated in the documentation.
Take the following query for example:
SELECT
'2 < ''10''' AS COMPARISON,
2 < '10' AS RESULT,
TYPEOF(2) AS TYPE1,
TYPEOF('10') AS TYPE2
UNION ALL
SELECT
'2 < CAST(''10'' AS TEXT)',
2 < CAST('10' AS TEXT),
TYPEOF(2),
TYPEOF(CAST('10' AS TEXT))
UNION ALL
SELECT
'CAST(2 AS INTEGER) < ''10''',
CAST(2 AS INTEGER) < '10',
TYPEOF(CAST(2 AS INTEGER)),
TYPEOF('10')
UNION ALL
SELECT
'CAST(2 AS INTEGER) < CAST(''10'' AS TEXT)',
CAST(2 AS INTEGER) < CAST('10' AS TEXT),
TYPEOF(CAST(2 AS INTEGER)),
TYPEOF(CAST('10' AS TEXT))
UNION ALL
SELECT
'''2'' < 10',
'2' < 10,
TYPEOF('2'),
TYPEOF(10)
UNION ALL
SELECT
'CAST(''2'' AS TEXT) < 10',
CAST('2' AS TEXT) < 10,
TYPEOF(CAST('2' AS TEXT)),
TYPEOF(10)
UNION ALL
SELECT
'''2'' < CAST(10 AS INTEGER)',
'2' < CAST(10 AS INTEGER),
TYPEOF('2'),
TYPEOF(CAST(10 AS INTEGER))
UNION ALL
SELECT
'CAST(''2'' AS TEXT) < CAST(10 AS INTEGER)',
CAST('2' AS TEXT) < CAST(10 AS INTEGER),
TYPEOF(CAST('2' AS TEXT)),
TYPEOF(CAST(10 AS INTEGER))
;
COMPARISON |RESULT |TYPE1 |TYPE2
2 < '10' |1 |integer |text
2 < CAST('10' AS TEXT) |0 |integer |text
CAST(2 AS INTEGER) < '10' |1 |integer |text
CAST(2 AS INTEGER) < CAST('10' AS TEXT) |1 |integer |text
'2' < 10 |0 |text |integer
CAST('2' AS TEXT) < 10 |0 |text |integer
'2' < CAST(10 AS INTEGER) |1 |text |integer
CAST('2' AS TEXT) < CAST(10 AS INTEGER) |1 |text |integer
According to the numeric-literal description at
http://sqlite.org/lang_expr.html, a number without a decimal should be
considered an integer literal. Similarly a string enclosed in single
quotes is a string constant. A TYPEOF of each value in the example
confirms they are INTEGER and TEXT respectively.
According to https://sqlite.org/datatype3.html section 4.2, if one
operator in a comparison is TEXT and the other is INTEGER or no
affinity, then the TEXT operator is converted to NUMERIC. Now it
doesn't state if this conversion follows the rules of a CAST to
NUMERIC, but in the example that would effectively make the NUMERIC an
INTEGER.
So going by what I read here, all these comparisons should be
comparing INTEGER to INTEGER, but that clearly isn't the case. What's
even more surprising is the introduction of a CAST alters the results,
at least for some of the comparisons.
Could someone explain the varying results? What is it really doing?
Jeremy
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
