CHAR et littéral : Oracle ajoute des blancs au littéral pour faire une comparaison (blank-padded semantics)

15 juillet 2017

CHAR et littéral : Oracle ajoute des blancs au littéral pour faire une comparaison (blank-padded semantics)

Introduction
En 1995 quand je travaillais sur Oracle 7 comme développeur, on m'avait mis en garde sur le fait suivant : ne mettre en CHAR que des colonnes pour lesquelles l'espace total est occupé par des caractères sinon l'espace en plus est complété avec des blancs par Oracle et cela pose des problèmes pour faire des recherches.

Ce que je viens de découvrir c'est que depuis la version 7, Oracle gère parfaitement bien ces espaces en plus dans le cas où on fait une recherche en comparant une colonne CHAR avec un littéral. Le développeur n'a même pas besoin d'utiliser la fonction TRIM ou d'ajouter lui même les espaces en plus.

Points d'attention
Aucun.

Base de tests
N'importe quelle base Oracle depuis la version 7.

Exemples
Ce qu'il faut comprendre est expliqué ici : https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF51037
"With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs... If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function."

Table de test
Nous créons une table avec deux colonnes de longueur 10 dont seul le type diffère et nous y insérons la même valeur.
           SQL> create table TEST_CHAR(ID01 CHAR(10), ID02 VARCHAR2(10));
           SQL> insert into TEST_CHAR VALUES ('A', 'A');
           SQL> commit;

Nous voyons bien que les deux colonnes n'ont pas la même longueur, ni en caractères ni en octets.
           SQL> select ID01, ID02, length(ID01), length(ID02), lengthb(ID01), lengthb(ID02) from test_char;
           ID01       ID02       LENGTH(ID01)      LENGTH(ID02)       LENGTHB(ID01)       LENGTHB(ID02)
           ---------- ----------- -------------------------- --------------------------- -----------------
           A              A           10 1                  10                      1

Néanmoins si je compare la valeur 'A' avec la valeur 'A         ' en ayant laissé dix blancs à la fin, Oracle fait une "conversion" automatique : il ajoute des blancs à la droite de la chaîne de caractères la plus petite pour atteindre la même longueur puis il fait son opération de comparaison. Là où ça devient un peu bizarre, c'est qu'il en ajoute aussi bien du côté du littéral que du côté de la colonne CHAR dans le cas où c'est le littéral qui a des blancs surnuméraires (à moins qu'il ne les enlève...).
           SQL> select * from test_char where ID01 = 'A'; -- longueur colonne CHAR > longueur littéral
           ID01       ID02
           ---------- ----------
           A              A

           SQL> select * from test_char where ID01 = 'A        '; -- longueur colonne CHAR = longueur littéral
           ID01       ID02
           ---------- ----------
           A              A

           SQL> select * from test_char where ID01 = 'A                   '; -- longueur colonne CHAR < longueur littéral
           ID01       ID02
           ---------- ----------
           A              A

Petite subtilité, Oracle ajoute des caractères blanc à droite mais ne les enlève pas à gauche.
           SQL> select * from test_char where id01 = '   A';
           no rows selected

Attention, cette "conversion" implicite d'Oracle ne fonctionne qu'avec des littéraux, pas quand la comparaison se fait avec le contenu d'une autre colonne. Dans ce cas là, il faut utiliser la fonction TRIM pour éliminer les blancs en trop.
           SQL> select * from test_char where ID01=ID02;
           no rows selected

           SQL> select * from test_char where trim(ID01)=ID02
           ID01       ID02
           ---------- ----------
           A              A

Je me suis demandé si Oracle réécrivait la requête mais, visiblement, même avec le hint NO_REWRITE, cette "conversion" fonctionne.
           SQL> select /*+ NO_REWRITE */ * from test_char where ID01 = 'A';
           ID01       ID02
           ---------- ----------
           A              A

Ensuite j'ai essayé de voir si le prédicat de filtre était réécrit mais ce n'est pas le cas.
           SQL> set autotrace on
           SQL> explain plan for select * from test_char where ID01 = 'A';
           Explained.

           SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
           PLAN_TABLE_OUTPUT
           --------------------------------------------------------------------------------
           Plan hash value: 718057891

           -------------------------------------------------------------------------------
           | Id | Operation      | Name      | Rows | Bytes | Cost (%CPU)| Time     |
           -------------------------------------------------------------------------------
           |   0 | SELECT STATEMENT |          |     1 |    19 |     3   (0)| 00:00:01 |
           |* 1 | TABLE ACCESS FULL| TEST_CHAR |     1 |    19 |     3   (0)| 00:00:01 |
           -------------------------------------------------------------------------------

           Predicate Information (identified by operation id):
           ---------------------------------------------------

           PLAN_TABLE_OUTPUT
           --------------------------------------------------------------------------------
              1 - filter("ID01"='A')


           SQL> explain plan for select * from test_char where ID01 = 'A         ';
           Explained.

           SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
           PLAN_TABLE_OUTPUT
           --------------------------------------------------------------------------------
           Plan hash value: 718057891

           -------------------------------------------------------------------------------
           | Id | Operation      | Name      | Rows | Bytes | Cost (%CPU)| Time     |
           -------------------------------------------------------------------------------
           |   0 | SELECT STATEMENT |          |     1 |    19 |     3   (0)| 00:00:01 |
           |* 1 | TABLE ACCESS FULL| TEST_CHAR |     1 |    19 |     3   (0)| 00:00:01 |
           -------------------------------------------------------------------------------

           Predicate Information (identified by operation id):
           ---------------------------------------------------
           PLAN_TABLE_OUTPUT
           --------------------------------------------------------------------------------
              1 - filter("ID01"='A    ')

Peut-être qu'avec la trace 10046 ou 10053 on aurait des informations supplémentaires... à voir :-)

Comme quoi on en découvre tous les jours sur Oracle, surtout sur les conversions automatiques que ce logiciel semble particulièrement affectionner.