Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Странный глюк при вызове функции из select  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 891
Вроде простая функция, с одним параметром, а в моем оракле почему-то глючит когда передаю параметр определенного формата.

SQL> create function Bracketize( s in varchar2 default '666' ) return varchar2 deterministic is
  2  begin
  3    return '['|| s ||']'; -- в квадратных скобках все выглядет красивее
  4  end Bracketize;
  5  /
Function created

SQL> select trim(column_value) as val, Bracketize(trim(column_value)) as str from XMLTABLE('"one","two","three"');
VAL              STR
-------------------------------------
one              [one]   -- работает замечательно!
two              [two]
three            [three]

SQL> select trim(column_value) as val, Bracketize(decode(trim(column_value),'two',null,'not two')) as str from XMLTABLE('"one","two","three"');
VAL              STR
-------------------------------------
one              [] -- должно быть [not two]
two              [] -- тут все верно
three            [] -- должно быть [not two]


Если переписать без decode(), то все в порядке:

SQL> select trim(column_value) as val, Bracketize(case trim(column_value) when 'two' then null else 'not two' end) as str from XMLTABLE('"one","two","three"');
VAL              STR
-----------------------------------------
one              [not two]
two              []
three            [not two]

SQL> select trim(column_value) as val, Bracketize(nvl2(nullif(trim(column_value),'two'),'not two',null)) as str from XMLTABLE('"one","two","three"');
VAL              STR
-----------------------------------------
one              [not two]
two              []
three            [not two]


Если убрать "default" в определении глобальной функции, то все работает как следует ожидать.
3 май 21, 21:58    [22317975]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
Правильный Вася
Member

Откуда:
Сообщений: 434
На моём ORA 19.3 EE отрабатывает корректно.
3 май 21, 22:30    [22317995]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 891
Правильный Вася
На моём ORA 19.3 EE отрабатывает корректно.


Наверное, просто старый баг. Хорошо, что исправили.
Надо будет грепнуть все исходники на предмет передачи decode() в функции с параметрами по умолчанию.
3 май 21, 22:40    [22317998]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
Sayan Malakshinov
Member

Откуда: Мск
Сообщений: 5871
Работает корректно и в 11.2, и 18.5, и 19+.
4 май 21, 00:04    [22318045]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 891
Sayan Malakshinov
Работает корректно и в 11.2, и 18.5, и 19+.


Спасибо что проверили. Значит, проблема именно с моим сервером.

Я попробовал с фиддл, у меня там не получилось создать функцию, поэтому пришлось найти библиотечную функцию с default у которой нет SQL аналога.

select utl_encode.text_encode('Ü',decode(trim(column_value),'two',null,'RU8PC866'))
  from XMLTABLE('"one","two","three"');


https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=d28fbfefad722bb8de232c2ec2a1af82

Фиддл (11.2 и 18) отработала как следует, а вот у меня три строки одинаковые, т.е. ошибка.
Попробовал в другой схеме - все равно ошибка.

Наверно что-то где-то сломано.
4 май 21, 01:40    [22318085]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
Melkomyagkii_newbi
Member

Откуда: из прошлого
Сообщений: 2027
НеофитSQL,

А тримы сами по себе корректно работают?

еще интересно было бы взглянуть на вывод
--query transformation (dbms_sql2.expand_sql_text если ora 11g):
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => '<твои запросы тут>',
    output_sql_text => l_clob
  );
  DBMS_OUTPUT.put_line(l_clob);
END;


Сообщение было отредактировано: 4 май 21, 12:00
4 май 21, 12:04    [22318188]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 891
Melkomyagkii_newbi
НеофитSQL,

А тримы сами по себе корректно работают?

еще интересно было бы взглянуть на вывод
+
--query transformation (dbms_sql2.expand_sql_text если ora 11g):
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => '<твои запросы тут>',
    output_sql_text => l_clob
  );
  DBMS_OUTPUT.put_line(l_clob);
