Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса / Oracle

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

41 сообщений из 41, показаны все 2 страниц

все

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124811

Кроик Семён

Участник

Откуда: СПб --> Dortmund

Сообщения: 7 039

Рейтинг: 0 / 0

Oracle 19.3.0.0.0

Приветствую,

я в шоке, такого точно не было в Oracle 9. Это баг?

Итак, Oracle 19.3.0.0.0
Есть таблица, в которой поле (IsDeleted) при выставлении его в "1" должно в том числе заставлять UNIQUE-индекс игнорировать данную строчку таблицы.
Так вот, при INSERT новой строки в таблицу проверка работает, но при UPDATE проверка перестала в Oracle 19 работать, если индекс определён с использованием decode, но работает при использовании CASE

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

/* тестовая таблица */

-- DROP TABLE test_1;

CREATE TABLE test_1(
   ID        int       PRIMARY KEY, 
   AnyDate   date      NOT NULL, 
   IsDeleted number(1) NOT NULL
   );

INSERT INTO test_1(ID, AnyDate, IsDeleted) values (1, to_date('01.01.2022','DD.MM.YYYY'), 0);
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (2, to_date('02.01.2022','DD.MM.YYYY'), 0);
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (3, to_date('03.01.2022','DD.MM.YYYY'), 0);

INSERT INTO test_1(ID, AnyDate, IsDeleted) values (11, to_date('01.01.2022','DD.MM.YYYY'), 1);
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (12, to_date('02.01.2022','DD.MM.YYYY'), 1);
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (13, to_date('03.01.2022','DD.MM.YYYY'), 1);   

commit;

при UPDATE правильно работает при использовании CASE в описании индекса

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

-- DROP INDEX X_Test_1_UNIQ;

CREATE UNIQUE INDEX X_Test_1_UNIQ
ON test_1(
   case IsDeleted 
      when 1 then NULL 
      else trunc(AnyDate) 
   end
   );   
  
UPDATE test_1 SET IsDeleted=0 WHERE ID=11;  -- ORA-00001: unique constraint (IBS.X_TEST_1_UNIQ) violated  (Всё правильно)
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (31, to_date('01.01.2022','DD.MM.YYYY'), 0); -- ORA-00001: unique constraint (IBS.X_TEST_1_UNIQ) violated
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (41, to_date('01.01.2022','DD.MM.YYYY'), 1); -- 1 row inserted
commit;

при UPDATE неправильно работает при использовании DECODE в описании индекса

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

DROP INDEX X_Test_1_UNIQ;

CREATE UNIQUE INDEX X_Test_1_UNIQ
ON test_1(
   decode(IsDeleted, 
          1, NULL,
          trunc(AnyDate))
   );   
  
UPDATE test_1 SET IsDeleted=0 WHERE ID=11;  -- 1 row updated (Что это сейчас было???)
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (51, to_date('01.01.2022','DD.MM.YYYY'), 0); -- ORA-00001: unique constraint (IBS.X_TEST_1_UNIQ) violated
INSERT INTO test_1(ID, AnyDate, IsDeleted) values (61, to_date('01.01.2022','DD.MM.YYYY'), 1); -- 1 row inserted
commit;

И вот что получаем:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SQL> select * from test_1 order by id;

       ID ANYDATE     ISDELETED
--------- ----------- ---------
        1 01.01.2022          0
        2 02.01.2022          0
        3 03.01.2022          0
       11 01.01.2022          0
       12 02.01.2022          1
       13 03.01.2022          1
       41 01.01.2022          1
       61 01.01.2022          1

8 rows selected

...

Рейтинг:

0 / 0

05.01.2022, 16:07

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124816

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

