UDF in WHERE clause causes an odd execution plan

[Vlad Arkhipov]

CREATE TABLE GIS2.SERVICES (
   ID INTEGER NOT NULL DEFAULT SERIAL(1) PRIMARY KEY,
   CODE VARCHAR(10) NOT NULL UNIQUE
)
CREATE TABLE GIS2.SERVICE_DATA (
   ID INTEGER NOT NULL DEFAULT SERIAL(1) PRIMARY KEY
)

CREATE FUNCTION GIS2.GETSERVICEID (
   RESID CHAR(10))
RETURNS INTEGER
AS
   VAR ID INTEGER;

   TRY
       SELECT ID INTO :ID FROM GIS2.SERVICES WHERE CODE = :RESID;
   CATCH
       IF $rc <> 100 THEN STOP($rc, 'unexpected error')
       ELSE RETURN NULL;

   RETURN ID;

EXPLAIN
SELECT ID
FROM GIS2.SERVICE_DATA
WHERE ID = 1

OWNER     TABLENAME      COLUMN_OR_INDEX     
STRATEGY                                PAGECOUNT   
--------  -------------  ------------------  
--------------------------------------  ------------
GIS2      SERVICE_DATA                       EQUAL CONDITION FOR 
KEY                        542  
                         ID                       (USED KEY 
COLUMN)                              
          JDBC_CURSOR_4                         RESULT IS NOT COPIED , 
COSTVALUE IS           1  

EXPLAIN
SELECT ID
FROM GIS2.SERVICE_DATA
WHERE ID = GIS2.GETSERVICEID('12345')

OWNER     TABLENAME      COLUMN_OR_INDEX     
STRATEGY                                PAGECOUNT   
--------  -------------  ------------------  
--------------------------------------  ------------
GIS2      SERVICE_DATA                       TABLE 
SCAN                                      35  
          JDBC_CURSOR_2                         RESULT IS NOT COPIED , 
COSTVALUE IS          35  

Tested on MaxDB 7.6.00.16 - 016-123-109-428.

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]