END;


Да, тримы нормально, проверил. dbms_sql2 пакет моей 11.2.0.1 версии почему-то неведом.
Я убрал XML таблицу чтоб не загромождала тримами, теперь так:
SQL> create or replace function Bracketize( s in varchar2 default '666' ) return varchar2 is
  2  begin
  3    return '['|| s ||']';
  4  end Bracketize;
  5  /

Function created.

SQL> select level, Bracketize(decode(level,2,null,'not two')) from dual connect by level <= 3;

     LEVEL     BRACKETIZE(DECODE(LEVEL,2,NULL,'NOTTWO'))
--------------------------------------------------------------------------------
         1        []
         2        []
         3        []

Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LEVEL<=3)


Баг довольно хрупкий. Если я делаю мало-мальский намек что третий параметр decode является строкой, то все работает нормально:

SQL> select level, Bracketize(decode(level,2,null||to_date(null),'not two')) from dual connect by level <= 3;

     LEVEL    BRACKETIZE(DECODE(LEVEL,2,NULL||TO_DATE(NULL),'NOTTWO'))
--------------------------------------------------------------------------------
         1       [not two]
         2       []
         3       [not two]

Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LEVEL<=3)


Если убрать default из определения функции, баг тоже исчезает.
Если параметр функции не текстовый, то бага нет, с дефолтом или без.
5 май 21, 00:59    [22318572]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
delphinotes
Member

Откуда: Санкт-Петербург
Сообщений: 390
НеофитSQL,

может быть проблема в том, что default '666' в какой-то момент трактуется как число? Другие варианты с default пробовали? default null? default to_char('666')?

Вообще похоже на какой-то баг в оракловом словаре, у меня была ошибка, когда триггер on delete срабатывал и на update (или наоборот, уже точно не помню)... и это случилось, когда сначала триггер был создан как триггер на update + delete, потом в ходе ряда правок осталась только одна операция и в момент компиляции моя сессия повисла.
Переименование триггера ситуацию исправило, но если вернуть имя обратно, то стабильно воспроизводится этот баг (на конкретном инстансе, 11.2.0.4 x64). Т.е. оно работает, будто в словаре осталось старое определение триггера.
5 май 21, 09:23    [22318623]     Ответить | Цитировать Сообщить модератору
 Re: Странный глюк при вызове функции из select  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 891
delphinotes
НеофитSQL,

может быть проблема в том, что default '666' в какой-то момент трактуется как число? Другие варианты с default пробовали? default null? default to_char('666')?

Вообще похоже на какой-то баг в оракловом словаре, у меня была ошибка, когда триггер on delete срабатывал и на update (или наоборот, уже точно не помню)... и это случилось, когда сначала триггер был создан как триггер на update + delete, потом в ходе ряда правок осталась только одна операция и в момент компиляции моя сессия повисла.
Переименование триггера ситуацию исправило, но если вернуть имя обратно, то стабильно воспроизводится этот баг (на конкретном инстансе, 11.2.0.4 x64). Т.е. оно работает, будто в словаре осталось старое определение триггера.


Я довольно детально поковырял эту проблему, значение дефолта не влияет. повторяется и с null, и с любой другой строкой дефолта. Для типов отличных от varchar/2 этот баг получить не удалось. Если в decode в третьем параметре вручную проставить тип как я сделал с конкатенацией, то баг исчезает.

Если бы это повторялось вне моего сервера, можно было бы порассуждать что где-то в этой неудобной для оракла конструкции остался косяк. Неудобной, потому что в PL/SQL коде decode не может быть параметром функции, соответственно и тест кейс нужен особый.

Но эта проблема только у меня, в различных БД и разных схемах. Надеюсь убедить моего и.о. ДБА пропатчить до последнего 11г, и тогда еще раз проверить.
5 май 21, 17:54    [22318932]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить