SQL statements and clauses

Column definition

Syntax:

column-name data-type [, column-constraint]

Define a column, which is a table element used in a CREATE TABLE statement.

The column-name must be an identifier which is valid according to the rules for identifiers.

Each column-name must be unique within a table.

Column definition – data type

Every column has a data type: ANY or ARRAY or BOOLEAN or DECIMAL or DOUBLE or INTEGER or MAP or NUMBER or SCALAR or STRING or UNSIGNED or UUID or VARBINARY. The detailed description of data types is in the section Operands.

Column definition – column-constraint or default clause

The column-constraint or default clause may be as follows:

Тип	Comment
NOT NULL	means «it is illegal to assign a NULL to this column»
PRIMARY KEY	explained in the Table constraint definition section
UNIQUE	explained in the Table constraint definition section
CHECK (expression)	explained in the Table constraint definition section
foreign-key-clause	explained in the Table constraint definition for foreign keys section
DEFAULT expression	means «if INSERT does not assign to this column then assign expression result to this column» – if there is no DEFAULT clause then DEFAULT NULL is assumed

If column-constraint is PRIMARY KEY, this is a shorthand for a separate table-constraint definition: «PRIMARY KEY (column-name)».

If column-constraint is UNIQUE, this is a shorthand for a separate table-constraint definition: «UNIQUE (column-name)».

If column-constraint is CHECK, this is a shorthand for a separate table-constraint definition: «CHECK (expression)».

Columns defined with PRIMARY KEY are automatically NOT NULL.

To enforce some restrictions that Tarantool does not enforce automatically, add CHECK clauses, like these:

CREATE TABLE t ("smallint" INTEGER PRIMARY KEY CHECK ("smallint" <= 32767 AND "smallint" >= -32768));
CREATE TABLE t ("shorttext" STRING PRIMARY KEY CHECK (length("shorttext") <= 10));

but this may cause inserts or updates to be slow.

-- the simple form with column-name and data-type
CREATE TABLE t (column1 INTEGER ...);
-- with column-name and data-type and column-constraint
CREATE TABLE t (column1 STRING PRIMARY KEY ...);
-- with column-name and data-type and collate-clause
CREATE TABLE t (column1 SCALAR COLLATE "unicode" ...);

-- with all possible data types and aliases
CREATE TABLE t
(column1 BOOLEAN, column2 BOOL,
 column3 INT PRIMARY KEY, column4 INTEGER,
 column5 DOUBLE,
 column6 NUMBER,
 column7 STRING, column8 STRING COLLATE "unicode",
 column9 TEXT, columna TEXT COLLATE "unicode_sv_s1",
 columnb VARCHAR(0), columnc VARCHAR(100000) COLLATE "binary",
 columnd UUID,
 columne VARBINARY,
 columnf SCALAR, columng SCALAR COLLATE "unicode_uk_s2",
 columnh DECIMAL,
 columni ARRAY,
 columnj MAP,
 columnk ANY);

-- with all possible column constraints and a default clause
CREATE TABLE t
(column1 INTEGER NOT NULL,
 column2 INTEGER PRIMARY KEY,
 column3 INTEGER UNIQUE,
 column4 INTEGER CHECK (column3 > column2),
 column5 INTEGER REFERENCES t,
 column6 INTEGER DEFAULT NULL);

Table constraint definition

A table constraint restricts the data you can add to the table. If you try to insert invalid data on a column, Tarantool throws an error.

A table constraint has the following syntax:

[CONSTRAINT [name]] constraint_expression

constraint_expression:
  | PRIMARY KEY (column_name, ...)
  | UNIQUE (column_name, ...)
  | CHECK (expression)
  | FOREIGN KEY (column_name, ...) foreign_key_clause

Define a constraint, which is a table element used in a CREATE TABLE statement.

A constraint name must be an identifier that is valid according to the rules for identifiers. A constraint name must be unique within the table for a specific constraint type. For example, the CHECK and FOREIGN KEY constraints can have the same name.

PRIMARY KEY constraints

PRIMARY KEY constraints look like this:

PRIMARY KEY (column_name, ...)

There is a shorthand: specifying PRIMARY KEY in a column definition.

• Every table must have one and only one primary key.
• Primary-key columns are automatically NOT NULL.
• Primary-key columns are automatically indexed.
• Primary-key columns are unique. That means it is illegal to have two rows with the same values for the columns specified in the constraint.

Example 1: one-column primary key

1. Create an author table with the id primary key column:

   CREATE TABLE author (
       id INTEGER PRIMARY KEY,
       name STRING NOT NULL
   );
   

   Insert data into this table:

   INSERT INTO author VALUES (1, 'Leo Tolstoy'),
                             (2, 'Fyodor Dostoevsky');
   

2. On an attempt to add an author with the existing id, the following error is raised:

   INSERT INTO author VALUES (2, 'Alexander Pushkin');
   /*
   - Duplicate key exists in unique index "pk_unnamed_author_1" in space "author" with
     old tuple - [2, "Fyodor Dostoevsky"] and new tuple - [2, "Alexander Pushkin"]
   */
   

Example 2: two-column primary key

1. Create a book table with the primary key defined on two columns:

   CREATE TABLE book (
       id INTEGER,
       title STRING NOT NULL,
       PRIMARY KEY (id, title)
   );
   

   Insert data into this table:

   INSERT INTO book VALUES (1, 'War and Peace'),
                           (2, 'Crime and Punishment');
   

