Crash selecting from SQLBase 11.6

[Peter.Hugk]

Here is a select statement which crashes SQLBase 11.6.1 when there is enough data in the tables:

Code: Select all

Select STOFF.STOFF_ID 
   From  STOFF, MSDS  
   Where  @SCAN(MSDS_LONGTEXT, '1') >-1 And 
   STOFF.STOFF_ID = MSDS.STOFF_ID (+) And 
   STOFF.STOFF_ID Not In (Select STOFF_ID From STOFF2FIRMEN);

You will get no Fail.sql.
It is the combination of @SCAN, Outer Join (+) and the Not-In-clause which is getting the server down.

[Mike Vandine]

Hi Peter,

I will need a repro case to give to Engineering to figure out what is going wrong. You've got my email address.

Have you tried this with SP2?

Best regards,

Mike

[FRBhote]

Anything further on this?

[Peter.Hugk]

Sorry, I did not have the time to prepare the test data. But I can say that with SP 2 the crash does not happen any longer with the above reduced select statement. But the original complete statement still crashes. Looks like SP 2 is better than SP 1 but still not perfect.

[FRBhote]

Would love to know what makes it crash - I get 2-3 every day with 11.0.2.

[FRBhote]

You're probably right about that condition of outer join and NOT IN:

Code: Select all

008,05-JAN-12 15:22:58.09,DEADLOCK,VSL11,ASHOK,Ashok,RL,30,2553767,277092,Temp S-Lock,
SELECT DM_DMAC_CODE, DM_PARTY, P_NAME, P_CAT, DM_CO, DM_TYPE, DM_DATE, 
   DM_BP_ID, DM_DP_ID, DM_CL_ID, DM_QTY, DM_SET, DEMAT.ROWID, 
   DM_MKT_TYPE, DM_DC_NO, DM_CITY, DM_S_DATE, DM_S_NO,  
   DM_CLEARED, DM_ISIN, DM_CHEQUE, DM_ENTRY_DATE, DM_TO, 
   CO_NAME, R_RATE                                                                                                                                                                                                                                                                                                        
FROM SYSADM.DEMAT, SYSADM.PARTY, SYSADM.SCRIPS, SYSADM.RATES 
WHERE  DM_PARTY = P_CODE AND DM_CO = CO_CODE AND  
    R_CO (+) = DM_CO AND R_CITY(+) = DM_CITY  AND P_CAT != 'DZ'  
     AND DM_DATE = :1       AND DM_CITY = :2          AND DM_SET = :3         AND DM_TYPE = 'S'  AND DM_CL_ID NOT IN ('10000083' )  AND DM_PARTY IN ( SELECT DPAC_PARTY FROM SYSADM.DPAC WHERE  DPAC_POA = 1 )  AND DM_DMAC_CODE = :4         
008,05-JAN-12 15:23:10.46,000,05-JAN-12 15:23:43.70,GLOBAL,ABC,8

It gave a deadlock (wonder why!) at 15:22:58 and the last line is the crash at 15:23:10.