Разный результат выполнения запросов. Почему?

medium
Дата: 11.03.2011 16:42:44
На одном инстансе:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Один запрос.
select pr.*
from
    (
        select 1 id
          , null person_id
          , case '2'
                when '1'
                THEN 'YES'
                else 'NO'
            end field1
        from dual d
    )
    p
left join
    (
        select 1 id, null || '.' fio from dual
    )
    Pr
ON  Pr.ID = p.Person_ID
Результат:
ID:1; FIO:'.'

Другой запрос:
select pr.*
from
    (
        select 1 id
          , null person_id
          /*, case '2'
                when '1'
                THEN 'YES'
                else 'NO'
            end field1*/
        from dual d
    )
    p
left join
    (
        select 1 id, null || '.' fio from dual
    )
    Pr
ON  Pr.ID = p.Person_ID
Результат:
ID:null; FIO:null

На другом инстансе:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Опять выполняю первый запрос:
select pr.*
from
    (
        select 1 id
          , null person_id
          , case '2'
                when '1'
                THEN 'YES'
                else 'NO'
            end field1
        from dual d
    )
    p
left join
    (
        select 1 id, null || '.' fio from dual
    )
    Pr
ON  Pr.ID = p.Person_ID
Результат:
ID:null; FIO:null

Вопрос: правильно ли работает первый инстанс?
orawish
Дата: 11.03.2011 16:58:15
medium,

ансишные джоины - штука глюкавая.
в вашем случае - первый сервер (имхо) право-с-левом попутал.
см:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> with p as
  2      (
  3          select 1 id
  4            , null person_id
  5            , case '2'
  6                  when '1'
  7                  THEN 'YES'
  8                  else 'NO'
  9              end field1
 10          from dual d
 11      )
 12  ,pr as
 13      (
 14          select 1 id, null || '.' fio from dual
 15      )
 16  select pr.*
 17    from p,pr
 18  where Pr.ID = p.Person_ID (+);

        ID F
---------- -
         1 .

SQL> with p as
  2      (
  3          select 1 id
  4            , null person_id
  5            , case '2'
  6                  when '1'
  7                  THEN 'YES'
  8                  else 'NO'
  9              end field1
 10          from dual d
 11      )
 12  ,pr as
 13      (
 14          select 1 id, null || '.' fio from dual
 15      )
 16  select pr.*
 17    from p,pr
 18  where Pr.ID (+) = p.Person_ID;

        ID F
---------- -

medium
Дата: 11.03.2011 17:47:58
Обращаю внимание, что выбрасывание Case из текста запроса приводит к нормальному (на мой взгляд) результату.
Поэтому "глюкавым" также можно считать не только Left Join, но и Case.
Но лично меня объяснение "он глюкавый" - пока не устраивает.
Если есть возможность, проверьте, пожалуйста, как у кого на десятке и на одиннадцатой работают эти запросы...
У всех ли так, как у меня?
dbms_photoshop
Дата: 11.03.2011 18:12:41
medium
Но лично меня объяснение "он глюкавый" - пока не устраивает.
Ты впервые в жизни столкнулся с багом Оракла???
Хотя этот не просто ansi, а серьезнее. :)
+ 10.2.0.1.0
SQL> select * from v$version where rownum = 1;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
 
1 row selected
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, null person_id FROM dual d) p
  3    LEFT JOIN (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5  ON pr.id = p.person_id;
 
        ID FIO
---------- ---
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, -1 person_id FROM dual d) p
  3    LEFT JOIN (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5  ON pr.id = p.person_id;
 
        ID FIO
---------- ---
 
1 row selected
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, NULL person_id FROM dual d) p,
  3  	   (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5   WHERE pr.id(+) = p.person_id;
 
        ID FIO
---------- ---
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, -1 person_id FROM dual d) p,
  3  	   (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5   WHERE pr.id(+) = p.person_id;
 
        ID FIO
---------- ---
 
1 row selected

+ 10.2.0.5.0
SQL> select * from v$version where rownum = 1;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
 
1 row selected
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, null person_id FROM dual d) p
  3    LEFT JOIN (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5  ON pr.id = p.person_id;
 
        ID FIO
---------- ---
 
1 row selected
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, -1 person_id FROM dual d) p
  3    LEFT JOIN (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5  ON pr.id = p.person_id;
 
        ID FIO
---------- ---
 
1 row selected
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, NULL person_id FROM dual d) p,
  3  	   (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5   WHERE pr.id(+) = p.person_id;
 
        ID FIO
---------- ---
 
1 row selected
SQL> SELECT pr.*
  2    FROM (SELECT 1 id, -1 person_id FROM dual d) p,
  3  	   (SELECT 1 id, NULL || '.' fio FROM dual) pr
  4  --
  5   WHERE pr.id(+) = p.person_id;
 
        ID FIO
---------- ---
 
1 row selected
Ах да, case я выбросил. :)
Asmodeus
Дата: 11.03.2011 18:17:30
medium,

10.2.0.4
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select pr.*
  2  from
  3      (
  4          select 1 id
  5            , null person_id
  6            , case '2'
  7                  when '1'
  8                  THEN 'YES'
  9                  else 'NO'
 10              end field1
 11          from dual d
 12      )
 13      p
 14  left join
 15      (
 16          select 1 id, null || '.' fio from dual
 17      )
 18      Pr
 19  ON  Pr.ID = p.Person_ID;

        ID F                                                                    
---------- -                                                                    
                                                                                