2. On an attempt to add the existing book, the following error is raised:

   INSERT INTO book VALUES (2, 'Crime and Punishment');
   /*
   - Duplicate key exists in unique index "pk_unnamed_book_1" in space "BOOK" with old
     tuple - [2, "Crime and Punishment"] and new tuple - [2, "Crime and Punishment"]
   */
   

PRIMARY KEY with the AUTOINCREMENT modifier may be specified in one of two ways:

• In a column definition after the words PRIMARY KEY, as in CREATE TABLE t (c INTEGER PRIMARY KEY AUTOINCREMENT);
• In a PRIMARY KEY (column-list) after a column name, as in CREATE TABLE t (c INTEGER, PRIMARY KEY (c AUTOINCREMENT));

When AUTOINCREMENT is specified, the column must be a primary-key column and it must be INTEGER or UNSIGNED.

Only one column in the table may be autoincrement. However, it is legal to say PRIMARY KEY (a, b, c AUTOINCREMENT) – in that case, there are three columns in the primary key but only the third column (c) is AUTOINCREMENT.

As the name suggests, values in an autoincrement column are automatically incremented. That is: if a user inserts NULL in the column, then the stored value will be the smallest non-negative integer that has not already been used. This occurs because autoincrement columns are associated with sequences.

UNIQUE constraints

UNIQUE constraints look like this:

UNIQUE (column_name, ...)

There is a shorthand: specifying UNIQUE in a column definition.

Unique constraints are similar to primary-key constraints, except that:

• A table may have any number of unique keys, and unique keys are not automatically NOT NULL.
• Unique columns are automatically indexed.
• Unique columns are unique. That means it is illegal to have two rows with the same values in the unique-key columns.

Example 1: one-column unique constraint

1. Create an author table with the unique name column:

   CREATE TABLE author (
       id INTEGER PRIMARY KEY,
       name STRING UNIQUE
   );
   

   Insert data into this table:

   INSERT INTO author VALUES (1, 'Leo Tolstoy'),
                             (2, 'Fyodor Dostoevsky');
   

2. On an attempt to add an author with the same name, the following error is raised:

   INSERT INTO author VALUES (3, 'Leo Tolstoy');
   /*
   - Duplicate key exists in unique index "unique_unnamed_author_2" in space "author"
     with old tuple - [1, "Leo Tolstoy"] and new tuple - [3, "Leo Tolstoy"]
   */
   

Example 2: two-column unique constraint

1. Create a book table with the unique constraint defined on two columns:

   CREATE TABLE book (
       id INTEGER PRIMARY KEY,
       title STRING NOT NULL,
       author_id INTEGER UNIQUE,
       UNIQUE (title, author_id)
   );
   

   Insert data into this table:

   INSERT INTO book VALUES (1, 'War and Peace', 1),
                           (2, 'Crime and Punishment', 2);
   

2. On an attempt to add a book with duplicated values, the following error is raised:

   INSERT INTO book VALUES (3, 'War and Peace', 1);
   /*
   - Duplicate key exists in unique index "unique_unnamed_book_2" in space "book" with
     old tuple - [1, "War and Peace", 1] and new tuple - [3, "War and Peace", 1]
   */
   

CHECK constraints

The CHECK constraint is used to limit the value range that a column can store. CHECK constraints look like this:

CHECK (expression)

There is a shorthand: specifying CHECK in a column definition.

The expression may be anything that returns a BOOLEAN result = TRUE or FALSE or UNKNOWN.
The expression may not contain a subquery.
If the expression contains a column name, the column must exist in the table.
If a CHECK constraint is specified, the table must not contain rows where the expression is FALSE. (The table may contain rows where the expression is either TRUE or UNKNOWN.)
Constraint checking may be stopped with ALTER TABLE … DISABLE CHECK CONSTRAINT and restarted with ALTER TABLE … ENABLE CHECK CONSTRAINT.

Example

1. Create an author table with the name column that should contain values longer than 4 characters:

   CREATE TABLE author (
       id INTEGER PRIMARY KEY,
       name STRING,
       CONSTRAINT check_name_length CHECK (CHAR_LENGTH(name) > 4)
   );
   

   Insert data into this table:

   INSERT INTO author VALUES (1, 'Leo Tolstoy'),
                             (2, 'Fyodor Dostoevsky');
   

2. On an attempt to add an author with a name shorter than 5 characters, the following error is raised:

   INSERT INTO author VALUES (3, 'Alex');
   /*
   - Check constraint 'check_name_length' failed for a tuple
   */
   

Table constraint definition for foreign keys

A foreign key is a constraint that can be used to enforce data integrity across related tables. A foreign key constraint is defined on the child table that references the parent table’s column values.

Foreign key constraints look like this:

FOREIGN KEY (referencing_column_name, ...)
    REFERENCES referenced_table_name (referenced_column_name, ...)

You can also add a reference in a column definition:

referencing_column_name column_definition
    REFERENCES referenced_table_name(referenced_column_name)

Note that a referenced column should meet one of the following requirements:

• A referenced column is a PRIMARY KEY column.
• A referenced column has a UNIQUE constraint.
• A referenced column has a UNIQUE index.

Note that before the 2.11.0 version, an index existence for the referenced columns is checked when creating a constraint (for example, using CREATE TABLE or ALTER TABLE). Starting with 2.11.0, this check is weakened and the existence of an index is checked during data insertion.

Example

This example shows how to create a relation between the parent and child tables through a single-column foreign key:

