Я обнаружил неправильное поведение функции RANDOM() в SQLite.
Я хочу генерировать случайные группы, используя random RANDOM() и CASE. Однако похоже, что CTE ведет себя некорректно.
Сначала создадим таблицу
DROP TABLE IF EXISTS tt10ROWS;
CREATE TEMP TABLE tt10ROWS (
some_int INTEGER);
INSERT INTO tt10ROWS VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
SELECT * FROM tt10ROWS;
Неправильное поведение
WITH
-- 2.a add columns with random number and save in CTE
STEP_01 AS (
SELECT
*,
ABS(RANDOM()) % 4 + 1 AS RAND_1_TO_4
FROM tt10ROWS)
-- 2.b - get random group
select
*,
CASE
WHEN RAND_1_TO_4 = 1 THEN 'GROUP_01'
WHEN RAND_1_TO_4 = 2 THEN 'GROUP_02'
WHEN RAND_1_TO_4 = 3 THEN 'GROUP_03'
WHEN RAND_1_TO_4 = 4 THEN 'GROUP_04'
END AS GROUP_IT
from STEP_01;
Используя такой запрос, мы получаем таблицу, которая генерирует правильные значения для столбцов RAND
DROP TABLE IF EXISTS tt10ROWS;
CREATE TEMP TABLE tt10ROWS (
some_int INTEGER);
INSERT INTO tt10ROWS VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
SELECT * FROM tt10ROWS;
Правильное поведение
Я нашел обходной путь для такой проблемы, создав временную таблицу вместо использования CTE. Это помогло.
-- 1.a - add column with random number 1-4 and save as TEMP TABLE
drop table if exists ttSTEP01;
CREATE TEMP TABLE ttSTEP01 AS
SELECT
*,
ABS(RANDOM()) % 4 + 1 AS RAND_1_TO_4
FROM tt10ROWS;
-- 1.b - get random group
select
*,
CASE
WHEN RAND_1_TO_4 = 1 THEN 'GROUP_01'
WHEN RAND_1_TO_4 = 2 THEN 'GROUP_02'
WHEN RAND_1_TO_4 = 3 THEN 'GROUP_03'
WHEN RAND_1_TO_4 = 4 THEN 'GROUP_04'
END AS GROUP_IT
from ttSTEP01;
ВОПРОС
Каковы причины такого поведения, когда столбец GROUP_IT не генерируется должным образом?
RAND_1_TO_4пересчитывается каждый раз, когда на него ссылаются (что, по-видимому, имеет место здесь), оно должно пересчитывается какABS(RANDOM()) % 4 + 1, поэтому в результате не должно бытьnull. Поскольку естьnull, это означает, что пересчет не выполняется так, как должен. Это пахнет ошибкой. 13.10.2020