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