SQL> select pr.*
  2  from
  3      (
  4          select 1 id
  5            , null person_id
  6            /*, case '2'
  7                  when '1'
  8                  THEN 'YES'
  9                  else 'NO'
 10              end field1*/
 11          from dual d
 12      )
 13      p
 14  left join
 15      (
 16          select 1 id, null || '.' fio from dual
 17      )
 18      Pr
 19  ON  Pr.ID = p.Person_ID;

        ID F                                                                    
---------- -                                                                    
                                                                                

11.2.0.1
SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          
PL/SQL Release 11.2.0.1.0 - Production                                          
CORE	11.2.0.1.0	Production                                                      
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production                         
NLSRTL Version 11.2.0.1.0 - Production                                          

SQL> select pr.*
  2  from
  3      (
  4          select 1 id
  5            , null person_id
  6            , case '2'
  7                  when '1'
  8                  THEN 'YES'
  9                  else 'NO'
 10              end field1
 11          from dual d
 12      )
 13      p
 14  left join
 15      (
 16          select 1 id, null || '.' fio from dual
 17      )
 18      Pr
 19  ON  Pr.ID = p.Person_ID;

        ID F                                                                    
---------- -                                                                    
                                                                                

SQL> select pr.*
  2  from
  3      (
  4          select 1 id
  5            , null person_id
  6            /*, case '2'
  7                  when '1'
  8                  THEN 'YES'
  9                  else 'NO'
 10              end field1*/
 11          from dual d
 12      )
 13      p
 14  left join
 15      (
 16          select 1 id, null || '.' fio from dual
 17      )
 18      Pr
 19  ON  Pr.ID = p.Person_ID;

        ID F                                                                    
---------- -                                                                    
                                                                                

11.2.0.2
SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production          
PL/SQL Release 11.2.0.2.0 - Production                                          
CORE	11.2.0.2.0	Production                                                      
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                         
NLSRTL Version 11.2.0.2.0 - Production                                          

SQL> select pr.*
  2  from
  3      (
  4          select 1 id
  5            , null person_id
  6            , case '2'
  7                  when '1'
  8                  THEN 'YES'
  9                  else 'NO'
 10              end field1
 11          from dual d
 12      )
 13      p
 14  left join
 15      (
 16          select 1 id, null || '.' fio from dual
 17      )
 18      Pr
 19  ON  Pr.ID = p.Person_ID;

        ID F                                                                    
---------- -                                                                    
         1 .                                                                    

SQL> select pr.*
  2  from
  3      (
  4          select 1 id
  5            , null person_id
  6            /*, case '2'
  7                  when '1'
  8                  THEN 'YES'
  9                  else 'NO'
 10              end field1*/
 11          from dual d
 12      )
 13      p
 14  left join
 15      (
 16          select 1 id, null || '.' fio from dual
 17      )
 18      Pr
 19  ON  Pr.ID = p.Person_ID;

        ID F                                                                    
---------- -                                                                    
                                                                                
medium
Дата: 11.03.2011 22:39:12
dbms_photoshop, спасибо. На десятке, не сомневаюсь, и Case даст тот же результат запроса. Одиннадцатой у Вас, как я понял, нету.
А с багом Oracle - действительно, впервые.

Asmodeus, большое спасибо.

Вот я и пытаюсь понять, "баг это, или фича": как оно будет в последующих версиях?
Прикольно, что замена Case на Decode в корне меняет результат.
dbms_photoshop
Дата: 12.03.2011 09:48:47
medium
На десятке, не сомневаюсь, и Case даст тот же результат запроса. Одиннадцатой у Вас, как я понял, нету.
А с багом Oracle - действительно, впервые.
Все у меня есть. :)
Только иногда надо большее внимание обращать не на номер версии, а на срок выхода патча.
То есть в 10.2.0.5, к примеру, пофиксены некоторые баги, имеющиеся в 11.1.0.6.
В твоем случае очевидно, что баг а не фича, и неприятно то, что в 11.2.0.1 - нет, а в 11.2.0.2 - есть.
А вообще сделай поиск по металинку по ключевым словам примерно "wrong results" и ужаснись.
И не забывай что баг может воспроизводится и нет на одной и той же версии в зависимости от настроек экземпляра.
medium
Дата: 12.03.2011 11:28:28
dbms_photoshop, спасибо.
Вот я и пытаюсь найти кого-нибудь, у кого 11.2.0.2, но не воспроизводится.
Тогда буду пытать "счастливчика": какие настройки.
Я "забыть" об этом я не могу, поскольку этого и не знал.
Обычный разработчик, работающий с Oracle пару лет, до этого плотно работавший с MS SQL.
И психологически не готов начать думать не в стандарте ANSI, поскольку на MS SQL остался огромный объём кода, требующий поддержки.
А что делают нормальные люди в моём случае:
1. Переписывают весь код, ставший неработоспособным после перехода на следующую версию?
2. Требуют от DBA "настроить" экземпляр?
3. Завязывают с ANSI?
С уважением....
GL
Дата: 12.03.2011 11:33:49
medium,

4. обращаются в техподдержку.
-2-
Дата: 12.03.2011 11:43:02
medium
А что делают нормальные люди в моём случае
Ни оракл ни mssql не следуют стандартам, сами же придумываемым. И ansi здесь в общем-то ни при чем. Для оракла "нормально" наличие определенного количества багов, что запросы могут падать или даже давать неверный результат. Поэтому серьезные системы сидят на определенной версии, где разработчики думают, что все баги уже знают и знают, как их обходить. Патчить и менять настройки БД означает поменять один набор багов на другой, пока неизведанный.

Если баг сводится к воспроизводиму тесткейзу - ему легкая дорога в саппорт.