Кроик Семёнdecode(IsDeleted,

А теперь вопрос на засыпку: это IsDeleted из контекста NEW или OLD?
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

05.01.2022, 16:16

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124819

Кроик Семён

Участник

Откуда: СПб --> Dortmund

Сообщения: 7 039

Рейтинг: 0 / 0

Dimitry Sibiryakov,

разве индекс не для NEW?

...

Рейтинг:

0 / 0

05.01.2022, 16:18

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124823

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

Кроик Семёнразве индекс не для NEW?

Да, пожалуй.

Походу, я его с CHECK CONSTRAINT перепутал.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

05.01.2022, 16:22

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124826

Asmodeus

Гость

Кроик Семён,

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.

select banner_full from v$version;

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0   

create table test_1
(
    id           INT primary key,
    AnyDate      DATE not null,
    IsDeleted    NUMBER (1) not null
);

insert into test_1 (id, AnyDate, IsDeleted)
     values (1, to_date ('01.01.2022', 'DD.MM.YYYY'), 0);

insert into test_1 (id, AnyDate, IsDeleted)
     values (2, to_date ('02.01.2022', 'DD.MM.YYYY'), 0);

insert into test_1 (id, AnyDate, IsDeleted)
     values (3, to_date ('03.01.2022', 'DD.MM.YYYY'), 0);

insert into test_1 (id, AnyDate, IsDeleted)
     values (11, to_date ('01.01.2022', 'DD.MM.YYYY'), 1);

insert into test_1 (id, AnyDate, IsDeleted)
     values (12, to_date ('02.01.2022', 'DD.MM.YYYY'), 1);

insert into test_1 (id, AnyDate, IsDeleted)
     values (13, to_date ('03.01.2022', 'DD.MM.YYYY'), 1);

commit;

create unique index X_Test_1_UNIQ
    on test_1 (decode (IsDeleted, 1, null, trunc (AnyDate)));
    
    
update test_1 SET IsDeleted=0 where id=11;
>> update test_1 SET IsDeleted=0 where id=11
error at line 1
ORA-00001: unique constraint (HR.X_TEST_1_UNIQ) violated

Похоже, где-то в середине поправили.

P.S. В рамках SR'а лет 8 назад поддержка рекомендовала отказываться от decode в пользу case (мол, "эти баги низкоприоритетные", "скоро decode на пенсию отправят"). Но вроде жив еще курилка.

...

Рейтинг:

0 / 0

05.01.2022, 16:29

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124828

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

Кроик Семён,

а если?

decode(IsDeleted, 1, cast(NULL as date) , trunc(AnyDate))

.....
stax

...

Рейтинг:

0 / 0

05.01.2022, 16:32

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124833

Кроик Семён

Участник

Откуда: СПб --> Dortmund

Сообщения: 7 039

Рейтинг: 0 / 0

Stax ,

классно, decode()+CAST заработало. Спасибо

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

UPDATE test_1 SET IsDeleted=1 WHERE ID=11;  -- чтобы не ругался при пересоздании индекса

DROP INDEX X_Test_1_UNIQ;

CREATE UNIQUE INDEX X_Test_1_UNIQ
ON test_1(
   decode(IsDeleted, 
          1, cast(NULL as date),
          trunc(AnyDate))
   );  


UPDATE test_1 SET IsDeleted=0 WHERE ID=11; -- ORA-00001: unique constraint (TTT.X_TEST_1_UNIQ) violated

...

Рейтинг:

0 / 0

05.01.2022, 16:43

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124834

Кроик Семён

Участник

Откуда: СПб --> Dortmund

Сообщения: 7 039

Рейтинг: 0 / 0

Asmodeus

Version 19.12.0.0.0
... ORA-00001

Похоже, где-то в середине поправили

таки баг был значит, спасибо

...

Рейтинг:

0 / 0

05.01.2022, 16:44

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124880

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Кроик Семён

таки баг был значит, спасибо

Да не баг это а твое не RTFM. Тип результата decode есть тип первого decoded expression - у тебя NULL. NULL не имеет типа. Oracle решил что в ситуациях когда необходим тип выражения а выражение NULL (не путать со значением выражения равным NULL) считать тип VARCHAR2. Посему:

Код: plsql

1.
2.
3.

   decode(IsDeleted, 
          1, NULL,
          trunc(AnyDate))

вернет TO_CHAR(trunc(AnyDate),<instance NLS_DATE_FORMAT>).

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

SQL> create table tbl(dt date);

Table created.

SQL> create unique index tbl_idx on tbl(to_char(dt));

Index created.

SQL> select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------


SQL> select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------
DD-MON-RR

SQL> insert into tbl values(sysdate);

1 row created.

SQL> -- ждем несколько секунд
SQL> insert into tbl values(sysdate);
insert into tbl values(sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TBL_IDX) violated


SQL>

По идее Oracle должен бы выругаться и не дать создать NLS зависимый индекс. Возможно он инвалидирует индекс при изменении instance NLS_DATE_FORMAT, не пробовал. В любом случае - индекс завязанный на неявные преобразования рано или поздно выстрелит. Так-что CAST(NULL AS DATE) решает все эти проблемы. И это не зависит от DECODE/CASE. Тип результата case также есть тип первого case expression.

SY.

...

Рейтинг:

0 / 0

05.01.2022, 20:21

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124889

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 744

Рейтинг: 2 / 0

SYвернет TO_CHAR(trunc(AnyDate),<instance NLS_DATE_FORMAT>).
Это не правда. Будет использован database NLS_DATE_FORMAT, что пример и продемонстрировал.
SYВозможно он инвалидирует индекс при изменении instance NLS_DATE_FORMAT, не пробовал.
Oracle так не будет делать.
SYИ это не зависит от DECODE/CASE. Тип результата case также есть тип первого case expression.
От DECODE и CASE зависит. CASE работает несколько иначе в определении типов данных и для приведенного примера тип данных CASE будет DATE.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

SQL> conn tc/tc@db-21/pdb
Connected.
SQL>
SQL> set echo on lin 100
SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.4.0.0.0


SQL>
SQL> DROP TABLE t;

Table dropped.

SQL>
SQL> CREATE TABLE t(
  2     d date,
  3     n number(1)
  4     );

Table created.

SQL>
SQL> CREATE UNIQUE INDEX t_i ON t(
  2    case n
  3      when 1
  4      then null
  5      else trunc(d)
  6    end);

Index created.

SQL>
SQL> col column_name for a15
SQL> col data_type for a15
SQL> col index_name for a15
SQL> col column_expression for a64
SQL> select column_name, data_type from user_tab_cols where table_name='T' and column_name like 'SYS%';

COLUMN_NAME     DATA_TYPE
--------------- ---------------
SYS_NC00003$    DATE

SQL> select index_name, column_expression from user_ind_expressions where index_name like 'T_I%';

INDEX_NAME      COLUMN_EXPRESSION
--------------- ----------------------------------------------------------------
T_I             CASE "N" WHEN 1 THEN NULL ELSE TRUNC("D") END

Очевидно, случился выстрел в ногу. Это баг. Чтобы понять, что происходит, нужно посмотреть небольшой пример.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.

SQL> conn tc/tc@db-21/pdb
Connected.
SQL>
SQL> set echo on lin 100
SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.4.0.0.0


SQL>
SQL> DROP TABLE t;

Table dropped.

SQL>
SQL> CREATE TABLE t(
  2     d date,
  3     n number(1)
  4     );

Table created.

SQL>
SQL> INSERT INTO t(d, n) values (date'2022-01-01', 0);

1 row created.

SQL> INSERT INTO t(d, n) values (date'2022-01-01', 1);

1 row created.

SQL> --INSERT INTO t(d, n) values (date'2022-01-01', 1);
SQL>
SQL> commit;

Commit complete.

SQL>

Здесь будет показано, что instance NLS_DATE_FORMAT не будет использован в индексе

SQL> select value from nls_database_parameters where parameter='NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------------------------------------------------
DD-MON-RR

SQL> select value from nls_instance_parameters where parameter='NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------------------------------------------------
dd-mon-yyyy

SQL>
SQL> create or replace function f(s varchar2)
  2    return varchar2 deterministic
  3  is
  4  begin
  5    return s;
  6  end;
  7  /

Function created.

SQL>
SQL> --alter session set nls_date_format='dy mon yyyy';
SQL> alter session set nls_date_format='dd.mm.yyyy';

Session altered.

SQL> --alter session set events 'sql_trace bind=true';
SQL> CREATE UNIQUE INDEX t_i ON t(decode(n, 1, null, trunc(d)));

Index created.

SQL>
SQL> create index t_i2 on t(to_char(d));

Index created.

SQL> create index t_i3 on t(decode(n,1,null,f(trunc(d))));

Index created.

SQL> --alter session set events 'sql_trace off';
SQL>
SQL> col column_name for a15
SQL> col data_type for a15
SQL> col index_name for a15
SQL> col column_expression for a64
SQL> select column_name, data_type from user_tab_cols where table_name='T' and column_name like 'SYS%';

COLUMN_NAME     DATA_TYPE
--------------- ---------------
SYS_NC00003$    VARCHAR2
SYS_NC00004$    VARCHAR2
SYS_NC00005$    VARCHAR2

Здесь демонстрируется, что Oracle старается использовать NLS_DATE_FORMAT сессии. В 2х индексах появился NLS_DATE_FORMAT сессии.

SQL> select index_name, column_expression from user_ind_expressions where index_name like 'T_I%';

INDEX_NAME      COLUMN_EXPRESSION
--------------- ----------------------------------------------------------------
T_I             DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))
T_I2            TO_CHAR("D",'dd.mm.yyyy')
T_I3            DECODE("N",1,NULL,"TC"."F"(TO_CHAR(TRUNC("D"),'dd.mm.yyyy')))

