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

Prepare statement should not inline query parameters

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 6.0.1
    • 5.5.2, 6.0.1, 6.5.0
    • query
    • None
    • Untriaged
    • Yes

    Description

      Repro:

      CREATE INDEX ix1 ON default(type);
      INSERT INTO default VALUES ("f001",{"type":"rule", "ruleVersions":[{"maxDischargeDate":"2017-01-01", "state":100,"groupTags":["tag1"]}]});
      \set -$groupTags ["tag1"];
      \set -$dischargeDate "2016-10-21T00:00:00";
       
      PREPARE p1 FROM SELECT  maxAppVer.theRule
      FROM default rules
      LET maxAppVer = (SELECT MAX(maxRule.version) AS ver, maxRule AS theRule
                       FROM rules.ruleVersions as maxRule
                       LET minApp =  (SELECT MIN(minRule.maxDischargeDate) AS minDate
                                      FROM rules.ruleVersions AS minRule
                                      WHERE minRule.maxDischargeDate >= $dischargeDate AND
                                            minRule.state IN [100]) [0]
                       WHERE maxRule.maxDischargeDate = minApp.minDate AND
                             maxRule.state IN [100] AND
                             ARRAY_LENGTH(ARRAY_INTERSECT($groupTags, maxRule.groupTags)) > 0
                       GROUP BY maxRule)[0]
      WHERE rules.type = "rule" AND maxAppVer IS NOT MISSING ;
      select meta().plan, * from system:prepareds;
      
      

      The plan prepareds cache looks fine
       
      EXECUTE p1;  
      -- should return 1 row
       
      select meta().plan, * from system:prepareds;
      Prepareds cache plan is replaced $dischargeDate with actual value. which is in correct
      \set -$dischargeDate "2017-10-21T00:00:00";
      EXECUTE p1;  
      -- Should return 0 row but returns 1 row
      \set -$dischargeDate "2016-10-21T00:00:00";
       EXECUTE p1;  
      -- should return 1 row
      
      

      {
                                                      "#operator": "Let",
                                                      "bindings": [
                                                          {
                                                              "expr": "(correlated (select max((`maxRule`.`version`)) as `ver`, `maxRule` as `theRule` from (`rules`.`ruleVersions`) as `maxRule` let `minApp` = (correlated (select min((`minRule`.`maxDischargeDate`)) as `minDate` from (`rules`.`ruleVersions`) as `minRule` where ((\"2016-10-21T00:00:00\" <= (`minRule`.`maxDischargeDate`)) and ((`minRule`.`state`) in [100])))[0]) where ((((`maxRule`.`maxDischargeDate`) = (`minApp`.`minDate`)) and ((`maxRule`.`state`) in [100])) and (0 < array_length(array_intersect($groupTags, (`maxRule`.`groupTags`)))))  group by `maxRule`)[0])",
                                                              "var": "maxAppVer"
                                                          }
                                                      ]
                                                  },
      

      The problem might be introduced in 5.0

      https://forums.couchbase.com/t/prepared-statement-with-request-plus-is-returning-stale-results/19088/9

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              ajay.bhullar Ajay Bhullar
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty