Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-44354

Cached LIKE operator pattern not updated on changes

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Yes

    Description

      The LIKE operator caches the compiled regular expression in expression tree and then continues to use it even if the input argument has changed.  This affects PREPARED statements.

      Attachments

        Issue Links

          Activity

            Build couchbase-server-7.0.0-4483 contains query commit 1aaca7b with commit message:
            MB-44354 note LIKE pattern and validate it is unchanged before using cached compiled regular expression

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-4483 contains query commit 1aaca7b with commit message: MB-44354 note LIKE pattern and validate it is unchanged before using cached compiled regular expression

            Build couchbase-server-6.6.2-9515 contains query commit 87f8726 with commit message:
            MB-44354 note LIKE pattern and validate it is unchanged before using cached compiled regular expression

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.2-9515 contains query commit 87f8726 with commit message: MB-44354 note LIKE pattern and validate it is unchanged before using cached compiled regular expression

            Verified on build 6.6.2-9515 and 7.0.0-4502.

            p2 is defined as: "prepare p2 as select airportname from `travel-sample` where type = \"airport\" and city like $ville;"

             

            cbq> \set -$ville "Lyon";
            cbq> execute p2;
            {
                "requestID": "e22a93ec-ae63-4e5d-8c63-80a9c0333529",
                "signature": {
                    "airportname": "json"
                },
                "results": [
                {
                    "airportname": "Lyon Part-Dieu Railway"
                },
                {
                    "airportname": "Saint Exupery"
                },
                {
                    "airportname": "Bron"
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "6.883394ms",
                    "executionTime": "6.677766ms",
                    "resultCount": 3,
                    "resultSize": 138
                }
            }
            cbq> \set -$ville "Paris";
            cbq> execute p2;
            {
                "requestID": "972f5c79-db9b-4b83-aa84-732ff64b4387",
                "signature": {
                    "airportname": "json"
                },
                "results": [
                {
                    "airportname": "Orly"
                },
                {
                    "airportname": "Gare Montparnasse"
                },
                {
                    "airportname": "La Defense Heliport"
                },
                {
                    "airportname": "Gare de Lyon"
                },
                {
                    "airportname": "All Airports"
                },
                {
                    "airportname": "Le Bourget"
                },
                {
                    "airportname": "Gare du Nord"
                },
                {
                    "airportname": "Gare de LEst"
                },
                {
                    "airportname": "Charles De Gaulle"
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "65.257726ms",
                    "executionTime": "64.986389ms",
                    "resultCount": 9,
                    "resultSize": 412
                }
            }
            cbq>  

            I also could repro issue on 6.6.2-9514:

            cbq> \set -$ville "Lyon";
            cbq> execute p2;
            {
                "requestID": "53bb8af6-5852-4923-b555-d8431619f602",
                "signature": {
                    "airportname": "json"
                },
                "results": [
                {
                    "airportname": "Bron"
                },
                {
                    "airportname": "Saint Exupery"
                },
                {
                    "airportname": "Lyon Part-Dieu Railway"
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "7.043359ms",
                    "executionTime": "6.681565ms",
                    "resultCount": 3,
                    "resultSize": 138
                }
            }
            cbq> \set -$ville "Paris";
            cbq> execute p2;
            {
                "requestID": "b252cfb3-e93c-4b36-a747-98bc7fcc6671",
                "signature": {
                    "airportname": "json"
                },
                "results": [
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "53.22673ms",
                    "executionTime": "53.041761ms",
                    "resultCount": 0,
                    "resultSize": 0
                }
            } 

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on build  6.6.2-9515 and 7.0.0-4502 . p2 is defined as:  "prepare p2 as select airportname from `travel-sample` where type = \"airport\" and city like $ville;"   cbq> \set -$ville "Lyon" ; cbq> execute p2; {     "requestID" : "e22a93ec-ae63-4e5d-8c63-80a9c0333529" ,     "signature" : {         "airportname" : "json"     },     "results" : [     {         "airportname" : "Lyon Part-Dieu Railway"     },     {         "airportname" : "Saint Exupery"     },     {         "airportname" : "Bron"     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "6.883394ms" ,         "executionTime" : "6.677766ms" ,         "resultCount" : 3 ,         "resultSize" : 138     } } cbq> \set -$ville "Paris" ; cbq> execute p2; {     "requestID" : "972f5c79-db9b-4b83-aa84-732ff64b4387" ,     "signature" : {         "airportname" : "json"     },     "results" : [     {         "airportname" : "Orly"     },     {         "airportname" : "Gare Montparnasse"     },     {         "airportname" : "La Defense Heliport"     },     {         "airportname" : "Gare de Lyon"     },     {         "airportname" : "All Airports"     },     {         "airportname" : "Le Bourget"     },     {         "airportname" : "Gare du Nord"     },     {         "airportname" : "Gare de LEst"     },     {         "airportname" : "Charles De Gaulle"     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "65.257726ms" ,         "executionTime" : "64.986389ms" ,         "resultCount" : 9 ,         "resultSize" : 412     } } cbq> I also could repro issue on 6.6.2-9514 : cbq> \set -$ville "Lyon" ; cbq> execute p2; {     "requestID" : "53bb8af6-5852-4923-b555-d8431619f602" ,     "signature" : {         "airportname" : "json"     },     "results" : [     {         "airportname" : "Bron"     },     {         "airportname" : "Saint Exupery"     },     {         "airportname" : "Lyon Part-Dieu Railway"     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "7.043359ms" ,         "executionTime" : "6.681565ms" ,         "resultCount" : 3 ,         "resultSize" : 138     } } cbq> \set -$ville "Paris" ; cbq> execute p2; {     "requestID" : "b252cfb3-e93c-4b36-a747-98bc7fcc6671" ,     "signature" : {         "airportname" : "json"     },     "results" : [     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "53.22673ms" ,         "executionTime" : "53.041761ms" ,         "resultCount" : 0 ,         "resultSize" : 0     } }

            Donald Haggart / Pierre Regazzoni We would like this to be in the Release Notes for 6.6.2, can you please provide the summary?

            malarky Chris Malarky added a comment - Donald Haggart / Pierre Regazzoni We would like this to be in the Release Notes for 6.6.2, can you please provide the summary?

            Chris Malarky, my summary would be:

            LIKE patterns are compiled and cached to avoid recompiling unnecessarily. This fix addresses a problem where a previously cached compiled pattern that did not match the active pattern was used by prepared statements.

            Or alternatively:

            This fix addresses a bug where prepared statements failed to observe changes to LIKE predicate patterns.

            Donald.haggart Donald Haggart added a comment - Chris Malarky , my summary would be: LIKE patterns are compiled and cached to avoid recompiling unnecessarily. This fix addresses a problem where a previously cached compiled pattern that did not match the active pattern was used by prepared statements. Or alternatively: This fix addresses a bug where prepared statements failed to observe changes to LIKE predicate patterns.
            malarky Chris Malarky added a comment - Thanks Donald Haggart !

            People

              pierre.regazzoni Pierre Regazzoni
              Donald.haggart Donald Haggart
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  PagerDuty