A very strange issue with an complex view.

IBM Informix Dynamic Server Version 9.40.HC5 HPUX 11iV1 I have a very weird issue with a very complex set of views. Here is what I'm seeing: SELECT bucket, id FROM lu_ar_bucket_vw WHERE bucket=5 ; bucket id --------- ------- 8 2058987 8 2078673 8 2059319 8 2062939 * snip * Which is rather crazy. I'm not sure how to distill this down to something simpler, here is the lu_ar_bucket_vw : create view informix.lu_ar_bucket_vw (bucket,id )asSELECT CASE when (deggrp like 'W/D%') then 10 when (deggrp like 'SAB%') then 9 when ((SELECT count(*)=0 FROM ctc_rec WHERE ctc_rec.id=lu_ar_prog_vw.id and ctc_rec.resrc='ISIR' and ctc_rec.stat='C' and ctc_rec.tick in ('FY06', 'FY07') )) then 8 when ((SELECT(count(A.ctc_no)>0) and (count(B.ctc_no)>0) FROM id_rec left join ctc_rec A on A.id=id_rec.id and (A.tick='FY06'AND A.resrc='NRF7') left join ctc_rec B on B.id=id_rec.id AND (B.tick='FY07' AND B.resrc='NRF8') WHERE id_rec.id=lu_ar_prog_vw.id)) then 7 when ((SELECT(count(A.ctc_no)>0) and (count(B.ctc_no)>0) FROM id_rec left join ctc_rec A on A.id=id_rec.id and (A.tick='FY06'AND A.resrc='FAC7') left join ctc_rec B on B.id=id_rec.id AND (B.tick='FY07' AND B.resrc='FAC8') WHERE id_rec.id=lu_ar_prog_vw.id)) then 6 WHEN ((SELECT COUNT(A.ctc_no)>0 AND COUNT(sec.bbay_entry_id)>0 FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND (A.tick='FY06' AND A.resrc='FAC7') LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND sec_tl.ay_slot=2 JOIN lu_bbay_entry_rec sec ON sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date > TODAY +30 WHERE id_rec.id=lu_ar_prog_vw.id)) then 5 WHEN ((SELECT COUNT(A.ctc_no)>0 AND COUNT(sec.bbay_entry_id)>0 AND COUNT(ISIR.ctc_no)=0 FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND (A.tick='FY06' AND A.resrc='FAC7') LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND sec_tl.ay_slot=2 JOIN lu_bbay_entry_rec sec ON sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date <= TODAY +30 LEFT JOIN ctc_rec ISIR on ISIR.id=id_rec.id AND ISIR.resrc='ISIR' AND ISIR.stat='C' AND ISIR.tick = 'FY07' WHERE id_rec.id=lu_ar_prog_vw.id)) then 4 WHEN ((SELECT COUNT(A.ctc_no)>0 AND COUNT(sec.bbay_entry_id)>0 AND COUNT(ISIR.ctc_no)>0 FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND (A.tick='FY06' AND A.resrc='FAC7') LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND sec_tl.ay_slot=2 JOIN lu_bbay_entry_rec sec ON sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date <= TODAY +30 LEFT JOIN ctc_rec ISIR on ISIR.id=id_rec.id AND ISIR.resrc='ISIR' AND ISIR.stat='C' AND ISIR.tick = 'FY07' WHERE id_rec.id=lu_ar_prog_vw.id)) then 3 WHEN ((SELECT COUNT(A.ctc_no)=0 AND COUNT(sec.bbay_entry_id)>0 AND COUNT(ANFORM.ctc_no)>0 FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND (A.tick='FY06' AND A.resrc='FAC7') LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND sec_tl.ay_slot=2 JOIN lu_bbay_entry_rec sec ON sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date > TODAY +30 left join ctc_rec ANFORM on ANFORM.tick='FY07' AND ANFORM.resrc in ('ANFORM', 'ANFBBY') and ANFORM.stat='C' and ANFORM.id=id_rec.id WHERE id_rec.id=lu_ar_prog_vw.id)) then 2 else 1 END as BUCKET, lu_ar_prog_vw.id FROM lu_ar_prog_vw ; ***************************** lu_ar_prog_vw is another view constructed as follows: CREATE VIEW informix.lu_ar_prog_vw ( id, bal_act, debit_flag, prog, deggrp, enr_date, last_class_date ) AS SELECT suba_rec.suba_no AS id, suba_rec.bal_act bal_act, suba_rec.bal_act>0 debit_flag, prog_first.prog, prog_first.deggrp, prog_first.enr_date, ( SELECT MAX(sec_rec.end_date) FROM cw_rec LEFT JOIN sec_rec ON sec_rec.crs_no=cw_rec.crs_no AND sec_rec.sec_no=cw_rec.sec AND sec_rec.cat=cw_rec.cat AND sec_rec.sess=cw_rec.sess AND sec_rec.yr=cw_rec.yr WHERE cw_rec.stat='R' AND cw_rec.id=suba_rec.suba_no ) AS last_Class_date FROM suba_rec JOIN prog_enr_rec prog_first ON suba_rec.id=prog_first.id JOIN prog_enr_rec prog_sec ON suba_rec.id=prog_sec.id WHERE subs = 'S/A' AND bal_act != 0 AND prog_first.subprog IN ('EC', 'NG', 'NU') GROUP BY 1, 2, 3, 4, 5, 6, 7 HAVING prog_first.enr_date=MAX(prog_sec.enr_date); Any idea's on this whatsoever? Other than cursing the day this entered your inbox that is :) -- -------------------------------------------- Chris Salch

