Issue with Postgres and Contains()

I'm using the following LINQ to query a table that has a comma separated list of tags:

var source = Pages.Where(p => p.Tags.ToLower().Contains(tag.ToLower()));

And the SQL that's produced for Postgres looks like it's got one too many '\' in it:

 SELECT

  roadkill_pages.Id AS "roadkill_pages.Id",

  roadkill_pages.CreatedBy AS "roadkill_pages.CreatedBy",

  roadkill_pages.CreatedOn AS "roadkill_pages.CreatedOn",

  roadkill_pages.IsLocked AS "roadkill_pages.IsLocked",

  roadkill_pages.ModifiedBy AS "roadkill_pages.ModifiedBy",

  roadkill_pages.ModifiedOn AS "roadkill_pages.ModifiedOn",

  roadkill_pages.Tags AS "roadkill_pages.Tags",

  roadkill_pages.Title AS "roadkill_pages.Title"

FROM

  roadkill_pages

WHERE

  LOWER(roadkill_pages.Tags) LIKE '%homepage%' ESCAPE '\\'

I'm not sure what ESCAPE does but when I run it through a Postgres db manager, it says it should only have 1 character in it.

Are you actually seeing a SQL exception raised when running this? If so can you provide some detail on this and the version of Postgres you are using. The ESCAPE clause is used to override the default escape sequence in conjunction with the LIKE operator.

Jeremy

I'm using Postgres 9.2 with a UTF8 database and the schema is:

CREATE TABLE roadkill_pages
(
  "id" SERIAL NOT NULL, 
  "title" TEXT NOT NULL, 
  "tags" TEXT NOT NULL, 
  "createdby" TEXT NOT NULL, 
  "createdon" TIMESTAMP(20) WITHOUT TIME ZONE NOT NULL, 
  "islocked" BOOLEAN NOT NULL, 
  "modifiedby" TEXT, 
  "modifiedon" TIMESTAMP(20) WITHOUT TIME ZONE, 
  PRIMARY KEY("id")
);

CREATE TABLE roadkill_pagecontent
(
  "id" UUID NOT NULL, 
  "editedby" TEXT NOT NULL, 
  "editedon" TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
  "versionnumber" INTEGER NOT NULL, 
  "text" TEXT, 
  "pageid" INTEGER NOT NULL, 
  PRIMARY KEY("id")
);

The tag column contains a comma separated list of tags ("tag1,tag2,tag3"). My current work around is this, which solves the issue:

    public IEnumerable<Page> FindPagesContainingTag(string tag)
    {
        IEnumerable<PageEntity> entities = new List<PageEntity>();

        if (_configuration.ApplicationSettings.DataStoreType != DataStoreType.Postgres)
        {
            entities = Pages.Where(p => p.Tags.ToLower().Contains(tag.ToLower()));
        }
        else
        {
            // Temporary Lightspeed Postgres LIKE bug work around
            IDbCommand command = UnitOfWork.Context.DataProviderObjectFactory.CreateCommand();
            command.CommandText = "SELECT * FROM roadkill_pages WHERE tags LIKE @Tag"; // case sensitive column name
            IDbDataParameter parameter = command.CreateParameter();
            parameter.DbType = DbType.String;
            parameter.ParameterName = "@Tag";
            parameter.Value = "%" +tag+ "%";
            command.Parameters.Add(parameter);

            entities = UnitOfWork.FindBySql<PageEntity>(command);
        }

        return FromEntity.ToPageList(entities);
    }

The project is here: https://bitbucket.org/mrshrinkray/roadkill/src - I can create a unit test if you prefer.

Thanks for this - however I am still unable to reproduce this here Im afraid. Ive set up a test case here with the schema above and the LINQ query from your code above but this runs correctly.

It would be helpful if you could send through a small repro which I could use against the schema above to compare with my current test. Also I am wondering if there is something in your server or connection settings which is impacting this. Lastly could you send through some details about the exception you are seeing come back?

Thanks!
Jeremy

I finally got round to creating a test case for this, which I've attached. I'm using Postgres on Windows, version 9 and default encoding.

Thanks for sending through the repro project, just a quick update on this to say I have added in a fix for the issue but this is just pending adding a new test environment to our build process so we can run tests to support this change. This should be all sorted early next week and Ill update you once we have this pushed up to the nightlies.

Thanks!
Jeremy

This fix will be available in the next nightly build.

Jeremy

Thanks for that, it's now working in the original app.

Hi,

I am having the same issue.

I have downloaded the June5-Nightly build for the Lighspeed5-Professional, and installed it and recompiled my code, but i have getting the same "[Err] ERROR: invalid escape string"

