SQL features

E011-01

INTEGER and SMALLINT

CREATE TABLE t (s1 INT PRIMARY KEY);

OK.

E011-02

REAL, DOUBLE PRECISION, and FLOAT data types

CREATE TABLE tr (s1 FLOAT PRIMARY KEY);

No. Tarantool's floating point data type is DOUBLE. Note: Floating point SQL types are not planned to be compatible between 2.1 and 2.2 releases. The reason is that in 2.1 we set 'number' format for columns of these types, but will restrict it to 'float32' and 'float64' in 2.2. The format change requires data migration and cannot be done automatically, because in 2.1 we have no information to distinguish 'number' columns (created from Lua) from FLOAT/DOUBLE/REAL ones (created from SQL).

E011-03

DECIMAL and NUMERIC data types

CREATE TABLE td (s1 NUMERIC PRIMARY KEY);

No, NUMERIC data types are not supported, although the DECIMAL data type is supported.

E011-04

Arithmetic operators

SELECT 10+1, 9-2, 8*3, 7/2 FROM t;

OK.

E011-05

Numeric comparisons

SELECT * FROM t WHERE 1 < 2;

OK.

E011-06

Implicit casting among the numeric data types

SELECT * FROM t WHERE s1 = 1.00;

OK, because Tarantool allows comparison of 1.00 with an INTEGER column.

E021-01

Character data type (including all its spellings)

CREATE TABLE t44 (s1 CHAR PRIMARY KEY);

No, CHAR is not supported. This type of unsupported features will only be counted once.

E021-02

CHARACTER VARYING data type (including all its spellings)

CREATE TABLE t45 (s1 VARCHAR PRIMARY KEY);

No, Tarantool only allows VARCHAR(n), which is a synonym for STRING.

E021-03

Character literals

INSERT INTO t45 VALUES ('');

OK, and the bad practice of accepting "" for character literals is avoided.

E021-04

CHARACTER_LENGTH function

SELECT character_length(s1) FROM t;

OK. Tarantool treats this as a synonym of LENGTH().

E021-05

OCTET_LENGTH

SELECT octet_length(s1) FROM t;

No. There is no such function.

E021-06

SUBSTRING function

SELECT substring(s1 FROM 1 FOR 1) FROM t;

No. There is no such function. There is a function SUBSTR(x,n,n), which is OK.

E021-07

Character concatenation

SELECT 'a' || 'b' FROM t;

OK.

E021-08

UPPER and LOWER functions

SELECT upper('a'),lower('B') FROM t;

OK. Tarantool supports both UPPER() and LOWER().

E021-09

TRIM function

SELECT trim('a ') FROM t;

OK.

E021-10

Implicit casting among the fixed-length and variable-length character string types

SELECT * FROM tm WHERE char_column > varchar_column;

No, there is no fixed-length character string type.

E021-11

POSITION function

SELECT position(x IN y) FROM z;

No. Tarantool's POSITION function requires ',' rather than 'IN'.

E021-12

Character comparison

SELECT * FROM t WHERE s1 > 'a';

OK. We should note here that comparisons use a binary collation by default, but it is easy to use a COLLATE clause.

E031

Identifiers

CREATE TABLE rank (ceil INT PRIMARY KEY);

No. Tarantool's list of reserved words differs from the standard's list of reserved words.

E031-01

Delimited identifiers

CREATE TABLE "t47" (s1 INT PRIMARY KEY);

OK. Also, enclosing identifiers inside double quotes means they won't be converted to upper case or lower case, this is the behavior that some other DBMSs lack.

E031-02

Lower case identifiers

CREATE TABLE t48 (s1 INT PRIMARY KEY);

OK.

E031-03

Trailing underscore

CREATE TABLE t49_ (s1 INT PRIMARY KEY);

OK.

E051-01

SELECT DISTINCT

SELECT DISTINCT s1 FROM t;

OK.

E051-02

GROUP BY clause

SELECT DISTINCT s1 FROM t GROUP BY s1;

OK.

E051-04

GROUP BY can contain columns not in select list

SELECT s1 FROM t GROUP BY lower(s1);

OK.

E051-05

Select list items can be renamed

SELECT s1 AS K FROM t ORDER BY K;

OK.

E051-06

HAVING clause

SELECT count(*) FROM t HAVING count(*) > 0;

OK. Tarantool supports HAVING, and GROUP BY is not mandatory before HAVING.

E051-07

Qualified * in SELECT list

SELECT t.* FROM t;

OK.

E051-08

Correlation names in the FROM clause

