Discussion :

[bob1979]

Bonjour,

J'ai obtenu la requete ci dessous apres réduction d'une requete qui me renvoyait des trucs bizarres, de sorte que je reproduis mon probleme uniquement avec dual. C'est pas tres joli, je vous l'accorde.
Mais soit je fais des trucs interdits, soit oracle se mélange compplètement les pinceaux.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT   z_date,
         to_char(z_date,'d') test1,
         --(select to_char(z_date,'d') from dual) test2,
         CASE WHEN to_char(z_date,'d') in (SELECT '7' from dual d3) THEN 2 ELSE 0 END test3
FROM
  (    SELECT to_date('12/08/2013','DD/MM/YYYY')                                                                                       
              + CASE WHEN (SELECT 1 FROM dual d2 where d1.dummy = d1.dummy) = 0                                                                               
                     THEN 0                                  
                     ELSE 1 END z_date
        FROM dual d1
  );

Résultat :
13/08/13 2 2

Donc faux pour test3
Je vous laisse jouer avec, mais avec moi, il se passe des trucs surnalurels avec cette requête, est-ce que vous aussi?
Juste pour le plaisir, voici quelques trucs qui m'arrivent avec :
-- si je décommente test2, test3 devient bon ( = 0) !!
-- si je change THEN 0 en THEN 1 (alors que ce n'est pas la valeur qu'il prend), test3 devient bon aussi (sans décommenter test2)

Et bien d'autres, bref, je suis fasciné, mais je ne sais pas dire ce qui se passe.

Merci si quelqu'un peut me remettre dans le droit chemin.

Bob

[pachot]

Bonjour,

La première chose que je peux dire, c'est qu'il y a un bug Oracle lié à une transformation (le CBO transforme les requêtes pour optimiser l'accès) qui s'appelle 'View Merging' car si je désactive cette transformation avec un hint, le résultat est bon:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> SELECT z_date,
  2           to_char(z_date,'d') test1,
  3           --(select to_char(z_date,'d') from dual) test2,
  4           CASE WHEN to_char(z_date,'d') IN (SELECT '7' FROM dual d3) THEN 2 ELSE 0 END test3
  5  FROM
  6    (    SELECT /*+ no_merge */ to_date('12/08/2013','DD/MM/YYYY')
  7                + CASE WHEN (SELECT 1 FROM dual d2 WHERE d1.dummy = d1.dummy) = 0
  8                       THEN 0
  9                       ELSE 1 END z_date
 10          FROM dual d1
 11    );
 
Z_DATE    T      TEST3
--------- - ----------
13-AUG-13 3          0

Evidemment les transformations ne sont pas censées changer le résultat...

Cordialement,
Franck.

[pachot]

En regardant vite fait la trace de l'optimiseur, la première transformation (Filter Push Down) semble correcte:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT TO_DATE(' 2013-08-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')+
  CASE
    WHEN ( (SELECT 1 "1"
      FROM "SYS"."DUAL" "D2"
      WHERE "D1"."DUMMY"="D1"."DUMMY")=0)
    THEN 0
    ELSE 1
  END "Z_DATE",
  TO_CHAR(TO_DATE(' 2013-08-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')+
  CASE
    WHEN ( (SELECT 1 "1" FROM "SYS"."DUAL" "D2" WHERE "D1"."DUMMY"="D1"."DUMMY")=0)
    THEN 0
    ELSE 1
  END ,'d') "TEST1",
  CASE
    WHEN EXISTS
      (SELECT 0
      FROM "SYS"."DUAL" "D3"
      WHERE TO_CHAR(TO_DATE(' 2013-08-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')+
        CASE
          WHEN ( (SELECT 1 "1" FROM "SYS"."DUAL" "D2" WHERE "D1"."DUMMY"="D1"."DUMMY")=0)
          THEN 0
          ELSE 1
        END ,'d')='7'
      )
    THEN 2
    ELSE 0
  END "TEST3"
FROM "SYS"."DUAL" "D1" 
/

Mais ensuite il perd quelques predicats dans le View Merging

Il faut voir avec le support si c'est un bug connu. Mais probablement pas fixé car j'ai le même comportement en 12c


a noter, le to_char(z_date,'d') dépends du NLS_TERRITORY. Certains pays commencent la semaine le lundi, d'autres le dimanche.

[bob1979]

Merci pachot pour l'intérêt porté à ma question.

Effectivement suite aux tests que j'ai réalisés, l'utilisation du hint dans ma requete initiale résoud le problème, du moins sur mon jeu de tests. Merci donc pour cette solution. Pour ma part je ne la maitrise pas il faut l'avouer, je n'ai pas le bonheur de bien connaitre ces mécanismes. La route est longue, petit scarabée !! Et sur mon projet nous nous limitons a des hint sur les index dans le cadre d'optimisations.

Nous avons rencontré ce probleme dans le cadre d'évolution de requetes, et évidemment contourné le problème rencontré en utilisant une syntaxe plus propre. Mais cette erreur me titillait, je voulais l'isoler, et avoir un avis pour savoir si je divaguais ou si j'étais bien devant un bug.

Par contre je n'ai aucune idée de comment formaliser une fiche d'anomalie chez Oracle ...

Encore merci

Bob

[pachot]

Par contre je n'ai aucune idée de comment formaliser une fiche d'anomalie chez Oracle ...

En ouvrant un Service Request auprès du support. Vu que tu as fait un testcase entièrement reproductible, le fait que le résultat soit différent avec et sans hint devrait suffire à leur faire ouvrir un bug.

[bob1979]

OK je vais voir pour le truc que j'ai jamais fait de ma vie : ouvrir un bug chez Oracle.
Sauf s'il y en a dont c'est le sport favori, dans ce cas je fais cadeau du cas test que j'ai encore simplifié.
Bob

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT CASE WHEN znumber IN (SELECT 2 FROM dual d3) THEN 2 ELSE 0 END test3
FROM
( SELECT CASE WHEN (SELECT 1 FROM dual d2 WHERE d1.dummy = d1.dummy) = 0 THEN 0 ELSE 1 END znumber
  FROM dual d1
);

[bob1979]

hello

https://forums.oracle.com/thread/2569585

Bob