Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Odd -1266 error with combined conditions and fragmented index

    Posted Tue September 06, 2022 09:48 PM

    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


  • 2.  RE: Odd -1266 error with combined conditions and fragmented index

    Posted Wed September 07, 2022 07:34 AM
    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
    ------------------------------

    Original Message


  • 3.  RE: Odd -1266 error with combined conditions and fragmented index

    Posted Wed September 07, 2022 10:53 AM

    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


  • 4.  RE: Odd -1266 error with combined conditions and fragmented index

    Posted Wed September 07, 2022 07:38 AM
    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
    ------------------------------

    Original Message


  • 5.  RE: Odd -1266 error with combined conditions and fragmented index

    Posted Wed September 07, 2022 10:49 AM
    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


  • 6.  RE: Odd -1266 error with combined conditions and fragmented index

    Posted Tue September 20, 2022 12:33 PM
    An update on this. Support has created idsdb00111228 for this issue. I have not yet received an APAR.

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

    Original Message


  • 7.  RE: Odd -1266 error with combined conditions and fragmented index

    Posted Tue September 20, 2022 12:55 PM
    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)
    AND
       d < 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
    ------------------------------

    Original Message