Outer Joins & Limits w/BrioQuery?

My organization has recently started using BrioQuery as a front-end tool to access an Oracle 7.3 database.

I have found it strange the way that Brio handles outer joins combined with limits. I thought I was seeing a bug, and reported it to Brio, but was told this behavior was correct, standard Oracle SQL.

I realize that I may very well not be understanding some aspect of the way that outer joins are supposed to work, but I can't seem to find any sensible logic in the way Brio does this. Let me give an example:

Table/Topic ALUMNI looks like this:

EXTRACT_DATE DATE

CAMPUS        VARCHAR2(4)
IDENTIFIER    VARCHAR2(9)
NAME          VARCHAR2(30)

The ALUMNI table contains 5,000 rows.

Table/Topic ALUMNI_DEGREE looks like this:

EXTRACT_DATE DATE

CAMPUS        VARCHAR2(4)
IDENTIFIER    VARCHAR2(9)
DEGREE        VARCHAR2(8)
YEAR_AWARDED  VARCHAR2(4)

The ALUMNI_DEGREE table contains 10,000 rows, but only 100 rows where YEAR_AWARDED=1996. There is a one-to-many relationship between ALUMNI and ALUMNI_DEGREE. The two tables are related based on EXTRACT_DATE, CAMPUS, and IDENTIFIER. I build that join in Brio, connecting the two topics by EXTRACT_DATE, CAMPUS, and IDENTIFIER, and I set the Join Type for those three connections to 'Left' (i.e., "Retrieve all rows from ALUMNI and those rows from ALUMNI_DEGREE which have matching joined column values.").

If I do a query, putting NAME, DEGREE, and YEAR_AWARDED on the Request line, and I build a Limit where YEAR_AWARDED=1996, then the results include 5,000 rows; 4,900 of them show blank for DEGREE and YEAR_AWARDED. I would expect the Limit of YEAR_AWARDED=1996 to have eliminated all those nulls. If I hadn't given the Limit, then I would expect to see the nulls (becuase of the Left join).

When I export the SQL, it looks like this:

SELECT AL1.NAME, AL4.DEGREE, AL4.YEAR_AWARDED FROM DSSDBA.ALUMNI_VIEW AL1, DSSDBA.ALUMNI_DEGREE_VIEW AL4 WHERE (AL1.EXTRACT_DATE=AL4.EXTRACT_DATE(+) AND AL1.CAMPUS=AL4.CAMPUS(+) AND AL1.IDENTIFIER=AL4.IDENTIFIER(+)) AND AL4.YEAR_AWARDED(+)='1996' Notice the plus sign near the end of the query in the "AND AL4.YEAR_AWARDED(+)='1996'" part. It seems to me that this plus sign should not be there.

If I do the same query, but also add in a Limit where YEAR_AWARDED is not NULL, then I get the results that I would have expected from the previous query; 100 rows.

The manager of tech support at Brio has told me that a few years ago, their product used to generate the SQL the way I would expect (without the plus sign on the limit), but power-Oracle-SQL users at University of California at Davis told Brio that they were using incorrect SQL by NOT adding the (+) to the limit. They decided to change it so that it did put the plus in for limits. Futhermore, he tells me that they contacted Oracle prior to making this change, and this was what they recommended in 1995.

I would really like to hear what other people have to say about this. Received on Mon Sep 08 1997 - 00:00:00 CDT