SQL>
SQL> sho parameter nls_date_f

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      dd.mm.yyyy
SQL>
SQL> UPDATE t SET n=0 WHERE n=1;

1 row updated.

SQL> INSERT INTO t(d, n) values (date'2022-01-01', 0);
INSERT INTO t(d, n) values (date'2022-01-01', 0)
*
ERROR at line 1:
ORA-00001: unique constraint (TC.T_I) violated


SQL> commit;

Commit complete.

SQL>
SQL> col dump for a40
SQL> select SYS_NC00003$, dump(SYS_NC00003$, 16) dump, t.* from t;

SYS_NC00003$       DUMP                                     D                   N
------------------ ---------------------------------------- ---------- ----------
01-JAN-22          Typ=1 Len=9: 30,31,2d,4a,41,4e,2d,32,32  01.01.2022          0
01-JAN-22          Typ=1 Len=9: 30,31,2d,4a,41,4e,2d,32,32  01.01.2022          0

Наблюдаем разное кол-во строк по индексу и при full scan
SQL>
SQL> select * from t where DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))='01-JAN-22';

D                   N
---------- ----------
01.01.2022          0

SQL> select /*+ full(t)*/* from t where DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))='01-JAN-22';

D                   N
---------- ----------
01.01.2022          0
01.01.2022          0

SQL>
SQL> col object_id old_v object_id
SQL> select object_id from obj where object_name='T_I';

 OBJECT_ID
----------
     77888

SQL>
SQL> col rid old_v rid
SQL> select sys_op_lbid(&object_id., 'L', rowid) rid from t where DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))='01-JAN-22';
old   1: select sys_op_lbid(&object_id., 'L', rowid) rid from t where DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))='01-JAN-22'
new   1: select sys_op_lbid(     77888, 'L', rowid) rid from t where DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))='01-JAN-22'

RID
------------------
AAATBAAALAAAMNLAAA

SQL>
SQL> alter system checkpoint;

System altered.

SQL> col fil old_v fil
SQL> col blk old_v blk
SQL>
SQL> select dbms_rowid.rowid_relative_fno('&rid.') fil, dbms_rowid.rowid_block_number('&rid.') blk from dual;
old   1: select dbms_rowid.rowid_relative_fno('&rid.') fil, dbms_rowid.rowid_block_number('&rid.') blk from dual
new   1: select dbms_rowid.rowid_relative_fno('AAATBAAALAAAMNLAAA') fil, dbms_rowid.rowid_block_number('AAATBAAALAAAMNLAAA') blk from dual

       FIL        BLK
---------- ----------
        11      49995

SQL>
SQL> alter system dump datafile &fil. block &blk.;
old   1: alter system dump datafile &fil. block &blk.
new   1: alter system dump datafile         11 block      49995

System altered.

Здесь наиболее интересен дамп индекса, который объясняет, почему Oracle пропустил update:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

row#0[7995] flag: -------, lock: 2, len=18, data:(6):  02 c0 c3 46 00 01
col 0; len 9; (9):  30 31 2d 4a 41 4e 2d 32 32
row#1[8013] flag: -------, lock: 0, len=19, data:(6):  02 c0 c3 46 00 00
col 0; len 10; (10):  30 31 2e 30 31 2e 32 30 32 32

SQL> select utl_raw.cast_to_varchar2(hextoraw(replace('30 31 2d 4a 41 4e 2d 32 32', ' '))) from dual;

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(REPLACE('30312D4A414E2D3232','')))
----------------------------------------------------------------------------------------------------
01-JAN-22

SQL> select utl_raw.cast_to_varchar2(hextoraw(replace('30 31 2e 30 31 2e 32 30 32 32', ' '))) from dual;

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(REPLACE('30312E30312E32303232','')))
----------------------------------------------------------------------------------------------------
01.01.2022

Пока все похоже на то, что без явного формата:
1. Oracle использует database NLS_DATE_FORMAT при построении индекса
2. при INSERT/UPDATE - использует NLS_DATE_FORMAT сессии. Например, можно закомментировать UPDATE и INSERT пройдет, что будет 2 строки и произойдет ситуация, которая никогда не должна происходить:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

SQL> select * from t where DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))='01-JAN-22';

D                   N
---------- ----------
01.01.2022          0

SQL> select /*+ full(t)*/* from t where DECODE("N",1,NULL,TO_CHAR(TRUNC("D")))='01-JAN-22';

D                   N
---------- ----------
01.01.2022          0
01.01.2022          0

3. при rebuild Oracle корректно определяет, что есть duplicate keys:

Код: plsql

1.
2.
3.
4.
5.

SQL> alter index t_i rebuild;
alter index t_i rebuild
                      *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

...

Рейтинг:

0 / 0

05.01.2022, 22:39

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124907

Кроик Семён

Участник

Откуда: СПб --> Dortmund

Сообщения: 7 039

Рейтинг: 0 / 0

классный разбор

...

Рейтинг:

0 / 0

06.01.2022, 01:02

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124913

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

SeaGate

Будет использован database NLS_DATE_FORMAT, что пример и продемонстрировал.

Похоже берет из сессии:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

SQL> select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------
DD-MON-RR

SQL> select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------
mm/dd/yyyy hh24:mi:ss

SQL> select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------
mm/dd/yyyy hh24:mi

SQL>

Создаем таблицу с двумя записями где даты разнятся > 1 секунды и затем создаем уникальный индекс:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

17:01:30 SQL> create table tbl(dt date);

Table created.

17:01:30 SQL> insert into tbl values(sysdate);

1 row created.

17:01:30 SQL> exec dbms_lock.sleep(2);

PL/SQL procedure successfully completed.

17:01:32 SQL> insert into tbl values(sysdate);

1 row created.

17:01:32 SQL> create unique index tbl_idx on tbl(to_char(dt));
create unique index tbl_idx on tbl(to_char(dt))
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


17:01:32 SQL>

Как видим да, не instance как как instance NLS_DATE_FORMAT='mm/dd/yyyy hh24:mi:ss' т.е. до секунд. Теперь тоже самое но даты разнятся > 1 минуты:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

17:06:23 SQL> drop table tbl purge;

Table dropped.

17:06:23 SQL> create table tbl(dt date);

Table created.

17:06:23 SQL> insert into tbl values(sysdate);

1 row created.

17:06:23 SQL> exec dbms_lock.sleep(62);

PL/SQL procedure successfully completed.

17:07:25 SQL> insert into tbl values(sysdate);

1 row created.

17:07:25 SQL> create unique index tbl_idx on tbl(to_char(dt));

Index created.

17:07:25 SQL>

Индекс создался - значит не database как как database NLS_DATE_FORMAT='DD-MON-RR' т.е. до дня. Так-что похоже мы оба промахнулись и используется session NLS_DATE_FORMAT.

SeaGate

От DECODE и CASE зависит. CASE работает несколько иначе в определении типов данных и для приведенного примера тип данных CASE будет DATE.

Проверил, таки да. И тогда это либо баг в CASE либо баг в доке, ибо CASE Expressions глаголит:

For both simple and searched CASE expressions, all of the return_exprs must either have the same data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric data type. If all return expressions have a numeric data type, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

А поскольку NULL datatype есть VARCHAR2, то согласно доке

Код: plsql

1.
2.
3.
4.

   case IsDeleted 
      when 1 then NULL 
      else trunc(AnyDate) 
   end

Oracle должен бы выругаться. Пример показывающий тип NULL:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

SQL> create or replace view v1 as select case sal when 1000 then null else null end x from emp;

View created.

SQL> desc v1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2

SQL>

Кстати у VARCHAR2 нет длины - похоже еще один баг :).

SY.

...

Рейтинг:

0 / 0

06.01.2022, 01:42

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124916

Alexander Anokhin

Гость

Если я правильно понял то что вы написали, это сводится к такому тест кейсу

test

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

set echo on

alter session set nls_date_format = 'dd.mm.yyyy';

select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';
select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';
select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

drop table t;

create table t (
  d date,
  d_virtcol varchar2(10) generated always as (to_char(trunc(d))) virtual
);

insert into t(d) values (date'2022-01-01');
commit;

col dump_d_virtcol for a48
col dump_func for a48

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t;

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where to_char(trunc(d)) = '01.01.2022';

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where d_virtcol = '01.01.2022';

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where d_virtcol = '01-JAN-22';

output

Код: plsql

SQL> 
SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> 
SQL> select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------
DD-MON-RR

1 row selected. 

SQL> select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------


1 row selected. 

SQL> select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------
dd.mm.yyyy

1 row selected. 

SQL> 
SQL> drop table t;

Table T dropped.

SQL> 
SQL> create table t (
  2    d date,
  3    d_virtcol varchar2(10) generated always as (to_char(trunc(d))) virtual
  4  );

Table T created.

SQL> 
SQL> insert into t(d) values (date'2022-01-01');

1 row inserted.

SQL> commit;

Commit complete.

SQL> 
SQL> col dump_d_virtcol for a48
SQL> col dump_func for a48
SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t;

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50      01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where to_char(trunc(d)) = '01.01.2022';

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50      01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where d_virtcol = '01.01.2022';

0 rows selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where d_virtcol = '01-JAN-22';

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01-JAN-22  Typ=1 Len=9: 48,49,45,74,65,78,45,50,50          01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected.

похоже на жука

...

Рейтинг:

0 / 0

06.01.2022, 02:15

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124917

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Все можно было проверить куда проще :

Код: plsql

1.
2.
3.
4.
5.
6.
7.

SQL> SELECT COLUMN_EXPRESSION FROM USER_IND_EXPRESSIONS WHERE INDEX_NAME = 'TBL_IDX';

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
TO_CHAR("DT",'mm/dd/yyyy hh24:mi')

SQL>

Так-что:

1. мы оба промахнулись и используется session NLS_DATE_FORMAT.
2. при INSERT/UPDATE - использует NLS_DATE_FORMAT сессии - тоже нет, т.к. неявное преобразование использующее session NLS_DATE_FORMAT на момент создания индекса закрепляется в COLUMN_EXPRESSION и становится явным для всех последующих действий. Но все равно я бы предпочел чтобы Oracle ругнулся на создание FBI с неявным преобразованием.

SY.

...

Рейтинг:

0 / 0

06.01.2022, 02:15

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124920

Alexander Anokhin

Гость

Alexander Anokhin

Если я правильно понял то что вы написали, это сводится к такому тест кейсу

test

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

set echo on

alter session set nls_date_format = 'dd.mm.yyyy';

select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';
select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';
select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

drop table t;

create table t (
  d date,
  d_virtcol varchar2(10) generated always as (to_char(trunc(d))) virtual
);

insert into t(d) values (date'2022-01-01');
commit;

col dump_d_virtcol for a48
col dump_func for a48

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t;

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where to_char(trunc(d)) = '01.01.2022';

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where d_virtcol = '01.01.2022';

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where d_virtcol = '01-JAN-22';

output

Код: plsql

SQL> 
SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> 
SQL> select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------
DD-MON-RR

1 row selected. 

SQL> select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------


1 row selected. 

SQL> select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------
dd.mm.yyyy

1 row selected. 

SQL> 
SQL> drop table t;

Table T dropped.

SQL> 
SQL> create table t (
  2    d date,
  3    d_virtcol varchar2(10) generated always as (to_char(trunc(d))) virtual
  4  );

Table T created.

SQL> 
SQL> insert into t(d) values (date'2022-01-01');

1 row inserted.

SQL> commit;

Commit complete.

SQL> 
SQL> col dump_d_virtcol for a48
SQL> col dump_func for a48
SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t;

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50      01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where to_char(trunc(d)) = '01.01.2022';

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50      01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where d_virtcol = '01.01.2022';

0 rows selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where d_virtcol = '01-JAN-22';

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01-JAN-22  Typ=1 Len=9: 48,49,45,74,65,78,45,50,50          01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected.

похоже на жука

немного короче

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

select d_virtcol
  from t;

D_VIRTCOL 
----------
01-JAN-22

1 row selected.

select d_virtcol, 
       to_char(trunc(d)) func
  from t;

D_VIRTCOL  FUNC      
---------- ----------
01.01.2022 01.01.2022

1 row selected.

...

Рейтинг:

0 / 0

06.01.2022, 02:37

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124921

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

я бы предпочел чтобы Oracle ругнулся на создание FBI с неявным преобразованием

имхо, желательно было бы вообще NLS-бомбы выявлять как можно раньше и не пытаться унифицировать такое, провоцируя такие разборы и дальнейшее использование минёх.

даже такие

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> create or replace view v1 as select decode(rownum,1,null,2,'z',trunc(sysdate)) x from dual x;

View created.

SQL> alter session set nls_date_format='yyyy-mm-dd"TTTTTTTTTTTTTTTTTTTXXXXXXXX"';

Session altered.

SQL> create or replace view v2 as select decode(rownum,1,null,2,'z',trunc(sysdate)) x from dual x;

View created.

SQL> alter session set nls_date_format='yyyy-mm-dd"TTTTTTTTTTTTTTTTTTTXXXXXXXXZZZ"';

Session altered.

SQL> create or replace view v3 as select decode(rownum,1,null,2,'z',trunc(sysdate)) x from dual x;

View created.

SQL> desc v1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2(19)

SQL> desc v2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2(37)

SQL> desc v3
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2(40)

По поводу "умного" case: можно уточнить, что тип берется из первого не-нуллового expression

Код: plsql

1.
2.
3.
4.
5.

QL> create or replace view v4 as select case when 1=0 then null when 2=0 then 1 else trunc(sysdate) end x from dual x;
create or replace view v4 as select case when 1=0 then null when 2=0 then 1 else trunc(sysdate) end x from dual x
                                                                                 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

...

Рейтинг:

0 / 0

06.01.2022, 02:45

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124924

Alexander Anokhin

Гость

Alexander Anokhin

Если я правильно понял то что вы написали, это сводится к такому тест кейсу

test

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

set echo on

alter session set nls_date_format = 'dd.mm.yyyy';

select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';
select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';
select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

drop table t;

create table t (
  d date,
  d_virtcol varchar2(10) generated always as (to_char(trunc(d))) virtual
);

insert into t(d) values (date'2022-01-01');
commit;

col dump_d_virtcol for a48
col dump_func for a48

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t;

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where to_char(trunc(d)) = '01.01.2022';

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where d_virtcol = '01.01.2022';

select d,
       d_virtcol, 
       dump(d_virtcol) dump_d_virtcol,  
       to_char(trunc(d)) func,
       dump(to_char(trunc(d))) dump_func
  from t
 where d_virtcol = '01-JAN-22';

output

Код: plsql

SQL> 
SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> 
SQL> select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------
DD-MON-RR

1 row selected. 

SQL> select value from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------


1 row selected. 

SQL> select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

VALUE                                                           
----------------------------------------------------------------
dd.mm.yyyy

1 row selected. 

SQL> 
SQL> drop table t;

Table T dropped.

SQL> 
SQL> create table t (
  2    d date,
  3    d_virtcol varchar2(10) generated always as (to_char(trunc(d))) virtual
  4  );

Table T created.

SQL> 
SQL> insert into t(d) values (date'2022-01-01');

1 row inserted.

SQL> commit;

Commit complete.

SQL> 
SQL> col dump_d_virtcol for a48
SQL> col dump_func for a48
SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t;

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50      01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where to_char(trunc(d)) = '01.01.2022';

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50      01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where d_virtcol = '01.01.2022';

0 rows selected. 

SQL> 
SQL> select d,
  2         d_virtcol, 
  3         dump(d_virtcol) dump_d_virtcol,  
  4         to_char(trunc(d)) func,
  5         dump(to_char(trunc(d))) dump_func
  6    from t
  7   where d_virtcol = '01-JAN-22';

D          D_VIRTCOL  DUMP_D_VIRTCOL                                   FUNC       DUMP_FUNC                                       
---------- ---------- ------------------------------------------------ ---------- ------------------------------------------------
01.01.2022 01-JAN-22  Typ=1 Len=9: 48,49,45,74,65,78,45,50,50          01.01.2022 Typ=1 Len=10: 48,49,46,48,49,46,50,48,50,50     

1 row selected.

похоже на жука

немного короче

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

select d_virtcol
  from t;

D_VIRTCOL 
----------
01-JAN-22

1 row selected.

select d_virtcol, 
       to_char(trunc(d)) func
  from t;

D_VIRTCOL  FUNC      
---------- ----------
01.01.2022 01.01.2022

1 row selected.

Забыл уточнить, что тест (выше) требует

Код: plsql

alter session set "_replace_virtual_columns" = false;

Без этого тест кейс мог быть такой

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

alter session set nls_date_format = 'dd.mm.yyyy';
alter session set "_replace_virtual_columns" = true;

drop table t;

create table t(d date);
insert into t(d) values (date'2022-01-01');
commit;

create index i on t(to_char(trunc(d)));

select to_char(trunc(d)) func
  from t;

FUNC      
----------
01-JAN-22

1 row selected.

select to_char(trunc(d)) func
  from t 
 where to_char(trunc(d)) = '01.01.2022';

FUNC      
----------
01.01.2022

1 row selected.

Посмотрю поглубже на днях и заведу багу, если это баг. Пока выглядит как баг.

...

Рейтинг:

0 / 0

06.01.2022, 04:31

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124962

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Sayan Malakshinov

По поводу "умного" case: можно уточнить, что тип берется из первого не-нуллового expression

Это эмпирика не отраженная в доке посему IMHO либо баг в CASE либо баг в доке. И во избежание сюрпризов в следующих версиях я бы использовал CAST/TO_DATE/TO_NUMBER... т.е. никаких неявностей.

SY.

...

Рейтинг:

0 / 0

06.01.2022, 14:13

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124965

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

SYЭто эмпирика не отраженная в доке

А не предписывается ли это ANSI стандартом?..
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

06.01.2022, 14:22

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40124985

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

Это эмпирика не отраженная в доке посему IMHO либо баг в CASE либо баг в доке.

Это похоже на типизацию union-а:

Код: plsql

1.
2.
3.
4.
5.
6.

create view qz_v as
select null c1, 'Y' as c2, null as c3  , sysdate as c4 from dual union all
select 132    , null     , to_clob('a'), null          from dual
;
desc qz_v
drop view qz_v;

Код: plsql

1.
2.
3.
4.
5.
6.

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 C1                                     NUMBER
 C2                                     CHAR(1 CHAR)
 C3                                     CLOB
 C4                                     DATE

я бы использовал CAST/TO_DATE/TO_NUMBER... т.е. никаких неявностей.

Это главное правило волшебника. Но ему не все следуют.

...

Рейтинг:

0 / 0

06.01.2022, 16:25

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125004

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

А поскольку NULL datatype есть VARCHAR2

это крайне спорное утверждение, лично я не согласен: то, что при `create view as select null x from dual` x определен как varchar2(0) не значит, что NULL имеет тип. Это просто ограничение на типы - просто нет спец типа NULL, а поле типа в словаре просто определено как not null. Я, вообще, более согласен с утверждением, что NULL это скорее статус, а не значение. Статус неопределенности

...

Рейтинг:

0 / 0

06.01.2022, 18:54

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125007

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Пример показывающий тип NULL

