Выражения

Конкатенация строк

Выполняется через бинарный оператор ||.

Как и у других бинарных операторов, если в данных с одной из сторон оказался NULL, то и результат будет NULL.

Не следует путать этот оператор с логическим «или», в SQL оно обозначается ключевым словом OR. Также не стоит пытаться делать конкатенацию через +.

Примеры

SELECT "fo" || "o";

Проверка строки на соответствие шаблону

REGEXP и RLIKE являются алиасами и представляют собой короткий способ записи для вызова Re2::Grep. MATCH — аналогично для Re2::Match.

LIKE работает по следующим принципам:

• В шаблонах используется два спецсимвола:
  • % — ноль и более любых символов;
  • _ — ровно один любой символ.
    Все остальные символы выступают как литералы, то есть обозначают сами себя.
• В отличие от REGEXP, с шаблоном LIKE строка должна совпасть полностью. Например, для поиска подстроки в середине нужно добавить % в начале и в конце шаблона.
• ILIKE является не чувствительной к регистру версией LIKE.
• Если LIKE применяется к ключевой колонке сортированной таблицы и шаблон начинается не со спецсимвола, то фильтрация по префиксу опускается прямо до кластера, что в некоторых случаях позволяет не сканировать всю таблицу целиком. Для ILIKE данная оптимизация отключена.
• Чтобы заэкранировать спецсимволы, необходимо после шаблона указать символ для экранирования с помощью ключевого слова ESCAPE '?'. Вместо ? можно использовать любой символ, кроме % и _. Например, если в качестве экранирующего символа используется знак вопроса, то выражения ?%, ?_ и ?? в шаблоне совпадут со своим вторым символом: процент, подчеркивание и знак вопроса, соответственно. По умолчанию экранирующий символ не определен.

Наиболее популярный способ использования ключевых слов LIKE и REGEXP — фильтрация таблицы в выражениях с WHERE. Однако ограничения на использование шаблонов именно в этом контексте нет, и их можно использовать в большинстве контекстов при работе со строками, наравне, например, с конкатенацией с помощью ||.

Примеры

SELECT * FROM my_table
WHERE string_column REGEXP '\\d+';
-- второй слеш нужен, так как все
-- стандартные строковые литералы в SQL
-- могут принимать С-escaped строки

SELECT
    string_column LIKE '___!_!_!_!!!!!!' ESCAPE '!'
    -- ищет строку из ровно 9 символов:
    --   3 произвольных,
    --   затем 3 подчеркивания
    --   и 3 восклицательных знака
FROM my_table;

SELECT * FROM my_table
WHERE key LIKE 'foo%bar';
-- при наличии сортировки таблицы по key просканирует только ключи,
-- начинающиеся на foo, и затем среди них
-- оставит только заканчивающиеся на bar

Операторы

Арифметические операторы

Операторы +, -, *, /, % определены для примитивных типов данных, являющихся разновидностями чисел.

Для типа данных Decimal используется банковское округление (до ближайшего четного).

Примеры

SELECT 2 + 2;

SELECT 0.0 / 0.0;

Операторы сравнения

Операторы =, ==, !=, <>, >, < определены для:

• Примитивных типов данных за исключением Yson и Json.
• Кортежей и структур с одинаковым набором полей. Для структур не определен порядок, но можно проверять на (не-)равенство, а кортежи сравниваются поэлементно слева направо.

Примеры

SELECT 2 > 1;

Логические операторы

С помощью операторов AND, OR, XOR осуществляются логические операции над булевыми значениями (Bool).

Примеры

SELECT 3 > 0 AND false;

Битовые операторы

Битовые операции над числами:

• &, |, ^ — AND, OR и XOR соответственно. Не следует путать битовые операции с аналогичными ключевыми словами. Ключевые слова AND, OR и XOR используются только для булевых значений, но не для чисел;
• ~ — отрицание;
• <<, >> — сдвиги влево-вправо;
• |<<, >>| — кольцевые сдвиги влево-вправо.

Примеры

SELECT
    key << 10 AS key,
    ~value AS value
FROM my_table;

Приоритет и ассоциативность операторов

Приоритет оператора определяет порядок вычисления выражения содержащего разные операторы.
Например, выражение 1 + 2 * 3 вычисляется как 1 + (2 * 3),
поскольку приоритет оператора умножения больше приоритета оператора сложения.

Ассоциативность определяет порядок вычисления в выражениях содержащих операторы одного типа.
Например, выражение 1 + 2 + 3 вычисляется как (1 + 2) + 3, поскольку оператор сложения является лево-ассоциативным.
С другой стороны, выражение a ?? b ?? c вычисляется как a ?? (b ?? c) из-за право-ассоциативности оператора ??

В таблице ниже указаны приоритет и ассоциативность операторов языка YQL.
Операторы в таблице перечислены в порядке убывания приоритета.

IS [NOT] NULL

Проверка на пустое значение (NULL). Так как NULL является особым значением, которое ничему не равно, то обычные операторы сравнения для этой задачи не подходят.

Примеры

SELECT key FROM my_table
WHERE value IS NOT NULL;

IS [NOT] DISTINCT FROM

Сравнение двух значений. В отличие от обычных операторов сравнения, нуллы считаются равными друг другу.
Точнее говоря, сравнение осуществляется по следующим правилам:

1. операторы IS DISTINCT FROM/IS NOT DISTINCT FROM определены для тех и только для тех аргументов, для которых определены операторы != и =;
2. результат IS NOT DISTINCT FROM равен логическому отрицанию результата IS DISTINCT FROM для данных аргументов;
3. если результат оператора == не равен нуллу для некоторых аргументов, то он совпадает с результатом оператора IS NOT DISTINCT FROM для тех же аргументов;
4. если оба аргумента являются незаполненными Optionalми или NULLами, то значение IS NOT DISTINCT FROM равно True
5. результат IS NOT DISTINCT FROM от незаполненного Optional или NULL и заполненного Optional или не-Optional значения равен False.

Для значений композитных типов эти правила применяются рекурсивно.

BETWEEN

Проверка на вхождение значения в диапазон. Эквивалентно паре условий с >= и <=, то есть границы диапазона включаются. Может использоваться с префиксом NOT для инверсии.

Примеры

SELECT * FROM my_table
WHERE key BETWEEN 10 AND 20;

IN

Проверка вхождения одного значения в набор значений. Логически эквивалентно цепочке сравнений на равенство через OR, но реализовано более эффективно.

Сразу после IN можно указать хинт COMPACT.
Если COMPACT не указан, то IN с подзапросом по возможности выполняется как соответствующий JOIN (LEFT SEMI для IN и LEFT ONLY для NOT IN).
Наличие COMPACT форсирует in-memory стратегию выполнения: из содержимого правой части IN в памяти сразу строится хеш-таблица, по которой затем фильтруется левая часть.

Хинтом COMPACT следует пользоваться с осторожностью. Поскольку хеш-таблица строится в памяти, то запрос может завершиться с ошибкой, если правая часть IN содержит много больших и/или различных элементов.

Так как в YQL есть лимит на размер запроса в байтах (порядка 1Мб), для больших списков значений нужно прикладывать их к запросу через URL и использовать функцию ParseFile.

Примеры

SELECT column IN (1, 2, 3)
FROM my_table;

SELECT * FROM my_table
WHERE string_column IN ("a", "b", "c");

$foo = AsList(1, 2, 3);
SELECT 1 IN $foo;

$values = (SELECT column + 1 FROM table);
SELECT * FROM my_table WHERE
    -- фильтрация по in-memory хеш-таблице на основе table
    column1 IN /*+ COMPACT() */ $values AND
    -- с последующим LEFT ONLY JOIN с other_table
    column2 NOT IN (SELECT other_column FROM other_table);

AS

Может использоваться в следующих сценариях:

• Присвоение короткого имени (алиаса) столбцам или таблицам в рамках запроса.
• Указание именованных аргументов при вызове функций.
• При явном приведении типов данных для указания целевого типа, смотрите CAST.

Примеры:

