SQL types comparison
This page contains comparison of types behaviour demonstrated by DB2, PostgreSQL, Tarantool and Oracle.
Tests may slightly differ from one DB to another.
Main features are shown in a separate table:
*
SELECT c + 10 FROM tc -- is not OK: operator does not exist: character + integer
SELECT i + '10' FROM ti -- is OK.
Current review doesn't touch distinct (aka user-defined) types.
List of types promotion: https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_promotionofdatatypes.html
List of all supported casts: https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_castingbetweendatatypes.html
As far as we are interested only in four types (INT, FLOAT, VARCHAR and BLOB) summary is following: INT, FLOAT and VARCHAR are compatible among each other; BLOB is not compatible with any of these types.
DB2 supports implicit cast between numeric data and string data: https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_implicitcastnum2str.html https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_implicitcaststr2num.html
Implicit cast of numeric value to a string results in type VARCHAR. Implicit cast of string value to a numeric results in type DECFLOAT(34).
During arithmetic operations (unary arithmetic operators + and - and infix arithmetic operators +, -, *, and /) DB2 implicitly casts string (but not BLOB) operand to the DECFLOAT data type.
Also, note that DB2 lacks of type BOOL.
By default floating point constants are represented as DECFLOAT(34).
Floating point special values: INF (INFINITY), -INF (INFINITY), NAN, -NAN, -0
Non-floating point special values: -SNaN, SNaN
-NaN < -SNaN < -INF < -0 < 0 < INF < SNaN < NaN
INF is equal to INF.
Assignment to INT from FLOAT takes place with truncation (1.9 -> 1).
Queries which lead to raise of error are marked with corresponding error message.
CREATE TABLE tf (id INT PRIMARY KEY NOT NULL, f FLOAT)
CREATE TABLE tc (id INT PRIMARY KEY NOT NULL, c CHAR(20))
CREATE TABLE ti (id INT PRIMARY KEY NOT NULL, i INT)
INSERT INTO tf VALUES (1, 1)
INSERT INTO tf VALUES (2, '2')
INSERT INTO tf VALUES (3, '2.0')
INSERT INTO tf VALUES (4, '2.123')
INSERT INTO tf VALUES (5, CAST (2 AS BLOB)) -- A value with data type "INTEGER" cannot be CAST to type "BLOB".
INSERT INTO tf VALUES (6, CAST (2.123 AS BLOB)) -- A value with data type "DECIMAL" cannot be CAST to type "BLOB".
INSERT INTO tf VALUES (7, 9007199254740993)
INSERT INTO tf VALUES (8, '1e+52')
INSERT INTO tf VALUES (9, '1e-52')
INSERT INTO tf VALUES (10, '1e+520') -- Overflow occurred during numeric data type conversion.
INSERT INTO tf VALUES (11, '1e-520') -- Overflow occurred during numeric data type conversion.
INSERT INTO tf VALUES (12, INF) -- Overflow occurred during numeric data type conversion.
INSERT INTO tf VALUES (13, -INF) -- Overflow occurred during numeric data type conversion.
INSERT INTO ti VALUES (1, 1.0)
INSERT INTO ti VALUES (2, 1.5)
INSERT INTO ti VALUES (3, '2')
INSERT INTO ti VALUES (4, '2.0')
INSERT INTO ti VALUES (5, '2.123')
INSERT INTO ti VALUES (6, CAST (2 AS BLOB)) -- A value with data type "INTEGER" cannot be CAST to type "BLOB".
INSERT INTO ti VALUES (7, CAST (2.123 AS BLOB)) -- A value with data type "DECIMAL" cannot be CAST to type "BLOB".
INSERT INTO ti VALUES (8, 9223372036854775808) -- Overflow occurred during numeric data type conversion.
INSERT INTO ti VALUES (9, INF) -- Overflow occurred during numeric data type conversion.
INSERT INTO tc VALUES (1, 1)
INSERT INTO tc VALUES (2, 1.0)
INSERT INTO tc VALUES (3, 1.5)
INSERT INTO tc VALUES (6, CAST (2 AS BLOB)) -- A value with data type "INTEGER" cannot be CAST to type "BLOB".
INSERT INTO tc VALUES (7, CAST ('abc' AS BLOB)) -- A value is not compatible with the data type of its assignment target. Target name is "C"
INSERT INTO tc VALUES (8, INF)
INSERT INTO tc VALUES (9, 9223372036854775808)
SELECT * FROM tf WHERE f = 1
SELECT * FROM tf WHERE f = '2'
SELECT * FROM tf WHERE f = '2.0'
SELECT * FROM tf WHERE f = '2.123'
SELECT * FROM tf WHERE f = '1e+52'+1
SELECT * FROM tf WHERE f = INF
SELECT * FROM tf WHERE f > 1
SELECT * FROM tf WHERE f > '1'
SELECT * FROM tf WHERE f > '1.0'
SELECT * FROM tf WHERE f > '2.122'
SELECT * FROM tf WHERE f < '1e+52'
SELECT * FROM tf WHERE f < '1e+54'
SELECT * FROM ti WHERE i = 1.0
SELECT * FROM ti WHERE i = '2'
SELECT * FROM ti WHERE i = '2.0'
SELECT * FROM ti WHERE i = '2.123'
SELECT * FROM ti WHERE i = 9223372036854775808
SELECT * FROM ti WHERE i = INF
SELECT * FROM ti WHERE i > 1.0
SELECT * FROM ti WHERE i > '1'
SELECT * FROM ti WHERE i > '1.0'
SELECT * FROM ti WHERE i > '2.122'
SELECT * FROM ti WHERE i < 9223372036854775808
SELECT * FROM ti WHERE i < INF
SELECT * FROM tc WHERE c = 1
SELECT * FROM tc WHERE c = 1.0
SELECT * FROM tc WHERE c = '1.5'
SELECT * FROM tc WHERE c = 1.5
SELECT * FROM tc WHERE c = INF
SELECT * FROM tc WHERE c > 1
SELECT * FROM tc WHERE c > '1'
SELECT * FROM tc WHERE c > '1.0'
SELECT * FROM tc WHERE c > '2.122'
SELECT * FROM tc WHERE c < INF
SELECT * FROM tf WHERE f IN (1, '2', '2.0', '2.123')
SELECT * FROM tf WHERE f BETWEEN 1 AND '2'
SELECT * FROM tf WHERE f BETWEEN '2.0' AND '2.123'
SELECT f + '10' FROM tf
SELECT * FROM ti WHERE i IN (1.0, '2', '2.0', '2.123', 9223372036854775808)
SELECT * FROM ti WHERE i BETWEEN '2' AND '2.0'
SELECT * FROM ti WHERE i BETWEEN '2.0' AND '2.123'
SELECT i + '10' FROM ti
SELECT * FROM tc WHERE c IN (1, 1.0, '1.5', 1.5)
SELECT * FROM tc WHERE c BETWEEN 1 AND 1.5
SELECT * FROM tc WHERE c BETWEEN '1' AND '1.5'
SELECT c + '10' FROM tc
SELECT c + 10 FROM tc
SELECT * FROM tf, ti WHERE f = i
SELECT * FROM tf, ti WHERE i = f
SELECT * FROM tf, tc WHERE f = c
SELECT * FROM tf, tc WHERE c = f
SELECT * FROM ti, tc WHERE i = c
SELECT * FROM ti, tc WHERE c = i
SELECT * FROM tf WHERE INF = INF
SELECT * FROM tf WHERE INF = -INF
SELECT * FROM tf WHERE 0 = -0
UPDATE tf SET f = 1
UPDATE tf SET f = '2'
UPDATE tf SET f = '2.0'
UPDATE tf SET f = '2.123'
UPDATE tf SET f = CAST(2 AS BLOB) -- A value with data type "INTEGER" cannot be CAST to type "BLOB".
UPDATE tf SET f = CAST(2.123 AS BLOB) -- A value with data type "DECIMAL" cannot be CAST to type "BLOB".
UPDATE tf SET f = 9007199254740993
UPDATE tf SET f = '1e+52'
UPDATE tf SET f = '1e-52'
UPDATE tf SET f = INF -- Overflow occurred during numeric data type conversion.
UPDATE ti SET i = 1.0
UPDATE ti SET i = 1.9
UPDATE ti SET i = '2'
UPDATE ti SET i = '2.0'
UPDATE ti SET i = '2.123'
UPDATE ti SET i = CAST (2 AS BLOB) -- A value with data type "INTEGER" cannot be CAST to type "BLOB".
UPDATE ti SET i = CAST (2.123 AS BLOB) -- A value with data type "DECIMAL" cannot be CAST to type "BLOB".
UPDATE ti SET i = 9223372036854775808 -- Overflow occurred during numeric data type conversion.
UPDATE tc SET c = 1
UPDATE tc SET c = 1.0
UPDATE tc SET c = 1.5
UPDATE tc SET c = '2.0'
UPDATE tc SET c = '2.123'
UPDATE tc SET c = CAST (2 AS BLOB) -- A value with data type "INTEGER" cannot be CAST to type "BLOB".
UPDATE tc SET c = CAST (2.123 AS BLOB) -- A value with data type "DECIMAL" cannot be CAST to type "BLOB".
UPDATE tc SET c = 9223372036854775808
PostgreSQL lacks of traditional BLOB type. Instead, it has BYTEA type to represent binary strings.
INT and FLOAT are compatible with each other. VARCHAR and numeric types (INT and FLOAT) are parially compatible with each other: it is OK to assign float with string values; it is OK to assign int with some string values ('1.0' or '1.5' are not handled); it is not OK to compare numeric with string values.
BLOB is not compatible with numeric types even if it is forced with CAST operator; BLOB can be assigned to VARCHAR, but can not be compared with it.
Arithmetic operations are not allowed on string types.
By default floating point constants are represented as numeric data. Integer constants represented as INT, BIGINT or numeric depending on their size.
Floating point special values: 'INF' ('INFINITY'), '-INF' (INFINITY), 'NAN', -0
INF is equal to INF, -0 is equal to 0.
Assignment to INT from FLOAT takes place with rounding (1.9 -> 2).
Queries which lead to raise of error are marked with corresponding error message.
CREATE TABLE tf (id INT PRIMARY KEY NOT NULL, f FLOAT)
CREATE TABLE tc (id INT PRIMARY KEY NOT NULL, c CHAR(20))
CREATE TABLE ti (id INT PRIMARY KEY NOT NULL, i INT)
CREATE TABLE tb (id INT PRIMARY KEY NOT NULL, b BYTEA)
INSERT INTO tf VALUES (1, 1)
INSERT INTO tf VALUES (2, '2')
INSERT INTO tf VALUES (3, '2.0')
INSERT INTO tf VALUES (4, '2.123')
INSERT INTO tf VALUES (5, CAST (2 AS BYTEA)) -- cannot cast type integer to bytea
INSERT INTO tf VALUES (6, CAST (2.123 AS BYTEA)) -- cannot cast type numeric to bytea
INSERT INTO tf VALUES (7, 9007199254740993)
INSERT INTO tf VALUES (8, '1e+52')
INSERT INTO tf VALUES (9, '1e-52')
INSERT INTO tf VALUES (10, '1e+520') -- "1e+520" is out of range for type double precision
INSERT INTO tf VALUES (11, '1e-520') -- "1e-520" is out of range for type double precision
INSERT INTO tf VALUES (12, 'INF')
INSERT INTO tf VALUES (13, '-INF')
INSERT INTO ti VALUES (1, 1.0)
INSERT INTO ti VALUES (2, 1.5)
INSERT INTO ti VALUES (3, '2')
INSERT INTO ti VALUES (4, '2.0') -- invalid input syntax for integer: "2.0"
INSERT INTO ti VALUES (5, '2.123') -- invalid input syntax for integer: "2.123"
INSERT INTO ti VALUES (6, CAST (2 AS BYTEA)) -- cannot cast type integer to bytea
INSERT INTO ti VALUES (7, CAST (2.123 AS BYTEA)) -- cannot cast type numeric to bytea
INSERT INTO ti VALUES (8, 9223372036854775808) -- integer out of range
INSERT INTO ti VALUES (9, 'INF') -- invalid input syntax for integer: "INF"
INSERT INTO tc VALUES (1, 1)
INSERT INTO tc VALUES (2, 1.0)
INSERT INTO tc VALUES (3, 1.5)
INSERT INTO tc VALUES (6, CAST (2 AS BYTEA)) -- cannot cast type integer to bytea
INSERT INTO tc VALUES (7, CAST ('abc' AS BYTEA))
INSERT INTO tc VALUES (8, 'INF')
INSERT INTO tc VALUES (9, 9223372036854775808)
INSERT INTO tb VALUES (1, 'abc')
INSERT INTO tb VALUES (2, '1')
INSERT INTO tb VALUES (3, 1.0) -- column "b" is of type bytea but expression is of type numeric
INSERT INTO tb VALUES (4, 1) -- column "b" is of type bytea but expression is of type integer
SELECT * FROM tf WHERE f = 1
SELECT * FROM tf WHERE f = '2'
SELECT * FROM tf WHERE f = '2.0'
SELECT * FROM tf WHERE f = '2.123'
SELECT * FROM tf WHERE f = '1e+52'+1
SELECT * FROM tf WHERE f = INF
SELECT * FROM tf WHERE f > 1
SELECT * FROM tf WHERE f > '1'
SELECT * FROM tf WHERE f > '1.0'
SELECT * FROM tf WHERE f > '2.122'
SELECT * FROM tf WHERE f < 1e+52
SELECT * FROM tf WHERE f < 1e+54
SELECT * FROM ti WHERE i = 1.0
SELECT * FROM ti WHERE i = '2'
SELECT * FROM ti WHERE i = '2.0' -- invalid input syntax for integer: "2.0"
SELECT * FROM ti WHERE i = '2.123' -- invalid input syntax for integer: "2.123"
SELECT * FROM ti WHERE i = 2.122
SELECT * FROM ti WHERE i = 9223372036854775808
SELECT * FROM ti WHERE i = 'INF' -- invalid input syntax for integer: "INF"
SELECT * FROM ti WHERE i > 1.0
SELECT * FROM ti WHERE i > '1'
SELECT * FROM ti WHERE i > '1.0' -- invalid input syntax for integer: "1.0"
SELECT * FROM ti WHERE i > '2.122' -- invalid input syntax for integer: "2.122"
SELECT * FROM ti WHERE i > 2.122
SELECT * FROM ti WHERE i < 9223372036854775808
SELECT * FROM ti WHERE i < 'INF' -- invalid input syntax for integer: "INF"
SELECT * FROM tc WHERE c = 1 -- operator does not exist: character = integer
SELECT * FROM tc WHERE c = 1.0 -- operator does not exist: character = numeric
SELECT * FROM tc WHERE c = 1.5 -- operator does not exist: character = numeric
SELECT * FROM tc WHERE c = '1.5'
SELECT * FROM tc WHERE c = 'INF'
SELECT * FROM tc WHERE c > 1 -- operator does not exist: character > integer
SELECT * FROM tc WHERE c > '1'
SELECT * FROM tc WHERE c > '1.0'
SELECT * FROM tc WHERE c > '2.122'
SELECT * FROM tc WHERE c < 'INF'
SELECT * FROM tb WHERE b = 'abc'
SELECT * FROM tb WHERE b = 1 -- operator does not exist: bytea = integer
SELECT b + '10' FROM tb -- operator does not exist: bytea + unknown
SELECT * FROM tf WHERE f IN (1, '2', '2.0', '2.123')
SELECT * FROM tf WHERE f BETWEEN 1 AND '2'
SELECT * FROM tf WHERE f BETWEEN '2.0' AND '2.123'
SELECT f + '10' FROM tf
SELECT * FROM ti WHERE i IN (1.0, '2', '2.0', '2.123', 9223372036854775808)
SELECT * FROM ti WHERE i BETWEEN '2' AND '2.0' -- invalid input syntax for integer: "2.0"
SELECT * FROM ti WHERE i BETWEEN '2.0' AND '2.123' -- invalid input syntax for integer: "2.0"
SELECT i + '10' FROM ti
SELECT * FROM tc WHERE c IN (1, 1.0, '1.5', 1.5) -- operator does not exist: character = integer
SELECT * FROM tc WHERE c BETWEEN 1 AND 1.5 -- operator does not exist: character >= integer
SELECT * FROM tc WHERE c BETWEEN '1' AND '1.5'
SELECT c + '10' FROM tc -- operator does not exist: character + unknown
SELECT c + 10 FROM tc -- operator does not exist: character + integer
SELECT * FROM tf, ti WHERE f = i
SELECT * FROM tf, ti WHERE i = f
SELECT * FROM tf, tc WHERE f = c -- operator does not exist: double precision = character
SELECT * FROM tf, tc WHERE c = f -- operator does not exist: character = double precision
SELECT * FROM ti, tc WHERE i = c -- operator does not exist: integer = character
SELECT * FROM ti, tc WHERE c = i -- operator does not exist: character = integer
SELECT * FROM tc, tb WHERE c = b -- operator does not exist: character = bytea
SELECT * FROM tf WHERE f = CAST(1 AS FLOAT)
SELECT * FROM tf WHERE f = CAST('1' AS FLOAT)
SELECT * FROM tf, ti WHERE f = CAST(i AS FLOAT)
SELECT * FROM tc WHERE c = CAST('abc' AS BYTEA) -- operator does not exist: character = bytea
SELECT * FROM tf WHERE 'INF' = 'INF'
SELECT * FROM tf WHERE 'INF = '-INF'
SELECT * FROM tf WHERE 0 = -0
UPDATE tf SET f = 1
UPDATE tf SET f = '2'
UPDATE tf SET f = '2.0'
UPDATE tf SET f = '2.123'
UPDATE tf SET f = CAST(2 AS BLOB) -- cannot cast type integer to bytea
UPDATE tf SET f = CAST(2.123 AS BLOB) -- cannot cast type numeric to bytea
UPDATE tf SET f = 9007199254740993
UPDATE tf SET f = '1e+52'
UPDATE tf SET f = '1e-52'
UPDATE tf SET f = 'INF'
UPDATE ti SET i = 1.0
UPDATE ti SET i = 1.9
UPDATE ti SET i = '2'
UPDATE ti SET i = '2.0' -- invalid input syntax for integer: "2.0"
UPDATE ti SET i = '2.123' -- invalid input syntax for integer: "2.123"
UPDATE ti SET i = 9223372036854775808 -- integer out of range
UPDATE ti SET i = INF -- invalid input syntax for integer: "INF"
UPDATE tc SET c = 1
UPDATE tc SET c = 1.0
UPDATE tc SET c = 1.5
UPDATE tc SET с = '2.0'
UPDATE tc SET c = '2.123'
UPDATE tc SET c = CAST ('abc' AS BYTEA)
UPDATE tc SET c = 9223372036854775808
CREATE TABLE tf (id INT PRIMARY KEY NOT NULL, f FLOAT)
CREATE TABLE tc (id INT PRIMARY KEY NOT NULL, c CHAR(20))
CREATE TABLE ti (id INT PRIMARY KEY NOT NULL, i INT)
CREATE TABLE tb (id INT PRIMARY KEY NOT NULL, b BLOB)
INSERT INTO tf VALUES (1, 1)
INSERT INTO tf VALUES (2, '2')
INSERT INTO tf VALUES (3, '2.0')
INSERT INTO tf VALUES (4, '2.123')
INSERT INTO tf VALUES (5, CAST (2 AS BLOB)) -- error: 'Type mismatch: can not convert 2 to real'
INSERT INTO tf VALUES (6, CAST (2.123 AS BLOB)) -- error: 'Type mismatch: can not convert 2.123 to real'
INSERT INTO tf VALUES (7, 9007199254740993)
INSERT INTO tf VALUES (8, '1e+52')
INSERT INTO tf VALUES (9, '1e-52')
INSERT INTO tf VALUES (10, '1e+520')
INSERT INTO tf VALUES (11, '1e-520')
INSERT INTO tf VALUES (12, 'INF') -- error: 'Type mismatch: can not convert INF to real'
INSERT INTO tf VALUES (13, '-INF') -- error: 'Type mismatch: can not convert -INF to real'
INSERT INTO ti VALUES (1, 1.0)
INSERT INTO ti VALUES (2, 1.5)
INSERT INTO ti VALUES (3, '2')
INSERT INTO ti VALUES (4, '2.0')
INSERT INTO ti VALUES (5, '2.123') -- error: 'Type mismatch: can not convert 2.123 to integer'
INSERT INTO ti VALUES (6, CAST (2 AS BLOB))
INSERT INTO ti VALUES (7, CAST (2.123 AS BLOB)) -- error: 'Type mismatch: can not convert 2.123 to integer'
INSERT INTO ti VALUES (8, 9223372036854775808) -- error: 'oversized integer: 9223372036854775808'
INSERT INTO ti VALUES (9, 'INF') -- error: 'Type mismatch: can not convert INF to integer'
INSERT INTO tc VALUES (1, 1)
INSERT INTO tc VALUES (2, 1.0)
INSERT INTO tc VALUES (3, 1.5)
INSERT INTO tc VALUES (6, CAST (2 AS BLOB)) -- error: 'Tuple field 2 type does not match one required by operation: expected string'
INSERT INTO tc VALUES (7, CAST ('abc' AS BLOB)) -- error: 'Tuple field 2 type does not match one required by operation: expected string'
INSERT INTO tc VALUES (8, 'INF')
INSERT INTO tc VALUES (9, 9223372036854775808) -- - error: 'oversized integer: 9223372036854775808'
INSERT INTO tb VALUES (1, 'abc')
INSERT INTO tb VALUES (2, '1')
INSERT INTO tb VALUES (3, 1.0)
INSERT INTO tb VALUES (4, 1)
SELECT * FROM tf WHERE f = 1
SELECT * FROM tf WHERE f = '2'
SELECT * FROM tf WHERE f = '2.0'
SELECT * FROM tf WHERE f = '2.123'
SELECT * FROM tf WHERE f = '1e+52'+1
SELECT * FROM tf WHERE f = 'INF'
SELECT * FROM tf WHERE f > 1
SELECT * FROM tf WHERE f > '1'
SELECT * FROM tf WHERE f > '1.0'
SELECT * FROM tf WHERE f > '2.122'
SELECT * FROM tf WHERE f < '1e+52'
SELECT * FROM tf WHERE f < '1e+54'
SELECT * FROM ti WHERE i = 1.0
SELECT * FROM ti WHERE i = '2'
SELECT * FROM ti WHERE i = '2.0'
SELECT * FROM ti WHERE i = '2.123'
SELECT * FROM ti WHERE i = 9223372036854775808 -- error: 'oversized integer: 9223372036854775808'
SELECT * FROM ti WHERE i = 'INF'
SELECT * FROM ti WHERE i > 1.0
SELECT * FROM ti WHERE i > '1'
SELECT * FROM ti WHERE i > '1.0'
SELECT * FROM ti WHERE i > '2.122'
SELECT * FROM ti WHERE i < 9223372036854775808 -- error: 'oversized integer: 9223372036854775808'
SELECT * FROM ti WHERE i < 'INF'
SELECT * FROM tc WHERE c = 1 -- - error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM tc WHERE c = 1.0 -- - error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM tc WHERE c = '1.5'
SELECT * FROM tc WHERE c = 1.5 -- - error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM tc WHERE c = 'INF'
SELECT * FROM tc WHERE c > 1 -- error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM tc WHERE c > '1'
SELECT * FROM tc WHERE c > '1.0'
SELECT * FROM tc WHERE c > '2.122'
SELECT * FROM tc WHERE c < 'INF'
SELECT * FROM tb WHERE b = 'abc' -- error: 'Type mismatch: can not convert abc to numeric'
SELECT * FROM tb WHERE b = 1 -- error: 'Type mismatch: can not convert abc to numeric'
SELECT b + '10' FROM tb -- error: 'Type mismatch: can not convert abc to numeric'
SELECT * FROM tf WHERE f IN (1, '2', '2.0', '2.123')
SELECT * FROM tf WHERE f BETWEEN 1 AND '2'
SELECT * FROM tf WHERE f BETWEEN '2.0' AND '2.123'
SELECT f + '10' FROM tf
SELECT * FROM ti WHERE i IN (1.0, '2', '2.0', '2.123', 9223372036854775808) -- error: 'oversized integer: 9223372036854775808'
SELECT * FROM ti WHERE i BETWEEN '2' AND '2.0'
SELECT * FROM ti WHERE i BETWEEN '2.0' AND '2.123'
SELECT i + '10' FROM ti
SELECT * FROM tc WHERE c IN (1, 1.0, '1.5', 1.5)
SELECT * FROM tc WHERE c BETWEEN 1 AND 1.5 -- error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM tc WHERE c BETWEEN '1' AND '1.5'
SELECT c + '10' FROM tc -- error: 'Type mismatch: can not convert INF to numeric'
SELECT c + 10 FROM tc -- error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM tf, ti WHERE f = i
SELECT * FROM tf, ti WHERE i = f
SELECT * FROM tf, tc WHERE f = c
SELECT * FROM tf, tc WHERE c = f -- error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM ti, tc WHERE i = c
SELECT * FROM ti, tc WHERE c = i -- error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM tc, tb WHERE c = b
SELECT * FROM tf WHERE f = CAST(1 AS FLOAT)
SELECT * FROM tf WHERE f = CAST('1' AS FLOAT)
SELECT * FROM tf, ti WHERE f = CAST(i AS FLOAT)
SELECT * FROM tc WHERE c = CAST('abc' AS BLOB)
SELECT * FROM tf WHERE 'INF' = 'INF'
SELECT * FROM tf WHERE 'INF' = '-INF'
SELECT * FROM tf WHERE 0 = -0
UPDATE tf SET f = 1
UPDATE tf SET f = '2'
UPDATE tf SET f = '2.0'
UPDATE tf SET f = '2.123'
UPDATE tf SET f = CAST(2 AS BLOB) -- error: 'Type mismatch: can not convert 2 to real'
UPDATE tf SET f = CAST(2.123 AS BLOB) -- error: 'Type mismatch: can not convert 2.123 to real'
UPDATE tf SET f = 9007199254740993
UPDATE tf SET f = '1e+52'
UPDATE tf SET f = '1e-52'
UPDATE tf SET f = 'INF' -- error: 'Type mismatch: can not convert INF to real'
UPDATE ti SET i = 1.0
UPDATE ti SET i = 1.9
UPDATE ti SET i = '2'
UPDATE ti SET i = '2.0'
UPDATE ti SET i = '2.123' -- error: 'Type mismatch: can not convert 2.123 to integer'
UPDATE ti SET i = CAST (2 AS BLOB)
UPDATE ti SET i = CAST (2.123 AS BLOB) -- error: 'Type mismatch: can not convert 2.123 to integer'
UPDATE ti SET i = 9223372036854775808 -- error: 'oversized integer: 9223372036854775808'
UPDATE ti SET i = 'INF' -- error: 'Type mismatch: can not convert INF to integer'
UPDATE tc SET c = 1
UPDATE tc SET c = 1.0
UPDATE tc SET c = 1.5
UPDATE tc SET c = '2.0'
UPDATE tc SET c = '2.123'
UPDATE tc SET c = CAST (2 AS BLOB) -- error: 'Tuple field 2 type does not match one required by operation: expected string'
UPDATE tc SET c = CAST (2.123 AS BLOB) -- error: 'Tuple field 2 type does not match one required by operation: expected string'
UPDATE tc SET c = 9223372036854775808 -- error: 'oversized integer: 9223372036854775808'
Strings and BLOBs are comparable and implicitly casted to each other.
INSERT INTO tf VALUES (5, CAST (2 AS BLOB)) -- error: 'Type mismatch: can not convert 2 to real'
This diagnostic error message may seem quite strange. However, 2 is of type BLOB after cast, and BLOB can not be converted to real. So, in fact error is OK here.
INSERT INTO tf VALUES (12, 'INF') -- error: 'Type mismatch: can not convert INF to real'
Tarantool interpets 'INF' not as special literal meaning INFINITY, but threats as a simple string.
INSERT INTO ti VALUES (5, '2.123') -- error: 'Type mismatch: can not convert 2.123 to integer'
This behaviour is quite similar to Oracle and PostgreSQL ones (but they also don't allow '2.0').
INSERT INTO tc VALUES (6, CAST (2 AS BLOB)) -- error: 'Tuple field 2 type does not match one required by operation: expected string'
It is the same error as in first case, but diagnostic message is different. Should be fixed.
SELECT * FROM ti WHERE i = 9223372036854775808 -- error: 'oversized integer: 9223372036854775808'
This error is OK, at least now (until numeric type is not implemented). Other DBs don't throw this error since they feature numeric type(s) and can fit numbers > int64.
SELECT * FROM ti WHERE i < 'INF'
At least seems strange since during assignment 'INF' can not be converted.
SELECT * FROM tc WHERE c = 1 -- - error: 'Type mismatch: can not convert INF to numeric'
The problem is that TC table contains values which can not be converted to numeric. The same behaviour demonstrates DB2 with error "Invalid character found in a character string argument of the function "DECFLOAT"" and Oracle with simple error "invalid number".
SELECT * FROM tf, tc WHERE f = c
SELECT * FROM tf, tc WHERE c = f -- error: 'Type mismatch: can not convert INF to numeric'
SELECT * FROM ti, tc WHERE i = c
SELECT * FROM ti, tc WHERE c = i -- error: 'Type mismatch: can not convert INF to numeric'
Resulting type of comparison depends on the LHS. This is not OK I guess.
UPDATE tc SET c = 9223372036854775808 -- error: 'oversized integer: 9223372036854775808'
UPDATE tf SET f = 9223372036854775808 -- error: 'oversized integer: 9223372036854775808'
These errors can be fixed: in the first case we simply shouldn't convert input string to int at all; in the second case we can store it as float avoiding intermediate storage in int.
Floating point special values: https://docs.oracle.com/cd/E37483_01/server.751/es_eql/src/ceql_literals_nan.html
A zero divided by zero results in NaN;
A positive number divided by zero results in inf;
A negative number divided by zero results in -inf.
List of compatible types: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm
List of implicitly convertible types: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm#i163326
The same as in PostgreSQL and DB2, BLOB can not be converted to any other type.
Arithmetic operations are allowed on string types.
Assignment to INT from FLOAT takes place with rounding (1.9 -> 2).
By default floating point constants are represented as numeric; integers > int64 are allowed.
CREATE TABLE tf (id INT PRIMARY KEY NOT NULL, f FLOAT)
CREATE TABLE tc (id INT PRIMARY KEY NOT NULL, c CHAR(20))
CREATE TABLE ti (id INT PRIMARY KEY NOT NULL, i INT)
CREATE TABLE tb (id INT PRIMARY KEY NOT NULL, b BLOB)
INSERT INTO tf VALUES (1, 1)
INSERT INTO tf VALUES (2, '2')
INSERT INTO tf VALUES (3, '2.0') -- invalid number
INSERT INTO tf VALUES (4, '2.123') -- invalid number
INSERT INTO tf VALUES (5, CAST (2 AS BLOB)) -- inconsistent datatypes: expected - got BLOB
INSERT INTO tf VALUES (6, CAST (2.123 AS BLOB)) -- inconsistent datatypes: expected - got BLOB
INSERT INTO tf VALUES (7, 9007199254740993) -- numeric overflow
INSERT INTO tf VALUES (8, '1e+52')
INSERT INTO tf VALUES (9, '1e-52')
INSERT INTO tf VALUES (10, '1e+520') -- numeric overflow
INSERT INTO tf VALUES (11, '1e-520')
INSERT INTO tf VALUES (12, 'INF') -- invalid number
INSERT INTO tf VALUES (13, '-INF') -- invalid number
INSERT INTO ti VALUES (1, 1.0)
INSERT INTO ti VALUES (2, 1.5)
INSERT INTO ti VALUES (3, '2')
INSERT INTO ti VALUES (4, '2.0') -- invalid number
INSERT INTO ti VALUES (5, '2.123') -- invalid number
INSERT INTO ti VALUES (6, CAST (2 AS BLOB)) -- inconsistent datatypes: expected - got BLOB
INSERT INTO ti VALUES (7, CAST (2.123 AS BLOB)) -- inconsistent datatypes: expected - got BLOB
INSERT INTO ti VALUES (8, 9223372036854775808)
INSERT INTO tc VALUES (1, 1)
INSERT INTO tc VALUES (2, 1.0)
INSERT INTO tc VALUES (3, 1.5)
INSERT INTO tc VALUES (6, CAST (2 AS BLOB)) -- inconsistent datatypes: expected - got BLOB
INSERT INTO tc VALUES (7, CAST ('abc' AS BLOB)) -- inconsistent datatypes: expected - got BLOB
INSERT INTO tc VALUES (8, 'inf')
INSERT INTO tc VALUES (9, 9223372036854775808)
INSERT INTO tb VALUES (1, 'abc')
INSERT INTO tb VALUES (2, '1')
INSERT INTO tb VALUES (3, 1.0) -- inconsistent datatypes: expected BLOB got NUMBER
INSERT INTO tb VALUES (4, 1) -- inconsistent datatypes: expected BLOB got NUMBER
SELECT * FROM tf WHERE f = 1
SELECT * FROM tf WHERE f = '2'
SELECT * FROM tf WHERE f = '2.0' -- invalid number
SELECT * FROM tf WHERE f = '2.123' -- invalid number
SELECT * FROM tf WHERE f = '1e+52'+1
SELECT * FROM tf WHERE f = 'inf'-- invalid number
SELECT * FROM tf WHERE f > 1
SELECT * FROM tf WHERE f > '1'
SELECT * FROM tf WHERE f > '1.0' -- invalid number
SELECT * FROM tf WHERE f > '2.122' -- invalid number
SELECT * FROM tf WHERE f < '1e+52'
SELECT * FROM tf WHERE f < '1e+54'
SELECT * FROM ti WHERE i = 1.0
SELECT * FROM ti WHERE i = '2'
SELECT * FROM ti WHERE i = '2.0' -- invalid number
SELECT * FROM ti WHERE i = '2.123' -- invalid number
SELECT * FROM ti WHERE i = 9223372036854775808
SELECT * FROM ti WHERE i = 'inf' -- invalid number
SELECT * FROM ti WHERE i > 1.0
SELECT * FROM ti WHERE i > '1'
SELECT * FROM ti WHERE i > '1.0' -- invalid number
SELECT * FROM ti WHERE i > '2.122' -- invalid number
SELECT * FROM ti WHERE i < 9223372036854775808
SELECT * FROM ti WHERE i < 'inf' -- invalid number
SELECT * FROM tc WHERE c = 1
SELECT * FROM tc WHERE c = 1.0
SELECT * FROM tc WHERE c = '1.5'
SELECT * FROM tc WHERE c = 1.5
SELECT * FROM tc WHERE c > 1
SELECT * FROM tc WHERE c > '1'
SELECT * FROM tc WHERE c > '1.0'
SELECT * FROM tc WHERE c > '2.122'
SELECT * FROM tb WHERE b = 'abc' -- inconsistent datatypes: expected - got BLOB
SELECT * FROM tb WHERE b = 1 -- inconsistent datatypes: expected - got BLOB
SELECT b + '10' FROM tb -- inconsistent datatypes: expected NUMBER got BLOB
SELECT * FROM tf WHERE f IN (1, '2', '2.0', '2.123') -- invalid number
SELECT * FROM tf WHERE f BETWEEN 1 AND '2'
SELECT * FROM tf WHERE f BETWEEN '2.0' AND '2.123' -- invalid number
SELECT f + '10' FROM tf
SELECT * FROM ti WHERE i IN (1.0, '2', '2.0', '2.123', 9223372036854775808) -- invalid number
SELECT * FROM ti WHERE i BETWEEN '2' AND '2.0' -- invalid number
SELECT * FROM ti WHERE i BETWEEN '2.0' AND '2.123' -- invalid number
SELECT i + '10' FROM ti
SELECT * FROM tc WHERE c IN (1, 1.0, '1.5', 1.5)
SELECT * FROM tc WHERE c BETWEEN 1 AND 1.5
SELECT * FROM tc WHERE c BETWEEN '1' AND '1.5'
SELECT c + '10' FROM tc
SELECT c + 10 FROM tc
SELECT * FROM tf, ti WHERE f = i
SELECT * FROM tf, ti WHERE i = f
SELECT * FROM tf, tc WHERE f = c
SELECT * FROM tf, tc WHERE c = f
SELECT * FROM ti, tc WHERE i = c
SELECT * FROM ti, tc WHERE c = i
SELECT * FROM tf WHERE f = CAST(1 AS FLOAT)
SELECT * FROM tf WHERE f = CAST('1' AS FLOAT)
SELECT * FROM tf, ti WHERE f = CAST(i AS FLOAT)
UPDATE tf SET f = 1
UPDATE tf SET f = '2'
UPDATE tf SET f = '2.0' -- invalid number
UPDATE tf SET f = '2.123' -- invalid number
UPDATE tf SET f = CAST(2 AS BLOB) -- inconsistent datatypes: expected - got BLOB
UPDATE tf SET f = CAST(2.123 AS BLOB) -- inconsistent datatypes: expected - got BLOB
UPDATE tf SET f = 9007199254740993
UPDATE tf SET f = '1e+52'
UPDATE tf SET f = '1e-52'
UPDATE ti SET i = 1.0
UPDATE ti SET i = 1.9
UPDATE ti SET i = '2'
UPDATE ti SET i = '2.0' -- invalid number
UPDATE ti SET i = '2.123' -- invalid number
UPDATE ti SET i = 9223372036854775808
UPDATE tc SET c = 1
UPDATE tc SET c = 1.0
UPDATE tc SET c = 1.5
UPDATE tc SET c = '2.0'
UPDATE tc SET c = '2.123'
UPDATE tc SET c = CAST ('abc' AS BLOB) -- inconsistent datatypes: expected - got BLOB
UPDATE tc SET c = CAST (2.123 AS BLOB) -- inconsistent datatypes: expected - got BLOB
UPDATE tc SET c = 9223372036854775808