Outer join/build-in functions bug

<mailto:[email protected]>DROP TABLE GIS2.Invests
/
CREATE TABLE GIS2.Invests(
  ID INTEGER NOT NULL PRIMARY KEY,
  VAL VARCHAR(10) NOT NULL
)
/
DROP TABLE GIS2.Revisions
/
CREATE TABLE GIS2.Revisions(
  INVEST_ID INTEGER,
  TO_DATE DATE,
  FOREIGN KEY (INVEST_ID)
    REFERENCES GIS2.Invests (ID)
)
/
INSERT INTO GIS2.Invests (ID, VAL)
VALUES (1, 'ABC')
/
INSERT INTO GIS2.Invests (ID, VAL)
VALUES (2, 'DEF')
/
INSERT INTO GIS2.Revisions (INVEST_ID, TO_DATE)
VALUES (1, '2000-01-01')
/
INSERT INTO GIS2.Revisions (INVEST_ID, TO_DATE)
VALUES (2, NULL)


SELECT i.VAL, SUBSTR(i.VAL, 1, 1)
FROM GIS2.Invests i,
     GIS2.Revisions r
WHERE i.ID = 2
      AND i.ID = r.INVEST_ID (+)
      AND (r.TO_DATE (+) IS NULL OR r.TO_DATE >= DATE)

 VAL     EXPRESSION1   
 ------  --------------
 DEF     D             

SELECT i.VAL
FROM GIS2.Invests i,
     GIS2.Revisions r
WHERE i.ID = 1
      AND i.ID = r.INVEST_ID (+)
      AND (r.TO_DATE (+) IS NULL OR r.TO_DATE >= DATE)

 VAL   
 ------
 ABC   

SELECT i.VAL, SUBSTR(i.VAL, 1, 1)
FROM GIS2.Invests i,
     GIS2.Revisions r
WHERE i.ID = 1
      AND i.ID = r.INVEST_ID (+)
      AND (r.TO_DATE (+) IS NULL OR r.TO_DATE >= DATE)


returns no records. You may change SUBSTR with any other function to get 
the same result.

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]