The generated SQL still adds 2 backslashes in the escape clause: (please see sql below...the ESCAPE '\' has 2 backslashses.

=====================================================================================

[SQL] SELECT campaignrunlog.campaignrunlogid AS "campaignrunlog.campaignrunlogid", campaignrunlog.runstatus AS "campaignrunlog.runstatus", campaignrunlog.runstatustext AS "campaignrunlog.runstatustext" FROM public.campaignrunlog WHERE (campaignrunlog.campaign_id = '203a4ee7-6659-4b3a-bb03-6055d8b95e82' AND (NOT ((campaignrunlog.runstatustext IS NULL OR campaignrunlog.runstatustext = '')) AND LOWER(campaignrunlog.runstatustext) LIKE '%er%' ESCAPE '\')) ORDER BY campaignrunlog.rundatetime

[Err] ERROR: invalid escape string HINT: Escape string must be empty or one character.

========================================================================================

is this fixed in the current Lighstpeed5-Professional Nightly build???

Please help. Thanks, alex.

BTW, the post here replace the double backslasshes with a single..when i cut/copy the sql into this POST it escapes the string... so u dont see the original sql

[SQL] SELECT campaignrunlog.campaignrunlogid AS "campaignrunlog.campaignrunlogid", campaignrunlog.campaignid AS "campaignrunlog.campaignid",

campaignrunlog.runstatus AS "campaignrunlog.runstatus", campaignrunlog.runstatustext AS "campaignrunlog.runstatustext" FROM public.campaignrunlog WHERE (campaignrunlog.campaign_id = '203a4ee7-6659-4b3a-bb03-6055d8b95e82' AND (NOT ((campaignrunlog.runstatustext IS NULL OR campaignrunlog.runstatustext = '')) AND LOWER(campaignrunlog.runstatustext) LIKE '%er%' ESCAPE '\\')) ORDER BY campaignrunlog.rundatetime

[Err] ERROR: invalid escape string HINT: Escape string must be empty or one character.

Hi Jeremy,

I found the issue with postgres 9.1 and higher. Please read these links here: http://openjpa.208410.n2.nabble.com/Postgres-V9-1-issue-with-LIKE-clause-and-Escape-Strings-td6848069.html http://momjian.us/main/blogs/pgblog/2010.html#August92010

 Basically for any escape string you need to prefix it with a constant
 "E".  "...These changes were made to encourage users to use the new
 escape string designation (E'') for all strings needing backslash
 escape processing.
     HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.""

so for the current generated sql query:

SELECT
  campaignrunlog.campaignrunlog_id AS "campaignrunlog.campaignrunlog_id",
  campaignrunlog.campaign_id AS "campaignrunlog.campaign_id",
    campaignrunlog.runstatustext AS "campaignrunlog.runstatustext"
FROM
  public.campaignrunlog
WHERE
  (campaignrunlog.campaign_id = '203a4ee7-6659-4b3a-bb03-6055d8b95e82' AND (NOT ((campaignrunlog.runstatustext IS NULL OR campaignrunlog.runstatustext = '')) 
   AND LOWER(campaignrunlog.runstatustext) LIKE '%er%' ESCAPE '\\'))
ORDER BY
  campaignrunlog.rundatetime

...you need to prefix the ESCAPE clause with 'E':

SELECT campaignrunlog.campaignrunlogid AS "campaignrunlog.campaignrunlogid", campaignrunlog.campaignid AS "campaignrunlog.campaignid", campaignrunlog.runstatustext AS "campaignrunlog.runstatustext" FROM public.campaignrunlog WHERE (campaignrunlog.campaign_id = '203a4ee7-6659-4b3a-bb03-6055d8b95e82' AND (NOT ((campaignrunlog.runstatustext IS NULL OR campaignrunlog.runstatustext = '')) AND LOWER(campaignrunlog.runstatustext) LIKE '%er%' ESCAPE E'\')) ORDER BY campaignrunlog.rundatetime

...this query with the E'\\' works

Can you please patch this so i can get this to work with my code? Can i please receive this fix in the next nighly build???

Please help. thx much.

I've attached a sample screenshot of the working query with the "E" prefix. Please let me know when this can be patched.... thanks a million.

Are you using the Postgres8 provider or Postgres9 provider when initialising your LightSpeedContext? The previous fix only applies when using Postgres9. If you are using Postgres9 can you please send through a small repro which we can use to trigger this as we do have tests from the previous bug report which are passing correctly.

Jeremy

My Project was on old project using Postgres8. But I did not change it to use the latest Postgres9 for lightspeed5. Changing it to Postgres9 works! this fixed the problem.

thanks much. awesome support.