SELECT key AS k FROM my_table;

SELECT t.key FROM my_table AS t;

SELECT
    MyFunction(key, 123 AS my_optional_arg)
FROM my_table;

CAST

Пробует привести значение к указанному типу. Попытка может оказаться неуспешной и вернуть NULL. Для чисел может потерять точность или старшие биты.
Для списков и словарей может либо удалить, либо заменить на NULL элементы, преобразование которых неудачно.
Для структур и кортежей удаляет элементы, которых нет в целевом типе.
Подробнее правила преобразований описаны здесь.

Для параметрического типа данных Decimal дополнительно указывается два аргумента:

• общее число десятичных знаков (до 35, включительно);
• число десятичных знаков после запятой (из общего числа, то есть строго не больше предыдущего аргумента).

Примеры

SELECT
    CAST("12345" AS Double),                -- 12345.0
    CAST(1.2345 AS Uint8),                  -- 1
    CAST(12345 AS String),                  -- "12345"
    CAST("1.2345" AS Decimal(5, 2)),        -- 1.23
    CAST("xyz" AS Uint64) IS NULL,          -- true, так как не удалось
    CAST(-1 AS Uint16) IS NULL,             -- true, отрицательное в беззнаковое
    CAST([-1, 0, 1] AS List<Uint8?>),             -- [null, 0, 1]
        --Тип элемента опциональный: неудачный элемент в null.
    CAST(["3.14", "bad", "42"] AS List<Float>),   -- [3.14, 42]
        --Тип элемента не опциональный: неудачный элемент удалён.
    CAST(255 AS Uint8),                     -- 255
    CAST(256 AS Uint8) IS NULL              -- true, выходит за диапазон

BITCAST

Выполняет побитное преобразование целочисленного значения к указанному целочисленному типу. Преобразование всегда успешно, но может потерять точность или старшие биты.

Примеры

SELECT
    BITCAST(100000ul AS Uint32),     -- 100000
    BITCAST(100000ul AS Int16),      -- -31072
    BITCAST(100000ul AS Uint16),     -- 34464
    BITCAST(-1 AS Int16),            -- -1
    BITCAST(-1 AS Uint16);           -- 65535

CASE

Условные выражения и ветвление. Аналог if, switch и тернарных операторов в императивных языках программирования.
Если результатом выражения WHEN оказывается true, значением выражения CASE становится результат,
следующий за условием, а остальная часть выражения CASE не вычисляется. Если же условие не выполняется,
за ним таким же образом проверяются все последующие предложения WHEN. Если не выполняется
ни одно из условий WHEN, значением CASE становится результат, записанный в предложении ELSE.
Ветка ELSE является обязательной в выражении CASE. Выражения в WHEN проверяются последовательно, сверху вниз.

Так как синтаксис достаточно громоздкий, зачастую удобнее пользоваться встроенной функцией IF.

Примеры

SELECT
  CASE
    WHEN value > 0
    THEN "positive"
    ELSE "negative"
  END
FROM my_table;

SELECT
  CASE value
    WHEN 0 THEN "zero"
    WHEN 1 THEN "one"
    ELSE "not zero or one"
  END
FROM my_table;

Именованные выражения

Сложные запросы могут выглядеть громоздко и содержать много уровней вложенности и/или повторяющихся частей. В YQL имеется возможность использовать именованные выражения – способ назначить имя произвольному выражению или подзапросу. На это именованное выражение можно ссылаться в других выражениях или подзапросах. При этом фактически происходит подстановка исходного выражения/подзапроса по месту использования.

Именованное выражение определяется следующим образом:

<named-expr> = <expression> | <subquery>;

Здесь <named-expr> состоит из символа $ и произвольного непустого идентификатора (например $foo).

Если выражение в правой части представляет собой кортеж, то его можно автоматически распаковать, указав в левой части несколько именованных выражений через запятую:

<named-expr1>, <named-expr2>, <named-expr3> ... = <expression-returning-tuple>;

В этом случае число выражений должно совпадать с размером кортежа.

