Crash selecting from SQLBase 11.6

Report SqlBase bugs and possible workarounds.
User avatar
Peter.Hugk
Germany
Posts: 340
Joined: 06 Mar 2017, 07:48
Location: Germany

Crash selecting from SQLBase 11.6

Post by Peter.Hugk » 09 Dec 2011, 16:06

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

Re: Crash selecting from SQLBase 11.6

Post by Mike Vandine » 12 Dec 2011, 04:48

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
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Crash selecting from SQLBase 11.6

Post by FRBhote » 16 Dec 2011, 07:23

Anything further on this?

User avatar
Peter.Hugk
Germany
Posts: 340
Joined: 06 Mar 2017, 07:48
Location: Germany

Re: Crash selecting from SQLBase 11.6

Post by Peter.Hugk » 16 Dec 2011, 08:10

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
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Crash selecting from SQLBase 11.6

Post by FRBhote » 16 Dec 2011, 12:06

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

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Crash selecting from SQLBase 11.6

Post by FRBhote » 05 Jan 2012, 12:18

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.

Return to “SqlBase Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests