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

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

Гость

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

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

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

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

25 сообщений из 41, страница 1 из 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

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

25 сообщений из 41, страница 1 из 2

все

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

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

total:	858ms