У каждого именованного выражения есть область видимости. Она начинается сразу после определения именованного выражения и заканчивается в конце ближайшего охватывающего scope имен (например в конце запроса либо в конце тела лямбда-функции, ACTION, SUBQUERY или цикла EVALUATE FOR).
Повторное определение именованного выражения с тем же именем приводит к сокрытию предыдущего выражения из текущей области видимости.

Если именованное выражение ни разу не использовалось, то генерируется предупреждение. Для того, чтобы избавиться от такого предупреждения, достаточно использовать символ подчеркивания в качестве первого символа идентификатора (например $_foo).
Именованное выражение $_ называется анонимным именованным выражением и обрабатывается специальным образом: оно работает, как если бы $_ автоматически заменялось на $_<some_uniq_name>.
Анонимными именованными выражениями удобно пользоваться в тех случаях, когда мы не интересуемся его значением. Например, для извлечения второго элемента из кортежа из трех элементов можно написать:

$_, $second, $_ = AsTuple(1, 2, 3);
select $second;

Попытка сослаться на анонимное именованное выражение приводит к ошибке:

$_ = 1;
select $_; --- ошибка: Unable to reference anonymous name $_
export $_; --- ошибка: Can not export anonymous name $_

Кроме того, нельзя импортировать именованное выражение под анонимным алиасом:

import utils symbols $sqrt as $_; --- ошибка: Can not import anonymous name $_

Анонимные имена аргументов поддерживаются также для лямбда-функций, ACTION, SUBQUERY и в EVALUATE FOR.

Примеры

$multiplier = 712;
SELECT
  a * $multiplier, -- $multiplier is 712
  b * $multiplier,
  (a + b) * $multiplier
FROM abc_table;
$multiplier = c;
SELECT
  a * $multiplier -- $multiplier is column c
FROM abc_table;

$intermediate = (
  SELECT
    value * value AS square,
    value
  FROM my_table
);
SELECT a.square * b.value
FROM $intermediate AS a
INNER JOIN $intermediate AS b
ON a.value == b.square;

$a, $_, $c = AsTuple(1, 5u, "test"); -- распаковка кортежа
SELECT $a, $c;

$x, $y = AsTuple($y, $x); -- swap значений выражений

Табличные выражения

Табличное выражения – это выражение, которое возвращает таблицу. Табличными выражениями в YQL являются:

• подзапросы: (SELECT key, subkey FROM T)
• именованные подзапросы: $foo = SELECT * FROM T; (использование именованного подзапроса $foo является табличным выражением)
• шаблоны подзапросов: DEFINE SUBQUERY $foo($name) AS ... END DEFINE; (вызов $foo("InputTable") является табличным выражением).

Семантика табличного выражения зависит от контекста в котором оно используется. В YQL табличные выражения могут применяться в следующих контекстах:

• табличный контекст - после FROM.
  Здесь табличные выражения работают как ожидается – например $input = SELECT a, b, c FROM T; SELECT * FROM $input вернет таблицу с тремя колонками.
  Табличный контекст также возникает после UNION ALL, JOIN, PROCESS, REDUCE;
• векторный контекст - после IN. В этом контексте табличное выражение обязано содержать ровно одну колонку (имя этой колонки никак не влияет на результат выражения).
  Табличное выражение в векторном контексте типизируется как список (тип элемента списка при этом совпадает с типом колонки). Пример: SELECT * FROM T WHERE key IN (SELECT k FROM T1);
• скалярный контекст возникает во всех остальных случаях. Как и в векторном контексте,
  табличное выражение должно содержать ровно одну колонку, но значением табличного выражения будет скаляр –
  произвольно выбранное значение этой колонки (если получилось ноль строк, то результатом будет NULL). Пример: $count = SELECT COUNT(*) FROM T; SELECT * FROM T ORDER BY key LIMIT $count / 2;

Порядок строк в табличном контексте, порядок элементов в векторном контексте и правило выбора значения в скалярном контексте (в случае если значений несколько) не определены. На этот порядок также нельзя повлиять с помощью ORDER BY: ORDER BY без LIMIT в табличных выражениях будет игнорироваться с выдачей предупреждения, а использование ORDER BY с LIMIT определяет множество элементов, но не порядок внутри этого множества.

Из этого правила есть исключение. Именованное выражение с PROCESS, будучи использованным в скалярном контексте, ведет себя как в табличном:

$input = SELECT 1 AS key, 2 AS value;
$process = PROCESS $input;

SELECT FormatType(TypeOf($process)); -- $process используется в скалярном контексте,
                                     -- но результат SELECT при этом - List<Struct<'key':Int32,'value':Int32>>

SELECT $process[0].key; -- вернет 1

SELECT FormatType(TypeOf($input)); -- ошибка: $input в скалярном контексте должен содержать одну колонку

$dict = SELECT key, value FROM T1;

DEFINE SUBQUERY $merge_dict($table, $dict) AS
SELECT * FROM $table LEFT JOIN $dict USING(key);
END DEFINE;

SELECT * FROM $merge_dict("Input", $dict); -- $dict здесь используется в скалярном контексте.
                                           -- ошибка - в скалярном контексте ожидается ровно одна колонка

DEFINE SUBQUERY $dict() AS
SELECT key, value FROM T1;
END DEFINE;

DEFINE SUBQUERY $merge_dict($table, $dict) AS
SELECT * FROM $table LEFT JOIN $dict() USING(key); -- использование табличного выражения $dict()
                                                   -- (вызов шаблона подзапроса) в табличном контексте
END DEFINE;

SELECT * FROM $merge_dict("Input", $dict); -- $dict - шаблон позапроса (не табличное выражение)
                                           -- передаваемый в качестве аргумента табличного выражения

Lambda функции

Позволяют комбинировать несколько выражений в одно вызываемое значение.

В круглых скобках перечисляются аргументы, далее после стрелки указывается тело lambda. Тело lambda состоит либо из выражения в круглых скобках, либо из фигурных скобок вокруг необязательной цепочки выражений с присвоением именованных выражений и результата вызова после ключевого слова RETURN в последнем выражении.

Область видимости для тела lambda — сначала локальные именованные выражения, затем аргументы, затем именованные выражения, определенные выше lambda на верхнем уровне запроса.

В теле lambda можно использовать только чистые выражения — в том числе другие lambda, возможно, переданные через аргументы. Но нельзя использовать SELECT, INSERT INTO и прочие выражения верхнего уровня.

Один или более последних параметров lambda могут быть помечены вопросиком как необязательные — если они не были указаны при вызове lambda, то им будет присвоено значение NULL.

Примеры

$f = ($y) -> {
    $prefix = "x";
    RETURN $prefix || $y;
};

$g = ($y) -> ("x" || $y);

$h = ($x, $y?) -> ($x + ($y ?? 0));

SELECT $f("y"), $g("z"), $h(1), $h(2, 3); -- "xy", "xz", 1, 5

-- если результат лямбды вычисляется единственным выражением, то можно использовать более компактный вариант синтаксиса:
$f = ($x, $_) -> ($x || "suffix"); -- второй аргумент не используется
SELECT $f("prefix_", "whatever");

Обращение к контейнерам

Для адресации к значениям внутри контейнеров:

• Struct<>, Tuple<> и Variant<> — используется точка. Набор ключей (для кортежа и соответствующего варианта — индексов) известен в момент компиляции запроса. Валидность ключа проверяется до начала выполнения запроса.
• List<> и Dict<> — используются квадратные скобки. Набор ключей (для списка — индексов) известен только во время выполнения запроса. Валидность ключа не проверяется до начала выполнения запроса. Если значение не найдено — будет возвращено пустое значение (NULL)

Описание и список доступных контейнеров.

При использовании этого синтаксиса для обращения к контейнерам в столбцах таблиц обязательно нужно указывать полное имя столбца, включая имя или алиас таблицы через точку (смотрите первый пример ниже).

Примеры

SELECT
  t.struct.member,
  t.tuple.7,
  t.dict["key"],
  t.list[7]
FROM my_table AS t;

SELECT
  Sample::ReturnsStruct().member;