Yes, exactly. That was also the point that Maxim made with his clever statement, “consider two infinities equal to each other” – in other words, divide by 0 and you obtain infinity.

I believe that Maxim stated it as such to give people a little more time to think about the problem and how it might be a larger statement related to Oracle. I don’t think that we ever reached the part of the larger statement about Oracle Database. Maybe something like the following:
* If you break the commonly accepted rules, you could see impossible results.
* A vertical 8 and a horizontal 8 (the symbol for infinity) are not the same thing – using information out of context could lead to significant problems.
* Even though a 0 and an empty set symbol ( Ø ) look similar, the two have very different meanings. I believe that someone tried to use this logic to prove that Oracle supports multiple block sizes for performance because it supports the KEEP and RECYCLE pools. 🙂
* If you set out to prove something, make certain that you are proving what you think that you are proving.

Any other ideas about the larger meaning in the context of Oracle Database?

]]>By: Troyhttps://hoopercharles.wordpress.com/2009/12/25/proving-that-12-is-oracle-wrong-to-short-circuit-an-execution-plan/#comment-3531
Thu, 23 Jun 2011 00:38:07 +0000http://hoopercharles.wordpress.com/?p=480#comment-3531The only reason No. 6 is true is because of the fact that a==b or (a-b)==0; we cannot divide both sides by (a-b) or 0 to get No. 7.
x*0==y*0 does not prove x==y.
]]>By: Charles Hooperhttps://hoopercharles.wordpress.com/2009/12/25/proving-that-12-is-oracle-wrong-to-short-circuit-an-execution-plan/#comment-88
Sat, 02 Jan 2010 12:38:48 +0000http://hoopercharles.wordpress.com/?p=480#comment-88I did not want to spoil the fun for everyone else – Maxim is correct regarding the proof.

If a=b then (a-b)=0, so in step 7 the proof is essentially dividing by 0. I remember the first time I saw the proof – I paused at step 6, trying to make certain that the change on the left side of the equation [a^2 – b^2 to (a + b) * (a – b)] was permitted/legal – that the two were equivalent. After verifying that the professor did not pull a fast one at that step, I moved on to the next step, competely losing sight of the assumption at the start of the proof that a=b. So, it was not a basic algebra equation that brought down the proof, but something more basic – dividing by 0 is not permitted (as the divisor approaches 0, the equation result grows infinitely large, as pointed out by Maxim).

I wonder if there is a deeper lesson in the proof that may be applied to Oracle databases, keeping in mind the description of mathematical proofs provided by the paper on the math.harvard.edu website. Mathematical type proofs are quite helpful for describing Oracle behavior, as long as the person preparing the proof does not ignore simple rules. Maybe the lesson is even deeper yet?

Incidentally, those are interesting queries and results. Here is what a portion of a 10053 trace file has to say about the first query:

Notice in the above that the query optimizer has completely eliminated the WHERE clause in the first query based on the output of the “Predicate Information” section of the plan. Note also how Oracle transformed the query that was submitted.

The second query produced the same type of plan that I showed in this blog entry. Note that Oracle also transformed the second SQL statement.

I think btw, it could be interesting as well to show, how Oracle produce “wrong” results in queries like
select dummy from dual where 3*(1/3) = 1;
seems to be well known fact about numbers precision, but people tend to forget about it again and again …
Best regards