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 OR
vcnumb 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
------------------------------

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
------------------------------

Original Message:
Sent: Wed September 07, 2022 07:33 AM
From: Art Kagel
Subject: Odd -1266 error with combined conditions and fragmented index

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
------------------------------

Original Message:
Sent: Wed September 07, 2022 07:38 AM
From: Andreas Legner
Subject: Odd -1266 error with combined conditions and fragmented index

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 tidWHERE d < TODAY - 1 UNITS DAYAND ( 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
------------------------------

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


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
------------------------------

Original Message:
Sent: Tue September 20, 2022 12:32 PM
From: TOM GIRSCH
Subject: Odd -1266 error with combined conditions and fragmented index

An update on this. Support has created idsdb00111228 for this issue. I have not yet received an APAR.

------------------------------
TOM GIRSCH
------------------------------