1. First, create a parent author table:

   CREATE TABLE author (
       id INTEGER PRIMARY KEY,
       name STRING NOT NULL
   );
   

   Insert data into this table:

   INSERT INTO author VALUES (1, 'Leo Tolstoy'),
                             (2, 'Fyodor Dostoevsky');
   

2. Create a child book table whose author_id column references the id column from the author table:

   CREATE TABLE book (
       id INTEGER PRIMARY KEY,
       title STRING NOT NULL,
       author_id INTEGER NOT NULL UNIQUE,
       FOREIGN KEY (author_id)
           REFERENCES author (id)
   );
   

   Alternatively, you can add a reference in a column definition:

   CREATE TABLE book (
       id INTEGER PRIMARY KEY,
       title STRING NOT NULL,
       author_id INTEGER NOT NULL UNIQUE REFERENCES author(id)
   );
   

   Insert data to the book table:

   INSERT INTO book VALUES (1, 'War and Peace', 1),
                           (2, 'Crime and Punishment', 2);
   

3. Check how the created foreign key constraint enforces data integrity. The following error is raised on an attempt to insert a new book with the author_id value that doesn’t exist in the parent author table:

   INSERT INTO book VALUES (3, 'Eugene Onegin', 3);
   /*
   - 'Foreign key constraint ''fk_unnamed_book_1'' failed: foreign tuple was not found'
   */
   

   On an attempt to delete an author that already has books in the book table, the following error is raised:

   DELETE FROM author WHERE id = 2;
   /*
   - 'Foreign key ''fk_unnamed_book_1'' integrity check failed: tuple is referenced'
   */
   

Trigger extra clauses

• UPDATE OF column-list

  After BEFORE|AFTER UPDATE it is optional to add OF column-list. If any of the columns in column-list is affected at the time the row is processed, then the trigger will be activated for that row. For example:

  CREATE TRIGGER table1_before_update
   BEFORE UPDATE  OF column1, column2 ON table1
   FOR EACH ROW
   BEGIN UPDATE table2 SET column1 = column1 + 1; END;
  UPDATE table1 SET column3 = column3 + 1; -- Trigger will not be activated
  UPDATE table1 SET column2 = column2 + 0; -- Trigger will be activated
  

• WHEN

  After table-name FOR EACH ROW it is optional to add [WHEN expression]. If the expression is true at the time the row is processed, only then will the trigger will be activated for that row. For example:

  CREATE TRIGGER table1_before_update BEFORE UPDATE ON table1 FOR EACH ROW
   WHEN (SELECT COUNT(*) FROM table1) > 1
   BEGIN UPDATE table2 SET column1 = column1 + 1; END;
  

  This trigger will not be activated unless there is more than one row in table1.

• OLD and NEW

  The keywords OLD and NEW have special meaning in the context of trigger action:

  • OLD.column-name refers to the value of column-name before the change.
  • NEW.column-name refers to the value of column-name after the change.

  Пример:

  CREATE TABLE table1 (column1 STRING, column2 INTEGER PRIMARY KEY);
  CREATE TABLE table2 (column1 STRING, column2 STRING, column3 INTEGER PRIMARY KEY);
  INSERT INTO table1 VALUES ('old value', 1);
  INSERT INTO table2 VALUES ('', '', 1);
  CREATE TRIGGER table1_before_update BEFORE UPDATE ON table1 FOR EACH ROW
   BEGIN UPDATE table2 SET column1 = old.column1, column2 = new.column1; END;
  UPDATE table1 SET column1 = 'new value';
  SELECT * FROM table2;
  

  At the beginning of the UPDATE for the single row of table1, the value in column1 is „old value“ – so that is what is seen as old.column1.

  At the end of the UPDATE for the single row of table1, the value in column1 is „new value“ – so that is what is seen as new.column1. (OLD and NEW are qualifiers for table1, not table2.)

  Therefore, SELECT * FROM table2; returns ['old value', 'new value'].

  OLD.column-name does not exist for an INSERT trigger.

  NEW.column-name does not exist for a DELETE trigger.

  OLD and NEW are read-only; you cannot change their values.

• Deprecated or illegal statements:

  It is illegal for the trigger action to include a qualified column reference other than OLD.column-name or NEW.column-name. For example, CREATE TRIGGER ... BEGIN UPDATE table1 SET table1.column1 = 5; END; is illegal.

  It is illegal for the trigger action to include statements that include a WITH clause, a DEFAULT VALUES clause, or an INDEXED BY clause.

  It is usually not a good idea to have a trigger on table1 which causes a change on table2, and at the same time have a trigger on table2 which causes a change on table1. For example:

  CREATE TRIGGER table1_before_update
   BEFORE UPDATE ON table1
   FOR EACH ROW
   BEGIN UPDATE table2 SET column1 = column1 + 1; END;
  CREATE TRIGGER table2_before_update
   BEFORE UPDATE ON table2
   FOR EACH ROW
   BEGIN UPDATE table1 SET column1 = column1 + 1; END;
  

  Luckily UPDATE table1 ... will not cause an infinite loop, because Tarantool recognizes when it has already updated so it will stop. However, not every DBMS acts this way.

Trigger activation

These are remarks concerning trigger activation.

Standard terminology:

• «trigger action time» = BEFORE or AFTER or INSTEAD OF
• «trigger event» = INSERT or DELETE or UPDATE
• «triggered statement» = BEGIN … DELETE|INSERT|REPLACE|SELECT|UPDATE … END
• «triggered when clause» = WHEN search-condition
• «activate» = execute a triggered statement
• some vendors use the word «fire» instead of «activate»

If there is more than one trigger for the same trigger event, Tarantool may execute the triggers in any order.

