none
Problème avec PATINDEX RRS feed

  • Question

  • Bonjour.

    Lors d'un développement utilisant Entity Framework sous VS2010 et SQL Server 2005, je dois effectuer un SELECT dans une base de données SQL avec un critère LIKE sur une colonne varchar(50). Je découvre que LIKE n'est pas supporté par EF, mais qu'il faut utiliser PATINDEX. Mais voila, mon programme ne fonctionne pas bien.

    En utilisant SQL Profiler, je constate que l'instruction SQL générée par EF est celle-ci :

    exec sp_executesql N'SELECT [Distinct1].[UasNumEmploye] AS [UasNumEmploye]
    FROM ( SELECT DISTINCT 
    	[Extent1].[UasNumEmploye] AS [UasNumEmploye]
    	FROM [dbo].[UserIdAS400] AS [Extent1]
    	WHERE ( CAST(PATINDEX(@p__linq__0, [Extent1].[UasNumUidAS400]) AS int)) > 0
    )  AS [Distinct1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'YI01424%2'

    laquelle ne renvoie aucune ligne. Ce que je confirme en l'exécutant directement sous SQL Server Management Studio.

    Pourtant, toujours sous Management Studio, l'instruction simple

    SELECT DISTINCT [UasNumEmploye] FROM dbo.UserIdAS400 
    WHERE PATINDEX('YI01424%2', UasNumUidAS400) > 0

    renvoie trois lignes, correspondant respectivement aux valeurs de la colonne UasNumUidAS400 YI014242,YI0142422 et YI0142482.

    En fait c'est la présence du N devant 'YI01424%2' qui fait semble-t-il la différence :

    SELECT DISTINCT *, [UasNumEmploye] FROM dbo.UserIdAS400 
    WHERE PATINDEX(N'YI01424%2', UasNumUidAS400) > 0

    ne renvoie aucune ligne.

    1. Pourquoi ?

    2. Comment modifier mon programme pour que EF génère une instruction qui trouve correctement les lignes recherchées ?

    Merci pour votre aide,

    Gilbert

    mercredi 3 octobre 2012 14:34

Réponses

  • Je me disais bien que cela ressemblait à un problème de PADDING mais je ne voyais pas vraiment pas pourquoi. En relisant la doc  http://msdn.microsoft.com/fr-fr/library/ms187403.aspx je tombe sur :
    nchar, nvarchar, ntext, text, image et sur les grandes valeurs.Le comportement par défaut est toujours celui de l'option SET ANSI_PADDING ON.Les espaces et les zéros à droite ne sont donc pas tronqués."
    d'où sans doute la différence.

    Je pense que le plus simple est de toujours garder PADDING à ON, de supprimer soi-même les espaces en fin de chaines si nécessaire et on devrait être dans la situation la plus naturelle (et celle recommandée par la doc).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    dimanche 7 octobre 2012 09:50

Toutes les réponses

  • Bonjour,

    Je n'arrive pas à reproduire ce problème. Cela donne quoi en testant sur une nouvelle table et/ou base ? Par exemple :

    create table t(col varchar(50))
    go
    insert into t values ('YI0142422')
    go
    exec sp_executesql N'SELECT [Distinct1].col
    FROM ( SELECT DISTINCT 
    	col
    	FROM [dbo].[t] AS [Extent1]
    	WHERE ( CAST(PATINDEX(@p__linq__0, [Extent1].[col]) AS int)) > 0
    )  AS [Distinct1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'YI01424%2'
    semble ok chez moi ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    mercredi 3 octobre 2012 16:26
  • Bonjour Patrice.

    J'ai suivi ton exemple pour approfondir le débogage. J'en suis arrivé au point où l'apparition d'une erreur (autre) dépend de la déclaration ou non d'une clé étrangère. Je reste perplexe, mais voici :

    USE [Informatique]
    GO
    IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_U_Personne]') AND parent_object_id = OBJECT_ID(N'[dbo].U'))
    ALTER TABLE [dbo].U DROP CONSTRAINT [FK_U_Personne]
    GO
    USE [Informatique]
    GO
    /****** Objet :  Table [dbo].U    Date de génération du script : 10/04/2012 11:57:28 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].U') AND type in (N'U'))
    DROP TABLE [dbo].U

    USE [Informatique]
    GO
    /****** Objet :  Table [dbo].U    Date de génération du script : 10/04/2012 11:58:03 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].U(
        [UasNumUidAS400] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
        [UasNumEmploye] [int] NOT NULL,
     CONSTRAINT [PK_U] PRIMARY KEY CLUSTERED
    (
        [UasNumUidAS400] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 20) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /*
    ALTER TABLE [dbo].U  WITH CHECK ADD  CONSTRAINT [FK_U_Personne] FOREIGN KEY([UasNumEmploye])
    REFERENCES [dbo].[Personne] ([NumPersonne])
    GO
    ALTER TABLE [dbo].U CHECK CONSTRAINT [FK_U_Personne]
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Intégrité référentielle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'U', @level2type=N'CONSTRAINT',@level2name=N'FK_U_Personne'
    GO
    */
    INSERT INTO U VALUES ('YI014242', 1188)
    INSERT INTO U VALUES ('YI0142422', 1188)
    GO
    SELECT DISTINCT UasNumUidAS400, UasNumEmploye, Nom FROM dbo.U
    JOIN [dbo].[Personne] ON U.UasNumEmploye = [dbo].[Personne].NumPersonne
    WHERE PATINDEX(N'YI01424%2', UasNumUidAS400) > 0
    GO

    ALTER TABLE [dbo].U  WITH CHECK ADD  CONSTRAINT [FK_U_Personne] FOREIGN KEY([UasNumEmploye])
    REFERENCES [dbo].[Personne] ([NumPersonne])
    GO
    ALTER TABLE [dbo].U CHECK CONSTRAINT [FK_U_Personne]
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Intégrité référentielle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'U', @level2type=N'CONSTRAINT',@level2name=N'FK_U_Personne'
    GO

    INSERT INTO U VALUES ('YI0142423', 1188)
    INSERT INTO U VALUES ('YI0142482', 1188)

    SELECT DISTINCT * FROM dbo.U
    WHERE PATINDEX(N'YI01424%2', UasNumUidAS400) > 0
    GO

    Si j'exécute les ALTER TABLE qui déclarent et vérifient la clé étrangère avant les INSERT, ceux-ci ne fonctionnent plus (Msg 8624, Niveau 16, État 1, Ligne 2 - Erreur interne du processeur de requêtes : le processeur de requêtes ne peut pas créer de plan de requête. Pour plus d'informations, contactez le support technique.).

    Si j'exécute les INSERT avant les ALTER TABLE, tout fonctionne bien. J'ai ajouté un JOIN au SELECT pour bien vérifier que la clé étrangère existait dans la table jointe, pas de problème.

    Je n'ai pas avancé sur le problème initial ; enlever la clé étrangère n'y a rien fait.

    Gilbert

    jeudi 4 octobre 2012 15:01
  • Essaye peut-être de partir simple et de complexifier peu à peu. Là je crois qu'il y a trop de choses qui changent à la fois. Par exemple si je pars de :

    DROP TABLE [dbo].U
    go
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].U(
    [UasNumUidAS400] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [UasNumEmploye] [int] NOT NULL,
    CONSTRAINT [PK_U] PRIMARY KEY CLUSTERED (    [UasNumUidAS400] ASC)
    WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 20) ON [PRIMARY])
    ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT INTO U VALUES ('YI014242', 1188)
    INSERT INTO U VALUES ('YI0142422', 1188)
    GO
    SELECT DISTINCT UasNumUidAS400
    FROM dbo.U
    --JOIN [dbo].[Personne] ON U.UasNumEmploye = [dbo].[Personne].NumPersonne
    WHERE PATINDEX(N'YI01424%2', UasNumUidAS400) > 0
    GO

    dans une nouvelle base c'est ok chez moi. Et donc comme je disais ce genre de script simple est-il ok chez toi dans une nouvelle base ?

    Si non, un problème de version de SQL Server ? (ou un autre réglage serveur même si je ne vois vraiment pas ?)

    Si oui, essaye de recréer peu à peu le même contexte sans trop changer de choses à la fois pour essayer de cerner à quel moment cela commence à coincer. Si cela marche toujours dans une nouvelle base, se pourrait-il que la base existante ait carrément "pris un pain" ?

    Bon courage.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    jeudi 4 octobre 2012 16:57
  • J'ai progressé. Le problème vient de la présence d'espaces en fin de chaîne. J'avais envisagé cette hypothèse, mais je l'avais écartée après avoir affiché la longueur du champ avec la fonction LEN. J'ignorais que la fonction LEN ignorait les espaces de droite en fin de chaîne.

    L'exemple suivant

    DROP TABLE [dbo].U
    go
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].U(
    [UasNumUidAS400] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [UasNumEmploye] [int] NOT NULL,
    CONSTRAINT [PK_U] PRIMARY KEY CLUSTERED (    [UasNumUidAS400] ASC)
    WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 20) ON [PRIMARY])
    ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT INTO U VALUES ('YI014242', 1188)
    INSERT INTO U VALUES ('YI0142422', 1188)
    INSERT INTO U VALUES ('YI094242  ', 1190)
    INSERT INTO U VALUES ('YI0942422  ', 1190)
    GO
    SELECT *, '1 sans N' AS Texte FROM dbo.U
    	WHERE PATINDEX('YI%2', UasNumUidAS400) > 0
    union
    SELECT *, '2 avec N sans RTRIM' AS Texte FROM dbo.U
    	WHERE PATINDEX(N'YI%2', UasNumUidAS400) > 0
    union
    SELECT *, '3 avec N avec RTRIM' AS Texte FROM dbo.U
    	WHERE PATINDEX(N'YI%2', RTRIM(UasNumUidAS400)) > 0
    	ORDER BY Texte, [UasNumUidAS400]
    GO
    

    renvoie 10 lignes (4  + 2 + 4) et met donc en évidence que PATINDEX considère ou non les espaces en fin de chaîne selon que le pattern est en unicode ou non.

    C'est documenté quelque part ?

    Gilbert

    vendredi 5 octobre 2012 09:43
  • Je me disais bien que cela ressemblait à un problème de PADDING mais je ne voyais pas vraiment pas pourquoi. En relisant la doc  http://msdn.microsoft.com/fr-fr/library/ms187403.aspx je tombe sur :
    nchar, nvarchar, ntext, text, image et sur les grandes valeurs.Le comportement par défaut est toujours celui de l'option SET ANSI_PADDING ON.Les espaces et les zéros à droite ne sont donc pas tronqués."
    d'où sans doute la différence.

    Je pense que le plus simple est de toujours garder PADDING à ON, de supprimer soi-même les espaces en fin de chaines si nécessaire et on devrait être dans la situation la plus naturelle (et celle recommandée par la doc).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    dimanche 7 octobre 2012 09:50
  • Merci Patrice.

    Bonne journée,

    Gilbert

    lundi 8 octobre 2012 07:36