Недетерминированные результаты с общими табличными выражениями (CTE) MariaDB

Содержание

Вопрос или проблема
Ответ или решение
Анализ проблемы
Решение проблемы
Заключение

Вопрос или проблема

Я пишу запрос, который использует случайные функции и, по сути, сводится к приведенному ниже примеру:

with test_a as (
    select 
        rand()
), test_b as (
    select
        *
    from 
        test_a
)

select
    *
from
    test_a
    
union all

select
    *
from
    test_b

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

0.013195173827181732
0.4233234193393247

Мне кажется, что такое поведение странно. Почему результат test_a не сохраняется для других запросов? Значит ли это, что test_a выполняется дважды? Я думал, что цель CTE заключается в том, чтобы инкапсулировать результат для простого и быстрого обращения в последующих SQL, чтобы это не создавало проблемы с производительностью, если он содержал сложный код. Если он выполняется дважды, чем это отличается от копирования и вставки подзапроса?

Я пришел из PostgreSQL, и для своей ясности я протестировал точно такой же код (заменив rand() на random()) и получил результат, который ожидал:

0.1106535559017996
0.1106535559017996

Любая помощь будет очень признательна – есть ли способ настроить сервер/запрос так, чтобы он был детерминированным?

Ответ или решение

Контекст: Неопределенные результаты с использованием CTE в MariaDB

Вопрос о получении различных значений из общего табличного выражения (CTE) в MariaDB при использовании функций генерации случайных чисел, таких как RAND(), требует детального разъяснения. Ваша проблема заключается в том, что результаты для одного и того же выражения в CTE в конечном итоге оказываются разными при выполнении запроса, что контрастирует с поведением в PostgreSQL.

Анализ проблемы

Поведение функций random: В MariaDB функция RAND() возвращает новое случайное значение каждый раз, когда она вызывается. Это значит, что при первом выполнении test_a, функция RAND() генерирует одно значение, и затем при выполнении test_b вызывается test_a еще раз, генерируя другое значение. Таким образом, select * from test_a и select * from test_b могут возвращать разные значения.
Сравнение с PostgreSQL: В PostgreSQL, использование random() в CTE будет возвращать то же значение для всех последующих ссылок на это выражение в рамках одного запроса. Это связано с тем, что PostgreSQL имеет другую стратегию работы с CTE и позволяет использовать результаты кэшированными.
Ожидания от CTE: Ожидание, что CTE будет вести себя как подзапрос с кэшированным результатом, не совсем справедливо для MariaDB. Хотя CTE и предоставляет простой синтаксис для роутинга результатов, его поведение зависит от конкретного механизма выполнения в СУБД.

Решение проблемы

Сохранение значений: Если вы хотите гарантировать, что для обеих выборок будет одно и то же случайное значение, вам следует использовать подзапрос, который будет возвращать значение только один раз. Например:
```
select rand() as random_value into @rand_value;

select @rand_value as random_value
union all
select @rand_value;
```
Это гарантирует, что значение будет сгенерировано один раз и использовано в повторяющихся выборках.
Использование временных таблиц: Если вы часто обращаетесь к одному и тому же значению случайности, может быть целесообразно сохранить его во временной таблице:
```
create temporary table temp_rand as
select rand() as random_value;

select * from temp_rand
union all
select * from temp_rand;
```
Проверка конфигурации сервера: Рассмотрите возможность настройки конфигурации сервера для работы с определенными функциями, но учтите, что это может не всегда привести к желаемым результатам из-за особенностей реализации.

Заключение

На практике, если вам нужна предсказуемость в выводе значений, использование переменных или временных таблиц, как описано выше, является самым надежным подходом, который позволяет избежать неопределенности, возникающей из-за вызова RAND() в нескольких местах. При переходе с одной СУБД на другую всегда полезно учитывать особенности реализации, такие как порядок выполнения запросов, кэширование результатов и тому подобное. Понимание этих нюансов поможет вам эффективно использовать MariaDB и избегать неожиданных проблем в будущем.