Conversation Rules

• INSERT and UPDATE
• Function
• Comparison Operation
• Range Operation
• Arithmetic Operation

INSERT and UPDATE

The type will be converted to the type of the column affected.

CREATE TABLE t(i INT);

INSERT INTO t VALUES('123');

 

SELECT * FROM t;

 

            i

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

          123

Function

If the parameter value entered in the function can be converted to the specified type, the parameter type will be converted. The strings are converted to numbers because the input parameter expected in the following function is a number.

SELECT MOD('123','2');

 

           mod('123', '2')

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

     1.000000000000000e+00

You can enter multiple type values in the function. If the type value not specified in the function is delivered, the type will be converted depending on the following priority order.

• Date/Time Type (DATETIME > TIMESTAMP > DATE > TIME)
• Approximate Numeric Type (MONETARY > DOUBLE > FLOAT)
• Exact Numeric Type (NUMERIC > BIGINT > INT > SHORT)
• String Type (CHAR/NCHAR > VARCHAR/VARNCHAR)

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:

• COLUMN <operator> value

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'

Range Operation

Numeric Type and String Type Operands

The string type operand will be converted to DOUBLE.

SELECT i FROM t WHERE i <= all {'11','12'};

 

            i

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

            1

            2

            3

            4

String Type and Date/Time Type Operands

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

SELECT s FROM t2;

 

          s

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

      '01/01/2000'

      '01/01/1999'

      '01/01/1998'

 

SELECT s FROM t2 WHERE s <= ALL {date'02/02/1998',date'01/01/2000'};

 

          s

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

       '01/01/1998'

An error will be returned if it cannot be converted to the corresponding type.

Arithmetic Operation

Date/Time Type Operand

If the date/time type operands are given to '-' operator and the types are different from each other, it will be converted to the type with a higher priority. The following example shows that the operand data type on the left is converted from DATE to DATETIME so that the result of '-' operation of DATETIME can be outputted in milliseconds.

SELECT date'2002-01-01' - datetime'2001-02-02 12:00:00 am';

 

   date '2002-01-01'- datetime '2001-02-02 12:00:00 am'

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

                                          28771200000

Numeric Type Operand

If the numeric type operands are given and the types are different from each other, it will be converted to the type with the higher priority.

Date/Time Type & Numeric Type Operands

If the date/time type and the numeric type operands are given to '+' or '-' operator, the numeric type operand is converted to either BIGINT, INT or SHORT.

Date/Time Type & String Type Operands

If a date/time type and a string type are operands, only '+' and '-' operators are allowed. If the '+' operator is used, it will be applied according to the following rules.

• The string type will be converted to BIGINT with an interval value. The interval is the smallest unit for operands in the Date/Time type, and the interval for each type is as follows:
• • DATE: Days
  • TIME, TIMESTAMP: Seconds
  • DATETIME: Milliseconds
• Floating-point numbers are rounded.
• The result type is the type of an date/time operand.

SELECT date'2002-01-01' + '10';

 

  date '2002-01-01'+'10'

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

  01/11/2002

If the date/time type and a string type are operands and the '-' operator is used, they will be applied according to the following rules.

• If the date/time type operands are DATE, DATETIME and TIMESTAMP, the string will be converted to DATETIME; if the date/time operand is TIME, the string is converted to TIME.
• The result type is always BIGINT.

SELECT date'2002-01-01'-'2001-01-01';

 

  date '2002-01-01'-'2001-01-01'

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

                    31536000000

 

-- this causes an error

 

SELECT date'2002-01-01'-'10';

 

 In line 1, column 13,

 ERROR: Cannot coerce '10' to type datetime.    

 

Numeric Type & String Type Operands

If a numeric type and a string type are operands, they will be applied according to the following rules.

• Strings will be converted to DOUBLE when possible.
• The result type is DOUBLE or MONETARY and depends on the type of the numeric operand.

SELECT 4 + '5.2';

 

                4+'5.2'

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

  9.199999999999999e+00

Unlike CUBRID 2008 R3.1 and the earlier versions, the string in the date/time format, that is, the string such as '2010-09-15' is not converted to the date/time type. You can use a literal (DATE'2010-09-15') with the date/time type for addition and subtraction operations.

SELECT '2002-01-01'+1;

   ERROR: Cannot coerce '2002-01-01' to type double.

SELECT DATE'2002-01-01'+1;

  date '2002-01-01'+1

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

  01/02/2002

String Type Operand

If you multiply, divide or subtract both strings, the result returns a DOUBLE type value.

SELECT '3'*'2';

 

                     '3'*'2'

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

       6.000000000000000e+00

The '+' operator action depends on how to set the system parameter plus_as_concat in the cubrid.conf file. For details, see Syntax/Type Related Parameter.

• If a value for plus_as_concat is yes (default value), the concatenation of two strings will be returned.
• SELECT '1'+'1';
•  
•                '1'+'1'
• ======================
•                   '11'
• If a value for plus_as_concat is no and two strings can be converted to numbers, the DOUBLE type value will be returned by adding the two numbers.
• SELECT '1'+'1';
•  
•                    '1'+'1'
• ==========================
•      2.000000000000000e+00

An error will be returned if it cannot be converted to the corresponding type.