Cleanup SQL types #4019

kyukhin · 2019-02-25T18:21:04Z

Tarantool version: 2.1

OS version: any

Bug description:

SQL type system of Tarantool 2.1 should be more or less consistent.

Let's do some cleanup:

Remove DATE/TIME/DATETIME from prarser
Remove CHAR from parser
Remove DECIMAL/NUMERIC/NUM from parser

So, at the end of the day following types should be supported by SQL:

TEXT/VARCHAR(N), N is ignored, represented as STRING in space format
INTEGER/INT, represented as INTEGER in space format
FLOAT/REAL/DOUBLE, represented as NUMBER in space format (subject to change in future)
[biggest part of the issue] Suppoty new type BLOB, represented as BINARY in format.
There's no such a type in formats, so need to introduce new field type and patch whole
Tarantool thoroughly

Totktonada · 2019-03-01T01:28:51Z

See #3650 re BLOBs.

Korablev77 · 2019-03-01T14:32:43Z

Last point

4. [biggest part of the issue] Suppoty new type BLOB, represented as BINARY in format.
There's no such a type in formats, so need to introduce new field type and patch whole
Tarantool thoroughly

now is blocked by #4023

Currently, there is no native (in Tarantool terms) types to represent time-like types. So, until we add implementation of those types, it makes no sense to allow to specify those types in table definition. Note that previously they were mapped to NUMBER type. For the same reason all built-in functions connected with DATE/TIME are disabled as well. Part of #4019

Since now no checks connected with length of string are performed, it might be misleading to allow specifying this type. Instead, users must rely on VARCHAR type. Part of #4019

NMERIC and DECIMAL were allowed to be specified as column types. But in fact, they were just synonyms for FLOAT type and mapped to NUMERIC Tarantool NoSQL type. So, we've decided to remove this type from parser and return back when NUMERIC will be implemented as a native type. Part of #4019

BLOB column type is represented by SCALAR field type in terms of NoSQL. We attempted at emulating BLOB behaviour, but such efforts turn out to be not decent enough. For this reason, we've decided to abandon these attempts and fairly replace it with SCALAR column type. SCALAR column type acts in the same way as it does in NoSQL: it is aggregator-type for INTEGER, NUMBER and STRING types. So, column declared with this type can contain values of these three (available in SQL) types. For instance: CREATE TABLE t1 (a SCALAR PRIMARY KEY); INSERT INTO t1 VALUES ('1'); SELECT * FROM t1 WHERE a = 1; [] -- Result is empty set since column "a" contains string literal value '1', not integer value 1. It is worth noting that CAST operator in this case does nothing: CAST(123 AS SCALAR); -- Returns 123 (integer) CAST('abc' AS SCALAR); -- Returns 'abc' (string) Still, we consider BLOB values as entries encoded in msgpack with MP_BIN format. To make this happen, values to be operated should be represented in BLOB form x'...' (e.g. x'000000'). What is more, there are two built-in functions returning BLOBs: randomblob() and zeroblob(). Closes #4019

@TarantoolBot