It is possible for a triggered statement to cause activation of another triggered statement. For example, this is legal:

CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1 FOR EACH ROW BEGIN DELETE FROM t2; END;
CREATE TRIGGER t2_before_delete BEFORE DELETE ON t2 FOR EACH ROW BEGIN DELETE FROM t3; END;

Activation occurs FOR EACH ROW, not FOR EACH STATEMENT. Therefore, if no rows are candidates for insert or update or delete, then no triggers are activated.

The BEFORE trigger is activated even if the trigger event fails.

If an UPDATE trigger event does not make a change, the trigger is activated anyway. For example, if row 1 column1 contains 'a', and the trigger event is UPDATE ... SET column1 = 'a';, the trigger is activated.

The triggered statement may refer to a function: RAISE(FAIL, error-message). If a triggered statement invokes a RAISE(FAIL, error-message) function, or if a triggered statement causes an error, then statement execution stops immediately.

The triggered statement may refer to column values within the rows being changed. in this case:

• The row «as of before» the change is called the «old» row (which makes sense only for UPDATE and DELETE statements).
• The row «as of after» the change is called the «new» row (which makes sense only for UPDATE and INSERT statements).

This example shows how an INSERT can be done to a view by referring to the «new» row:

CREATE TABLE t (s1 INTEGER PRIMARY KEY, s2 INTEGER);
CREATE VIEW v AS SELECT s1, s2 FROM t;
CREATE TRIGGER v_instead_of INSTEAD OF INSERT ON v
  FOR EACH ROW
  BEGIN INSERT INTO t VALUES (new.s1, new.s2); END;
INSERT INTO v VALUES (1, 2);

Ordinarily saying INSERT INTO view_name ... is illegal in Tarantool, so this is a workaround.

It is possible to generalize this so that all data-change statements on views will change the base tables, provided that the view contains all the columns of the base table, and provided that the triggers refer to those columns when necessary, as in this example:

CREATE TABLE base_table (primary_key_column INTEGER PRIMARY KEY, value_column INTEGER);
CREATE VIEW viewed_table AS SELECT primary_key_column, value_column FROM base_table;
CREATE TRIGGER viewed_table_instead_of_insert INSTEAD OF INSERT ON viewed_table FOR EACH ROW
  BEGIN
    INSERT INTO base_table VALUES (new.primary_key_column, new.value_column); END;
CREATE TRIGGER viewed_table_instead_of_update INSTEAD OF UPDATE ON viewed_table FOR EACH ROW
  BEGIN
    UPDATE base_table
    SET primary_key_column = new.primary_key_column, value_column = new.value_column
    WHERE primary_key_column = old.primary_key_column; END;
CREATE TRIGGER viewed_table_instead_of_delete INSTEAD OF DELETE ON viewed_table FOR EACH ROW
  BEGIN
    DELETE FROM base_table WHERE primary_key_column = old.primary_key_column; END;

When INSERT or UPDATE or DELETE occurs for table X, Tarantool usually operates in this order (a basic scheme):

For each row
  Perform constraint checks
  For each BEFORE trigger that refers to table X
    Check that the trigger's WHEN condition is true.
    Execute what is in the triggered statement.
  Insert or update or delete the row in table X.
  Perform more constraint checks
  For each AFTER trigger that refers to table X
    Check that the trigger's WHEN condition is true.
    Execute what is in the triggered statement.

However, Tarantool does not guarantee execution order when there are multiple constraints, or multiple triggers for the same event (including NoSQL on_replace triggers or SQL INSTEAD OF triggers that affect a view of table X).

The maximum number of trigger activations per statement is 32.

INSTEAD OF triggers

A trigger which is created with the clause
INSTEAD OF INSERT|UPDATE|DELETE ON view-name
is an INSTEAD OF trigger. For each affected row, the trigger action is performed «instead of» the INSERT or UPDATE or DELETE statement that causes trigger activation.

For example, ordinarily it is illegal to INSERT rows in a view, but it is legal to create a trigger which intercepts attempts to INSERT, and puts rows in the underlying base table:

CREATE TABLE t1 (column1 INTEGER PRIMARY KEY, column2 INTEGER);
CREATE VIEW v1 AS SELECT column1, column2 FROM t1;
CREATE TRIGGER v1_instead_of INSTEAD OF INSERT ON v1 FOR EACH ROW BEGIN
 INSERT INTO t1 VALUES (NEW.column1, NEW.column2); END;
INSERT INTO v1 VALUES (1, 1);
-- ... The result will be: table t1 will contain a new row.

INSTEAD OF triggers are only legal for views, while BEFORE or AFTER triggers are only legal for base tables.

It is legal to create INSTEAD OF triggers with triggered WHEN clauses.

Limitations:

• It is legal to create INSTEAD OF triggers with UPDATE OF column-list clauses, but they are not standard SQL.

Пример:

CREATE TRIGGER ev1_instead_of_update
  INSTEAD OF UPDATE OF column2,column1 ON ev1
  FOR EACH ROW BEGIN
  INSERT INTO et2 VALUES (NEW.column1, NEW.column2); END;

Select list

Syntax:

select-list-column [, select-list-column ...]

select-list-column:

Define what will be in a result set; this is a clause in a SELECT statement.

The select list is a comma-delimited list of expressions, or * (asterisk). An expression can have an alias provided with an [[AS] column-name] clause.

