MySQL Bugs: #10121: DBPROC fails every second call

Bug #10121	DBPROC fails every second call
Submitted:	24 Apr 2005 9:17	Modified:	4 Jan 2006 10:13
Reporter:	Ties Meyer-Jark	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MaxDB	Severity:	S3 (Non-critical)
Version:	7.5, 7.6	OS:	Windows (w2k)
Assigned to:	Ulf Wendel	CPU Architecture:	Any

Description:
When i execute the following procedure from SQL Studio, it fails every second try with error '-4024' 
When i use sqlcli it works properly.

CREATE DBPROC DIHAAUSWAHL30(IN DATUM DATE, IN DZID INTEGER)
 
RETURNS CURSOR AS  
VAR 
BEGINN TIMESTAMP;
ENDE TIMESTAMP;
MONTAG BOOLEAN;
DIENSTAG BOOLEAN;
MITTWOCH BOOLEAN;
DONNERSTAG BOOLEAN;
FREITAG BOOLEAN;
SAMSTAG BOOLEAN;
SONNTAG BOOLEAN;
LASTDAY INTEGER;

 $CURSOR = 'DIHAAUSWAHL30';
SELECT MDG.MONTAG, MDG.DIENSTAG, MDG.MITTWOCH, MDG.DONNERSTAG, MDG.FREITAG, MDG.SAMSTAG, MDG.SONNTAG FROM DBA.MAPDIGRUPERSON MDG JOIN
DBA.DIENSTGRUPPEN DG ON 
DG.ID = MDG.DIGRUID JOIN
DBA.SCHICHTEN SCH ON
DG.ID = SCH.DIGRUID JOIN 
DBA.DIENSTZEITEN DZ ON
SCH.ID = DZ.SCHICHTENID WHERE DZ.ID = :dzid;
FETCH INTO :montag, :dienstag, :mittwoch, :donnerstag, :freitag, :samstag, :sonntag;
IF DAYOFWEEK(DATUM) = 1 THEN MONTAG = TRUE;
IF DAYOFWEEK(DATUM) = 2 THEN DIENSTAG = TRUE;
IF DAYOFWEEK(DATUM) = 3 THEN MITTWOCH = TRUE;
IF DAYOFWEEK(DATUM) = 4 THEN DONNERSTAG = TRUE;
IF DAYOFWEEK(DATUM) = 5 THEN FREITAG = TRUE;
IF DAYOFWEEK(DATUM) = 6 THEN SAMSTAG = TRUE;
IF DAYOFWEEK(DATUM) = 7 THEN SONNTAG = TRUE;
IF MONTH(DATUM) IN (1,3,5,7,8,10,12) THEN LASTDAY = 31;
IF MONTH(DATUM) IN (4,6,9,11) THEN LASTDAY = 30;
IF MONTH(DATUM) IN (2) THEN LASTDAY = 28;

SELECT TIMESTAMP(:datum, BEGIN) AS BEGINN, TIMESTAMP(:datum, ADDTIME(BEGIN, MAKETIME(DAUER+FREINACH,0,0))) AS ENDE FROM DBA.DIENSTZEITEN WHERE ID =  :dzid; 
FETCH INTO :beginn, :ende;

 

DECLARE :$CURSOR CURSOR FOR
SELECT DISTINCT PE.NAME , SUBSTR(PE.VORNAME, 1), DIZAHL.ANZAHL, WOE.ANZAHL, PE.ID FROM 

DBA.PERSON PE LEFT OUTER JOIN 

(SELECT COUNT(*) AS ANZAHL, PE.ID FROM DBA.DIENSTE DI JOIN 
DBA.PERSON PE ON
DI.USERID = PE.ID 
WHERE DI.DATUM >= SUBDATE(:datum, DAY(:datum)) AND DI.DATUM <= ADDDATE( SUBDATE(:datum, DAY(:datum)), :lastday) 
GROUP BY PE.ID) DIZAHL 
ON
PE.ID = DIZAHL.ID LEFT OUTER JOIN 
(SELECT COUNT(*) AS ANZAHL, PE.ID FROM DBA.DIENSTE DI JOIN 
DBA.PERSON PE ON
DI.USERID = PE.ID 
WHERE DI.DATUM >= SUBDATE(:datum, DAY(:datum)) AND DI.DATUM <= ADDDATE( SUBDATE(:datum, DAY(:datum)), :lastday) 
AND DAYOFWEEK(DI.DATUM) IN (5,6,7)
GROUP BY PE.ID) WOE  ON 
WOE.ID=PE.ID JOIN

