SQL user guide

Tokens

The token is the minimum SQL-syntax unit that Tarantool understands. These are the types of tokens:

Ключевые слова — официальные слова языка, например SELECT.
Литералы — числовые или строковые константы, например 15.7 или 'Taranto'.
Идентификаторы — имена объектов, например column55 или table_of_accounts.
Операторы (строго говоря, неалфавитные операторы) — математические операторы, например * / + - ( ) , ; < = >=.

Токены могут быть отделены друг от друга одним или несколькими разделителями.
* Символы-разделители: табуляция (U+0009), перевод строки (U+000A), вертикальная табуляция (U+000B), смена страницы (U+000C), возврат каретки (U+000D), пробел (U+0020), следующая строка (U+0085), а также все редкие символы классов Zl, Zp и Zs Юникода. Полный список символов вы найдете на странице https://github.com/tarantool/tarantool/issues/2371.
* Комментарии (начинаются с /* и заканчиваются */).
* Однострочные комментарии (начинаются с -- и заканчиваются переводом строки).
Разделители не нужны ни перед операторами, ни после них.
Разделители необходимы после ключевых слов, числовых значений или обычных идентификаторов, если только следующий токен не является оператором.
Таким образом, Tarantool может понять следующую серию из шести токенов:
SELECT'a'FROM/**/t;
Но для удобства чтения токены обычно разделяют пробелами:
SELECT 'a' FROM /**/ t;

Literals

There are eight kinds of literals: BOOLEAN INTEGER DOUBLE DECIMAL STRING VARBINARY MAP ARRAY.

BOOLEAN literals:
TRUE | FALSE | UNKNOWN
A literal has data type = BOOLEAN if it is the keyword TRUE or FALSE. UNKNOWN is a synonym for NULL. A literal may have type = BOOLEAN if it is the keyword NULL and there is no context to indicate a different data type.

INTEGER literals:
[plus-sign | minus-sign] digit [digit …]
or, for a hexadecimal integer literal,
[plus-sign | minus-sign] 0X | 0x hexadecimal-digit [hexadecimal-digit …]
Examples: 5, -5, +5, 55555, 0X55, 0x55
Hexadecimal 0X55 is equal to decimal 85. A literal has data type = INTEGER if it contains only digits and is in the range -9223372036854775808 to +18446744073709551615, integers outside that range are illegal.

DOUBLE literals:
[E|e [plus-sign | minus-sign] digit …]
Examples: 1E5, 1.1E5.
A literal has data type = DOUBLE if it contains «E». DOUBLE literals are also known as floating-point literals or approximate-numeric literals. To represent «Inf» (infinity), write a real numeric outside the double-precision numeric range, for example 1E309. To represent «nan» (not a number), write an expression that does not result in a real numeric, for example 0/0, using Tarantool/NoSQL. This will appear as NULL in Tarantool/SQL. In an earlier version literals containing periods were considered to be NUMBER literals. In a future version «nan» may not appear as NULL. Prior to Tarantool v. 2.10.0, digits with periods such as .0 were considered to be DOUBLE literals, but now they are considered to be DECIMAL literals.

DECIMAL literals:
[plus-sign | minus-sign] [digit [digit …]] period [digit [digit …]]
Examples: .0, 1.0, 12345678901234567890.123456789012345678
A literal has data type = DECIMAL if it contains a period, and does not contain «E». DECIMAL literals may contain up to 38 digits; if there are more, then post-decimal digits may be subject to rounding. In earlier Tarantool versions literals containing periods were considered to be NUMBER or DECIMAL literals.

STRING literals:
[quote] [character …] [quote]
Examples: 'ABC', 'AB''C'
A literal has data type type = STRING if it is a sequence of zero or more characters enclosed in single quotes. The sequence '' (two single quotes in a row) is treated as ' (a single quote) when enclosed in quotes, that is, 'A''B' is interpreted as A'B.

VARBINARY literals:
X|x [quote] [hexadecimal-digit-pair …] [quote]
Example: X'414243', which will be displayed as 'ABC'.
A literal has data type = VARBINARY («variable-length binary») if it is the letter X followed by quotes containing pairs of hexadecimal digits, representing byte values.

