Была задача сгенерировать случайные пары чисел
(s, e) такие, что
s <= e . Для этого решил выбирать пары значений
dbms_random.value в подзапросе, и при необходимости переставлять значения в паре, чтобы соблюсти условие
s <= e . Перестановку можно написать с помощью
least и
greatest , но можно и вручную с помощью
case . Однако выясняется, что вариант с
case совсем не прост, я не могу понять логику его работы.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
with
rnd as
(select /*+ materialize */
trunc(dbms_random.value(0, 11)) as s,
trunc(dbms_random.value(0, 11)) as e
from dual
--1 union all
--2 select 1 as s, 2 as e from dual
)
--3 rnd as (select 0 as s, 1 as e from dual)
select case when s > e then e else s end as "case_start",
case when s > e then s else e end as "case_end",
least(s, e) as "least_start",
greatest(s, e) as "greatest_end",
case when s > e then 'fired' else 'no need' end as "swap",
'derived' as "source"
from rnd
union all
select r.s, r.e, r.s, r.e, '', 'original' from rnd r;
Этот запрос порождает, например, такие результаты:
1.
2.
case_start case_end least_start greatest_end swap source
7 1 1 7 no need derived
1 7 1 7 original
Обратите внимание, перестановка не требовалась, она не была заложена в логике
case -операторов, но она произошла.
Почему?
Почему
least и
greatest сработали правильно, а
case - нет? Последние делают перестановку независимо от исходных данных. Причём, если заменить именованный запрос
rnd на тот, что закомментирован в строке №3, то всё заработает:
1.
2.
case_start case_end least_start greatest_end swap source
0 1 0 1 no need derived
0 1 0 1 original
Если не заменять выборку, а добавить к случайным числам фиксированные, раскомментировав строчки 1 и 2,
то всё будет работать правильно:
1.
2.
3.
4.
case_start case_end least_start greatest_end swap source
2 5 2 5 no need derived
1 2 1 2 no need derived
2 5 2 5 original
1 2 1 2 original
Также всё будет работать, если в первый и второй
case вставить дополнительное условие, например
1.
2.
3.
case when s > e then e else s end as "case: start"
=>
case when s > e then e when 1 = 2 then null else s end as "case: start"
или если в
case произвести с возвращаемыми
s или
e математические действия, не меняющие их значений:
1.
2.
3.
4.
case when s > e then e else s end as "case: start"
=>
case when s > e then e*1 else s end as "case: start"
case when s > e then e else s*1 end as "case: start"
Я подменял обращения к
dbms_random на свою функцию, генерирующую последовательные номера,
и проверял, что она вызывается лишь дважды.
План выглядит так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 4 (50)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6670_EF0739EE | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6670_EF0739EE | 1 | 26 | 2 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6670_EF0739EE | 1 | 26 | 2 (0)| 00:00:01 |
Подскажите, пожалуйста, в чём подвох?