DBA.MAPDIGRUPERSON MDG ON
PE.ID = MDG.PERSONALID JOIN 
DBA.DIENSTGRUPPEN DG ON 
MDG.DIGRUID = DG.ID JOIN
DBA.SCHICHTEN SCH ON
DG.ID = SCH.DIGRUID JOIN
DBA.DIENSTZEITEN DZ ON
SCH.ID = DZ.SCHICHTENID 
WHERE DZ.ID = :dzid AND
(PE.AUSTRITT > :datum) AND 
MDG.GUELTIGVON <= :datum AND 
(MDG.GUELTIGBIS >= :datum OR MDG.GUELTIGBIS IS NULL) AND 
MDG.MONTAG = :montag AND 
MDG.DIENSTAG = :dienstag  AND 
MDG.MITTWOCH = :mittwoch AND
MDG.DONNERSTAG = :donnerstag AND
MDG.FREITAG = :freitag AND 
MDG.SAMSTAG = :samstag AND
MDG.SONNTAG = :sonntag AND
NOT (PE.ID IN( 
SELECT  PE.ID FROM DBA.PERSON PE 
JOIN DBA.DIENSTE DI ON
DI.USERID = PE.ID 
JOIN DBA.DIENSTZEITEN DZ  ON 
DZ.ID =  DI.DIENSTZEITENID JOIN
DBA.SCHICHTEN SCH ON
SCH.ID = DZ.SCHICHTENID
WHERE
(:beginn > TIMESTAMP(DI.DATUM, ADDTIME(DZ.BEGIN,MAKETIME(DZ.DAUER+DZ.FREINACH,0,0))) AND 
:ende < TIMESTAMP(DI.DATUM, DZ.BEGIN)) OR
(:beginn < TIMESTAMP(DI.DATUM, ADDTIME(DZ.BEGIN,MAKETIME(DZ.DAUER+DZ.FREINACH,0,0))) AND 
:ende > TIMESTAMP(DI.DATUM, DZ.BEGIN)) OR 
(SCH.FREISCHICHT = TRUE AND  DI.DATUM = :datum)
));

Message: Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
 General error;-4024 POS(1) 
call dihaauswahl30('2005-04-07', 48)

How to repeat:
Just do it more than one time.

Hi Ties,

I'm afraid there's a plenty of tables missing to reproduce the problem. Please provide me with the necessary schema.

Thanks!
Ulf

Catalog provided as file

Hi Ties,

this is the simplified test case to verify the bug. The developers are looking into it. I'll keep you posted on the progress.

Best regards,
Ulf

CREATE TABLE t1 (id INTEGER)
//
CREATE TABLE t2 (id INTEGER)
//
SELECT
       t1.id AS id
    FROM 
      t1
    LEFT OUTER JOIN 
      ( 
        SELECT 
          t1.id 
        FROM 
          t1
       ) left_outer_join_res ON left_outer_join_res.id = t1.id
    WHERE
      NOT 
        ( 
          t1.id IN
          (
            SELECT
            t1.id
          FROM 
            t1 AS t1
          JOIN 
            t2 AS t2
          ON 
            t1.id = t2.id
          )
        )
//
SELECT
       t1.id AS id
    FROM 
      t1
    LEFT OUTER JOIN 
      ( 
        SELECT 
          t1.id 
        FROM 
          t1
       ) left_outer_join_res ON left_outer_join_res.id = t1.id
    WHERE
      NOT 
        ( 
          t1.id IN
          (
            SELECT
            t1.id
          FROM 
            t1 AS t1
          JOIN 
            t2 AS t2
          ON 
            t1.id = t2.id
          )
        )

Hi,

this is a missing feature. MaxDB does not store PK names anywhere. That's why the JDBC driver can't return any names.

It won't be fixed in the near future.

Regards,
Ulf

Uuups, 

wrong bug number for that comment. Excuse me!

Ulf

Please continue tracking the progress on your own on http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1136281. 

Thanks!
Ulf