Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ HetuEngine SQL Tutorial/ Implicit Conversion Table
Updated on 2022-11-18 GMT+08:00
View PDF

Implicit Conversion Table

After the implicit conversion function is enabled, implicit conversion is performed when the data type does not match. However, not all data types support implicit conversion. The following table lists the data type conversion tables supported by the implicit conversion function.

Table 1 Implicit conversion table

-

BOOLEAN

TINYINT

SMALLINT

INTEGER

BIGINT

REAL

DOUBLE

DECIMAL

VARCHAR

BOOLEAN

\

Y(1)

Y

Y

Y

Y

Y

Y

Y(2)

TINYINT

Y(3)

\

Y

Y

Y

Y

Y

Y

Y

SMALLINT

Y

Y(4)

\

Y

Y

Y

Y

Y

Y

INTEGER

Y

Y

Y

\

Y

Y

Y

Y

Y

BIGINT

Y

Y

Y

Y

\

Y

Y

Y

Y

REAL

Y

Y

Y

Y

Y

\

Y

Y(5)

Y

DOUBLE

Y

Y

Y

Y

Y

Y

\

Y

Y

DECIMAL

Y

Y

Y

Y

Y

Y

Y

\(6)

Y

VARCHAR

Y(7)

Y

Y

Y

Y

Y

Y

Y(8)

\

CHAR

N

N

N

N

N

N

N

N

Y

VARBINARY

N

N

N

N

N

N

N

N

N

JSON

N

N

N

N

N

N

N

N

Y

DATE

N

N

N

N

N

N

N

N

Y

TIME

N

N

N

N

N

N

N

N

Y

TIME WITH TIME ZONE

N

N

N

N

N

N

N

N

Y

TIMESTAMP

N

N

N

N

N

N

N

N

Y

TIMESTAMP WITH TIME ZONE

N

N

N

N

N

N

N

N

Y
Table 2 Implicit conversion table(continued)

-

CHAR

VARBINARY

JSON

DATE

TIME

TIME WITH TIME ZONE

TIMESTAMP

TIMESTAMP WITH TIME ZONE

BOOLEAN

N

N

Y

N

N

N

N

N

TINYINT

N

N

Y

N

N

N

N

N

SMALLINT

N

N

Y

N

N

N

N

N

INTEGER

N

N

Y

N

N

N

N

N

BIGINT

N

N

Y

N

N

N

N

N

REAL

N

N

Y

N

N

N

N

N

DOUBLE

N

N

Y

N

N

N

N

N

DECIMAL

N

N

Y

N

N

N

N

N

VARCHAR

Y(9)

Y

Y

Y(10)

Y(11)

Y(12)

Y(13)

Y

CHAR

\

N

N

N

N

N

N

N

VARBINARY

N

\

N

N

N

N

N

N

JSON

N

N

\

N

N

N

N

N

DATE

N

N

Y

\

N

N

Y(14)

Y

TIME

N

N

N

N

\

Y(15)

Y(16)

Y

TIME WITH TIME ZONE

N

N

N

N

Y

\

Y

Y

TIMESTAMP

N

N

N

Y

Y

Y

\

Y

TIMESTAMP WITH TIME ZONE

N

N

N

Y

Y

Y

Y

\
  • The result of BOOLEAN->NUMBER can only be 0 or 1.
  • The result of BOOLEAN->VARCHAR can only be TRUE or FALSE.
  • For NUMBER -> BOOLEAN, 0 is false, and other values are true.
  • The value of BIG PRECISION -> SMALL cannot be greater than the value range of the target type. Otherwise, an error is reported.
  • The integer part of REAL/FLOAT ->DECIMAL must be greater than or equal to the integer part of REAL/FLOAT. Otherwise, an error is reported during conversion. If the decimal part is insufficient, the data is truncated.
  • The integer part of the DECIMAL->DECIMAL target type must be greater than or equal to that of the source type. Otherwise, the conversion fails and the decimal part will be truncated if it is insufficient.
  • For VARCHAR->BOOLEAN, only 0, 1, TRUE, and FALSE can be converted.
  • For VARCHAR->DECIMAL, if the number of decimal places is greater than the number of decimal places of the target decimal, truncation occurs. If the number of integer places is greater than the number of decimal places of the target decimal, an error is reported.
  • For VARCHAR->CHAR, if the length of VARCHAR exceeds the target length, truncation occurs.
  • For VARCHAR->DATE, dates can only be separated by hyphens (-), for example, 2000-01-01.
  • For VARCHAR->TIME, only the strict date format HH:MM:SS.XXX is supported.
  • For VARCHAR->TIME ZONE, only the strict time format is supported, for example, 01:02:03.456 America/Los_Angeles.
  • For VARCHAR->TIMESTAMP, only the strict format YYYY-MM-DD HH:MM:SS.XXX is supported.
  • DATE->TIMESTAMP automatically supplements the time by adding 0s to the end., for example, 2010-01-01 to 2010-01-01 00:00:00.000.
  • TIME->TIME WITH TIME ZONE automatically supplement the time zone.
  • TIME->TIMESTAMP automatically supplement date. The default value 1970-01-01 is used.
Parent topic: HetuEngine SQL Tutorial