это, собственно, тоже

эмпирика не отраженная в доке

Кстати у VARCHAR2 нет длины - похоже еще один баг :).

нулевая длина в словаре - це не баг, просто извращение над словарем создавать столбцы с NULL

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

SQL> create view vnull as select null a from dual;

View created.

SQL> select column_name,data_type,data_length from user_tab_columns where table_name='VNULL';

COLUMN_NAM DATA_TYPE   DATA_LENGTH
---------- ---------- ------------
A          VARCHAR2              0

SQL> select cast(null as varchar2(0)) x from dual;
select cast(null as varchar2(0)) x from dual
                              *
ERROR at line 1:
ORA-01723: zero-length columns are not allowed

...

Рейтинг:

0 / 0

06.01.2022, 19:02

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125009

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

Sayan Malakshinov

то, что при `create view as select null x from dual` x определен как varchar2(0) не значит, что NULL имеет тип

Null- значения в оракеле вполне себе типизованные.
Неопределенность возникает лишь при выводе типа из null- литерала .
Поскольку логически эта проблема неразрешима без разделения самих null-литералов на типы - в качестве default выбран относительно универсальный тип varchar2.
Это надо просто помнить :)

...

Рейтинг:

0 / 0

06.01.2022, 19:02

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125144

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymous

Null- значения в оракеле вполне себе типизованные.

Смотря что считать "значениями". С моей точки зрения, тип имеют переменные, столбцы, проекции, прочие литералы(кроме NULL), но не значения - что есть просто набор байтиков. Как, например, null - 0xFF - просто нулевая длина без значения в датафайле будет тем, что говорят метаданные столбца.

andrey_anonymous

Неопределенность возникает лишь при выводе типа из null- литерала .

что значит выводе типа из NULL-литерала? Откуда таки у него тип? Какой? Как его получить?

andrey_anonymous

Поскольку логически эта проблема неразрешима без разделения самих null-литералов на типы - в качестве default выбран относительно универсальный тип varchar2.

сразу несколько крайне спорных моментов: почему это проблема? Если это проблема - то почему она неразрешима без разделения NULL-литералов на типы? Например, введение для ясности отдельного NULL data type, вместо недопустимого varchar2(0). И что значит "в качестве default выбран" - default чего и где? Где это документировано?

...

Рейтинг:

0 / 0

07.01.2022, 20:06

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125147

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

andrey_anonymous

Это надо просто помнить :)

Нет в Oracle NULL литералов. Есть NULL expressions:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

simple_expression::=
{ [ query_name.
  | [schema.] { table. | view. | materialized view. }
  | t_alias.
  ] { column | ROWID }
| ROWNUM
| string
| number
| sequence. { CURRVAL | NEXTVAL }
| NULL
}

SY.

...

Рейтинг:

0 / 0

07.01.2022, 20:18

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125165

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

Нет в Oracle NULL литералов. Есть NULL expressions

Вы правы, Соломон, прошу прощения за терминологическую неточность.
К моей удаче, в данном конкретном случае на тезис не влияет.

Sayan Malakshinov

andrey_anonymous

Null- значения в оракеле вполне себе типизованные.

Смотря что считать "значениями". С моей точки зрения, тип имеют переменные, столбцы, проекции, прочие литералы(кроме NULL), но не значения - что есть просто набор байтиков.

Предлагаю не путать тёплое с мягким...

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

with t(s) as (select 'Саян' s from dual)
   , e(e) as (select * from sys.odcivarchar2list('al32utf8','cl8mswin1251','ru8pc866'))
select s "Значение", utl_raw.cast_to_raw(convert(s,e)) "Представление в байтах"
from t, e;

Значение Представление в байтах
-------- --------------------------------------------------------------------------------
Саян     D0A1D0B0D18FD0BD
Саян     D1E0FFED
Саян     91A0EFAD

SQL>

Или, быть может, так будет понятнее:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SQL> with t(n) as (select 123 from dual)
        , e("Значение", "Внутреннее представление") as (select n, dump(cast (n as binary_float),16)  from t
     	     union all select n, dump(cast (n as binary_double),16)  from t
     	     union all select n, dump(cast (n as number),16)  from t
     	 )
     select * from e;

  Значение Внутреннее представление
---------- --------------------------------------------------------------------------------
       123 Typ=100 Len=4: c2,f6,0,0
       123 Typ=101 Len=8: c0,5e,c0,0,0,0,0,0
       123 Typ=2 Len=3: c2,2,18

SQL>

И хотя, казалось бы:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SQL> select dump(cast (null as binary_float),16)  from dual
  2  		 union all select dump(cast (null as binary_double),16)  from dual
  3  		 union all select dump(cast (null as number),16)  from dual
  4  		 union all select dump(cast (null as date),16)  from dual
  5  		 union all select dump(cast (null as timestamp),16)  from dual
  6  ;

DUMP(CAST(NULLASBINARY_FLOAT),16)
--------------------------------------------------------------------------------
NULL
NULL
NULL
NULL
NULL

и при этом

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

SQL> select sysdate + cast(null as number) from dual
  2  ;

SYSDATE+CAST(NULLASNUMBER)
--------------------------
SQL> select sysdate + cast(null as interval day to second) from dual
  2  ;

SYSDATE+CAST(NULLASINTERVALDAYTOSECOND)
---------------------------------------
SQL> select sysdate + cast(null as date) from dual
  2  ;

Однако:

Код: plsql

1.
2.
3.
4.
5.

select sysdate + cast(null as date) from dual

ORA-00975: date + date not allowed

SQL>

Sayan Malakshinov

andrey_anonymous

Неопределенность возникает лишь при выводе типа из null- литерала .

что значит выводе типа из NULL-литерала?

Минимальный пример:

Код: plsql

select null from dual

Sayan Malakshinov

почему это проблема?

См. данный топик.

Sayan Malakshinov

Если это проблема - то почему она неразрешима без разделения NULL-литералов на типы?

авторНо как, Холмс?!

Sayan Malakshinov

Например, введение для ясности отдельного NULL data type

Вот тут вообще не понял что имелось ввиду.

...

Рейтинг:

0 / 0

07.01.2022, 21:47

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125196

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

andrey_anonymous

Предлагаю не путать тёплое с мягким...

У тебя пост с примерами какой-то сумбурный получился. Пока не понимаю, что конкретно ты подразумеваешь под "значениями", дай твое определение? А то, действительно, из твоих примеров пока выходит непонятное: то ли теплое, то ли мягкое...

andrey_anonymous

Код: plsql

1.
2.
3.