BLOB column type is represented by SCALAR field type in terms of NoSQL. We attempted at emulating BLOB behaviour, but such efforts turn out to be not decent enough. For this reason, we've decided to abandon these attempts and fairly replace it with SCALAR column type. SCALAR column type acts in the same way as it does in NoSQL: it is aggregator-type for INTEGER, NUMBER and STRING types. So, column declared with this type can contain values of these three (available in SQL) types. It is worth mentioning that CAST operator in this case does nothing. Still, we consider BLOB values as entries encoded in msgpack with MP_BIN format. To make this happen, values to be operated should be represented in BLOB form x'...' (e.g. x'000000'). What is more, there are two built-in functions returning BLOBs: randomblob() and zeroblob(). On the other hand, columns with STRING NoSQL type don't accept BLOB values. @TarantoolBot document Title: SQL types changes There are couple of recently introduced changes connected with SQL types. Firstly, we've removed support of DATE/TIME types from parser due to confusing behaviour of these types: they were mapped to NUMBER NoSQL type and have nothing in common with generally accepted DATE/TIME types (like in other DBs). In addition, all built-in functions related to these types (julianday(), date(), time(), datetime(), current_time(), current_date() etc) are disabled until we reimplement TIME-like types as a native NoSQL ones (see #3694 issue). Secondly, we've removed CHAR type (i.e. alias to VARCHAR and TEXT). The reason is that according to ANSI SQL CHAR(len) must accept only strings featuring length exactly equal to given in type definition. Obviously, now we don't provide such checks. Types VARCHAR and TEXT are still legal. For the same reason, we've removed NUMERIC and DECIMAL types, which were aliases to NUMBER NoSQL type. REAL, FLOAT and DOUBLE are still exist as aliases. Finally, we've renamed BLOB column type to SCALAR. We've decided that all our attempts to emulate BLOB behaviour using SCALAR NoSQL type don't seem decent enough, i.e. without native NoSQL type BLOB there always will be inconsistency, especially taking into account possible NoSQL-SQL interactions. In SQL SCALAR type works exactly in the same way as in NoSQL: it can store values of INTEGER, FLOAT and TEXT SQL types at the same time. Also, with this change behaviour of CAST operator has been slightly corrected: now cast to SCALAR doesn't affect type of value at all. Couple of examples: CREATE TABLE t1 (a SCALAR PRIMARY KEY); INSERT INTO t1 VALUES ('1'); SELECT * FROM t1 WHERE a = 1; [] -- Result is empty set since column "a" contains string literal value '1', not integer value 1. CAST(123 AS SCALAR); -- Returns 123 (integer) CAST('abc' AS SCALAR); -- Returns 'abc' (string) Note that in NoSQL values of BLOB type defined as ones decoded in msgpack with MP_BIN format. In SQL there are still a few ways to force this format: declaring literal in "BLOB" format (x'...') or using one of two built-in functions (randomblob() and zeroblob()). TEXT and VARCHAR SQL types don't accept BLOB values: CREATE TABLE t (a TEXT PRIMARAY KEY); INSERT INTO t VALUES (randomblob(5)); --- - error: 'Tuple field 1 type does not match one required by operation: expected string' ... BLOB itself is going to be reimplemented in scope of #3650. Closes #4019 Closes #4023

@TarantoolBot

BLOB column type is represented by SCALAR field type in terms of NoSQL. We attempted at emulating BLOB behaviour, but such efforts turn out to be not decent enough. For this reason, we've decided to abandon these attempts and fairly replace it with SCALAR column type. SCALAR column type acts in the same way as it does in NoSQL: it is aggregator-type for INTEGER, NUMBER and STRING types. So, column declared with this type can contain values of these three (available in SQL) types. It is worth mentioning that CAST operator in this case does nothing. Still, we consider BLOB values as entries encoded in msgpack with MP_BIN format. To make this happen, values to be operated should be represented in BLOB form x'...' (e.g. x'000000'). What is more, there are two built-in functions returning BLOBs: randomblob() and zeroblob(). On the other hand, columns with STRING NoSQL type don't accept BLOB values. Closes #4019 Closes #4023 @TarantoolBot document Title: SQL types changes There are couple of recently introduced changes connected with SQL types. Firstly, we've removed support of DATE/TIME types from parser due to confusing behaviour of these types: they were mapped to NUMBER NoSQL type and have nothing in common with generally accepted DATE/TIME types (like in other DBs). In addition, all built-in functions related to these types (julianday(), date(), time(), datetime(), current_time(), current_date() etc) are disabled until we reimplement TIME-like types as a native NoSQL ones (see #3694 issue). Secondly, we've removed CHAR type (i.e. alias to VARCHAR and TEXT). The reason is that according to ANSI SQL CHAR(len) must accept only strings featuring length exactly equal to given in type definition. Obviously, now we don't provide such checks. Types VARCHAR and TEXT are still legal. For the same reason, we've removed NUMERIC and DECIMAL types, which were aliases to NUMBER NoSQL type. REAL, FLOAT and DOUBLE are still exist as aliases. Finally, we've renamed BLOB column type to SCALAR. We've decided that all our attempts to emulate BLOB behaviour using SCALAR NoSQL type don't seem decent enough, i.e. without native NoSQL type BLOB there always will be inconsistency, especially taking into account possible NoSQL-SQL interactions. In SQL SCALAR type works exactly in the same way as in NoSQL: it can store values of INTEGER, FLOAT and TEXT SQL types at the same time. Also, with this change behaviour of CAST operator has been slightly corrected: now cast to SCALAR doesn't affect type of value at all. Couple of examples: CREATE TABLE t1 (a SCALAR PRIMARY KEY); INSERT INTO t1 VALUES ('1'); SELECT * FROM t1 WHERE a = 1; [] -- Result is empty set since column "a" contains string literal value '1', not integer value 1. CAST(123 AS SCALAR); -- Returns 123 (integer) CAST('abc' AS SCALAR); -- Returns 'abc' (string) Note that in NoSQL values of BLOB type defined as ones decoded in msgpack with MP_BIN format. In SQL there are still a few ways to force this format: declaring literal in "BLOB" format (x'...') or using one of two built-in functions (randomblob() and zeroblob()). TEXT and VARCHAR SQL types don't accept BLOB values: CREATE TABLE t (a TEXT PRIMARAY KEY); INSERT INTO t VALUES (randomblob(5)); --- - error: 'Tuple field 1 type does not match one required: expected string' ... BLOB itself is going to be reimplemented in scope of #3650.

