|
Replies:
25
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
Aug 27, 2008 10:05 PM
Last Post By: Steve Cosner
|
|
|
Posts:
3,913
Registered:
08/11/98
|
|
|
|
Simple compare condition fails [Solved: Bug 4619731]
Posted:
Aug 15, 2008 4:01 PM
|
|
|
Edit: I have found the root of the problem, and reported it below. I have opened a Service Request 7045745.993 on the problem.
Original text:
I have encountered the most bizarre bug today. An associate is trying to debug a 1900 line package that was NOT terminating a loop properly.
The package has the code:
Exit when x y;
where x and y are VARCHAR2(8) variables.
The problem is that even though the values were UNequal, the loop never ended. We added dbms_output debugging code to display x and y, and we could easily see that their values were NOT equal.
For instance, one was 19824E1A and the other was 19824E2A. Or one was 19824E1A while the other was 99999999.
I even added a block of code re-testing the condition like this:
If x y then
dbms_output.put_line(x||''||y);
Else
dbms_output.put_line(x||'='||y);
End if;
But no matter what the values of x and y, it always reported them equal.
I found two workarounds:
1. concatenate a character (I used a period) to the end of each variable in the condition.
Exit when x||'.' y||'.'
2. Or, add an anonymous block before or after the Exit when..., such as:
Declare
v varchar2(2);
Begin
v := 'AB';
End;
Curiously, when I replaced the v := 'AB'; with NULL;, the bug came back.
This bug occurs on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
I am not sure where to start to try to solve or report this problem. It pretty much destabilizes the entire PL/SQL programming foundation for all our packages. It leaves me wondering where else the code may be failing.
Has anyone seen something like this reported before?
Edited by: Steve Cosner on Aug 27, 2008 10:05 PM |
|
|
Posts:
9,551
Registered:
10/15/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 15, 2008 4:47 PM
in response to: Steve Cosner
|
|
|
Wow. I have 11.1.0.6 and your code works here.
So if you cut/pasted this code exactly as is, it comes back '='?
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
declare
x varchar2(8);
y varchar2(8);
begin
x := '19824E1A';
y := '19824E2A';
If x y then
dbms_output.put_line(x||''||y);
Else
dbms_output.put_line(x||'='||y);
End if;
end;
/
19824E1A19824E2A
PL/SQL procedure successfully completed.
|
|
|
Posts:
3,913
Registered:
08/11/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 15, 2008 4:55 PM
in response to: SomeoneElse
|
|
|
|
> So if you cut/pasted this code exactly as is, it comes back '='?
No, that is part of the problem -- I created a simple test in an anonymous block and tried it in SQL Plus, and could not reproduce the problem.
Since adding the dummy block within the code caused the package and condition to work correctly, it appears to be related to the size of the compiled object or the location within the package.
I even added a block similar to yours below the EXIT WHEN condition, and THAT caused the EXIT WHEN to work. As soon as I removed the block, the EXIT WHEN failed again.
....go figure!
|
|
|
Posts:
9,551
Registered:
10/15/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 15, 2008 5:09 PM
in response to: Steve Cosner
|
|
|
|
Is there way you can create a reproducible test case?
Of course, you've already logged an SR with Oracle...
|
|
|
Posts:
3,913
Registered:
08/11/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 15, 2008 5:26 PM
in response to: SomeoneElse
|
|
|
|
Well, it's Friday evening, so no more work until Monday. And then it is my associate's issue, so not sure who will pursue it.
And don't want to log an SR until I can convert the package into something that I can compile and reproduce without any dependencies on our db tables. So a bunch of work will be involved. I'm waiting until Monday...
|
|
|
Posts:
3,439
Registered:
07/17/00
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 16, 2008 6:21 AM
in response to: Steve Cosner
|
|
|
There is no bug, just a complete misunderstanding of NULLs in Oracle. When comparing two values in PL/SQL you can get not two (TRUE or FALSE) but three outcomes - TRUE, FALSE, NULL (or UNKNOWN in SQL). You get NULL when at least one of the values is NULL. Now look at:
Exit when x y;
If either x or y is NULL,, x y will result in NULL and you will never exit the loop. Similarly:
If x y then
dbms_output.put_line(x||''||y);
Else
dbms_output.put_line(x||'='||y);
End if;
If either x or y is NULL,, x y will result in NULL, therefore ELSE branch of the IF statement is executed. That is why you get "Curiously, when I replaced the v := 'AB'; with NULL;, the bug came back". Bottom line - your EXIT WHEN statement does not account for NULLs. Change it to
Exit when nvl(x y,true);
and you should be OK:
SQL> declare
2 x varchar2(8);
3 y varchar2(8);
4
5 begin
6 x := '19824E1A';
7 y := '19824E2A';
8
9 If x y then
10 dbms_output.put_line(x||''||y);
11 Else
12 dbms_output.put_line(x||'='||y);
13 End if;
14 end;
15 /
19824E1A19824E2A
PL/SQL procedure successfully completed.
SQL> declare
2 x varchar2(8);
3 y varchar2(8);
4 begin
5 --x := '19824E1A';
6 y := '19824E2A';
7 If x y then
8 dbms_output.put_line(x||''||y);
9 Else
10 dbms_output.put_line(x||'='||y);
11 End if;
12 end;
13 /
=19824E2A
PL/SQL procedure successfully completed.
SQL> declare
2 x varchar2(8);
3 y varchar2(8);
4 begin
5 --x := '19824E1A';
6 y := '19824E2A';
7 If nvl(x y,true) then
8 dbms_output.put_line(x||''||y);
9 Else
10 dbms_output.put_line(x||'='||y);
11 End if;
12 end;
13 /
19824E2A
PL/SQL procedure successfully completed.
SQL> declare
2 x varchar2(8);
3 y varchar2(8);
4 begin
5 If nvl(x y,true) then
6 dbms_output.put_line(x||''||y);
7 Else
8 dbms_output.put_line(x||'='||y);
9 End if;
10 end;
11 /
PL/SQL procedure successfully completed.
SY. |
|
|
Posts:
3,913
Registered:
08/11/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 16, 2008 3:44 PM
in response to: Solomon Yakobson
|
|
|
|
SY, thank you for the lesson in Three Value Logic. However, I have been writing code for over 30 years, the last 10 with Oracle PL/SQL, so I am fully aware of the issues with null values, especially when using the Not Equal logical operator. I've even created my own function to return the text, "True", "False" or "Unknown" if I am debugging conditions where three value logic is an issue.
But in this case, BOTH variables always contain 8 alphanumeric characters. Note the two lines in my original post:
> one was 19824E1A and the other was 19824E2A. Or one was 19824E1A while the other was 99999999.
and
> But no matter what the values of x and y, it always reported them equal.
This is not an issue with either variable being null.
...by the way, Three Value Logic has been one of my pet peeves with Oracle. I am keenly aware of the pitfalls it creates, and wish they would come up with something like special operators (maybe something like .= and .) to instruct PL/SQL to treat null as a known entity. But I gave up on that quest years ago -- there is no point in rehashing the semantics of whether null is a known value. This thread is about a serious bug in PL/SQL processing, and not about three value logic.
|
|
|
Posts:
3,439
Registered:
07/17/00
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 16, 2008 4:57 PM
in response to: Steve Cosner
|
|
|
Hi Steve.
I did not mean to offend you in any way. Anyway, I still believe it is NULL issue and your workaround 1 suggests that. By concatenating dot to X and Y you make these expressions not null. Now your second workaround is puzzling.It declares variable V. Any chance you use X, Y, V just for simplified names of same two variables you are comparing in real code? If so, it could suggest scope issue. What I mean, is there a chance variable named X or/and Y was also declared in some inner block. Something like:
SQL> declare
2 x varchar2(8);
3 y varchar2(8);
4
5 begin
6 x := '19824E1A';
7 y := '19824E2A';
8 declare
9 y varchar2(8);
10 begin
11 If x y then
12 dbms_output.put_line(x||''||y);
13 Else
14 dbms_output.put_line(x||'='||y);
15 End if;
16 end;
17 end;
18 /
19824E1A=
PL/SQL procedure successfully completed.
SY. |
|
|
Posts:
3,913
Registered:
08/11/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 16, 2008 11:48 PM
in response to: Solomon Yakobson
|
|
|
No offense taken, SY. I was just trying to express how aware I am of the issue of comparing null values in condition statements.
All I can do here is underscore that it is not an issue with null values. The problem is with the statement in the first line. To prove that it was not null values, I coded the second line following the first:
exit when x y;
dbms_output.put_line('x='||x ||' and y='||y);
The output from the run showed in the first pass through the loop, x and y were equal and were not null. So the process ran correctly that time. But in subsequent passes through the loop, they were NOT equal and were not null, but the debugging code was executed.
There is no way the values were null.
I certainly wondered about the null issue when I concatenated a character to the variables in the conditional test. But my debugging lines first confirmed that they were not null. I suspect more that the bug has something to do with the size of the package and the variables being exactly eight characters, which is 64 bits, which has special significance in many computer languages.
Regarding your concern of the variable names and scope issues, the actual variable names in the actual code can be seen below (I just used x and y for simplicity within this thread):
LOOP
EXIT WHEN V_LAST_PERIOD
SIPK0003.COURSE_ADJUST_REC.ACAD_REC_PERIOD
while the silly anonymous block that caused the process to work, using the variable named v, was the actual code. There is no way we use simple one-character variable names in a 1900 line package. There is no way the scope of variable V extended outside the embedded anonymous block. My point was that somehow, changing the size of the compiled package object, or at least the size of the code generated for the loop process caused the package to run correctly. In fact, thinking about the
Declare V... Begin Null; End;
sequence, I'll bet the PL/SQL compiler is sharp enough to notice that it was a do-nothing block, so never included it in the compiled object. As soon as I assigned V a value, the code WAS included, so the size of the generated code for the loop changed, and somehow, that caused it to run correctly.
Regarding the use of the package.record.column value above, we even copied the value to a local variable, and then used the local variable in the Exit When statement. Doing that made no difference. |
|
|
Posts:
4,745
Registered:
05/13/99
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 17, 2008 1:38 AM
in response to: Steve Cosner
|
|
|
|
Could it be a problem of the plsql optimizer?
What value has the plsql_optimize_level parameter?
|
|
|
Posts:
3,913
Registered:
08/11/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 17, 2008 12:39 PM
in response to: Jens Petersen
|
|
|
|
> What value has the plsql_optimize_level parameter?
Is there some way I can find that without bothering a DBA? The server is off in another city. I can ask during work hours.
|
|
|
Posts:
3,439
Registered:
07/17/00
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 17, 2008 1:08 PM
in response to: Steve Cosner
|
|
|
Is there some way I can find that without bothering a DBA?
show parameter plsql_optimize_level
or
select value from v$parameter where name = 'plsql_optimize_level';
You would need SELECT on V$PARAMETER.
SY. |
|
|
Posts:
3,439
Registered:
07/17/00
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 17, 2008 1:14 PM
in response to: Solomon Yakobson
|
|
|
BTW, if you do not have SELECT on V$PARAMETER, you could do three tests, where N is 0,1,2:
alter session set plsql_optimize_level = N;
then compile and test the package.
SY. |
|
|
Posts:
420
Registered:
04/08/01
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 17, 2008 5:57 PM
in response to: Steve Cosner
|
|
|
|
Also try a
alter session set PLSQL_WARNINGS = 'ENABLE:ALL';
It may indicate something that the PL/SQL optimizer is picking up on.
I suspect the PL/SQL optimizer as it is new so you may have found something that confounded it and which may not reproduce in a simplified test case.
"Curiously, when I replaced the v := 'AB'; with NULL;, the bug came back."
I'd guess the optimizer thinks the code is redundant and optimizing it away or it is rewriting it (maybe from a loop..end loop to a while/repeat loop) but is changing the logic somewhere. Maybe it thinks that x will always be the same as y (or that one/both will always be null).
Can you outline the logic by which the X and Y get assigned. I guess X is 'fixed' for the the duration of the loop and the Y is maybe initialized to X but changes in the loop.
|
|
|
Posts:
3,913
Registered:
08/11/98
|
|
|
|
Re: Simple compare condition fails
Posted:
Aug 17, 2008 10:58 PM
in response to: g.myers
|
|
|
|
Thanks for the help. plsql_optimize_level = 2.
I'll ask my associate to give the package test a try with level set to 0 and 1, and also will try setting PLSQL_Warnings.
|
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