SELECT * FROM t AS K;

OK.

E051-09

Rename columns in the FROM clause

SELECT * FROM t AS x(q,c);

No.

E061-01

Comparison predicate

SELECT * FROM t WHERE 0 = 0;

OK.

E061-02

BETWEEN predicate

SELECT * FROM t WHERE ' ' BETWEEN '' AND ' ';

OK.

E061-03

IN predicate with list of values

SELECT * FROM t WHERE s1 IN ('a', upper('a'));

OK.

E061-04

LIKE predicate

SELECT * FROM t WHERE s1 LIKE '_';

OK.

E061-05 LIKE predicate: ESCAPE clause

VALUES ('abc_' LIKE 'abcX_' ESCAPE 'X');

OK.

E061-06

NULL predicate

SELECT * FROM t WHERE s1 IS NOT NULL;

OK.

E061-07

Quantified comparison predicate

SELECT * FROM t WHERE s1 = ANY (SELECT s1 FROM t);

No. Syntax error.

E061-08

EXISTS predicate

SELECT * FROM t WHERE NOT EXISTS (SELECT * FROM t);

OK.

E061-09

Subqueries in comparison predicate

SELECT * FROM t WHERE s1 > (SELECT s1 FROM t);

OK.

E061-11

Subqueries in IN predicate

SELECT * FROM t WHERE s1 IN (SELECT s1 FROM t);

OK.

E061-12

Subqueries in quantified comparison predicate

SELECT * FROM t WHERE s1 >= ALL (SELECT s1 FROM t);

No. Syntax error.

E061-13

Correlated subqueries

SELECT * FROM t WHERE s1 = (SELECT s1 FROM t2 WHERE t2.s2 = t.s1);

OK.

E061-14

E071-01

UNION DISTINCT table operator

SELECT * FROM t UNION DISTINCT SELECT * FROM t;

No. However, SELECT * FROM t UNION SELECT * FROM t; is OK.

E071-02

UNION ALL table operator

SELECT * FROM t UNION ALL SELECT * FROM t;

OK.

E071-03

EXCEPT DISTINCT table operator

SELECT * FROM t EXCEPT DISTINCT SELECT * FROM t;

No. However, SELECT * FROM t EXCEPT SELECT * FROM t; is OK.

E071-05

Columns combined via table operators need not have exactly the same data type

SELECT s1 FROM t UNION SELECT 5 FROM t;

OK.

E071-06

Table operators in subqueries

SELECT * FROM t WHERE 'a' IN (SELECT * FROM t UNION SELECT * FROM t);

OK.

E091-01

AVG

SELECT avg(s1) FROM t7;

No. Tarantool supports AVG but there is no warning that NULLs are eliminated.

E091-02

COUNT

SELECT count(*) FROM t7 WHERE s1 > 0;

OK.

E091-03

MAX

SELECT max(s1) FROM t7 WHERE s1 > 0;

OK.

E091-04

MIN

SELECT min(s1) FROM t7 WHERE s1 > 0;

OK.

E091-05

SUM

SELECT sum(1) FROM t7 WHERE s1 > 0;

OK.

E091-06

ALL quantifier

SELECT sum(ALL s1) FROM t7 WHERE s1 > 0;

OK.

E091-07

DISTINCT quantifier

SELECT sum(DISTINCT s1) FROM t7 WHERE s1 > 0;

OK.

E101-01

INSERT statement

INSERT INTO t (s1,s2) VALUES (1,''), (2,NULL), (3,55);

OK.

E101-03

Searched UPDATE statement

UPDATE t SET s1 = NULL WHERE s1 IN (SELECT s1 FROM t2);

OK.

E101-04

Searched DELETE statement

DELETE FROM t WHERE s1 IN (SELECT s1 FROM t);

OK.

E111

Single row SELECT statement

SELECT count(*) FROM t;

OK.

E121-01

DECLARE CURSOR

No. Tarantool doesn't support cursors.

E121-02

ORDER BY columns need not be in select list

SELECT s1 FROM t ORDER BY s2;

OK.

E121-03

Value expressions in ORDER BY clause

SELECT s1 FROM t7 ORDER BY -s1;

OK.

E121-04

OPEN statement

No. Tarantool doesn't support cursors.

E121-06

Positioned UPDATE statement

No. Tarantool doesn't support cursors.

E121-07

Positioned DELETE statement

No. Tarantool doesn't support cursors.

E121-08

CLOSE statement

No. Tarantool doesn't support cursors.

E121-10

FETCH statement implicit next

