# SQL Analytics data types

## Supported data types

SQL Analytics supports the following data types:

- Numeric types
- ByteType: Represents 1-byte signed integer numbers. The range of numbers is from
`-128`

to`127`

. - ShortType: Represents 2-byte signed integer numbers. The range of numbers is from
`-32768`

to`32767`

. - IntegerType: Represents 4-byte signed integer numbers. The range of numbers is from
`-2147483648`

to`2147483647`

. - LongType: Represents 8-byte signed integer numbers. The range of numbers is from
`-9223372036854775808`

to`9223372036854775807`

. - FloatType: Represents 4-byte single-precision floating point numbers.
- DoubleType: Represents 8-byte double-precision floating point numbers.
- DecimalType: Represents arbitrary-precision signed decimal numbers. Backed internally by
`java.math.BigDecimal`

. A`BigDecimal`

consists of an arbitrary precision integer unscaled value and a 32-bit integer scale.

- ByteType: Represents 1-byte signed integer numbers. The range of numbers is from
- String type:
- StringType: Represents character string values.

- Binary type:
- BinaryType: Represents byte sequence values.

- Boolean type:
- BooleanType: Represents Boolean values.

- Datetime types
- TimestampType: Represents values comprising values of fields year, month, day, hour, minute, and second, with the session local time-zone. The timestamp value represents an absolute point in time.
- DateType: Represents values comprising values of fields year, month and day, without a time-zone.

- Complex types
- ArrayType(elementType, containsNull): Represents values comprising a sequence of elements with the type of elementType.
`containsNull`

indicates if elements in a ArrayType value can have`null`

values. - MapType(keyType, valueType, valueContainsNull): Represents values comprising a set of key-value pairs. The data type of keys is described by keyType and the data type of values is described by valueType. For a MapType value, keys are not allowed to have
`null`

values.`valueContainsNull`

indicates if values of a MapType value can have`null`

values. - StructType(fields): Represents values with the structure described by a sequence of StructFields (fields).
- StructField(name, dataType, nullable): Represents a field in a StructType. The name of a field is indicated by
`name`

. The data type of a field is indicated by dataType.`nullable`

indicates if values of these fields can have`null`

values.

- StructField(name, dataType, nullable): Represents a field in a StructType. The name of a field is indicated by

- ArrayType(elementType, containsNull): Represents values comprising a sequence of elements with the type of elementType.

## Language mapping

The following table shows the type names as well as aliases used in SQL Analytics parser for each data type.

Data type | SQL name |
---|---|

BooleanType | BOOLEAN |

ByteType | BYTE, TINYINT |

ShortType | SHORT, SMALLINT |

IntegerType | INT, INTEGER |

LongType | LONG, BIGINT |

FloatType | FLOAT, REAL |

DoubleType | DOUBLE |

DateType | DATE |

TimestampType | TIMESTAMP |

StringType | STRING |

BinaryType | BINARY |

DecimalType | DECIMAL, DEC, NUMERIC |

CalendarIntervalType | INTERVAL |

ArrayType | ARRAY<element_type> |

StructType | STRUCT<field1_name: field1_type, field2_name: field2_type, …> |

MapType | MAP<key_type, value_type> |

## Special floating point values

SQL Analytics supports several special floating point values in a case-insensitive manner:

- Inf, +Inf, Infinity, +Infinity: positive infinity
- -Inf, -Infinity: negative infinity
- NaN: not a number

### Positive and negative infinity semantics

There is special handling for positive and negative infinity. They have the following semantics:

- Positive infinity multiplied by any positive value returns positive infinity.
- Negative infinity multiplied by any positive value returns negative infinity.
- Positive infinity multiplied by any negative value returns negative infinity.
- Negative infinity multiplied by any negative value returns positive infinity.
- Positive/negative infinity multiplied by 0 returns NaN.
- Positive/negative infinity is equal to itself.
- In aggregations, all positive infinity values are grouped together. Similarly, all negative infinity values are grouped together.
- Positive infinity and negative infinity are treated as normal values in join keys.
- Positive infinity sorts lower than NaN and higher than any other values.
- Negative infinity sorts lower than any other values.

### NaN semantics

There is special handling for NaN when dealing with `float`

or `double`

types that
do not exactly match standard floating point semantics. Specifically:

- NaN = NaN returns true.
- In aggregations, all NaN values are grouped together.
- NaN is treated as a normal value in join keys.
- NaN values go last when in ascending order, larger than any other numeric value.

### Examples

```
SELECT double('infinity') AS col;
+--------+
| col|
+--------+
|Infinity|
+--------+
SELECT float('-inf') AS col;
+---------+
| col|
+---------+
|-Infinity|
+---------+
SELECT float('NaN') AS col;
+---+
|col|
+---+
|NaN|
+---+
SELECT double('infinity') * 0 AS col;
+---+
|col|
+---+
|NaN|
+---+
SELECT double('-infinity') * (-1234567) AS col;
+--------+
| col|
+--------+
|Infinity|
+--------+
SELECT double('infinity') < double('NaN') AS col;
+----+
| col|
+----+
|true|
+----+
SELECT double('NaN') = double('NaN') AS col;
+----+
| col|
+----+
|true|
+----+
SELECT double('inf') = double('infinity') AS col;
+----+
| col|
+----+
|true|
+----+
CREATE TABLE test (c1 int, c2 double);
INSERT INTO test VALUES (1, double('infinity'));
INSERT INTO test VALUES (2, double('infinity'));
INSERT INTO test VALUES (3, double('inf'));
INSERT INTO test VALUES (4, double('-inf'));
INSERT INTO test VALUES (5, double('NaN'));
INSERT INTO test VALUES (6, double('NaN'));
INSERT INTO test VALUES (7, double('-infinity'));
SELECT COUNT(*), c2 FROM test GROUP BY c2;
+---------+---------+
| count(1)| c2|
+---------+---------+
| 2| NaN|
| 2|-Infinity|
| 3| Infinity|
+---------+---------+
```