The * «asterisk» shorthand is valid if and only if the SELECT statement also contains a FROM clause which specifies the table or tables (details about the FROM clause are in the next section). The simple form is * which means «all columns» – for example, if the select is done for a table which contains three columns s1 s2 s3, then SELECT * ... is equivalent to SELECT s1, s2, s3 .... The qualified form is table-name.* which means «all columns in the specified table», which again must be a result of the FROM clause – for example, if the table is named table1, then table1.* is equivalent to a list of the columns of table1.

The [[AS] column-name] clause determines the column name. The column name is useful for two reasons:

• in a tabular display, the column names are the headings
• if the results of the SELECT are used when creating a new table (such as a view), then the column names in the new table will be the column names in the select list.

If [[AS] column-name] is missing, and the expression is not simply the name of a column in the table, then Tarantool makes a name COLUMN_n where n is the number of the non-simple expression within the select list, for example SELECT 5.88, table1.x, 'b' COLLATE "unicode_ci" FROM table1; will cause the column names to be COLUMN_1, X, COLUMN_2. This is a behavior change since version 2.5.1. In earlier versions, the name would be equal to the expression; see Issue#3962. It is still legal to define tables with column names like COLUMN_1 but not recommended.

Примеры:

-- the simple form:
SELECT 5;
-- with multiple expressions including operators:
SELECT 1, 2 * 2, 'Three' || 'Four';
-- with [[AS] column-name] clause:
SELECT 5 AS column1;
-- * which must be eventually followed by a FROM clause:
SELECT * FROM table1;
-- as a list:
SELECT 1 AS a, 2 AS b, table1.* FROM table1;

FROM clause

Syntax:

FROM [SEQSCAN] table-reference [, table-reference ...]

Specify the table or tables for the source of a SELECT statement.

The table-reference must be a name of an existing table, or a subquery, or a joined table.

A joined table looks like this:

table-reference-or-joined-table join-operator table-reference-or-joined-table [join-specification]

A join-operator must be any of the standard types:

• [NATURAL] LEFT [OUTER] JOIN,
• [NATURAL] INNER JOIN, or
• CROSS JOIN

A join-specification must be any of:

• ON expression, or
• USING (column-name [, column-name …])

Parentheses are allowed, and [[AS] correlation-name] is allowed.

The maximum number of joins in a FROM clause is 64.

The SEQSCAN keyword (since 2.11) marks the queries that perform sequential scans during the execution. It happens if the query can’t use indexes, and goes through all the table rows one by one, sometimes causing a heavy load. Such queries are called scan queries. If a scan query doesn’t have the SEQSCAN keyword, Tarantool raises an error. SEQSCAN must precede all names of the tables that the query scans.

To find out if a query performs a sequential scan, use EXPLAIN QUERY PLAN. For scan queries, the result contains SCAN TABLE table_name.

Примеры:

-- the simplest form:
SELECT * FROM SEQSCAN t;
-- with two tables, making a Cartesian join:
SELECT * FROM SEQSCAN t1, SEQSCAN t2;
-- with one table joined to itself, requiring correlation names:
SELECT a.*, b.* FROM SEQSCAN t1 AS a, SEQSCAN t1 AS b;
-- with a left outer join:
SELECT * FROM SEQSCAN t1 LEFT JOIN SEQSCAN t2;

WHERE clause

Syntax:

WHERE condition;

Specify the condition for filtering rows from a table; this is a clause in a SELECT or UPDATE or DELETE statement.

The condition may contain any expression that returns a BOOLEAN (TRUE or FALSE or UNKNOWN) value.

For each row in the table:

• if the condition is true, then the row is kept;
• if the condition is false or unknown, then the row is ignored.

In effect, WHERE condition takes a table with n rows and returns a table with n or fewer rows.

Примеры:

-- with a simple condition:
SELECT 1 FROM t WHERE column1 = 5;
-- with a condition that contains AND and OR and parentheses:
SELECT 1 FROM t WHERE column1 = 5 AND (x > 1 OR y < 1);

GROUP BY clause

Syntax:

GROUP BY expression [, expression ...]

Make a grouped table; this is a clause in a SELECT statement.

The expressions should be column names in the table, and each column should be specified only once.

In effect, the GROUP BY clause takes a table with rows that may have matching values, combines rows that have matching values into single rows, and returns a table which, because it is the result of GROUP BY, is called a grouped table.

Thus, if the input is a table:

a    b      c
-    -      -
1    'a'   'b
1    'b'   'b'
2    'a'   'b'
3    'a'   'b'
1    'b'   'b'

then GROUP BY a, b will produce a grouped table:

a    b      c
-    -      -
1    'a'   'b'
1    'b'   'b'
2    'a'   'b'
3    'a'   'b'

The rows where column a and column b have the same value have been merged; column c has been preserved but its value should not be depended on – if the rows were not all „b“, Tarantool could pick any value.

It is useful to envisage a grouped table as having hidden extra columns for the aggregation of the values, for example:

a    b      c    COUNT(a) SUM(a) MIN(c)
-    -      -    -------- ------ ------
1    'a'    'b'         2      2    'b'
1    'b'    'b'         1      1    'b'
2    'a'    'b'         1      2    'b'
     'a'    'b'         1      3    'b'

These extra columns are what aggregate functions are for.

Примеры:

-- with a single column:
SELECT 1 FROM t GROUP BY column1;
-- with two columns:
SELECT 1 FROM t GROUP BY column1, column2;

Limitations:

• SELECT s1, s2 FROM t GROUP BY s1; is legal.
• SELECT s1 AS q FROM t GROUP BY q; is legal.
• SELECT s1 FROM t GROUP by 1; is legal.

