Tweet
In short - in a CCSID37 SQLRPGLE source file, if there is a hex 05 tab character before a component line of an Exec SQL statement, it can cause the compiler/SQL precompiler to silently ignore that source line. The line is even omitted from the diagnostic check source listing spool.
The bug was noticed in v7.3, and today we updated test to v7.4 and the bug is still present. I was unable to reproduce the bug on pub400.com even when targeting v7.3, so there may be some other subtleties of job/compile setup that have an effect.
It's also not as simple as "tab character causes line to be ignored", because in my test case I have two lines with tab characters and only one is ignored. This is also the reason for the complex SQL example, as it is designed to mimic the original program where the bug was found as closely as possible due to the "flakiness" of the bug.
Source member is CCSID37, jobs are CCSID 65535 with default CCSID 37.
My test code is transcribed below.
Table SQLBUGTST2 columns:
COL1 char(6)
COL2 char(2)
COL3 char(2)
COL4 decimal(7)
COL5 decimal(7)
The bug was noticed in v7.3, and today we updated test to v7.4 and the bug is still present. I was unable to reproduce the bug on pub400.com even when targeting v7.3, so there may be some other subtleties of job/compile setup that have an effect.
It's also not as simple as "tab character causes line to be ignored", because in my test case I have two lines with tab characters and only one is ignored. This is also the reason for the complex SQL example, as it is designed to mimic the original program where the bug was found as closely as possible due to the "flakiness" of the bug.
Source member is CCSID37, jobs are CCSID 65535 with default CCSID 37.
My test code is transcribed below.
Code:
ctl-Opt option(*srcstmt:*nodebugio:*nounref) optimize(*basic)
dftActGrp(*no) actGrp(*caller) extBinInt(*yes)
bndDir('HCBNDDIR':'CMBNDDIR') main(SQLBUGTST1);
dcl-proc SQLBUGTST1;
dcl-c MAX_RECORDS const(100);
dcl-ds record extname('WSPEEDJ/SQLBUGTST2') qualified template inz
end-ds;
dcl-ds records likeDs(record) dim(MAX_RECORDS) inz(*likeDs);
dcl-s outTxt varchar(50);
dcl-s rp char(1);
reset records;
SQLER3 = 0;
exec sql
declare csr1 cursor for
select * from WSPEEDJ/SQLBUGTST2 nsl
where nsl.COL1 = 'TEST01' and nsl.COL4 <= 1210506
and (nsl.COL5 >= 1210506 or nsl.COL5 = 0)
and nsl.COL4 = (Select max(nsl2.COL4)
from WSPEEDJ/SQLBUGTST2 nsl2
where nsl2.COL1 = nsl.COL1 and
nsl2.COL2 = nsl.COL2 and
nsl2.COL3 = nsl.COL3 and
(nsl2.COL5 >= 1210506 or nsl2.COL5 = 0)
and nsl2.COL4 <= 1210506)
order by nsl.COL1, nsl.COL2, nsl.COL3, nsl.COL4 desc;
exec sql open csr1;
exec sql fetch csr1 for :MAX_RECORDS rows into :records;
outTxt = outTxt + %char(SQLER3)+'; ';
exec sql close csr1;
// The above csr1 SQL has a hex '05' character in position 6 in
// source lines 0028.00 and 0029.00
// nsl2.COL2 = nsl.COL2 and
// nsl2.COL3 = nsl.COL3 and
// It returns 50 rows
// It should return 52, but it acts like line 0028.00
// nsl2.COL2 = nsl.COL2 and
// is not there.
exec sql
declare csr2 cursor for
select * from WSPEEDJ/SQLBUGTST2 nsl
where nsl.COL1 = 'TEST01' and nsl.COL4 <= 1210506
and (nsl.COL5 >= 1210506 or nsl.COL5 = 0)
and nsl.COL4 = (Select max(nsl2.COL4)
from WSPEEDJ/SQLBUGTST2 nsl2
where nsl2.COL1 = nsl.COL1 and
nsl2.COL2 = nsl.COL2 and
nsl2.COL3 = nsl.COL3 and
(nsl2.COL5 >= 1210506 or nsl2.COL5 = 0)
and nsl2.COL4 <= 1210506)
order by nsl.COL1, nsl.COL2, nsl.COL3, nsl.COL4 desc;
exec sql open csr2;
exec sql fetch csr2 for :MAX_RECORDS rows into :records;
outTxt = outTxt + %char(SQLER3)+'; ';
exec sql close csr2;
// The above csr2 SQL is identical to the csr1 sql except it does not
// have the hex '05' characters.
// It returns the correct 52 rows
exec sql
declare csr9 cursor for
select * from WSPEEDJ/SQLBUGTST2 nsl
where nsl.COL1 = 'TEST01' and nsl.COL4 <= 1210506
and (nsl.COL5 >= 1210506 or nsl.COL5 = 0)
and nsl.COL4 = (Select max(nsl2.COL4)
from WSPEEDJ/SQLBUGTST2 nsl2
where nsl2.COL1 = nsl.COL1 and
// nsl2.COL2 = nsl.COL2 and
nsl2.COL3 = nsl.COL3 and
(nsl2.COL5 >= 1210506 or nsl2.COL5 = 0)
and nsl2.COL4 <= 1210506)
order by nsl.COL1, nsl.COL2, nsl.COL3, nsl.COL4 desc;
exec sql open csr9;
exec sql fetch csr9 for :MAX_RECORDS rows into :records;
outTxt = outTxt + %char(SQLER3)+'; ';
exec sql close csr9;
// The above csr9 SQL is identical to the csr2 sql except it does not
// have the hex '05' characters, and the source line
// nsl2.COL2 = nsl.COL2 and
// is commented out.
// It produces the same 50 rows as csr1 does, thus suggesting that
// the hex '50' characters in csr1 are causing it to ignore this line
dsply outTxt '' rp;
*inlr = *on;
end-Proc;
COL1 char(6)
COL2 char(2)
COL3 char(2)
COL4 decimal(7)
COL5 decimal(7)
Comment