PUZZLE

[julian]

To jmodic: I read your long explanation. It sounds reasonable. This is what I sort of suspected.

[Heath]

So who all thinks Jurij should post this at the Ask Tom webpage and see what Mr. Kyte has to say about this? :)

[chrisrlong]

Originally posted by jmodic

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

Hehe.

A very respectable length, if I do say so myself. You may indeed have the record now. Wear it proudly

So basically, Anti-Chris was right, eh? I'll have to listen to him more often

- Chris

[gd_1976]

HI Jmodic,
Sorry about the wrong interpretation about the null statements. I tried the Ist one even with an assignment statement. It stopped. I just included a DBMS_output statement and it went infinite(when set severoutput is on)
BEGIN
WHILE sysdate = sysdate LOOP
dbms_output.put_line('i am testing');
END LOOP;
END;
Can you please tell me the reason for this?
Thanks.
Gd_1976

[jmodic]

Originally posted by gd_1976
I tried the Ist one even with an assignment statement. It stopped. I just included a DBMS_output statement and it went infinite(when set severoutput is on)

I sincerely doubt it went infinite. What I think realy happened was either (depending on your DBMS_OUTPUT size and your "luck" on hitting a rare moment of FALSE evaluation):
a) your DBMS_OUTPUT buffer got filled up (its max size can be set to 1.000.000 bytes) before the loop was exited by WHILE condition, and it returned an error "ORA-20000: ORU-10027: buffer overflow, limit of NNNN bytes". But before you were able to see this ORA error it had to print out all 'i am testing' messages that fitted into the buffer, which itself can take quite a long time (couple of minutes)
b) WHILE condition returned FALSE before the buffer was filled, again the printing of 'i am testing' messages can take a long time, but at the end you should get "PL/SQL procedure successfully completed." message if you are testing this in SQL*Plus.

A better way of testing this would be to increment a counter for each loop iteration, and when loop actually finishes let the program print out the number of iterations. Here is a couple of runs I made on my home computer. As you can see the number of loops made each time is quite randomm, but the loop is definitely not infinite.

SQL> DECLARE
  2    i NUMBER := 0;
  3  BEGIN 
  4    WHILE sysdate = sysdate LOOP
  5      i := i+1;
  6    END LOOP; 
  7    dbms_output.put_line('Loop ended after ' || i || ' iterations'); 
  8  END; 
  9  /
Loop ended after 16750 iterations

PL/SQL procedure successfully completed.

SQL> /
Loop ended after 156681 iterations

PL/SQL procedure successfully completed.

SQL> /
Loop ended after 219569 iterations

PL/SQL procedure successfully completed.

SQL> /
Loop ended after 21362 iterations

PL/SQL procedure successfully completed.

[chrisrlong]

As a side note, the probability of actually 'crossing second boundaries' with 2 calls of SYSDATE is directly related to how fast your machine is. In other words, if your machine can only perform 5000 SYSDATE calls /second, then you have a greater chance per call of hitting the issue than if your machine can perform 500,000 SYSDATE calls/sec.

Now, while the probability per call is lower, the number of calls/sec is obviously correspondingly higher, so.....

...I would assume that you are still just as likely to hit the issue within a *given amount of time*. It is simply that you may run through *many* more iterations than Jurij before you hit the issue. If memory serves, his home machine is quite slow , so a screaming production box might get up to a million+ iterations before hitting the same issue.

Not that this means much, I just found it interesting and also figured it was going to be questioned sooner or later.

- Chris

[jmodic]

Chris is right about the influendce of your computer's CPU power on the probability of hitting the FALSE evaluation.

The following is not in any direct connection with his remark, but I think there might be of some benefit to someone if I explain a direct real life threat that this behaviour can impose if you are not aware of it. After all my loop examples were totaly artifitial, in real life no one will ever test the condition "IF SYSDATE = SYSDATE ...", yet alone make the exit of a loop depend on it's result. But it realy doesn't have to be such a direct comparison and it doesn't have to be in a loop to hit this nuisance!

In one of our application we had to find the diferent records that actually represented the same client and merge those client's data into one single record. The (simplified) bussines rule was:

1. find two records where the name and address of the client are the same
2. merge two records into one if
a.) both birthdays are the same
or
b) both birthdays are unknown (NULL)

So once we found the two records with the same name and address, we proceed to testing the birthdates. Because of the possibility of both birthdates being unknown, we used NVL() function in the test condition. And because we knew that no birthdate in the database can be equal to current timestamp, it was more than naturally that we use SYSDATE as our second parameter to the NVL() function. So our code was something like:

...
IF NVL(birthdateA, SYSDATE) = NVL(birthdateB, SYSDATE) THEN
merge_records;
END IF;
...

Now this NVL(birthdateA, SYSDATE) = NVL(birthdateB, SYSDATE) condition made my co-worker suspicious: do both NVL() realy return the same sysdate if both birthdates are null? So he made a test loop and found out the truth...... And yes, we changed our bad coding practice after that .

[Edited by jmodic on 09-06-2001 at 04:51 PM]

[sambavan]

Jurij its


One who knows, and knows that they know is a teacher - learn from them.


That was one great place one could make a mistake.


One who doesn't know, and knows that they don't know is a child - teach them.


I thought of pulling Chris' inspirations would give a better sense here!


Sam

[balajiyes]

the first query will exit's soon
i don't know Y?

ii i convert the first one a TO_CHAR(SYSDATE,'DD-MON-YY') = TO_CHAR(SYSDATE,'DD-MON-YY')
this is a infinite LOOP


am i write
revertback

[jmodic]

Originally posted by balajiyes

ii i convert the first one a TO_CHAR(SYSDATE,'DD-MON-YY') = TO_CHAR(SYSDATE,'DD-MON-YY')
this is a infinite LOOP

No, *theoreticaly* it is still not infinite. The only difference is that in this case the "second crossing event" can now only happen when the year cahnges. In this case on one side of the equation you'll have '31-DEC-xx' and on the other side it will be '01-JAN-xy'. *Much* less chance, but it *can* happen.

Leave it running for a couple of thousands/million years and you'll see, it will come out of the loop!

[Edited by jmodic on 09-07-2001 at 06:59 AM]