No. Tarantool doesn't support cursors.

E121-17

WITH HOLD cursors

E131

Null value support (nulls in lieu of values)

SELECT s1 FROM t7 WHERE s1 IS NULL;

OK.

E141-01

NOT NULL constraints

CREATE TABLE t8 (s1 INT PRIMARY KEY, s2 INT NOT NULL);

OK.

E141-02

UNIQUE constraints of NOT NULL columns

CREATE TABLE t9 (s1 INT PRIMARY KEY , s2 INT NOT NULL UNIQUE);

OK.

E141-03

PRIMARY KEY constraints

CREATE TABLE t10 (s1 INT PRIMARY KEY);

OK, although Tarantool shouldn't always insist on having a primary key.

E141-04

Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete and referential update actions

CREATE TABLE t11 (s0 INT PRIMARY KEY, s1 INT REFERENCES t10);

OK.

E141-06

CHECK constraints

CREATE TABLE t12 (s1 INT PRIMARY KEY, s2 INT, CHECK (s1 = s2));

OK.

E141-07

Column defaults

CREATE TABLE t13 (s1 INT PRIMARY KEY, s2 INT DEFAULT -1);

OK.

E141-08

NOT NULL inferred on primary key

CREATE TABLE t14 (s1 INT PRIMARY KEY);

OK. We are unable to insert NULL although we don't explicitly say the column is NOT NULL.

E141-10

Names in a foreign key can be specified in any order

CREATE TABLE t15 (s1 INT, s2 INT, PRIMARY KEY (s1,s2)); CREATE TABLE t16 (s1 INT PRIMARY KEY, s2 INT, FOREIGN KEY (s2,s1) REFERENCES t15 (s1,s2));

OK.

E151-01

COMMIT statement

COMMIT;

No. Tarantool supports COMMIT but it is necessary to say START TRANSACTION first.

E151-02

ROLLBACK statement

ROLLBACK;

OK.

E152-01 SET TRANSACTION statement: ISOLATION SERIALIZABLE clause

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

No. Syntax error.

E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses

E153

Updatable queries with subqueries

UPDATE "view_containing_subquery" SET column1=0;

No.

E161

SQL comments using leading double minus

--comment;

OK.

E171

SQLSTATE support

DROP TABLE no_such_table;

No. Tarantool returns an error message but not an SQLSTATE string.

F021

Basic information schema

SELECT * from information_schema.tables;

No. Tarantool's metadata is not in a schema with that name (not counted in the final score).

F031-01

CREATE TABLE statement to create persistent base tables

CREATE TABLE t20 (t20_1 INT NOT NULL);

No. We always have to specify PRIMARY KEY (we only count this flaw once).

F031-02

CREATE VIEW statement

CREATE VIEW t21 AS SELECT * FROM t20;

OK.

F031-03

GRANT statement

No. Tarantool doesn't support privileges except via NoSQL.

F031-04 ALTER TABLE statement: add column

ALTER TABLE t7 ADD COLUMN t7_2 VARCHAR(1) DEFAULT 'q';

OK. Tarantool supports ALTER TABLE, and support for ADD COLUMN was added in Tarantool 2.7.

F031-13 DROP TABLE statement: RESTRICT clause

DROP TABLE t20 RESTRICT;

No. Tarantool supports DROP TABLE but not this clause.

F031-16 DROP VIEW statement: RESTRICT clause

DROP VIEW v2 RESTRICT;

No. Tarantool supports DROP VIEW but not this clause.

F041-01

Inner join but not necessarily the INNER keyword

SELECT a.s1 FROM t7 a JOIN t7 b;

OK.

F041-02

INNER keyword

SELECT a.s1 FROM t7 a INNER JOIN t7 b;

OK.

F041-03

LEFT OUTER JOIN

SELECT t7.*,t22.* FROM t22 LEFT OUTER JOIN t7 ON (t22_1 = s1);

OK.

F041-04

RIGHT OUTER JOIN

SELECT t7.*,t22.* FROM t22 RIGHT OUTER JOIN t7 ON (t22_1 = s1);

No. Syntax error.

F041-05

Outer joins can be nested

SELECT t7.*,t22.* FROM t22 LEFT OUTER JOIN t7 ON (t22_1 = s1) LEFT OUTER JOIN t23;

OK.

F041-07

The inner table in a left or right outer join can also be used in an inner join

SELECT t7.* FROM (t22 LEFT OUTER JOIN t7 ON (t22_1 = s1)) j INNER JOIN t22 ON (j.t22_4 = t7.s1);

OK.

