Discussion :

[boyere]

Bonjour

j'ai une requete a faire sur un type real et j'ai des résultats surprenants à mon gout mais je suis sur que vous avez une explication a mon péhnomène paranormal :

Ma table note contient un type real :
\d infos_note;
Table « public.infos_note »
Colonne | Type | Modificateurs
----------------+---------+--------------------------------------------------------------------
id_infos_note | integer |
note | real |

Mes requetes :
***** un chiffre non décimal => OK

Code :

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

SELECT * from infos_note where note=5;

id_infos_note | note
1467 | 5 |

***** un chiffre décimal => KO

Code :

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

SELECT * from infos_note where note=5.29;

(0 lignes)
ou alors

Code :

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

SELECT * from infos_note where note=5.1;

(0 lignes)

***** un chiffre décimal entouré de quote => OK

Code :

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

SELECT * from infos_note where note='5.29';

id_infos_note | note
1563 | 5.29
ou alors

Code :

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

SELECT * from infos_note where note='5.1';

id_infos_note | note
1506 | 5.1

***** un chiffre décimal avec .5 entouré de quote ou non => OK

Code :

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

SELECT * from infos_note where note=5.5;

id_infos_note | note
1470 | 5.5

Code :

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

SELECT * from infos_note where note='5.5';

id_infos_note | note
1470 | 5.5



Alors questions :
pourquoi faut il des quotes sur des chiffres à virgules et pourquoi ca fonctionne sans quand c'est .5 ?

Merci de votre aide

[CinePhil]

Dans la doc, je lis ceci sur le type real (et notamment la phrase que j'ai mise en rouge) :

8.1.3. Types à virgule flottante

Les types de données real et double precision sont des types numériques à précision variable inexacts. En pratique, ils sont généralement conformes à la norme IEEE 754 pour l'arithmétique binaire à virgule flottante (respectivement simple et double précision), dans la mesure où les processeurs, le système d'exploitation et le compilateur les supportent.
Inexact signifie que certaines valeurs ne peuvent être converties exactement dans le format interne, et sont stockées sous forme d'approximations, si bien que stocker puis réafficher ces valeurs peut faire apparaître de légers écarts. Prendre en compte ces erreurs et la façon dont elles se propagent au cours des calculs est le sujet d'une branche entière des mathématiques et de l'informatique. Nous n'en dirons pas plus que ce qui suit:

• Si vous avez besoin d'un stockage et de calculs exacts, comme pour les valeurs monétaires, utilisez plutôt le type numeric.
• Si vous voulez faire des calculs compliqués avec ces types pour quoi que ce soit d'important, et particulièrement si vous comptez sur certains comportements aux limites (infinis, zéro), alors vous devriez étudier le comportement de votre plate-forme avec soin.
• Tester l'égalité de deux valeurs à virgule flottante peux ne pas donner le résultat attendu.

Sur la plupart des plates-formes, le type real a une étendue d'au moins 1E-37 à 1E37 avec une précision d'au moins 6 chiffres décimaux. Le type double precision a généralement une étendue de 1E-307 à 1E+308 avec une précision d'au moins 15 chiffres. Les valeurs trop grandes ou trop petites produisent une erreur. Un arrondi peut avoir lieu si la précision d'un nombre en entrée est trop grande. Les nombres trop proches de zéro qui ne peuvent être représentés autrement que par zéro produisent une erreur (underflow).

C'est un peu court comme explication au phénomène bizarre mais c'est peut-être ça non ?

Puisqu'il s'agit de notes, ne vaudrait-il pas mieux utiliser un type exact comme 'numeric' ?

[boyere]

effectivement peut etre qu'avec numeric ca irait mieux ou pas ...

Qu'appelles tu un type "exact" ?

[CinePhil]

Qu'appelles tu un type "exact" ?

Comme dit dans la doc, real est un nombre codé en un certain nombre d'octets et les effets de bord font que le nombre peut être arrondi pour entrer dans les bornes donc ne pas être exact.

Numeric est un type "exact", dans le sens où c'est le concepteur qui définit le degré de précision (nombres de chiffres après la virgule) :

8.1.2. Nombres à précision arbitraire

Le type numeric peut stocker des nombres avec jusqu'à 1000 chiffres significatifs et effectuer des calculs exacts. Il est spécialement recommandé pour stocker les montants financiers et autres quantités pour lesquelles l'exactitude est indispensable. Néanmoins, l'arithmétique sur les valeurs numeric est très lent comparé aux types entiers ou aux types à virgule flottante décrits dans la section suivante.
Dans ce qui suit, on utilise les termes suivants : l'échelle d'un numeric est le nombre de chiffres décimaux de la partie fractionnaire. La précision d'un numeric est le nombre total de chiffres significatifs dans le nombre entier, c'est-à-dire avant et après la virgule. Donc, le nombre 23,5141 a une précision de 6 et une échelle de 4. On peut considérer que les entiers ont une échelle de 0.
La précision maximum et l'échelle maximum d'une colonne numeric peuvent être réglés. Pour déclarer une colonne de type numérique, il faut utiliser la syntaxe :
NUMERIC(précision, échelle)
La précision pour être strictement positive, l'échelle positive ou NULL. Alternativement,
NUMERIC(précision)
indique une échelle de 0. Préciser
NUMERIC
sans précision ni échelle crée une colonne dans laquelle on peut stocker des valeurs de n'importe quelle précision ou échelle, jusqu'à la limite de précision. Une colonne de ce type ne forcera aucune valeur entrée à une précision particulière, alors que les colonnes numeric avec une échelle forcent les valeurs entrées à cette échelle. (Le standard SQL demande une précision par défaut de 0, c'est-à-dire de forcer la transformation en entiers. Nous trouvons ça inutile. Si vous êtes soucieux de portabilité, précisez toujours la précision et l'échelle explicitement.)
Si l'échelle d'une valeur est supérieure à l'échelle d'une colonne, le système tentera d'arrondir la valeur au nombre de chiffres après la virgule spécifiée. Ensuite, si le nombre de chiffres à gauche du point décimal dépasse la précision déclarée moins l'échelle déclarée, une erreur est levée.
Les valeurs numériques sont stockées physiquement sans zéro avant ou après. Du coup, la précision déclarée et l'échelle de la colonne sont des valeurs maximums, pas des allocations fixes (en ce sens, le type numérique est plus proche de varchar(n) que de char(n)). Le besoin pour le stockage actuel est de deux octets pour chaque groupe de quatre chiffres décimaux, plus huit octets d'en-tête.
En plus des valeurs numériques ordinaires, le type numeric autorise la valeur spéciale NaN, signifiant « not-a-number » (NdT : pas un nombre). Toute opération sur NaN renvoie un autre NaN. En écrivant cette valeur comme une constante dans une requête SQL, vous devez placer des guillemets autour. Par exemple, UPDATE table SET x = 'NaN'. En saisie, la chaîne NaN est reconnue quelque soit la casse utilisée.
Les types decimal et numeric sont équivalents. Les deux types sont dans le standard SQL.

[estofilo]

pourquoi faut il des quotes sur des chiffres à virgules et pourquoi ca fonctionne sans quand c'est .5 ?

C'est la coïncidence de deux choses qui expliquent ces résultats.

D'abord, le stockage au format flottant utilisé par le type "real" induit une perte de précision qui peut intervenir dès le 1er chiffre après la virgule parce que ces nombres sont stockés en binaire et non pas en base 10.
Pour éviter ça il faut utiliser le type numeric.

Ensuite pourquoi WHERE note=5.1 ne trouve pas la ligne, alors que note='5.1' la retrouve bien? C'est à cause des conversions implicites de type.

Quand on écrit 5.1 en SQL, l'interpréteur considère que c'est une constante du type numeric. Ensuite pour calculer note=5.1 il convertit note en numeric parce que c'est le type le plus fort des 2 et récupère à cause de la perte de précision une valeur du genre 5.09999... qui fait que l'égalité est fausse.

Quand on écrit '5.1', en revanche il considère que c'est du type de chaîne de caractères. Plus loin dans le traitement pour faire note='5.1' il doit comparer un réel et une chaine, alors il convertit la chaine en réel. Ca donne un résultat de 5.1 avec perte de précision (donc en fait 5.0999...) mais comme la perte de précision est exactement la même que celle de la colonne, et bien les deux termes sont bien égaux.

On peut éviter ça en typant explicitement les constantes. En faisant

Code :

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

select * from .. WHERE note=5.1::real

normalement le résultat est celui attendu.

[SQLpro]

Normalement ceci ne devrait pas se produire, si le logiciel qui permet de lancer la requête et le SGBDR étaient sur la même longueur d'onde, à savoir la norme (32-bit) IEEE 754. Malheureusement certains OS et certains logiciels ne s'y conforment pas !!!!!

Or il est difficile pour un SGBDR multi OS d'être en conformité avec des versions discordantes. Ceci n'est pas le cas d'ailleurs de SQL Server qui possède l'avantage du mono plateforme !

A +