MAP literals:
[left curly bracket] key [colon] value [right curly bracket]
Examples: {'a':1}, {1:'a'}
A map literal is a pair of curly brackets (also called «braces») enclosing a STRING or INTEGER or UUID literal (called the map «key») followed by a colon followed by any type of literal (called the map «value»). This is a minimal form of a MAP expression.

ARRAY literals:
[left square bracket] [literal] [right square bracket]
Examples: [1], ['a']
An ARRAY literal is a literal value which is enclosed inside square brackets. This is a minimal form of an ARRAY expression.

Here are four ways to put non-ASCII characters,such as the Greek letter α alpha, in string literals:
First make sure that your shell program is set to accept characters as UTF-8. A simple way to check is
SELECT hex(cast('α' as VARBINARY)); If the result is CEB1 – which is the hexadecimal value for the UTF-8 representation of α – it is good.

• (1) Simply enclose the character inside '...',
  'α'
• (2) Find out what is the hexadecimal code for the UTF-8 representation of α, and enclose that inside X'...', then cast to STRING because X'...' literals are data type VARBINARY not STRING,
  CAST(X'CEB1' AS STRING)
  
• (3) Find out what is the Unicode code point for α, and pass that to the CHAR function.
  CHAR(945)  /* remember that this is α as data type STRING not VARBINARY */
  
• (4) Enclose statements inside double quotes and include Lua escapes, for example box.execute("SELECT '\206\177';")
  

One can use the concatenation operator || to combine characters made with any of these methods.