@TarantoolBot

BLOB column type is represented by SCALAR field type in terms of NoSQL. We attempted at emulating BLOB behaviour, but such efforts turn out to be not decent enough. For this reason, we've decided to abandon these attempts and fairly replace it with SCALAR column type. SCALAR column type acts in the same way as it does in NoSQL: it is aggregator-type for INTEGER, NUMBER and STRING types. So, column declared with this type can contain values of these three (available in SQL) types. It is worth mentioning that CAST operator in this case does nothing. Still, we consider BLOB values as entries encoded in msgpack with MP_BIN format. To make this happen, values to be operated should be represented in BLOB form x'...' (e.g. x'000000'). What is more, there are two built-in functions returning BLOBs: randomblob() and zeroblob(). On the other hand, columns with STRING NoSQL type don't accept BLOB values. Closes #4019 Closes #4023 @TarantoolBot document Title: SQL types changes There are couple of recently introduced changes connected with SQL types. Firstly, we've removed support of DATE/TIME types from parser due to confusing behaviour of these types: they were mapped to NUMBER NoSQL type and have nothing in common with generally accepted DATE/TIME types (like in other DBs). In addition, all built-in functions related to these types (julianday(), date(), time(), datetime(), current_time(), current_date() etc) are disabled until we reimplement TIME-like types as a native NoSQL ones (see #3694 issue). Secondly, we've removed CHAR type (i.e. alias to VARCHAR and TEXT). The reason is that according to ANSI SQL CHAR(len) must accept only strings featuring length exactly equal to given in type definition. Obviously, now we don't provide such checks. Types VARCHAR and TEXT are still legal. For the same reason, we've removed NUMERIC and DECIMAL types, which were aliases to NUMBER NoSQL type. REAL, FLOAT and DOUBLE are still exist as aliases. Finally, we've renamed BLOB column type to SCALAR. We've decided that all our attempts to emulate BLOB behaviour using SCALAR NoSQL type don't seem decent enough, i.e. without native NoSQL type BLOB there always will be inconsistency, especially taking into account possible NoSQL-SQL interactions. In SQL SCALAR type works exactly in the same way as in NoSQL: it can store values of INTEGER, FLOAT and TEXT SQL types at the same time. Also, with this change behaviour of CAST operator has been slightly corrected: now cast to SCALAR doesn't affect type of value at all. Couple of examples: CREATE TABLE t1 (a SCALAR PRIMARY KEY); INSERT INTO t1 VALUES ('1'); SELECT * FROM t1 WHERE a = 1; -- [] Result is empty set since column "a" contains string literal value '1', not integer value 1. CAST(123 AS SCALAR); -- Returns 123 (integer) CAST('abc' AS SCALAR); -- Returns 'abc' (string) Note that in NoSQL values of BLOB type defined as ones decoded in msgpack with MP_BIN format. In SQL there are still a few ways to force this format: declaring literal in "BLOB" format (x'...') or using one of two built-in functions (randomblob() and zeroblob()). TEXT and VARCHAR SQL types don't accept BLOB values: CREATE TABLE t (a TEXT PRIMARAY KEY); INSERT INTO t VALUES (randomblob(5)); --- - error: 'Tuple field 1 type does not match one required: expected string' ... BLOB itself is going to be reimplemented in scope of #3650.

Currently, there is no native (in Tarantool terms) types to represent time-like types. So, until we add implementation of those types, it makes no sense to allow to specify those types in table definition. Note that previously they were mapped to NUMBER type. For the same reason all built-in functions connected with DATE/TIME are disabled as well. Part of #4019

Since now no checks connected with length of string are performed, it might be misleading to allow specifying this type. Instead, users must rely on VARCHAR type. Part of #4019

NMERIC and DECIMAL were allowed to be specified as column types. But in fact, they were just synonyms for FLOAT type and mapped to NUMERIC Tarantool NoSQL type. So, we've decided to remove this type from parser and return back when NUMERIC will be implemented as a native type. Part of #4019

@TarantoolBot

BLOB column type is represented by SCALAR field type in terms of NoSQL. We attempted at emulating BLOB behaviour, but such efforts turn out to be not decent enough. For this reason, we've decided to abandon these attempts and fairly replace it with SCALAR column type. SCALAR column type acts in the same way as it does in NoSQL: it is aggregator-type for INTEGER, NUMBER and STRING types. So, column declared with this type can contain values of these three (available in SQL) types. It is worth mentioning that CAST operator in this case does nothing. Still, we consider BLOB values as entries encoded in msgpack with MP_BIN format. To make this happen, values to be operated should be represented in BLOB form x'...' (e.g. x'000000'). What is more, there are two built-in functions returning BLOBs: randomblob() and zeroblob(). On the other hand, columns with STRING NoSQL type don't accept BLOB values. Closes #4019 Closes #4023 @TarantoolBot document Title: SQL types changes There are couple of recently introduced changes connected with SQL types. Firstly, we've removed support of DATE/TIME types from parser due to confusing behaviour of these types: they were mapped to NUMBER NoSQL type and have nothing in common with generally accepted DATE/TIME types (like in other DBs). In addition, all built-in functions related to these types (julianday(), date(), time(), datetime(), current_time(), current_date() etc) are disabled until we reimplement TIME-like types as a native NoSQL ones (see #3694 issue). Secondly, we've removed CHAR type (i.e. alias to VARCHAR and TEXT). The reason is that according to ANSI SQL CHAR(len) must accept only strings featuring length exactly equal to given in type definition. Obviously, now we don't provide such checks. Types VARCHAR and TEXT are still legal. For the same reason, we've removed NUMERIC and DECIMAL types, which were aliases to NUMBER NoSQL type. REAL, FLOAT and DOUBLE are still exist as aliases. Finally, we've renamed BLOB column type to SCALAR. We've decided that all our attempts to emulate BLOB behaviour using SCALAR NoSQL type don't seem decent enough, i.e. without native NoSQL type BLOB there always will be inconsistency, especially taking into account possible NoSQL-SQL interactions. In SQL SCALAR type works exactly in the same way as in NoSQL: it can store values of INTEGER, FLOAT and TEXT SQL types at the same time. Also, with this change behaviour of CAST operator has been slightly corrected: now cast to SCALAR doesn't affect type of value at all. Couple of examples: CREATE TABLE t1 (a SCALAR PRIMARY KEY); INSERT INTO t1 VALUES ('1'); SELECT * FROM t1 WHERE a = 1; -- [] Result is empty set since column "a" contains string literal value '1', not integer value 1. CAST(123 AS SCALAR); -- Returns 123 (integer) CAST('abc' AS SCALAR); -- Returns 'abc' (string) Note that in NoSQL values of BLOB type defined as ones decoded in msgpack with MP_BIN format. In SQL there are still a few ways to force this format: declaring literal in "BLOB" format (x'...') or using one of two built-in functions (randomblob() and zeroblob()). TEXT and VARCHAR SQL types don't accept BLOB values: CREATE TABLE t (a TEXT PRIMARAY KEY); INSERT INTO t VALUES (randomblob(5)); --- - error: 'Tuple field 1 type does not match one required: expected string' ... BLOB itself is going to be reimplemented in scope of #3650.