with t(s) as (select 'Саян' s from dual)
   , e(e) as (select * from sys.odcivarchar2list('al32utf8','cl8mswin1251','ru8pc866'))
select s "Значение", utl_raw.cast_to_raw(convert(s,e)) "Представление в байтах"

Значение - это то, что клиент на экране выводит? Или проекция varchar2(4)? Или строковый литерал?
"Представление в байтах" - это к чему? Собственно, наглядно видно, что в нем нет типа. A convert() принимает и возвращает вообще строго один конкретный тип. dump же покажет, что независимо от того какой у тебя nls_lang покажет одно и то же - датабазную унутрянку:

с al32utf8 базы, хоть с 1251 хоть c utf8

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

SQL> select dump('Саян',1016) from dual;

DUMP('САЯН',1016)
-----------------------------------------------------------
Typ=96 Len=8 CharacterSet=AL32UTF8: d0,a1,d0,b0,d1,8f,d0,bd

SQL> create table ss as select 'Саян' s from dual;

Table created.

SQL> select dump(s,1016) from ss;

DUMP(S,1016)
---------------------------------------------------------------
Typ=96 Len=8 CharacterSet=AL32UTF8: d0,a1,d0,b0,d1,8f,d0,bd

andrey_anonymous

Или, быть может, так будет понятнее:

Код: plsql

1.
2.
3.
4.
5.

  Значение Внутреннее представление
---------- --------------------------------------------------------------------------------
       123 Typ=100 Len=4: c2,f6,0,0
       123 Typ=101 Len=8: c0,5e,c0,0,0,0,0,0
       123 Typ=2 Len=3: c2,2,18

пока не стало понятнее: ты показываешь 3 разных числовых типа и говоришь, что у них одно значение, но разное "внутреннее представление"? То есть значение - это число 123? какого тогда оно типа из показанных 3х? Может все-таки это разные "значения"? А если разные, то к чему пример? Чтобы показать что?
Ну и в дампе видно, что тип не хранится во "внутреннем представлении". Тип берется из метаданных (переменной/столбца/литерала...). Например, ту же требуху можем засунуть в разные типы:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

SQL> col v for a10
SQL> ;
  1  select
  2     utl_raw.cast_to_number           (hextoraw('c20218')) n,
  3     utl_raw.cast_to_varchar2         (hextoraw('c20218')) v,
  4     utl_raw.cast_to_binary_integer   (hextoraw('c20218')) i
  5* from dual
SQL> /

         N V                   I
---------- ---------- ----------
       123 &#65533;           12714520

andrey_anonymous

Минимальный пример:

Код: plsql

select null from dual

Погоди-ка, ты выделил слово "вывод": "при выводе типа из NULL-литерала". Будто тип есть, но вывести его проблематично...
ну и возвращаясь к контексту:

andrey_anonymous

Sayan Malakshinovто, что при `create view as select null x from dual` x определен как varchar2(0) не значит, что NULL имеет тип

каким образом твой минимальный пример подтверждает, что у NULL есть тип? На этот пример есть куча контр-примеров - тот же пример с CASE в начале, пример UNION от Elic'a, оверлоадед функции и тд...

извращенный sys.standard.add_months

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

SQL> select dbms_xmlgen.getxml(null) x from dual;
select dbms_xmlgen.getxml(null) x from dual
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'GETXML' match this call


SQL> select sys.standard.add_months(null,null) x from dual;
select sys.standard.add_months(null,null) x from dual
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'ADD_MONTHS' match this call

...

Рейтинг:

0 / 0

08.01.2022, 05:02

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125316

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

Sayan Malakshinov

не понимаю

Не судьба, сталбыть.
Остается надеяться, что тот, кому надо - таки поймет.

...

Рейтинг:

0 / 0

09.01.2022, 14:13

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125331

booby

Участник

Сообщения: 2 667

Рейтинг: 0 / 0

Sayan,
"значения" - это набор битов вместе со способом их интерпретации.
Способ интерпретации набора битов определяет тип значения.

Один и тот же набор битов может может интерпретироваться как не связанные между собой значения,
а andrey_anonymous показывает, как одно и то же "значение" представляется в разных типах разными наборами битов.

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

Касательно Null - это специальная штука, говорящая, что вот у нас есть такое-то место для размещения значения,
но в нём еще никакого значения не размещено.
Поскольку место предназначено для размещения значений определенного типа, то и к Null, сидящему в этом месте,
всегда можно относиться как к типизированному, даже если его двоичное представление универсально.

"минимальный пример" строит однострочное отношение, кортеж в котором состоит из одного элемента,
и этот элемент не может не иметь типа, даже если не содержит значения.
А "где написано", каким должен оказаться тип элемента в таком случае, я не знаю.
Умеренно естественно ждать его как Varchar2

...

Рейтинг:

0 / 0

09.01.2022, 16:39

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125343

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

booby,

Весьма спорно, тк часто говорится что null - это отсутствие значения. Те null value == no value. И тогда вы едите его не с той стороны. Если же вы утверждаете, что значение - это то, что имеет тип, то это вообще получается софистика и в общем случае к null, включая кучи примеров из этого топика (и даже "минимальный пример" - попробуйте создать таблицу из него) не применимы.

...

Рейтинг:

0 / 0

09.01.2022, 17:52

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125344

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

А, главное, возвращаясь к предмету спора, этот "минимальный пример" не может служить доказательством того, что null имеет тип.

...

Рейтинг:

0 / 0

09.01.2022, 17:58

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125375

booby

Участник

Сообщения: 2 667

Рейтинг: 0 / 0

Sayan Malakshinov,

я пробежал по тексту сейчас и не смог себе вполне уяснить, в чем же существо спора.
Без дополнительных пояснений, что необходимо доказать утверждением, или его опровержением, не ясно,
о чём вообще идет речь.

Прочитанное я могу интерпретировать так:
существом спора является вопрос - является ли Null значением, или флагом статуса переменной, предназначенной для хранения значения и имеющим два состояния - "значение переменной сформировано"/"значение переменной не сформировано".
Я склоняюсь ко второй интерпретации.

Но затрудняюсь при этом придать точный смысл высказыванию "Null не имеет типа", если только под Null не понимать собственно литерал, используемый как местозаместитель значения для переменной, способной его не содержать.

Мне очевидно, что такой литерал, сам по себе, типа не имеет, и тип значения, которое может располагаться в месте использования
такого литерала, выводится из общего контекста выражения.

...

Рейтинг:

0 / 0

09.01.2022, 19:53

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125390

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Sayan Malakshinov

