Votre base de données est-elle intelligente ?

Parmi les nombreuses contribution de Chris Date sur le sujet des bases de données relationnelles en général et de SQL en particulier, il en est une qui permet de savoir quel niveau d’intelligence possède le moteur sous-jacent à votre SGBDR…

En effet, considérons table suivante :


CREATE TABLE T_DATA
(DATA_VALEUR   VARCHAR(256),
 DATA_TYPE     VARCHAR(8))

Avec les données suivantes :


INSERT INTO T_DATA VALUES ('abcdef', 'alpha')
INSERT INTO T_DATA VALUES ('123456', 'nombre')
INSERT INTO T_DATA VALUES ('21"''t', 'alpha')
INSERT INTO T_DATA VALUES ('987456', 'nombre')

Maintenant voici le test d’intelligence à faire subir à votre SGBDR favori :


SELECT *
FROM   T_DATA
WHERE  DATA_TYPE = 'nombre'
  AND  CAST(DATA_VALEUR AS INTEGER) > 500000

Pire, voici quelques autres formulations qui sont plus difficiles à contrôler :


SELECT *
FROM   (SELECT *
        FROM   T_DATA
        WHERE  CAST(DATA_VALEUR AS INTEGER) > 500000) AS T
WHERE  DATA_TYPE = 'nombre'

ou bien :


SELECT *
FROM   (SELECT *
        FROM   T_DATA
        WHERE  DATA_VALEUR > 500000) AS T
WHERE  DATA_TYPE = 'nombre'

L’ayant testé sur MS SQL Server 2005, j’ai été assez agréablement surpris de constater que dans tous les cas la bonne réponse apparaît :


DATA_VALEUR      DATA_TYPE
---------------- ---------
987456           nombre

A vous de voir si votre SGBDR est assez intelligent pour réussir ce tour de force.
Bien entendu SQL étant un langage basé sur une logique de prédicat, on n’impose pas au SGBDR la manière de faire. Le serveur à donc toute latitude pour trouver la solution à ce problème en étudiant différentes stratégies logiques et doit en principe trouver la solution quelle que soit la manière d’exprimer cette requête. Le contraire reviendrait à dire que la façon d’écrire impose au serveur la manière de traiter le problème ce qui enlèverait tout intérêt au langage SQL !


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

6 réflexions au sujet de « Votre base de données est-elle intelligente ? »

  1. Avatar de alassanediakitealassanediakite

    salut
    avec ACCESS 2007
    [code]SELECT *
    FROM T_DATA
    WHERE (((T_DATA.[DATA_TYPE])=’nombre’) AND ((CLng([DATA_VALEUR]))>500000));[/code]
    et
    [code]SELECT *
    FROM (SELECT *
    FROM T_DATA
    WHERE CLng(DATA_VALEUR) > 500000) AS T
    WHERE DATA_TYPE = ‘nombre'[/code]
    donnent
    ______________________________
    data_valeur| data_type
    987456 | nombre
    ______________________________
    La troisième requête
    [code]SELECT *
    FROM (SELECT *
    FROM T_DATA
    WHERE DATA_VALEUR > 500000) AS T
    WHERE DATA_TYPE = ‘nombre'[/code]
    renvoie une erreur: »type de donnée incompatible dans l’expression de critère »

  2. Avatar de cinephilcinephil

    Avec MySQL, dans les tests, il faut remplacer INTEGER par SIGNED ou UNSIGNED car MySQL ne sait pas caster en entier sans préciser si c’est un entier signé ou non signé !
    Test 1 :
    SELECT *
    FROM T_DATA
    WHERE DATA_TYPE = ‘nombre’
    AND CAST(DATA_VALEUR AS SIGNED) > 500000

    => 987456 ‘nombre’

    Avec UNSIGNED : idem.

    Test 2 : OK avec SIGNED et UNSIGNED

    Test 3 : OK

  3. Avatar de sqlprosqlpro Auteur de l’article

    À NOTER :

    l’exécution sur ORACLE 10 G de la requête n°2 échoue avec le message :

    ORA-00933: la commande SQL ne se termine pas correctement.

    Sur PostGreSQL 8.4, c’est la requête n°3 qui échoue avec le message :

    
    
    ERREUR:  l'opérateur n'existe pas : character varying &gt; integer&nbsp;<br />
    LINE 4:         WHERE  DATA_VALEUR &gt; 500000) AS T &nbsp;<br />
    &nbsp;                                  ^&nbsp;<br />
    HINT:  Aucun opérateur ne correspond au nom donné et aux types d'arguments.&nbsp;<br />
    Vous devez ajouter des conversions explicites de type.&nbsp;<br />
    ********** Erreur **********&nbsp;<br />
    ERREUR: l'opérateur n'existe pas : character varying &gt; integer&nbsp;<br />
    État SQL :42883&nbsp;<br />
    Astuce : Aucun opérateur ne correspond au nom donné et aux types d'arguments.&nbsp;<br />
    Vous devez ajouter des conversions explicites de type.&nbsp;<br />
    Caractère : 79
  4. Avatar de sqlprosqlpro Auteur de l’article

    Ce n’est même pas le problème du CAST. C’est plus généralement le fait que SQL est en quelque sortes un méta langage. Habituellement un langage donne des ordres qu’une machine doit exécuter à la lettre. On oublie trop souvent qu’un bon moteur SQL est libre de faire comme il le souhaite et donc il est normal qu’il n’exécute pas exactement de la façon dont on lui demande, mais qu’il ait l’intelligence de transformer la demande pour la traiter plus aisément… C’est cela l’intelligence de SQL… mais tous les SGBD soit-disant relationnels ne sont pas intelligent !

  5. Avatar de ImmobilisImmobilis

    Salut,

    Tu ne conclue pas en disant si tu trouve que c’est intelligent ou pas. Quel est ton avis?

    Personnellement, je ne trouve pas que ce soit une preuve d' »intelligence » de faire un CAST implicite comme dans le troisième exemple.

    SELECT *
    FROM (SELECT *
    FROM T_DATA
    WHERE DATA_VALEUR > 500000) AS T
    WHERE DATA_TYPE = ‘nombre’

    Il me semble que tous les langages modernes prévoient un typage explicite.

    A+

Laisser un commentaire