PUZZLE

[jmodic]

Originally posted by helpme
i think......

the 2nd 1 will just exit after 1 execution...
1st 1 will go on as an infinite loop

Think again.....

[helpme]

next guess

sysdate=sysdate will always be untrue....

[jmodic]

Originally posted by helpme
next guess

sysdate=sysdate will always be untrue....

Wrong again (at least with the current oracle date precision of 1 second).

[helpme]

one of reply of 1 of the programmers inmy firm

I think in case one.. it will go in endless loop

and in second case it will give an error..

because trying to fetch a NULL into date defined column...


and i duuno if he executed it b4.....

[jmodic]

Originally posted by helpme
and i duuno if he executed it b4.....

No, he didn't, as he is wrong...

[OCPIP]

In 1st case it is not an infinite loop. May be because Oracle assumes sysdate to be a variable on leftside and a function on right.

as per the def. of sysdate in standard package,

function sysdate return date is
d date;
begin
select sysdate into d from sys.dual;
return d;
end;

May be the same logic in the second question as well.

My Guess !!!
-OCP IP.

[chrisrlong]

Cool puzzle.

Okay, my thought would be that the first statement will either run forever or not at all. The second statement would run for a random amount of time.

I thought that PL/SQL only asked for SYSDATE once within a statement. Therefore, each time it hits the WHILE statement, it will ask for sysdate once and it will always equal itself.

In the second case, my theory is that SYSDATE will be calculated each time it is asked for because the SQL engine is now in charge. In such a case, both calls will almost always be equal. However, every once in a while, the calls will cross second boundaries and the loop will suddenly stop - almost randomly.

So am I completely off-base?

- Chris

[Victoria]

Since it is now clear that the 2nd case is an infinite loop, let me try this explanation ).

Oralce will substitute the values for all the bind variables and constants ( sysdate etc) before the query actually starts.
and I think the same sysdate ( from an internal oracle query)
will get substituted everywhere !!!!

[gd_1976]

HI Jmodic,
I think the 1st one is not infinite bcoz of the null statement. Null statement is a dummy statement
The 2nd one will go into infinite bcoz you are just assigning nul to a variable
Gd_1976

[Edited by gd_1976 on 09-05-2001 at 03:30 PM]

[jmodic]

Ok, now I realy think that I shall reveal what is the right answer and give (my own) explanation.

For all those who gave the wrong answers: My admirations, you all realy are sports. Obviously you've taken the challange in the most honest way, not even tried to run the code (as if you were, your answers obviously wouldn't be wrong!).

For all those who gave the correct answer: My congratulations. I myself would never give the correct one if I would given this same question half a year ago whan I was not yet aware of this behaviour. I would without a doubt claim that both cases are infinite loop.