I've attached an sqexplain on a similar query, if that might help. On Wed, 2007-11-07 at 19:30 -0500, Chris Salch wrote: > IBM Informix Dynamic Server Version 9.40.HC5 > HPUX 11iV1 > > I have a very weird issue with a very complex set of views. Here is > what I'm seeing: > > SELECT > > bucket, id > FROM > > lu_ar_bucket_vw > WHERE > > bucket=5 > ; > > bucket id > --------- ------- > 8 2058987 > 8 2078673 > 8 2059319 > 8 2062939 > * snip * QUERY: ------ create view "informix".lu_ar_prog_vw (id,bal_act,debit_flag,prog,deggrp,enr_date,last_class_date) as select x0.suba_no ,x0.bal_act ,(x0.bal_act > '0.00' ) ,x1.prog ,x1.deggrp ,x1.enr_date ,(select max(x4.end_date ) from ("informix".cw_rec x3 left join "informix".sec_rec x4 on (((((x4.crs_no = x3.crs_no ) AND (x4.sec_no = x3.sec ) ) AND (x4.cat = x3.cat ) ) AND (x4.sess = x3.sess ) ) AND (x4.yr = x3.yr ) ) )where ((x3.stat = 'R' ) AND (x3.id = x0.suba_no ) ) ) from (("informix".suba_rec x0 join "informix".prog_enr_rec x1 on (x0.id = x1.id ) )join "informix".prog_enr_rec x2 on (x0.id = x2.id ) )where (((x0.subs = 'S/A' ) AND (x0.bal_act != '0.00' ) ) AND (x1.subprog IN ('EC' ,'NG' ,'NU' )) ) group by x0.suba_no ,x0.bal_act ,3 ,x1.prog ,x1.deggrp ,x1.enr_date ,7 having (x1.enr_date = max(x2.enr_date ) ) ; Estimated Cost: 4144 Estimated # of Rows Returned: 70 Temporary Files Required For: Group By 1) informix.suba_rec: INDEX PATH Filters: informix.suba_rec.bal_act != $0.00 (1) Index Keys: subs Lower Index Filter: informix.suba_rec.subs = 'S/A' 2) informix.prog_enr_rec: INDEX PATH Filters: informix.prog_enr_rec.subprog IN ('EC' , 'NG' , 'NU' ) (1) Index Keys: id prog site Lower Index Filter: informix.suba_rec.id = informix.prog_enr_rec.id ON-Filters:informix.suba_rec.id = informix.prog_enr_rec.id NESTED LOOP JOIN 3) informix.prog_enr_rec: INDEX PATH (1) Index Keys: id prog site Lower Index Filter: informix.suba_rec.id = informix.prog_enr_rec.id ON-Filters:informix.suba_rec.id = informix.prog_enr_rec.id NESTED LOOP JOIN PostJoin-Filters:(((informix.suba_rec.subs = 'S/A' AND informix.suba_rec.bal_act != $0.00 ) AND informix.prog_enr_rec.subprog IN ('EC' , 'NG' , 'NU' )) AND CASE WHEN informix.prog_enr_rec.deggrp LIKE 'W/D%' THEN 10 WHEN informix.prog_enr_rec.deggrp LIKE 'SAB%' THEN 9 WHEN <subquery> = t THEN 8 WHEN <subquery> = t THEN 7 WHEN <subquery> = t THEN 6 WHEN <subquery> = t THEN 5 WHEN <subquery> = t THEN 4 WHEN <subquery> = t THEN 3 WHEN <subquery> = t THEN 2 ELSE 1 END= 2 ) Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) salchc.x1: INDEX PATH Filters: salchc.x1.stat = 'C' (1) Index Keys: tick id cmpl_date resrc (Key-First) Lower Index Filter: (salchc.x1.id = informix.suba_rec.suba_no AND salchc.x1.tick = 'FY06' ) Key-First Filters: (salchc.x1.resrc = 'ISIR' ) (2) Index Keys: tick id cmpl_date resrc (Key-First) Lower Index Filter: (salchc.x1.id = informix.suba_rec.suba_no AND salchc.x1.tick = 'FY07' ) Key-First Filters: (salchc.x1.resrc = 'ISIR' ) UDRs in query: -------------- UDR id : -113 UDR name: equal Subquery: --------- Estimated Cost: 2 Estimated # of Rows Returned: 1 1) salchc.x2: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: salchc.x2.id = informix.suba_rec.suba_no 2) salchc.x3: INDEX PATH (1) Index Keys: tick id cmpl_date resrc (Key-First) Lower Index Filter: (salchc.x3.id = salchc.x2.id AND salchc.x3.tick = 'FY06' ) Key-First Filters: (salchc.x3.resrc = 'NRF7' ) ON-Filters:(salchc.x3.id = salchc.x2.id AND (salchc.x3.tick = 'FY06' AND salchc.x3.resrc = 'NRF7' ) ) NESTED LOOP JOIN 3) salchc.x4: INDEX PATH (1) Index Keys: tick id cmpl_date resrc (Key-First) Lower Index Filter: (salchc.x4.id = salchc.x2.id AND salchc.x4.tick = 'FY07' ) Key-First Filters: (salchc.x4.resrc = 'NRF8' ) ON-Filters:(salchc.x4.id = salchc.x2.id AND (salchc.x4.tick = 'FY07' AND salchc.x4.resrc = 'NRF8' ) ) NESTED LOOP JOIN PostJoin-Filters:salchc.x2.id = informix.suba_rec.suba_no UDRs in query: -------------- UDR id : -113 UDR name: equal Subquery: --------- Estimated Cost: 2 Estimated # of Rows Returned: 1 1) salchc.x5: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: salchc.x5.id = informix.suba_rec.suba_no 2) salchc.x6: INDEX PATH (1) Index Keys: tick id cmpl_date resrc (Key-First) Lower Index Filter: (salchc.x6.id = salchc.x5.id AND salchc.x6.tick = 'FY06' ) Key-First Filters: (salchc.x6.resrc = 'FAC7' ) ON-Filters:(salchc.x6.id = salchc.x5.id AND (salchc.x6.tick = 'FY06' AND salchc.x6.resrc = 'FAC7' ) ) NESTED LOOP JOIN 3) salchc.x7: INDEX PATH (1) Index Keys: tick id cmpl_date resrc (Key-First) Lower Index Filter: (salchc.x7.id = salchc.x5.id AND salchc.x7.tick = 'FY07' ) Key-First Filters: (salchc.x7.resrc = 'FAC8' ) ON-Filters:(salchc.x7.id = salchc.x5.id AND (salchc.x7.tick = 'FY07' AND salchc.x7.resrc = 'FAC8' ) ) NESTED LOOP JOIN PostJoin-Filters:salchc.x5.id = informix.suba_rec.suba_no UDRs in query: -------------- UDR id : -113 UDR name: equal Subquery: --------- Estimated Cost: 6 Estimated # of Rows Returned: 1 1) salchc.x8: INDEX PATH (1) Index Keys: id (Key-Only) Lower Index Filter: salchc.x8.id = informix.suba_rec.suba_no 2) salchc.x9: INDEX PATH (1) Index Keys: tick id cmpl_date resrc (Key-First) Lower Index Filter: (salchc.x9.id = salchc.x8.id AND salchc.x9.tick = 'FY06' ) Key-First Filters: (salchc.x9.resrc = 'FAC7' ) ON-Filters:(salchc.x9.id = salchc.x8.id AND (salchc.x9.tick = 'FY06' AND salchc.x9.resrc = 'FAC7' ) ) NESTED LOOP JOIN 3) salchc.x10: INDEX PATH (1) Index Keys: id ay_slot bbay_entry_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (salchc.x10.id = salchc.x8.id AND salchc.x10.ay_slot = 2 ) ON-Filters:(salchc.x10.id = salchc.x8.id AND salchc.x10.ay_slot = 2 ) NESTED LOOP JOIN 4) salchc.x11: INDEX PATH Filters: salchc.x11.beg_date > TODAY + 30 (1) Index Keys: bbay_entry_id (Serial, fragments: ALL) Lower Index Filter: salchc.x10.bbay_entry_id = salchc.x11.bbay_entry_id ON-Filters:(salchc.x10.bbay_entry_id = salchc.x11.bbay_entry_id AND salchc.x11.beg_date > TODAY + 30 ) NESTED LOOP JOIN PostJoin-Filters:salchc.x8.id = informix.suba_rec.suba_no UDRs in query: -------------- UDR id : -113 UDR name: equal Subquery: --------- Estimated Cost: 7 Estimated # of Rows Ret