Re: New 8.1.6 bug of the day

Bullshit. The columns are fully qualified by their table names, and as you can calculate by yourself the result MUST be 1 - no matter what indexes or what optimizer settings you chose. This is simple ANSI defined behaviour.

I tested with 8.1.5 under NT 4.0 and 8.1.5 under Linux SuSE 6.3. Same shit happens when the query is executed with rule based or all_rows. first_rows hint works fine.

So I am pretty sure this is a really severe bug as Oracle failed to implement the optimizer properly. As the name says, an optimizer should optimize a query, not damage the result. If the parser were wrong, first_rows hint would not yield the correct result.

I call this a catastrophe because you can no longer trust the results of a simple three-table join, not even in good old RBO.

By the way, it is not important if the tables are analyzed or not. I checked both variations.

So please propagate this to Oracle with highest priority.

Martin

DNP wrote:
>
> Here's my guess to the cause of the original problem :
>
> (quoting from the original SQL :)
>
> SELECT TABLE1.F1, TABLE1.F2, TABLE2.F1, TABLE2.F2, TABLE2.F3
> FROM TABLE3, TABLE2, TABLE1
> WHERE TABLE1.F1='test1'
> AND TABLE1.F1=TABLE2.F3 .. and one more line (ommitted)
> | | | |
> | | | |
> | | | |
> ^^^^^^^^^ | -- selection
> ^^^^^^^^^ -- join
>
> leading to the combination (because of the first AND)
> of :
>
> | |
> ^^^^^^^^^^^^^^^ * which is indeterminate.
>
> or to put it another way, if you're limiting TABLE1.F1 to a particular
> value then
> it seems more logical to do EXACTLY the same thing with TABLE2.F3.
>
> Otherwise you're rightly going to confuse the parser because it doesn't
> always know if you want to do a join on TABLE.F1 and then something else
> or if you just want to do a straight-forward selection based on a
> string literal.
>
> It's not so much Oracle's parser being wrong but that you're not talking
> to it in a form it understands properly.
>
> Fair enough though, it IS only a computer program after all ;-)
>
> P.S. I don't much care for the subject title of this thread but I'm
> loathe to change it as this confuses some newsgroup client software.
> Anyway I'm sure you get my hint.
>
> David P.
>
> Oracle Certified DBA.
>
> ===============================================================================
Received on Sun May 14 2000 - 00:00:00 CDT