Comparison operator quirks

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Comparison operator quirks

Jeremy Zeiber
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparison operator quirks

Richard Hipp-3
On 6/28/17, Jeremy Zeiber <[hidden email]> wrote:
>
> 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.
>
> Could someone explain the varying results?  What is it really doing?
>

The affinity (TEXT, INTEGER, REAL, NUMERIC, BLOB) is determined by the
declared type of the table column in which the value is stored.  Since
your example as no tables and no columns, all of the affinities are
BLOB.  No automatic conversions occur when comparing two values with
an affinity of BLOB.

Probably this seems very strange to someone who has grown up in the
rigidly-typed world of old-school RDBMSes and programming languages
taught at many universites. The conversion rules of SQLite may seem
strange, but they are designed so that the same code that works on
PostgreSQL will also work in the same way in SQLite, while still
allowing flexible typing.

If you just ignore all the rules, things generally work out the way
you want.  Most people never have to think about it - they just get
the answer they were expecting.

What unusual thing are you doing that requires you to become an expert
on the arcane type-conversion rules of SQLite?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...