Limitations: (Issue#2344)
* LENGTH('A''B') = 3 which is correct, but on the Tarantool console the display from SELECT A''B; is A''B, which is misleading.
* It is unfortunate that X'41' is a byte sequence which looks the same as 'A', but it is not the same. box.execute("select 'A' < X'41';") is not legal at the moment. This happens because TYPEOF(X'41') yields 'varbinary'. Also it is illegal to say UPDATE ... SET string_column = X'41', one must say UPDATE ... SET string_column = CAST(X'41' AS STRING);.

Identifiers

All database objects – tables, triggers, indexes, columns, constraints, functions, collations – have identifiers. An identifier should begin with a letter or underscore ('_') and should contain only letters, digits, dollar signs ('$'), or underscores. The maximum number of bytes in an identifier is between 64982 and 65000. For compatibility reasons, Tarantool recommends that an identifier should not have more than 30 characters.

Letters in identifiers do not have to come from the Latin alphabet, for example the Japanese syllabic ひ and the Cyrillic letter д are legal. But be aware that a Latin letter needs only one byte but a Cyrillic letter needs two bytes, so Cyrillic identifiers consume a tiny amount more space.

_A1   -- legal, begins with underscore and contains underscore | letter | digit
1_A   -- illegal, begins with digit
A$« -- legal, but not recommended, try to stick with digits and letters and underscores
+ -- illegal, operator token
grant -- illegal, GRANT is a reserved word
"grant" -- legal, delimited identifiers may be reserved words
"_space" -- legal, but Tarantool already uses this name for a system space
"A"."X" -- legal, for columns only, inside statements where qualifiers may be necessary
'a' -- illegal, single quotes are for literals not identifiers
A123456789012345678901234567890 -- legal, identifiers can be long
ддд -- legal, and will be converted to upper case in identifiers

CREATE TABLE "q" ("q" INTEGER PRIMARY KEY);
SELECT * FROM q;
-- Result = "error: 'no such table: Q'.

Operands

An operand is something that can be operated on. Literals and column identifiers are operands. So are NULL and DEFAULT.

NULL and DEFAULT are keywords which represent values whose data types are not known until they are assigned or compared, so they are known by the technical term «contextually typed value specifications». (Exception: for the non-standard statement «SELECT NULL FROM table-name;» NULL has data type BOOLEAN.)

tarantool> create table T2(d datetime primary key);
---
- row_count: 1
...

tarantool> insert into t2 values ('2022-01-01');
---
- null
- 'Type mismatch: can not convert string(''2022-01-01'') to datetime'
...

tarantool> insert into t2 values (cast('2022-01-01' as datetime));
---
- row_count: 1
...

tarantool> select * from t2;
---
- metadata:
  - name: D
    type: datetime
  rows:
  - ['2022-01-01T00:00:00Z']
...

tarantool> create table T(d datetime primary key, i interval);
---
- row_count: 1
...

tarantool> insert into T values (cast('2022-02-02T01:01' as datetime), cast({'year': 1, 'month': 1} as interval));
---
- row_count: 1
...

tarantool> select * from t;
---
- metadata:
  - name: D
    type: datetime
  - name: I
    type: interval
  rows:
  - ['2022-02-02T01:01:00Z', '+1 years, 1 months']
...

tarantool> select * from t
---
- metadata:
  - name: D
    type: datetime
  - name: I
    type: interval
  rows:
  - ['2022-02-02T01:01:00Z', '+1 years, 1 months']
...

tarantool> select d, d + i, d + cast({'year': 1, 'month': 2} as interval) from t
---
- metadata:
  - name: D
    type: datetime
  - name: COLUMN_1
    type: datetime
  - name: COLUMN_2
    type: datetime
  rows:
  - ['2022-02-02T01:01:00Z', '2023-03-02T01:01:00Z', '2023-04-02T01:01:00Z']
...

tarantool> select i + cast({'year': 1, 'month': 2} as interval) from t
---
- metadata:
  - name: COLUMN_1
    type: interval
  rows:
  - ['+2 years, 3 months']
...

tarantool> select cast({'year': 1, 'month': 1, 'week': 1, 'day': 1, 'hour': 1, 'min': 1, 'sec': 1} as interval)
---
- metadata:
  - name: COLUMN_1
    type: interval
  rows:
  - ['+1 years, 1 months, 1 weeks, 1 days, 1 hours, 1 minutes, 1 seconds']
...

tarantool> \set language lua


tarantool> v = {year = 1, month = 1, week = 1, day = 1, hour = 1,
         >      min = 1, sec = 1, nsec = 1, adjust = 'none'}
---
...

tarantool> box.execute('select cast(#v as interval);', {{['#v'] = v}})

---
- metadata:
  - name: COLUMN_1
    type: interval
  rows:
  - ['+1 years, 1 months, 1 weeks, 1 days, 1 hours, 1 minutes, 1.000000001 seconds']
...

Operators

An operator signifies what operation can be performed on operands.

Almost all operators are easy to recognize because they consist of one-character or two-character non-alphabetic tokens, except for six keyword operators (AND IN IS LIKE NOT OR).

Almost all operators are «dyadic», that is, they are performed on a pair of operands – the only operators that are performed on a single operand are NOT and ~ and (sometimes) -.

The result of an operation is a new operand. If the operator is a comparison operator then the result has data type BOOLEAN (TRUE or FALSE or UNKNOWN). Otherwise the result has the same data type as the original operands, except that: promotion to a broader type may occur to avoid overflow. Arithmetic with NULL operands will result in a NULL operand.

В списке операторов ниже пометка «(арифметическая операция)» указывает на то, что все операнды должны быть числовыми значениями (кроме NUMBER) и в результате тоже должно получиться число. Пометка «(сравнение)» указывает, что операнды должны иметь схожие типы данных и результат будет типа BOOLEAN. Пометка «(логическая операция)» указывает, что операнды должны быть типа BOOLEAN и результат также будет типа BOOLEAN. Если операция невозможна, выбрасывается исключение. Кроме того, существуют особые ситуации: их описание приводится после списка операторов. На месте указанных в примерах конкретных значений (литералов) могут с тем же успехом стоять идентификаторы столбцов.

Начиная с версии Tarantool 2.10.1 арифметические операнды не могут быть типа NUMBER.

• + addition (arithmetic)

  Add two numerics according to standard arithmetic rules. Example: 1 + 5, result = 6.

• - subtraction (arithmetic)

  Subtract second numeric from first numeric according to standard arithmetic rules.

  Example: 1 - 5, result = -4.

• * multiplication (arithmetic)

  Multiply two numerics according to standard arithmetic rules.

  Example: 2 * 5, result = 10.

• / division (arithmetic)

  Divide second numeric into first numeric according to standard arithmetic rules. Division by zero is not legal. Division of integers always results in rounding toward zero, use CAST to DOUBLE or to DECIMAL to get non-integer results.

  Example: 5 / 2, result = 2.

• % modulus (arithmetic)

  Divide second numeric into first numeric according to standard arithmetic rules. The result is the remainder. Starting with Tarantool version 2.10.1, operands must be INTEGER or UNSIGNED.

  Examples: 17 % 5, result = 2; -123 % 4, result = -3.

• << shift left (arithmetic)

  Shift the first numeric to the left N times, where N = the second numeric. For positive numerics, each 1-bit shift to the left is equivalent to multiplying times 2.

  Example: 5 << 1, result = 10.

  Примечание

  Starting with Tarantool version 2.10.1, operands must be non-negative INTEGER or UNSIGNED.

• >> shift right (arithmetic)

  Shift the first numeric to the right N times, where N = the second numeric. For positive numerics, each 1-bit shift to the right is equivalent to dividing by 2.

  Example: 5 >> 1, result = 2.

  Примечание

  Starting with Tarantool version 2.10.1, operands must be non-negative INTEGER or UNSIGNED.

• & and (arithmetic)

  Combine the two numerics, with 1 bits in the result if and only if both original numerics have 1 bits.

  Example: 5 & 4, result = 4.

  Примечание

  Starting with Tarantool version 2.10.1, operands must be non-negative INTEGER or UNSIGNED.

• | or (arithmetic)

  Combine the two numerics, with 1 bits in the result if either original numeric has a 1 bit.

  Example: 5 | 2, result = 7.

  Примечание

  Starting with Tarantool version 2.10.1, operands must be non-negative INTEGER or UNSIGNED.

• ~ negate (arithmetic), sometimes called bit inversion

  Change 0 bits to 1 bits, change 1 bits to 0 bits.

  Example: ~5, result = -6.

  Примечание

  Starting with Tarantool version 2.10.1, the operand must be non-negative INTEGER or UNSIGNED.

• < less than (comparison)

  Return TRUE if the first operand is less than the second by arithmetic or collation rules.

  Example for numerics: 5 < 2, result = FALSE

  Example for strings: 'C' < ' ', result = FALSE

• <= less than or equal (comparison)

  Return TRUE if the first operand is less than or equal to the second by arithmetic or collation rules.

  Example for numerics: 5 <= 5, result = TRUE

  Example for strings: 'C' <= 'B', result = FALSE

• > greater than (comparison)

  Return TRUE if the first operand is greater than the second by arithmetic or collation rules.

  Example for numerics: 5 > -5, result = TRUE

  Example for strings: 'C' > '!', result = TRUE

• >= greater than or equal (comparison)

  Return TRUE if the first operand is greater than or equal to the second by arithmetic or collation rules.

  Example for numerics: 0 >= 0, result = TRUE Example for strings: 'Z' >= 'Γ', result = FALSE

• = equal (assignment or comparison)

  After the word SET, «=» means the first operand gets the value from the second operand. In other contexts, «=» returns TRUE if operands are equal.

  Example for assignment: ... SET column1 = 'a';

  Example for numerics: 0 = 0, result = TRUE

  Example for strings: '1' = '2 ', result = FALSE

• == equal (assignment), or equal (comparison)

  This is a non-standard equivalent of «= equal (assignment or comparison)».

• <> not equal (comparison)

  Return TRUE if the first operand is not equal to the second by arithmetic or collation rules.

  Example for strings: 'A' <> 'A     ' is TRUE.

• != not equal (comparison)

  This is a non-standard equivalent of «<> not equal (comparison)».

• [ , ] (indexed access operator)

  Array example: ['a', 'b', 'c'] [2] (returns 'b')

  Map example: {'a' : 123, 7: 'asd'}['a'] (returns 123)

  See also: ARRAY index expression and MAP index expression.

• IS NULL and IS NOT NULL (comparison)

  For IS NULL: Return TRUE if the first operand is NULL, otherwise return FALSE. Example: column1 IS NULL, result = TRUE if column1 contains NULL.

  For IS NOT NULL: Return FALSE if the first operand is NULL, otherwise return TRUE. Example: column1 IS NOT NULL, result = FALSE if column1 contains NULL.

• LIKE (comparison)

  Perform a comparison of two string operands. If the second operand contains '_', the '_' matches any single character in the first operand. If the second operand contains '%', the '%' matches 0 or more characters in the first operand. If it is necessary to search for either '_' or '%' within a string without treating it specially, an optional clause can be added, ESCAPE single-character-operand, for example 'abc_' LIKE 'abcX_' ESCAPE 'X' is TRUE because X' means «following character is not special». Matching is also affected by the string’s collation.

• BETWEEN (comparison)

  x BETWEEN y AND z is shorthand for x >= y AND x <= z.

• NOT negation (logic)

  Return TRUE if operand is FALSE return FALSE if operand is TRUE, else return UNKNOWN.

  Example: NOT (1 > 1), result = TRUE.

• IN is equal to one of a list of operands (comparison)

  Return TRUE if first operand equals any of the operands in a parenthesized list.

  Example: 1 IN (2,3,4,1,7), result = TRUE.

• AND and (logic)

  Return TRUE if both operands are TRUE. Return UNKNOWN if both operands are UNKNOWN. Return UNKNOWN if one operand is TRUE and the other operand is UNKNOWN. Return FALSE if one operand is FALSE and the other operand is (UNKNOWN or TRUE or FALSE).

• OR or (logic)

  Return TRUE if either operand is TRUE. Return FALSE if both operands are FALSE. Return UNKNOWN if one operand is UNKNOWN and the other operand is (UNKNOWN or FALSE).

• || concatenate (string manipulation)

  Return the value of the first operand concatenated with the value of the second operand.

  Example: 'A' || 'B', result = 'AB'.

The precedence of dyadic operators is:

||
* / %
+ -
<< >> & |
<  <= > >=
=  == != <> IS IS NOT IN LIKE
AND
OR

To ensure a desired precedence, use () parentheses.

Special situations

If one of the operands has data type DOUBLE, Tarantool uses floating-point arithmetic. This means that exact results are not guaranteed and rounding may occur without warning. For example, 4.7777777777777778 = 4.7777777777777777 is TRUE.

The floating-point values inf and -inf are possible. For example, SELECT 1e318, -1e318; will return «inf, -inf». Arithmetic on infinite values may cause NULL results, for example SELECT 1e318 - 1e318; is NULL and SELECT 1e318 * 0; is NULL.

SQL operations never return the floating-point value -nan, although it may exist in data created by Tarantool’s NoSQL. In SQL, -nan is treated as NULL.

В предыдущих версиях Tarantool строка преобразовывалась в числовое значение, если она использовалась с арифметическим оператором и преобразование было возможно. Например, результатом выражения '7' + '7' было 14. Для операций сравнения строка '7' преобразовывалась в значение 7. Это называется неявным приведением. Оно было применимо для значений типа STRING и всех числовых типов данных. Начиная с версии Tarantool 2.10 неявное приведение в числа больше не поддерживается.

Ограничения (подробнее в Issue#2346 на GitHub):
* Некоторые ключевые слова, например MATCH и REGEXP, зарезервированы, но в текущих или будущих версиях Tarantool их пока не планируется использовать.
* 99999999999999999 << 210 возвращает 0.

Expressions

An expression is a chunk of syntax that causes return of a value. Expressions may contain literals, column-names, operators, and parentheses.

Therefore these are examples of expressions: 1, 1 + 1 << 1, (1 = 2) OR 4 > 3, 'x' || 'y' || 'z'.

Also there are two expressions that involve keywords:

• value IS [NOT] NULL: determine whether value is (not) NULL.
• CASE ... WHEN ... THEN ... ELSE ... END: set a series of conditions.

CREATE TABLE plays (user_id INTEGER PRIMARY KEY, scores ARRAY);
INSERT INTO plays VALUES (1, [23, 17, 55, 48]);
INSERT INTO plays VALUES (2, [12, 8, 20, 33]);
SELECT scores[2] FROM plays;
/* ---
  rows:
  - [17]
  - [8]
... */

CREATE TABLE bands (id INTEGER PRIMARY KEY, info MAP);
INSERT INTO bands VALUES (1, {'name': 'The Beatles', 'year': 1960});
INSERT INTO bands VALUES (2, {'name': 'The Doors', 'year': 1965});
SELECT info['name'] FROM bands;
/* ---
  rows:
  - ['The Beatles']
  - ['The Doors']
... */

Statements

Инструкция (statement) состоит из ключевых слов и выражений языка SQL, которые предписывают Tarantool выполнять какие-либо действия с базой данных. Инструкции начинаются с одного из ключевых слов: ALTER, ANALYZE, COMMIT, CREATE, DELETE, DROP, EXPLAIN, INSERT, PRAGMA, RELEASE, REPLACE, ROLLBACK, SAVEPOINT, SELECT, SET, START, TRUNCATE, UPDATE, VALUES или WITH. В конце инструкции ставится точка с запятой ;, хотя это и не является обязательным.

A client sends a statement to the Tarantool server. The Tarantool server parses the statement and executes it. If there is an error, Tarantool returns an error message.

Implicit string/numeric cast

Примеры в этом разделе справедливы только для версий Tarantool до 2.10. Начиная с Tarantool 2.10 неявное приведение строк и числовых значений больше не допускается.

Приведение STRING к INTEGER/DOUBLE/NUMBER/UNSIGNED (любому числовому типу) и наоборот, выполняемое в ходе операции присвоения или сравнения, может требовать особых условий.

1 = '1' /* сравнение значения STRING с числовым значением */
UPDATE ... SET string_column = 1 /* запись в STRING числового значения */

Для операций сравнения всегда выполняется приведение STRING к числовому значению.
Поэтому 1e2 = '100' вернет TRUE, как и 11 > '2'.
Если приведение не удалось, числовое значение считается меньше, чем значение типа STRING.
Так что 1e400 < '' тоже вернет TRUE.
Исключение: для оператора BETWEEN приведение производится к типу данных первого и последнего операндов.
Поэтому выражение '66' BETWEEN 5 AND '7' вернет TRUE.

Начиная с Tarantool 2.5.1 действует измененный алгоритм присваивания. В связи с этим неявные приведения строк к числам недопустимы. Например, INSERT INTO t (integer_column) VALUES ('5'); выдаст ошибку.

Implicit cast does happen if STRINGS are used in arithmetic.
Therefore '5' / '5' = 1. If the cast fails, then the result is an error.
Therefore '5' / '' is an error.

Неявное приведение не производится, если числовые значения используются в конкатенации или в LIKE.
Поэтому выражение 5 || '5' недопустимо.

In the following examples, implicit cast does not happen for values in SCALAR columns:
DROP TABLE scalars;
CREATE TABLE scalars (scalar_column SCALAR PRIMARY KEY);
INSERT INTO scalars VALUES (11), ('2');
SELECT * FROM scalars WHERE scalar_column > 11;   /* 0 rows. So 11 > '2'. */
SELECT * FROM scalars WHERE scalar_column < '2';  /* 1 row. So 11 < '2'. */
SELECT max(scalar_column) FROM scalars; /* 1 row: '2'. So 11 < '2'. */
SELECT sum(scalar_column) FROM scalars; /* 1 row: 13. So cast happened. */
These results are not affected by indexing, or by reversing the operands.

Implicit cast does NOT happen for GREATEST() or LEAST(). Therefore LEAST('5',6) is 6.

For function arguments:
If the function description says that a parameter has a specific data type, and implicit assignment casts are allowed, then arguments which are not passed with that data type will be converted before the function is applied.
For example, the LENGTH() function expects a STRING or VARBINARY, and INTEGER can be converted to STRING, therefore LENGTH(15) will return the length of '15', that is, 2.
But implicit cast sometimes does NOT happen for parameters. Therefore ABS('5') will cause an error message after Issue#4159 is fixed. However, TRIM(5) will still be legal.

Although it is not a requirement of the SQL standard, implicit cast is supposed to help compatibility with other DBMSs. However, other DBMSs have different rules about what can be converted (for example they may allow assignment of 'inf' but disallow comparison with '1e5'). And, of course, it is not possible to be compatible with other DBMSs and at the same time support SCALAR, which other DBMSs do not have.