So, the correct answer is:
The first case (with "WHILE sysdate = sysdate ...") *is not* an infinite loop, while the second case (with "... WHERE sysdate = sysdate ..." *is* an infinite loop. The result of "IF sysdate = sysdate" is most of the time TRUE, but from time to time it will result in FALSE! So the first time this happens the loop wil be exited and PL/SQL block will finish succesfully. On the other hand the condition "WHERE sydate = sysdate" in SQL is always TRUE, without an exception.

Before I try to explain the background of why this is happening, let me first give some comments to some of the latest answers that I haven't responded to yet.

Originally posted by OCPIP
In 1st case it is not an infinite loop. May be because Oracle assumes sysdate to be a variable on leftside and a function on right.
...SNIP...

Explanation is not exactly correct. SYDATE realy is a buit-in function, but as such it appears on both sides of the expression. But the point is that in PL/SQL condition sometimes two sysdate functions do not return the same value, while in SQL condition (WHERE) both SYDATE functions allways return the same value.

Originally posted by chrisrlong
...SNIP...
So am I completely off-base?

Yes, Chriss, completely off-base! A perfect example of how people with the highest reputation (no one excluded) in this forums can sometimes give completely wrong answers (when they play the game honestly, I would add). Exactly *all* of your answers are 100% wrong. If you would apply NOT operator to each and every one of your assumptions you would be the winner .

Originally posted by gd_1976
I think the 1st one is not infinite bcoz of the null statement. Null statement is a dummy statement.
The 2nd one will go into infinite bcoz you are just assigning nul to a variable

Both of your answers are correct, but both explanations are plain wrong. NULL statement in PL/SQL statement realy is a dummy statement, but perfectly valid one. In my example I used it only for simplicity reasons, so that all your attention would be focused on the IF condition. You can replace it with whatever PL/SQL code you want but the loop will still behave exactly the same (unless you force loop exit within your code). In the second case, the reason for infinite loop has nothing to do with the NULL value being assigned to the variable. Again you can replace this assigment with whatever you want, the behaviour will still be the same. It is the WHERE condition that causes this infinity.

Originally posted by Victoria
Oralce will substitute the values for all the bind variables and constants ( sysdate etc) before the query actually starts. And I think the same sysdate ( from an internal oracle query) will get substituted everywhere !!!!

Kudos, Victoria. Your explanation about how Oracle reacts in the second case is totaly correct. However we also must understand *why* Oracle *must* behave like that... And why it behaves differently in the first example....


So here comes my vision of why this is happening. And I must warn you that this realy only is *my* explanation - I haven't noticed this phenomenon explained (or even mentioned!) in any article or whitepaper.

As we already said, SYDATE is a simple built-in function. But it also have one particular property: it is nondeterministic function. A function is nondeterministic when two calls with the same arguments (SYDATE doesn't even take any arguments!) will not allways result in the same answer. In PL/SQL there is no reason why Oracle wouldn't call SYDATE function each time it encounters the SYSDATE call in the code. So in the expression "WHERE sysdate = sysdate LOOP ..." it makes two calls for this function. The function returns the result of type DATE with the smalest granularity being 1 second. So in most cases this two calls will be executed within the same second and will return the same result. But sooner or later there will come the case, where the first call will be executed at the very end of the second and the next one at the very begining of the next second. The returned results will differ for one second and the result of the expression will be FALSE, resulting in the termination of the loop.

But why dos this never happen in the second example? The true answer is: READ CONSISTENCY! (Haven't I been a little annoying lately with this read consistency mantra? ) In the second case we are evaluating the condition that should potentially cause the loop to be terminated *within an SQL statement*!!! And what does Oracle guarantie us for the duration of the query? It guaranties that everything it will use in a query and return as its result will be exactly the same as it was in the moment when query began. And this doesn't apply only to the changes that happens within database blocks, it aplies literaly to *everything*. SYSDATE is not read from any database block, so Oracle can't use rollback segments to assure read consistency for SYDATE as it does for block changes. In case of any ocurrance of SYSDATE within a query, it must first obrain the value of SYDATE *once* and and then apply it to each and every occurance of the SYSDATE.

Maybe a better example of the read consistency applied to a SYSDATE within SQL would be the following query. I'm sure all of use have encountered the following before, but have allways simply take it for granted, without giving it a second thought. Suppose I have a large table, 100.000 of records, and the full scan of that table takes more than one second. Lets say the following query takes 10 seconds:

SELECT * FROM my_table;

The following will also take 10 seconds:

SELECT sysdate, my_table.* FROM my_table;

If the SYDATE would be evaluated for every record, then the query would probably taken more than 10 seconds to complete. But that is not the main point or proof that SYSDATE is evaluated only once per query, not once per record returned. The proof is the following: if we inspect the returned values we will see that SYSDATE for all 100.000 records is exactly the same, although the last record was fetched 10 seconds after the first one.

Once more, not to be forgotten:
- In SQL read consistency is allways assured at statement level (it can also be assured at transaction level, but this is not by default)
- In PL/SQL there is no read consistency where it comes to calls to nondeterministic functions. We must be avare that there is no such read consistency in PL/SQL, not at the block level, not at statement level, not even at expression level.

Ugh, Chris, I think with this post I've bitten your "longest answer" record, don't you think?