Aggregate functions

Syntax:

function-name (one or more expressions)

Apply a built-in aggregate function to one or more expressions and return a scalar value.

Aggregate functions are only legal in certain clauses of a SELECT statement for grouped tables. (A table is a grouped table if a GROUP BY clause is present.) Also, if an aggregate function is used in a select list and the GROUP BY clause is omitted, then Tarantool assumes SELECT ... GROUP BY [all columns];.

NULLs are ignored for all aggregate functions except COUNT(*).

AVG([DISTINCT] expression)

Return the average value of expression.

Example: AVG(column1)

COUNT([DISTINCT] expression)

Return the number of occurrences of expression.

Example: COUNT(column1)

COUNT(*)

Return the number of occurrences of a row.

Example: COUNT(*)

GROUP_CONCAT(expression-1 [, expression-2]) or GROUP_CONCAT(DISTINCT expression-1)

Return a list of expression-1 values, separated by commas if expression-2 is omitted, or separated by the expression-2 value if expression-2 is not omitted.

Example: GROUP_CONCAT(column1)

MAX([DISTINCT] expression)

Return the maximum value of expression.

Example: MAX(column1)

MIN([DISTINCT] expression)

Return the minimum value of expression.

Example: MIN(column1)

SUM([DISTINCT] expression)

Возвращает сумму значений выражения или NULL, если строк не было.

Example: SUM(column1)

TOTAL([DISTINCT] expression)

Возвращает сумму значений выражения или ноль, если строк не было.

Example: TOTAL(column1)

HAVING clause

Syntax:

HAVING condition;

Specify the condition for filtering rows from a grouped table; this is a clause in a SELECT statement.

The clause preceding the HAVING clause may be a GROUP BY clause. HAVING operates on the table that the GROUP BY produces, which may contain grouped columns and aggregates.

If the preceding clause is not a GROUP BY clause, then there is only one group and the HAVING clause may only contain aggregate functions or literals.

For each row in the table:

• if the condition is true, then the row is kept;
• if the condition is false or unknown, then the row is ignored.

In effect, HAVING condition takes a table with n rows and returns a table with n or fewer rows.

Примеры:

-- with a simple condition:
SELECT 1 FROM t GROUP BY column1 HAVING column2 > 5;
-- with a more complicated condition:
SELECT 1 FROM t GROUP BY column1 HAVING column2 > 5 OR column2 < 5;
-- with an aggregate:
SELECT x, SUM(y) FROM t GROUP BY x HAVING SUM(y) > 0;
-- with no GROUP BY and an aggregate:
SELECT SUM(y) FROM t GROUP BY x HAVING MIN(y) < MAX(y);

Limitations:

• HAVING without GROUP BY is not supported for multiple tables.

ORDER BY clause

Syntax:

ORDER BY expression [ASC|DESC] [, expression [ASC|DESC] ...]

Put rows in order; this is a clause in a SELECT statement.

An ORDER BY expression has one of three types which are checked in order:

1. Expression is a positive integer, representing the ordinal position of the column in the select list. For example, in the statement
   SELECT x, y, z FROM t ORDER BY 2;
ORDER BY 2 means «order by the second column in the select list», which is y.
2. Expression is a name of a column in the select list, which is determined by an AS clause. For example, in the statement
   SELECT x, y AS x, z FROM t ORDER BY x;
ORDER BY x means «order by the column explicitly named x in the select list», which is the second column.
3. Expression contains a name of a column in a table of the FROM clause. For example, in the statement
   SELECT x, y FROM t1 JOIN t2 ORDER BY z;
ORDER BY z means «order by a column named z which is expected to be in table t1 or table t2».

If both tables contain a column named z, then Tarantool will choose the first column that it finds.

Выражение может содержать операторы и имена функций, а также конкретные значения. Например, в инструкции SELECT x, y FROM t ORDER BY UPPER(z); часть ORDER BY UPPER(z) означает «упорядочить по значениям столбца t.z, представленным в заглавном регистре». Вероятно, эта операция аналогична упорядочиванию с помощью одной из нечувствительных к регистру сортировок Tarantool.

Type 3 is illegal if the SELECT statement contains UNION or EXCEPT or INTERSECT.

If an ORDER BY clause contains multiple expressions, then expressions on the left are processed first and expressions on the right are processed only if necessary for tie-breaking. For example, in the statement
SELECT x, y FROM t ORDER BY x, y; if there are two rows which both have the same values for column x, then an additional check is made to see which row has a greater value for column y.

In effect, ORDER BY clause takes a table with rows that may be out of order, and returns a table with rows in order.

Sorting order:

• The default order is ASC (ascending), the optional order is DESC (descending).
• Первыми идут значения NULL, затем значения типа BOOLEAN, числовые значения, STRING, VARBINARY и, наконец, UUID.
• Ordering does not matter for ARRAYs or MAPs or ANYs because they are not legal for comparisons.
• Within STRINGs, ordering is according to collation.
• Collation may be specified with a COLLATE clause within the ORDER BY column-list, or may be default.

Примеры:

-- with a single column:
SELECT 1 FROM t ORDER BY column1;
-- with two columns:
SELECT 1 FROM t ORDER BY column1, column2;
-- with a variety of data:
CREATE TABLE h (s1 NUMBER PRIMARY KEY, s2 SCALAR);
INSERT INTO h VALUES (7, 'A'), (4, 'a'), (-4, 'AZ'), (17, 17), (23, NULL);
INSERT INTO h VALUES (17.5, 'Д'), (1e+300, 'A'), (0, ''), (-1, '');
SELECT * FROM h ORDER BY s2 COLLATE "unicode_ci", s1;
-- The result of the above SELECT will be:
- - [23, null]
  - [17, 17]
  - [-1, '']
  - [0, '']
  - [4, 'a']
  - [7, 'A']
  - [1e+300, 'A']
  - [-4, 'AZ']
  - [17.5, 'Д']
