Forum Stats

  • 3,854,962 Users
  • 2,264,441 Discussions
  • 7,905,847 Comments

Discussions

Outer join condition bug/feature when using an OR?

659583
659583 Member Posts: 5
edited Sep 15, 2008 2:18PM in Discoverer
I first picked up on this bug/feature in an article at he Armstrong blog ( [http://learndiscoverer.blogspot.com/2008/05/using-case-to-solver-outer-join-issues.html] )

It seems that when you use an OR condition in Disco Plus 10g it ignores the outer join conditions on the table...

I have included the SQL from a couple reports to illustrate my point:

Here is the SQL generated when I join the views, notice the outer join(i.e. "( + )")
SELECT O100288.UNIQUE_ID, O100291.UNIQUE_ID
FROM ODSTHING.TABLE1 O100288, ODSTHING.TABLE2 O100291
WHERE ( ( O100291.UNIQUE_ID = O100288.UNIQUE_ID(+) ) )
;

Here is what happens if I simply add a condition for is not null. Notice the outer join on the "IS NOT NULL" that makes it do nothing
SELECT O100288.UNIQUE_ID, O100291.UNIQUE_ID
FROM ODSTHING.TABLE1 O100288, ODSTHING.TABLE2 O100291
WHERE ( ( O100291.UNIQUE_ID = O100288.UNIQUE_ID(+) ) ) AND ( O100288.UNIQUE_ID(+) IS NOT NULL )
;

Here is what happens when I AND the same condition with itself, it stays outer joined
SELECT O100288.UNIQUE_ID, O100291.UNIQUE_ID
FROM ODSTHING.TABLE1 O100288, ODSTHING.TABLE2 O100291
WHERE ( ( O100291.UNIQUE_ID = O100288.UNIQUE_ID(+) ) ) AND ( ( ( O100288.UNIQUE_ID(+) IS NOT NULL AND O100288.UNIQUE_ID(+) IS NOT NULL ) ) )
;+

Here is what happens if I OR the same condition with itself. MAGIC!!! The outer join disappears!!
SELECT O100288.UNIQUE_ID, O100291.UNIQUE_ID
FROM ODSTHING.TABLE1 O100288, ODSTHING.TABLE2 O100291
WHERE ( ( O100291.UNIQUE_ID = O100288.UNIQUE_ID(+) ) ) AND ( ( ( O100288.UNIQUE_ID IS NOT NULL OR O100288.UNIQUE_ID IS NOT NULL ) ) )
;

Is this the way it is supposed to work?????? If it is then I see no sign of it in the documentation ( [http://download.oracle.com/docs/html/B13915_04/conditions.htm] )

Is this just a useful bug?

What do people think about using it?

Edited by: user4404031 on Sep 12, 2008 8:23 AM
Tagged:

Best Answer

  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Answer ✓
    Hi,

    It is documented (http://download.oracle.com/docs/html/B13916_04/conditions.htm#sthref854) that Discoverer does not use outer joins with IN and OR conditions. But the implications of this are not spelt out. The documentation never tries to explain the underlying SQL.

    Rod West

Answers

  • 659583
    659583 Member Posts: 5
    edited Sep 12, 2008 11:25AM
    Oops, extra post

    Edited by: user4404031 on Sep 12, 2008 8:25 AM
  • Michael Armstrong-Smith
    Michael Armstrong-Smith Member Posts: 4,403
    edited Sep 12, 2008 4:25PM
    Hi
    This is Michael. You posed the same question on my blog. I was going to reply there but seeing as you raised it here as well, let me answer you here. First of all, I'm not sure why you keep saying this is a bug. If you are convinced this is a bug then you should raise it with Oracle Support.

    As I have commented before, I personally do not think this is a bug and I think Discoverer is very clever. Oracle wrote some special logic code around Boolean operators that enable Discoverer to do things other tools cannot. For example, you might think that having two separate conditions, one of which is an analytic, is the same as having a single Boolean condition using an AND clause between the two components.

    This is wrong. Normally, the method of processing is to run non-analytic conditions first to filter the data, get the data, then run the analytics. If you use a Boolean AND the processing becomes: use the non-Boolean conditions to filter the data, get the data, run the analytic part of the Boolean and then process the standard condition, then process any other analytics.

    There is a presentation on my downloads page (http://learndiscoverer.com/downloads/downloads.htm) called Calculations. If you download that and take a look at the section called Sequencing towards the end of the presentation you will see how Boolean logic can change a query. Here is my statement from the presentation:

    There are a number of benefits to using Discoverer to create reports using analytic functions.

    The Oracle database does not support analytic functions directly referencing other analytic functions (nesting) or directly applying conditions to the query (e.g. only show Top N products).

    To achieve this in a SQL statement requires the creation of inline views.

    Discoverer removes this layer of complexity from the end user by breaking down the query into it’s components and resolving such conflicts by creating the appropriate inline views

    So this is why I don't believe you are seeing a bug. Oracle deliberately wrote the Boolean logic to work with inline views, hence we can achieve the amazing results you are seeing. Just because you don't see reference to this inside the official documentation does not mean you can imply that this is a bug. The reason why folks like me take the time to write blogs is so that we can pass on useful tips which don't necessarilly make it into the official documentation.

    Finally, may I add some advice. Posing a question to a forum / blog and then reposting the same question to another forum is considered spamming in some quarters. It would have been nice if you had at least given me the chance to answer your original question on my blog before you posted it here. I'm not mad at you, just saying that there is a protocol to follow here.

    Does my posting help?

    Best wishes
    Michael
  • 659583
    659583 Member Posts: 5
    No, sorry your post doesn't help. As you can see from the SQL I posted, there are no analytic functions involved here.

    To try to make this clearer to you if I go into Discoverer and put in "UNIQUE_ID IS NOT NULL" and the AND that to "UNIQUE_ID(+) IS NOT NULL" it generates the following SQL "( ( ( O100288.UNIQUE_ID(+) IS NOT NULL AND O100288.UNIQUE_ID(+) IS NOT NULL ) ) )". If I go in and change nothing but that AND to an OR it changes the SQL to the following "( ( ( O100288.UNIQUE_ID IS NOT NULL OR O100288.UNIQUE_ID IS NOT NULL ) ) )"

    I'm beginning to think you don't use SQL very often Micheal...

    Anyhoo, I thought this would be the best place to bring my problem since analysts generally surf their company's official message boards. I have never tried to file a bug with Oracle before.
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Hi,

    This is not a bug, it is how Discoverer is supposed to work. By default Discoverer automatically outer joins conditions where the join between the folders has been specified as an outer join in the EUL. But Oracle SQL will not allow you to use an outer join with an OR or an IN condition, e.g. col1(+) IN ('A', 'B'). You will get the database exception "ORA-01719: outer join operator (+) not allowed in operand of OR or IN" if you try to include outer joins with an IN or OR. This SQL is not allowed by Oracle because it is thought to be ambiguous so to avoid these exceptions Discoverer does not add the outer join when an OR or IN is used in the condition.

    This does have some unexpected side effects. For example, a condition using IN (col IN ('A') ) can have a different result from a condition using = (col = 'A' ) because an outer join will be used with the = but not the IN. There are usually workarounds for the unwanted side effects.

    As Michael pointed out, it has the benefit that if you don't want an outer join to be used with a condition then OR'ing the condition to itself is the solution.

    Rod West
  • 659583
    659583 Member Posts: 5
    It certainly seems like you are correct Rod, but is this documented anywhere? I just can't believe that Oracle feels that this is not an important enough design decision to put into their documentation, as Michael has implied.

    Rod, where did you learn about OR dropping outer joins in Discoverer?

    The only place I ran across this was on Michael's blog, and in that posting he was claiming it was because of the way OR operators short-circuit with case statements...
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Answer ✓
    Hi,

    It is documented (http://download.oracle.com/docs/html/B13916_04/conditions.htm#sthref854) that Discoverer does not use outer joins with IN and OR conditions. But the implications of this are not spelt out. The documentation never tries to explain the underlying SQL.

    Rod West
  • Michael Armstrong-Smith
    Michael Armstrong-Smith Member Posts: 4,403
    edited Sep 15, 2008 1:15PM
    Hi
    I'm sorry you feel that my SQL experience is lacking. I hear your frustration but the truth is as I originally said, and confirmed by Rod, that this is mostly an undocumented, yet deliberate, feature of Discoverer. It does exactly the way you describe and it does it unashamedly and quite intentionally.

    Let me try an clarify one thing. In your most recent posting you say: The only place I ran across this was on Michael's blog, and in that posting he was claiming it was because of the way OR operators short-circuit with case statements...

    What I was getting at is not that the CASE statement causes the SQL to change but that using a CASE statement in combination with the OR feature we have been discussing allows Discoverer reports to be very flexible. We can make them handle both INNER and OUTER conditions at the same time without the need to create multiple joins between folders. This flexibility allows users to create reports that will obey an INNER-join rule even when the Administrator has set up an OUTER join between the folders. This in my opinion is one of Discoverer's powerful, and yes mainly undocumented, features which is what prompted me to make my original blog posting. The lack of official documentation is frustrating I know. Actually in a quirky sort of way I find this to be one of the things I like about Oracle. The fact that we can sometimes find undocumented (wanted) features makes for interesting discussions. The ones I don't like are those undocumented, unwanted features!

    I hope this helps
    Best wishes
    Michael
  • 659583
    659583 Member Posts: 5
    edited Sep 15, 2008 2:18PM
    Thanks Rod, that page was exactly what I was looking for.

    I'm going to try to flip the DisableAutoOuterJoinsOnFilters feature on, which I think will make our users much happier than using this OR/IN workaround as it will make the tool a lot more intuitive. Technically the OR/IN-workaround is less limiting, but if we have a real power-user we can always import SQL with Disco Desktop.

    If I had known that you can't use the ( + ) syntax inside an OR condition then this would have all made more sense from the start. I generally don't use the ( + ) syntax for joining, I prefer the OUTER JOIN / ON syntax which allows ORing.

    I imagine the fact that using a ( + ) inside an OR is impossible is why the Discoverer developers didn't write much documentation on this; generally the documentation is pretty good. The problem is that a LOT of people don't use the ( + ) syntax and are going to be taken off-guard by that limitation. Even if the user knows nothing about SQL, changing an AND to an OR and getting a lot less data back is going to be very confusing.

    Edited by: gabebear on Sep 15, 2008 11:17 AM
This discussion has been closed.