Currently, there is no native (in Tarantool terms) types to represent time-like types. So, until we add implementation of those types, it makes no sense to allow to specify those types in table definition. Note that previously they were mapped to NUMBER type. For the same reason all built-in functions connected with DATE/TIME are disabled as well. Part of #4019

Since now no checks connected with length of string are performed, it might be misleading to allow specifying this type. Instead, users must rely on VARCHAR type. Part of #4019

NMERIC and DECIMAL were allowed to be specified as column types. But in fact, they were just synonyms for FLOAT type and mapped to NUMERIC Tarantool NoSQL type. So, we've decided to remove this type from parser and return back when NUMERIC will be implemented as a native type. Part of #4019

NUMERIC/DATE-related types were removed and BLOB type was replaced by SCALAR in scope of new SQL type changes. See tarantool/tarantool#4019 for more information. Fixes #130.

NUMERIC/DATE-related types were removed and BLOB type was replaced by SCALAR in scope of new SQL type changes. See tarantool/tarantool#4019 for more information. Fixes tarantool#130. (cherry picked from commit d2dfa4c)

Enable a broken test which was blocked by Tarantool serverissue related to SQL BLOB type support. Closes: #45 See also: tarantool/tarantool#3650, tarantool/tarantool#4019

Enable a broken test which was blocked by Tarantool server issue related to SQL BLOB type support. Closes: #45 See also: tarantool/tarantool#3650, tarantool/tarantool#4019

kyukhin added sql ansi sql sql static types labels Feb 25, 2019

kyukhin added this to the 2.1.2 milestone Feb 25, 2019

Korablev77 self-assigned this Feb 27, 2019

Korablev77 mentioned this issue Feb 28, 2019

sql: introduce conventional <DATE> and <TIME> types #3694

Closed

This was referenced Mar 1, 2019

sql: make VARBINARY accept only binary format #3650

Closed

Clarify FLOAT/DOUBLE/REAL SQL types status in 2.1 beta release tarantool/doc#728

Closed

kyukhin pushed a commit that referenced this issue Mar 7, 2019

sql: remove support of CHAR type from parser

46168bf

Since now no checks connected with length of string are performed, it might be misleading to allow specifying this type. Instead, users must rely on VARCHAR type. Part of #4019

kyukhin closed this as completed in 0d0f53a Mar 7, 2019

This was referenced Mar 11, 2019

Reflect recent SQL type changes tarantool/tarantool-java#130

Closed

jdbc: map Tarantool/SQL types into JDBC/Java types tarantool/tarantool-java#92

Closed

nicktorwald mentioned this issue Mar 11, 2019

jdbc: BLOB type roundtrip fails tarantool/tarantool-java#45

Closed

nicktorwald mentioned this issue Mar 21, 2019

jdbc: BLOB type roundtrip fails tarantool/tarantool-java#138

Closed

kyukhin added the tmp label Mar 29, 2019

sergos added the teamL label Mar 12, 2021

igormunkin removed the teamL label Oct 20, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Cleanup SQL types #4019

Cleanup SQL types #4019

kyukhin commented Feb 25, 2019 •

edited by kostja

Loading

Totktonada commented Mar 1, 2019

Korablev77 commented Mar 1, 2019

Cleanup SQL types #4019

Cleanup SQL types #4019

Comments

kyukhin commented Feb 25, 2019 • edited by kostja Loading

Totktonada commented Mar 1, 2019

Korablev77 commented Mar 1, 2019

kyukhin commented Feb 25, 2019 •

edited by kostja

Loading