...

Limitations:

• ORDER BY 1 is legal. This is common but is not standard SQL nowadays.

LIMIT clause

Syntax:

• LIMIT limit-expression [OFFSET offset-expression]
• LIMIT offset-expression, limit-expression

Specify a maximum number of rows and a start row; this is a clause in a SELECT statement.

Expressions may contain integers and arithmetic operators or functions, for example ABS(-3 / 1). However, the result must be an integer value greater than or equal to zero.

Usually the LIMIT clause follows an ORDER BY clause, because otherwise Tarantool does not guarantee that rows are in order.

Примеры:

-- simple case:
SELECT * FROM t LIMIT 3;
-- both limit and order:
SELECT * FROM t LIMIT 3 OFFSET 1;
-- applied to a UNIONed result (LIMIT clause must be the final clause):
SELECT column1 FROM table1 UNION SELECT column1 FROM table2 ORDER BY 1 LIMIT 1;

Limitations:

• If ORDER BY … LIMIT is used, then all order-by columns must be ASC or all must be DESC.

Subquery

Syntax:

• SELECT-statement syntax
• VALUES-statement syntax

A subquery has the same syntax as a SELECT statement or VALUES statement embedded inside a main statement.

Subqueries may be the second part of INSERT statements. For example:

INSERT INTO t2 SELECT a, b, c FROM t1;

Subqueries may be in the FROM clause of SELECT statements.

Subqueries may be expressions, or be inside expressions. In this case they must be parenthesized, and usually the number of rows must be 1. For example:

SELECT 1, (SELECT 5), 3 FROM t WHERE c1 * (SELECT COUNT(*) FROM t2) > 5;

Subqueries may be expressions on the right side of certain comparison operators, and in this unusual case the number of rows may be greater than 1. The comparison operators are: [NOT] EXISTS and [NOT] IN. For example:

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

Subqueries may refer to values in the outer query. In this case, the subquery is called a «correlated subquery».

Subqueries may refer to rows which are being updated or deleted by the main query. In that case, the subquery finds the matching rows first, before starting to update or delete. For example, after:

CREATE TABLE t (s1 INTEGER PRIMARY KEY, s2 INTEGER);
INSERT INTO t VALUES (1, 3), (2, 1);
DELETE FROM t WHERE s2 NOT IN (SELECT s1 FROM t);

only one of the rows is deleted, not both rows.

WITH clause

WITH clause (common table expression)

Syntax:

WITH temporary-table-name AS (subquery)
[, temporary-table-name AS (subquery)]
SELECT statement | INSERT statement | DELETE statement | UPDATE statement | REPLACE statement;

WITH v AS (SELECT * FROM t) SELECT * FROM v;

is equivalent to creating a view and selecting from it:

CREATE VIEW v AS SELECT * FROM t;
SELECT * FROM v;

The difference is that a WITH-clause «view» is temporary and only useful within the same statement. No CREATE privilege is required.

The WITH-clause can also be thought of as a subquery that has a name. This is useful when the same subquery is being repeated. For example:

SELECT * FROM t WHERE a < (SELECT s1 FROM x) AND b < (SELECT s1 FROM x);

can be replaced with:

WITH s AS (SELECT s1 FROM x) SELECT * FROM t,s WHERE a < s.s1 AND b < s.s1;

This «factoring out» of a repeated expression is regarded as good practice.

Примеры:

