Comparison Operation

The following are the conversion rules according to an operand type of the comparison operator.

operand1 Type

operand2 Type

Conversion

Comparison

Numeric Type

Numeric Type

None

NUMERIC

String Type

Converts operand2 to DOUBLE

NUMERIC

Date/Time Type

None

N/a

String Type

Numeric Type

Converts operand1 to DOUBLE

NUMERIC

String Type

None

String

Date/Time Type

Converts operand1 to date/time type

Date/Time

Date/Time Type

Numeric Type

None

N/A

String Type

Converts operand2 to date/time type

Date/Time

Date/Time Type

Converts it to the type with higher priority

Date/Time

The following are the exceptions in the conversion rules for comparison operators:

operand1 Type

operand2 Type

Conversion

Comparison

String type

Numeric type

Converts operand2 to the string type

String

Date/Time type

Converts operand2 to the string type

String

If operand2 is a set operator(IS IN, IS NOT IN, = ALL, = ANY, < ALL, < ANY, <= ALL, <= ANY, >= ALL, >= ANY), the exception above is not applied.

Numeric Type & String Type Operands

The string type operand will be converted to DOUBLE.

CREATE TABLE t(i INT, s STRING);

INSERT INTO t VALUES(1,'1'),(2,'2'),(3,'3'),(4,'4'), (12,'12');

 

SELECT i FROM t WHERE i < '11.3';

 

            i

=============

            1

            2

            3

            4

 

SELECT ('2' <= 11);

 

     ('2'<11)

=============

            1

String Type & Date/Time Type Operands

The string type operand will be converted to the date/time type.

SELECT ('2010-01-01' < date'2010-02-02');

 

   ('2010-01-01'<date '2010-02-02')

==================================

                                1

 

SELECT (date'2010-02-02' >= '2010-01-01');

 

  (date '2010-02-02'>='2010-01-01')

===================================

                                1

String Type & Numeric Type Host Variable Operands

The numeric type host variable will be converted to the string type.

PREPARE s FROM 'SELECT s FROM t WHERE s < ?';

EXECUTE s USING 11;

       s

===================

     '1'

String Type & Numeric Type value Operands

The numeric type value will be converted to the string type.

SELECT s FROM t WHERE s > 11;

       s

==================

     '2'

     '3'

     '4'

     '12'

 

SELECT s FROM t WHERE s BETWEEN 11 AND 33;

        s

======================

      '2'

      '3'

      '12'

String Type Column & Date/Time Type Value Operands

The date/time type value will be converted to the string type.

SELECT s FROM t;

 

           s

======================

    '01/01/1998'

    '01/01/1999'

    '01/01/2000'

 

SELECT s FROM t WHERE s <= date'02/02/1998';

            s

======================

    '01/01/1998'

    '01/01/1999'

    '01/01/2000'