Odd -1266 error with combined conditions and fragmented index
Tom Girsch hit error -1266 on 12.10.FC14 and 14.10.FC7/FC8 for a query combining date/interval conditions AND (OR integer subquery/filter) when an index is used. Art Kagel reproduced it; Andreas Legner reduced it to a small test case (index on date first, DAY and MONTH units). Support opened defect idsdb00111228 and Andreas cited APAR IT42103.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hey, everyone. Hitting a weird issue which I can replicate on 12.10.FC14, 14.10.FC7 and 14.10.FC8 (all on CentOS). Run the script below (change the dbspace names in the index creation statement to ones that exist on your system). All of the statements work except for the final one, which gives error -1266. Without the index there (or if you avoid the index by directive) it runs fine. Something clearly haywire with the indexed scan.
Can anyone else here reproduce this?
Thanks,
- TJG
CREATE TEMP TABLE tjvouch (
vcnumb integer not null ,
vopdate2 date,
enc_vccrcard char(40)
) WITH NO LOG;create index "informix".ix03
on "informix".tjvouch (vopdate2,enc_vccrcard,vcnumb) using btree
fragment by expression
partition vouch_enc_crcard_null (enc_vccrcard IS NULL ) IN chaos_tmp1,
partition vouch_enc_crcard_blank (enc_vccrcard <= ' ' ) IN chaos_tmp2,
partition vouch_enc_crcard_realvalue (enc_vccrcard > ' ' ) IN chaos_tmp3;
CREATE TEMP TABLE tjcsfiles (
cs_res integer not null ,
cs_filestat char(2)
) WITH NO LOG;
SELECT COUNT(*)
FROM tjvouch
WHERE vopdate2 < TODAY - 60 UNITS DAY
AND vcnumb NOT IN (
SELECT cs_res
FROM tjcsfiles
WHERE cs_filestat <> "C"
);
SELECT COUNT(*)
FROM tjvouch
WHERE vopdate2 < TODAY - 60 UNITS DAY
AND vopdate2 < TODAY - 13 UNITS MONTH;
SELECT COUNT(*)
FROM tjvouch
WHERE vopdate2 < TODAY - 60 UNITS DAY
AND ( vopdate2 < TODAY - 13 UNITS MONTH ORvcnumb NOT IN ( SELECT cs_res FROM tjcsfiles WHERE cs_filestat <> "C" ) );
------------------------------
TOM GIRSCH
------------------------------
#Informix
Tom:
Reproduces here on 14.10.FC8. I played with the script a bit so I can rerun it more easily with minor tweaks. That last query works fine as long as the last phrase
[
OR
vcnumb NOT IN ( SELECT cs_res FROM tjcsfiles WHERE cs_filestat <> "C" )
]
isn't part of the query (ie I comment it out). Also the phrase itself is OK (see the two additional queries I added below). Something strange going on in the parser I think. It also doesn't seem to matter whether there are any rows in tjcsfiles either satisfying the subquery or not satisfying the subquery.
Art
CREATE TEMP TABLE if not exists tjvouch (
vcnumb integer not null ,
vopdate2 date,
enc_vccrcard char(40)
) WITH NO LOG;
create index if not exists ix03
on tjvouch (vopdate2,enc_vccrcard,vcnumb) using btree
fragment by expression
partition vouch_enc_crcard_null (enc_vccrcard IS NULL ) IN tempdbs,
partition vouch_enc_crcard_blank (enc_vccrcard <= ' ' ) IN tempdbs,partition vouch_enc_crcard_realvalue (enc_vccrcard > ' ' ) IN tempdbs;
CREATE TEMP TABLE if not exists tjcsfiles (
cs_res integer not null ,
cs_filestat char(2)
) WITH NO LOG;
insert into tjcsfiles values (1, 'C');
insert into tjcsfiles values (1, 'D');
SELECT COUNT(*)
FROM tjvouch
WHERE vopdate2 < TODAY - 60 UNITS DAY
AND vcnumb NOT IN (
SELECT cs_res
FROM tjcsfiles
WHERE cs_filestat <> "C"
);
SELECT COUNT(*)
FROM tjvouch
WHERE vopdate2 < TODAY - 60 UNITS DAY
AND vopdate2 < TODAY - 13 UNITS MONTH;
SELECT cs_res FROM tjcsfiles WHERE cs_filestat <> "C";
select count(*) AS seltest
from tjvouch
where vcnumb NOT IN ( SELECT cs_res FROM tjcsfiles WHERE cs_filestat <> "C" );
SELECT COUNT(*)
FROM tjvouch
WHERE vopdate2 < (TODAY - 60 UNITS DAY)
AND ( vopdate2 < (TODAY - 13 UNITS MONTH)
OR
vcnumb NOT IN ( SELECT cs_res FROM tjcsfiles WHERE cs_filestat <> "C" )
);
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Hi Tom,
I can.
And I reduced the SQL to:
CREATE temp TABLE tid ( i integer not null , d date);
create index tid_i on tid (d,i);
SELECT * FROM tid
WHERE d < TODAY - 1 UNITS DAY
AND ( d < TODAY - 1 UNITS MONTH OR i = 1);As you said, the index is required and must be used by the query, or at least considered (since this occurs in the optimizer, it would not even have settled for and you'd not get a query plan if the error occurs).
Per my further testing all these need to be true too:
- index must be defined on (d,i), not (i,d)
- those two DAY and MONTH units need to be different (can't both be DAY)
- the WHERE ... AND ( ... OR ...) needs to exist
I'm going to log a defect. Please approach tech support if you require this fixed.
BR,
Andreas
------------------------------
Andreas Legner
------------------------------
Thanks, Andreas. I was just about to note that the index needn't be fragmented. I opened case TS010528668 on this issue yesterday. ------------------------------ TOM GIRSCH ------------------------------
Art: I went with the simplest test I could conceive of (Andreas managed to get it even simpler), and it replicates with no data in the tables at all. He and I both independently figured out that the index doesn't need to be fragmented. My initial example was intended to illustrate that the clauses work just fine separately, but not together. I logged a case on this yesterday (see reply to Andreas below). ------------------------------ TOM GIRSCH ------------------------------
An update on this. Support has created idsdb00111228 for this issue. I have not yet received an APAR. ------------------------------ TOM GIRSCH ------------------------------
Hi Tom
APAR IT42103: -1266 FOR A COMBINATION OF DATE/INTERVAL AND INTEGER FILTERS
This simple test case reproduces an undue -1266 error:
CREATE TABLE td (d date);
CREATE INDEX tdi on td (d);
SELECT * FROM td
WHERE
( d < TODAY - 1 UNITS DAY OR d = TODAY)
ANDd < TODAY - 1 UNITS MONTH;
Variants of this simplified demo statement will do as well.
Conditions for hitting the problem, per some testing:
- Index having column d in first position (if multiple columns)
- Those interval expressions
- An ... AND ( ... OR ... ) filter with two different date/interval comparisons
- Those two units (MONTH and DAY) can't be the same
Do let us know, please, via case update, if this is important to you as not all defects can be fixed in the same swift manner.
BR,
Andreas
------------------------------
Andreas Legner
------------------------------