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.
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
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
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'
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'
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'