F041-08

All comparison operators are supported

SELECT * FROM t WHERE 0 = 1 OR 0 > 1 OR 0 < 1 OR 0 <> 1;

OK.

F051-01

DATE data type (including support of DATE literal)

CREATE TABLE dates (s1 DATE);

No. Tarantool does not support the DATE data type.

F051-02

TIME data type (including support of TIME literal)

CREATE TABLE times (s1 TIME DEFAULT TIME '1:2:3');

No. Syntax error.

F051-03

TIMESTAMP data type (including support of TIMESTAMP literal)

CREATE TABLE timestamps (s1 TIMESTAMP);

No. Syntax error.

F051-04

Comparison predicate on DATE, TIME and TIMESTAMP data types

SELECT * FROM dates WHERE s1 = s1;

No. Date and time data types are not supported.

F051-05

Explicit CAST between date-time types and character string types

SELECT cast(s1 AS VARCHAR(10)) FROM dates;

No. Date and time data types are not supported.

F051-06

CURRENT_DATE

SELECT current_date FROM t;

No. Syntax error.

F051-07

LOCALTIME

SELECT localtime FROM t;

No. Syntax error.

F051-08

LOCALTIMESTAMP

SELECT localtimestamp FROM t;

No. Syntax error.

F081

UNION and EXCEPT in views

CREATE VIEW vv AS SELECT * FROM t7 EXCEPT SELECT * * FROM t15;

OK.

F131-01

WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views

CREATE VIEW vv2 AS SELECT * FROM vv GROUP BY s1;

OK.

F131-02

Multiple tables supported in queries with grouped views

CREATE VIEW vv3 AS SELECT * FROM vv2,t30;

OK.

F131-03

Set functions supported in queries with grouped views

CREATE VIEW vv4 AS SELECT count(*) FROM vv2;

OK.

F131-04

Subqueries with GROUP BY and HAVING clauses and grouped views

CREATE VIEW vv5 AS SELECT count(*) FROM vv2 GROUP BY s1 HAVING count(*) > 0;

OK.

F131-05

Single row SELECT with GROUP BY and HAVING clauses and grouped views

SELECT count(*) FROM vv2 GROUP BY s1 HAVING count(*) > 0;

OK.

F201

CAST function

SELECT cast(s1 AS INT) FROM t;

OK.

F221

Explicit defaults

UPDATE t SET s1 = DEFAULT;

No. Syntax error.

F261-01

Simple CASE

SELECT CASE WHEN 1 = 0 THEN 5 ELSE 7 END FROM t;

OK.

F261-02

Searched CASE

SELECT CASE 1 WHEN 0 THEN 5 ELSE 7 END FROM t;

OK.

F261-03

NULLIF

SELECT nullif(s1,7) FROM t;

OK

F261-04

COALESCE

SELECT coalesce(s1,7) FROM t;

OK.

F311-01

CREATE SCHEMA

No. Tarantool doesn't have schemas or databases.

F311-02

CREATE TABLE for persistent base tables

No. Tarantool doesn't have CREATE TABLE inside CREATE SCHEMA.

F311-03

CREATE VIEW

No. Tarantool doesn't have CREATE VIEW inside CREATE SCHEMA.

F311-04 CREATE VIEW: WITH CHECK OPTION

No. Tarantool doesn't have CREATE VIEW inside CREATE SCHEMA.

F311-05

F471

Scalar subquery values

SELECT s1 FROM t WHERE s1 = (SELECT count(*) FROM t);

OK.

F481

Expanded NULL predicate

SELECT * FROM t WHERE row(s1,s1) IS NOT NULL;

No. Syntax error.

S011

Distinct types

CREATE TYPE x AS FLOAT;

No. Tarantool doesn't support distinct types.

T321-01

User-defined functions with no overloading

CREATE FUNCTION f() RETURNS INT RETURN 5;

No. User-defined functions for SQL are created in Lua with a different syntax.

T321-02

User-defined procedures with no overloading

CREATE PROCEDURE p() BEGIN END;

No. User-defined functions for SQL are created in Lua with a different syntax.

T321-03

Function invocation

SELECT f(1) FROM t;

OK. Tarantool can invoke Lua user-defined functions.

T321-04

CALL statement

CALL p();

No. Tarantool doesn't support CALL statements.

T321-05

RETURN statement

CREATE FUNCTION f() RETURNS INT RETURN 5;

No. Tarantool doesn't support RETURN statements.

T631

IN predicate with one list element

SELECT * FROM t WHERE 1 IN (1);

OK.