Ну нет у NULL типа. Есть заповедь спущенная Oracle на одной из скрижалей "там где требуется определить тип выражения, а выражение NULL типом выражения считать VARCHAR2". Правда в CASE сам Oracle заповедь и нарушил.

SY.

...

Рейтинг:

0 / 0

09.01.2022, 21:58

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125391

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Кстати, "там где требуется определить тип выражения, а выражение NULL типом выражения считать VARCHAR2" тоже не оригинальная заповедь. В ооочень древних версиях это был CHAR - VARCHAR2 еще не существовало.

SY.

...

Рейтинг:

0 / 0

09.01.2022, 22:08

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125393

booby

Участник

Сообщения: 2 667

Рейтинг: 0 / 0

Sayan Malakshinov

Думаю, в этом определении есть дефект.
я бы формулировал так: если выражение эволюционирует в Null, и тип выражения не определяется из контекста,
тогда трам-парам-папам.

В этом смысле, в case ничего не нарушено, как и в union или "минимальном примере".

Тo, что для decode поведение, связанное с выводом типа результата, отличается от case, может быть и удивительно само по себе,
но вряд ли говорит о том, что decode несомненно правильно устроен, a case нет.

Использованы два разных правила, но каждое из них имеет вполне разумный смысл, хотя и не сходятся друг с другом.
Имхо, это обстоятельство недостаточно документировано, это может и плохо.
А поведение case expression, имхо, выглядит более разумным по отношению к "жадному" decode, старающемуся определить
тип по первому "возвращаемому значению".
И особенно, если не считать Null преждевременно значением.

...

Рейтинг:

0 / 0

09.01.2022, 22:59

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125396

booby

Участник

Сообщения: 2 667

Рейтинг: 0 / 0

booby,

для decode, кстати, все правила как раз хорошо документированы, и, сами по себе, не имеют отношения к "существу спора":

Oracle automatically converts expr and each search value to the data type of the first search value before comparing. Oracle automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2.

https://docs.oracle.com/database/121/SQLRF/functions057.htm#SQLRF00631

...

Рейтинг:

0 / 0

09.01.2022, 23:31

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125397

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

booby

Думаю, в этом определении есть дефект.
я бы формулировал так: если выражение эволюционирует в Null, и тип выражения не определяется из контекста,
тогда трам-парам-папам.

Непонятно что имеется ввиду под "эволюционирует"? По моему все просто - есть ряд ситуаций где имеется набор выражений связанных неким условием относительно их типов. Что делать когда какие-то выражения NULL (не путать со значениями выражений)? Как проверить условие если у выражения NULL (опять, не путать со значением выражения) типа нет? Oracle решил что в таких случаях мы мы используем VARCHAR2 в качестве типа. И это не значит что тип выражения NULL есть VARCHAR2 или что у выражения NULL вообще есть тип.

SY.

...

Рейтинг:

0 / 0

09.01.2022, 23:42

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125419

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Ну нет у NULL типа.

Именно! И на этом надо остановиться, т.к.

Есть заповедь спущенная Oracle на одной из скрижалей "там где требуется определить тип выражения, а выражение NULL типом выражения считать VARCHAR2"

andrey_anonymous

в качестве default выбран относительно универсальный тип varchar2.
Это надо просто помнить

такие ответы порождают мифы и приводят к таким вопросам как у ТС. И отталкиваться от нескольких примеров, когда NULL интерпретируется как varchar2(0) нельзя(что и есть "эмпирика не отраженная в доке"), т.к. контрпримеров будет не меньше, а то и в разы больше.

К уже перечисленным CASE, UNION и overloaded functions, я еще вспомнил NVL и COALESCE:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

Но как видим nvl(null,1) не кастуется в varchar2, как было бы при '':

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

SQL> create view v1 as select nvl(null,1) x1, nvl(null,'x') x2 from dual;

View created.

SQL> desc v1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X1                                                 NUMBER
 X2                                                 VARCHAR2(1)

Такая же ситуация и с coalesce только он идет от numeric:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/COALESCE.html If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

SQL> create view v2 as select coalesce(null,null,1) x1, coalesce('',null,'2') x2,coalesce(null,null,'3') x3 from dual;

View created.

SQL> desc v2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X1                                                 NUMBER
 X2                                                 VARCHAR2(1)
 X3                                                 CHAR(1)

SQL> select coalesce(null,null*null,null||null) x from dual;

         X -- < это number
----------

SQL> select coalesce(null,null||null,'') x from dual;

X -- < это varchar2
-


SQL> select coalesce(null,null||null,null*null) x from dual;

         X -- < снова number
----------



SQL> select coalesce(null,null*null,null||null,'') x from dual;
select coalesce(null,null*null,null||null,'') x from dual
                                          *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

SQL> select coalesce(null,null||null,'',null*null) x from dual;

X
-


SQL> select coalesce(null,null||null,'',null*null,1) x from dual;
select coalesce(null,null||null,'',null*null,1) x from dual
                                             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

...

Рейтинг:

0 / 0

10.01.2022, 03:03

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125509

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Sayan Malakshinov

Именно! И на этом надо остановиться, т.к.
такие ответы порождают мифы и приводят к таким вопросам как у ТС.

На этом остановиться не получится ибо у ТС возникнет другой вопрос: что делать когда имеется набор выражений связанных неким условием относительно их типов а выражение NULL?

SY.

...

Рейтинг:

0 / 0

10.01.2022, 14:22

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125523

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

SY,

Избегать неоднозначностей - достаточно универсальный и корректный ответ. Иначе в мифотворчестве придётся вам все время уточнять в каких именно случаях ваш миф/заповедь работает.

...

Рейтинг:

0 / 0

10.01.2022, 15:24

| Ответить | Цитировать | Написать

Нарушение UNIQUE-индекса при decode() и соблюдение при CASE в описании индекса

#40125537

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Sayan Malakshinov

Иначе в мифотворчестве придётся вам все время уточнять в каких именно случаях ваш миф/заповедь работает.

Вот и я о том-же. Mифотворчество порождается отсутствием заповедей . Вот есть-же в DECODE: "If the first result has the data type CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2". А вот для CASE/set operations и других случаев дока молчит что и порождает мифы.

SY.

...

Рейтинг:

0 / 0

10.01.2022, 16:01

| Ответить | Цитировать | Написать

41 сообщений из 41, показаны все 2 страниц

все

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=52&tid=1879617]:	0ms
get settings:	17ms
get forum list:	5ms
check forum access:	1ms
check topic access:	1ms
track hit:	37ms
get topic data:	3ms
get forum data:	1ms
get page messages:	673ms
get tp. blocked users:	0ms
others:	372ms

total:	1110ms