Joins & Functions

[Fabio Pinotti]

Hi, 
    I have a problem with queries that combines joins and functions. I provide the 
test case.
(MaxDb 7.5.01.00)


These are the tables:

CREATE TABLE DOCUMENT
 (K_DOCUMENT NUMBER(9,0) NOT NULL
 ,N_DOCUMENT VARCHAR2(250) NOT NULL
 ,N_DESCRIPTION VARCHAR2(4000) 
 ,D_START DATE NOT NULL
 ,O_MODIFY DATE NOT NULL
 ,E_OPERATOR_K_OPERATOR VARCHAR2(20) NOT NULL
 ,E_DOC_TYP_K_DOCUMENT_TYPE NUMBER(6,0) NOT NULL
 ,E_SUBJ_TYP_K_SUBJECT_TYPE NUMBER(6,0) NOT NULL
 ,E_COMPANY_K_COMPANY NUMBER(9,0)
 ,E_HUM_RES_K_HUMAN_RESOURCE NUMBER(9,0)
 ,K_REFERENCE VARCHAR2(50)
 ,C_NOTE VARCHAR2(250)
 ,D_END DATE
 ,K_DOC_TYPE_PROG NUMBER(9)
 )

CREATE TABLE DOCUMENT_VERSION
 (E_DOC_K_DOCUMENT NUMBER(9,0) NOT NULL
 ,K_VERSION VARCHAR2(20) NOT NULL
 ,D_START DATE NOT NULL
 ,O_MODIFY DATE NOT NULL
 ,E_OPERATOR_K_OPERATOR VARCHAR2(20) NOT NULL
 ,K_PROG NUMBER(3,0)
 ,C_NOTE VARCHAR2(250)
 ,D_END DATE
 )

and here's the query that raises the problem:

SELECT D.*, V.*
FROM
    DOCUMENT D
    left join DOCUMENT_VERSION V on (D.K_DOCUMENT = V.E_DOC_K_DOCUMENT)
WHERE
    D.K_DOCUMENT='14'
    AND (1=hasPermOnDocument(
    D.K_DOCUMENT, 
    D.E_DOC_TYP_K_DOCUMENT_TYPE,
    to_number('1'), 
    to_number('2'),
    to_date('15/11/2002','dd/mm/yyyy'), 
    to_number('1'))) 
ORDER BY
D.K_DOCUMENT DESC

The function is quiet complex: it checks permissions of users on documents, reading 
from various tables and performing nested calls.
Executing the statement from SQL studio i get the error        "General error;-8 
POS(1) Execution failed, parse again."

If I change the query in the follwing way (without changing the meaning):

SELECT D.*, V.*
FROM
    DOCUMENT D
    left join DOCUMENT_VERSION V on (D.K_DOCUMENT = V.E_DOC_K_DOCUMENT)
WHERE
D.K_DOCUMENT IN 
    (SELECT K_DOCUMENT
    FROM DOCUMENT
    WHERE
        K_DOCUMENT='14'
        AND (1=hasPermOnDocument(
                        K_DOCUMENT, 
                        E_DOC_TYP_K_DOCUMENT_TYPE,
                        to_number('1'), 
                        to_number('2'),
                        to_date('15/11/2002','dd/mm/yyyy'), 
                        to_number('1'))) )
ORDER BY
D.K_DOCUMENT DESC

...it works!

But if I remove the condition "K_DOCUMENT='14'" from the second query, even this one 
fails with error "General error;-602 POS(1) System error: Move error.".
The quries are simplified version of the one used in application, and I cannot always 
use the workaround i found.


It is a known problem, or do you need more information about it? (vtrace or something 
else).

Thanks in advace
Fabio Pinotti