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

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

Гость

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

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

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

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

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

все

Нарушение 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

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

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

все

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=52&msg=40125393&tid=1879617]:	0ms
get settings:	26ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	45ms
get topic data:	10ms
get forum data:	3ms
get page messages:	342ms
get tp. blocked users:	1ms
others:	15ms

total:	461ms