WITH cte AS (VALUES (7, '') INSERT INTO j SELECT * FROM cte;
WITH cte AS (SELECT s1 AS x FROM k) SELECT * FROM cte;
WITH cte AS (SELECT COUNT(*) FROM k WHERE s2 < 'x' GROUP BY s3)
  UPDATE j SET s2 = 5
  WHERE s1 = (SELECT s1 FROM cte) OR s3 = (SELECT s1 FROM cte);

WITH can only be used at the beginning of a statement, therefore it cannot be used at the beginning of a subquery or after a set operator or inside a CREATE statement.

A WITH-clause «view» is read-only because Tarantool does not support updatable views.

WITH RECURSIVE

WITH RECURSIVE clause (iterative common table expression)

The real power of WITH lies in the WITH RECURSIVE clause, which is useful when it is combined with UNION or UNION ALL:

WITH RECURSIVE recursive-table-name AS
(SELECT ... FROM non-recursive-table-name ...
UNION [ALL]
SELECT ... FROM recursive-table-name ...)
statement-that-uses-recursive-table-name;

In non-SQL this can be read as: starting with a seed value from a non-recursive table, produce a recursive viewed table, UNION that with itself, UNION that with itself, UNION that with itself … forever, or until a condition in the WHERE clause says «stop».

Пример:

CREATE TABLE ts (s1 INTEGER PRIMARY KEY);
INSERT INTO ts VALUES (1);
WITH RECURSIVE w AS (
  SELECT s1 FROM ts
  UNION ALL
  SELECT s1 + 1 FROM w WHERE s1 < 4)
SELECT * FROM w;

First, table w is seeded from t1, so it has one row: [1].

Then, UNION ALL (SELECT s1 + 1 FROM w) takes the row from w – which contains [1] – adds 1 because the select list says «s1+1», and so it has one row: [2].

Then, UNION ALL (SELECT s1 + 1 FROM w) takes the row from w – which contains [2] – adds 1 because the select list says «s1+1», and so it has one row: [3].

Then, UNION ALL (SELECT s1 + 1 FROM w) takes the row from w – which contains [3] – adds 1 because the select list says «s1+1», and so it has one row: [4].

Then, UNION ALL (SELECT s1 + 1 FROM w) takes the row from w – which contains [4] – and now the importance of the WHERE clause becomes evident, because «s1 < 4» is false for this row, and therefore the «stop» condition has been reached.

So, before the «stop», table w got 4 rows – [1], [2], [3], [4] – and the result of the statement looks like:

tarantool> WITH RECURSIVE w AS (
         >   SELECT s1 FROM ts
         >   UNION ALL
         >   SELECT s1 + 1 FROM w WHERE s1 < 4)
         > SELECT * FROM w;
---
- - [1]
  - [2]
  - [3]
  - [4]
...

In other words, this WITH RECURSIVE ... SELECT produces a table of auto-incrementing values.

UNION, EXCEPT, and INTERSECT clauses

Syntax:

• select-statement UNION [ALL] select-statement [ORDER BY clause] [LIMIT clause];
• select-statement EXCEPT select-statement [ORDER BY clause] [LIMIT clause];
• select-statement INTERSECT select-statement [ORDER BY clause] [LIMIT clause];

UNION, EXCEPT, and INTERSECT are collectively called «set operators» or «table operators». In particular:

• a UNION b means «take rows which occur in a OR b».
• a EXCEPT b means «take rows which occur in a AND NOT b».
• a INTERSECT b means «take rows which occur in a AND b».

Duplicate rows are eliminated unless ALL is specified.

The select-statements may be chained: SELECT ... SELECT ... SELECT ...;

Each select-statement must result in the same number of columns.

The select-statements may be replaced with VALUES statements.

The maximum number of set operations is 50.

Пример:

CREATE TABLE t1 (s1 INTEGER PRIMARY KEY, s2 STRING);
CREATE TABLE t2 (s1 INTEGER PRIMARY KEY, s2 STRING);
INSERT INTO t1 VALUES (1, 'A'), (2, 'B'), (3, NULL);
INSERT INTO t2 VALUES (1, 'A'), (2, 'C'), (3,NULL);
SELECT s2 FROM t1 UNION SELECT s2 FROM t2;
SELECT s2 FROM t1 UNION ALL SELECT s2 FROM t2 ORDER BY s2;
SELECT s2 FROM t1 EXCEPT SELECT s2 FROM t2;
SELECT s2 FROM t1 INTERSECT SELECT s2 FROM t2;

В данном примере:

• The UNION query returns 4 rows: NULL, „A“, „B“, „C“.
• The UNION ALL query returns 6 rows: NULL, NULL, „A“, „A“, „B“, „C“.
• The EXCEPT query returns 1 row: „B“.
• The INTERSECT query returns 2 rows: NULL, „A“.

Limitations:

• Parentheses are not allowed.
• Evaluation is left to right, INTERSECT does not have precedence.

Пример:

CREATE TABLE t01 (s1 INTEGER PRIMARY KEY, s2 STRING);
CREATE TABLE t02 (s1 INTEGER PRIMARY KEY, s2 STRING);
CREATE TABLE t03 (s1 INTEGER PRIMARY KEY, s2 STRING);
INSERT INTO t01 VALUES (1, 'A');
INSERT INTO t02 VALUES (1, 'B');
INSERT INTO t03 VALUES (1, 'A');
SELECT s2 FROM t01 INTERSECT SELECT s2 FROM t03 UNION SELECT s2 FROM t02;
SELECT s2 FROM t03 UNION SELECT s2 FROM t02 INTERSECT SELECT s2 FROM t03;
-- ... results are different.

INDEXED BY clause

Syntax:

INDEXED BY index-name

The INDEXED BY clause may be used in a SELECT, DELETE, or UPDATE statement, immediately after the table-name. For example:

DELETE FROM table7 INDEXED BY index7 WHERE column1 = 'a';

In this case the search for „a“ will take place within index7. For example:

SELECT * FROM table7 NOT INDEXED WHERE column1 = 'a';

In this case the search for „a“ will be done via a search of the whole table, what is sometimes called a «full table scan», even if there is an index for column1.

Ordinarily Tarantool chooses the appropriate index or lookup method depending on a complex set of «optimizer» rules; the INDEXED BY clause overrides the optimizer choice. If the index was defined with the exclude_null parts option, it will only be used if the user specifies it.

Пример:

Suppose a table has two columns:

• The first column is the primary key and therefore it has an automatic index named pk_unnamed_T_1.
• The second column has an index created by the user.

The user selects with INDEXED BY the-index-on-column1, then selects with INDEXED BY the-index-on-column-2.

CREATE TABLE t (column1 INTEGER PRIMARY KEY, column2 INTEGER);
CREATE INDEX idx_column2_t_1 ON t (column2);
INSERT INTO t VALUES (1, 2), (2, 1);
SELECT * FROM t INDEXED BY "pk_unnamed_T_1";
SELECT * FROM t INDEXED BY idx_column2_t_1;
-- Result for the first select: (1, 2), (2, 1)
-- Result for the second select: (2, 1), (1, 2).

Limitations:
Often INDEXED BY has no effect.
Often INDEXED BY affects a choice of covering index, but not a WHERE clause.