Embedded SQL bug?
Hi
Is this a bug? We came across it on an older version of Cache but I've also just tried it in version "Cache for Windows (x86-64) 2017.2.2 (Build 865U)" and got the same result.
Create a Class like so:
Class My.Email Extends %Persistent
{
Property AddSeq As %Integer(MAXVAL = 32767, MINVAL = -32768) [ Required ];
Property EffBegDat As User.SQLdatetime;
Property EffEndDat As User.SQLdatetime;
Property EntSys As %Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ Required ];
Property Lbl As %String(MAXLEN = 20, TRUNCATE = 1);
Property Email As %String(MAXLEN = 60, TRUNCATE = 1);
Index EntSysLbl On (EntSys, Lbl);
Index LblKey On (EntSys, Lbl, EffEndDat);
}
Create an entry:
SET ;
s email=##class(My.Email).%New()
s email.EntSys=2726
s email.AddSeq=0
s email.EffBegDat="2018-09-14 00:00:00"
s email.EffEndDat=""
s email.Lbl="HOME"
S email.Email="test1@sky.com"
s sc=email.%Save()
q
Run a bit of embedded SQL:
GETEMAILDETS
S SN=2726
K (SN)
S (PrevEntEmailCID,PREVEMAIL,vPrevEmailSt,vPrevEmailEnd)=""
&sql(SELECT %ID,Email,EffBegDat,EffEndDat INTO :PrevEntEmailCID,:PREVEMAIL,:vPrevEmailSt,:vPrevEmailEnd FROM My.Email WHERE EntSys = :SN AND Lbl = 'HOME' AND AddSeq=0 AND EffBegDat IS NOT NULL AND EffEndDat IS NOT NULL ORDER BY EffBegDat Desc)
W !,"vPrevEmailSt=",vPrevEmailSt
Q
and vPrevEmailSt contains a value even though there are no rows that match all the conditions.