dasini.net – Journal d'un expert MySQL

Le problème du jour, une table qui contient (entre autre) 2 colonnes de type entier non signé ( NombPart & NombInscr).

 

CREATE TABLE VoteInfo (

NombPart int unsigned DEFAULT NULL,

NombInscr int unsigned DEFAULT NULL,

…

) ENGINE=InnoDB

 

 

Une requête, qui fait la soustraction entre NombInscr et NombPart, pour remplir une autre table à la volée :

 

CREATE TABLE …

SELECT … NombInscr – NombPart as NombAbs …

FROM … ;

 

 

Et là, c’est le drame…

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ‘(NombInscr – NombPart)’

 

 

Après quelques tests, le problème est identifié, il vient de la soustraction : SELECT … NombInscr – NombPart .

Pour faire simple, on a donc un entier non signé qui est retranché d’un autre entier non signé et le tout ne rentre pas dans un entier long non signé… ça ressemble à un problème de signes 🙂

 

Un coup d’œil dans la table montre rapidement que les données ne sont pas cohérentes ie NombInscr < NombPart ce qui implique un résultat négatif qui ne peut être géré dans une colonne non signée ie le BIGINT UNSIGNED du message d’erreur.

En effet, les 2 colonnes étant de type entier non signé, le résultat de l’opération est donc géré avec le plus grand type d’entier (BIGINT) mais en mode non signé (sic) d’où mon problème.

Qu’à cela ne tienne, il suffit juste de s’assurer que le résultat est toujours positif et le problème est réglé (?).

Essayons la fonction ABS par exemple

 

SELECT … ABS(NombInscr – NombPart) as NombAbs …

FROM … ;

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ‘(NombInscr – NombPart)’

 

 

pas mieux 🙁

La fonction ABS, s’appliquant après la soustraction, le problème reste donc le même.

 

 

Quelles solutions ?

 

Dans mon contexte, plusieurs contournements du problème s’offrent à moi

 

• Corriger les problèmes d’incohérence dans la table (malheureusement pas si simple)

 

• Ne faire le calcul que lorsque NombInscr est supérieur à NombPart (et tant pis pour les données non cohérentes) :

SELECT … NombInscr – NombPart as NombAbs

FROM …

WHERE NombInscr >= NombPart ;

 

• Faire le calcul que lorsque NombInscr est supérieur à NombPart et afficher NULL (ou autre chose) sinon :

SELECT … IF(NombInscr >= NombPart, NombInscr – NombPart, NULL) as NombAbs

FROM … ;

 

• Faire le calcul « dans le bon sens » {update @Cyril}:

SELECT … IF(NombInscr >= NombPart, NombInscr – NombPart, – (NombPart – NombInscr)) as NombAbs

FROM … ;

 

 

Il est aussi possible de s’attaquer de manière frontale au problème, en jouant avec le transtypage :

 

• Avec la fonction CAST

SELECT … CAST(NombInscr AS signed) – CAST(NombPart AS signed) AS NombAbs

FROM … ;

 

• Avec du transtypage implicite

SELECT … (NombInscr+0.0 – NombPart+0.0) as NombAbs

FROM … ;

 

• Avec du transtypage explicite {update @armenweb}

SELECT … (CAST(NombInscr AS DECIMAL(20, 0)) – CAST(NombPart AS DECIMAL(20, 0))) as NombAbs

FROM … ;

 

Une autre manière de faire (plus classe?) est de gérer ce problème au niveau serveur, en initialisant la variable SQL_MODE (qui permet de modifier le comportement du serveur) avec la valeur  NO_UNSIGNED_SUBTRACTION

 

 

SET SESSION sql_mode = ‘NO_UNSIGNED_SUBTRACTION’;

SELECT … NombInscr – NombPart as NombAbs

FROM … ;

 

Dans ce mode, le résultat est géré avec un entier signé. Cependant, le souci potentiel est alors de dépasser l’intervalle de validité du BIGINT signé et d’avoir l’erreur suivante :

ERROR 1690 (22003): BIGINT value is out of range in ‘(NombInscr – NombPart)’

 

Pas de méthodes miracles donc…

Pour plus d’infos, RTFM 🙂

 

 

P.S. Dédicace à Clem, à qui je cède tout les droits relatifs à cet article 😉

 

Filed under:
Astuce by Olivier DASINI