22 October 2020

Qualified Expressions Quite Puzzling - Part Three: Bug!

After some back and forth with Patrick Barel and Steven Feuerstein, it became clear that it most likely is a bug with Qualified Expressions.
A simple case to reproduce the bug is below

SQL> set serveroutput on


SQL> declare
  2     type tbl_t is table of pls_integer
  3        index by pls_integer;
  4     l_tbl tbl_t;
  5     l_idx pls_integer;
  6  begin
  7     for i in 1..3
  8     loop
  9        l_tbl := tbl_t (i => i);
 10     end loop;
 11     --
 12     l_idx := l_tbl.first;
 13     while l_idx is not null
 14     loop
 15        sys.dbms_output.put_line (to_char (l_idx)
 16                                 ||' - '||
 17                                 to_char (l_tbl(l_idx))
 18                                 );
 19        l_idx := l_tbl.next (l_idx);
 20     end loop;
 21  end;
 22  /


1 - 1
2 - 2
3 - 3


PL/SQL procedure successfully completed.
Why it is the above a bug? If you do the a similar assignment, but without a loop, the outcome is different (and most likely correct)
SQL> declare
  2     type tbl_t is table of pls_integer
  3        index by pls_integer;
  4     l_tbl tbl_t;
  5     l_idx pls_integer;
  6  begin
  7     l_tbl := tbl_t (1 => 1);
  8     l_tbl := tbl_t (2 => 2);
  9     l_tbl := tbl_t (3 => 3);
 10     --
 11     l_idx := l_tbl.first;
 12     while l_idx is not null
 13     loop
 14        sys.dbms_output.put_line (to_char (l_idx)
 15                                 ||' - '||
 16                                 to_char (l_tbl(l_idx))
 17                                 );
 18        l_idx := l_tbl.next (l_idx);
 19     end loop;
 20  end;
 21  /


3 - 3


PL/SQL procedure successfully completed.

Bug 32057533 - ASSIGNMENT TO COLLECTION USING QUALIFIED EXPRESSION APPENDS INSTEAD OF REPLACING.

21 October 2020

Qualified Expressions Quite Puzzling - Part Two

After I published my blog about Qualified Expressions, my dear friend Patrick Barel made a comment that my remarks about the Cursor For Loop was not at all what he expected to happen.

This is the code block that Patrick referred to.

 10     for r in (select * from t)
 11     loop
 12           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 13                                                    ,num2 => r.id));
 14     end loop;
  
with these results
1 - 1
2 - 2
3 - 3
   
A different way of looking at this code example might expect the outcome to be only one record, being the last one.
Each iteration would replace the complete Associative Array with a new instance of it.

So, there was another Puzzling thing in the original blogpost that I hadn't noticed before, probably because I wasn't looking for it. Why isn't the whole Associative Array replaced? It seems that this code would append values in the Associative Array and this was my objective. Strange indeed.
Anyhoo, probably the better way of working with Qualified Expressions to append values to the Associative Array, the index should be on the other side of the assignment:
 10     for r in (select * from t)
 11     loop
 12        l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 13                                         ,num2 => r.id);
 14     end loop;
   

When inserting the pointless conditions inside the For Loop, all values from the table are inserted into the Associative Array

 10     for r in (select * from t)
 11     loop
 12        if r.yes_no = 'Y'
 13        then
 14           l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 15                                            ,num2 => r.id);
 16        else
 17           l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 18                                            ,num2 => r.id);
 19        end if;
 20     end loop;
  
with all records from the table that get populated in the Associative Array:
1 - 1
2 - 2
3 - 3
   

20 October 2020

Qualified Expressions Quite Puzzling

Qualified expressions for PL/SQL were introduced in version 18 of the Oracle database. They provide a clearer way of working with collections. At the bottom of this post is the link to the official documentation.
However recently I encountered something that left me puzzled and I still can't explain the reason why. So this blogpost is just about my observations and I welcome any comments.

First a short demo of how you can work with Qualified Expressions.

  SQL> select banner
  2    from v$version;

BANNER 
___________________________________________________________________ 
Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production   
  
  SQL> set serveroutput on


SQL> declare
  2     type rec_t is record
  3         (num1 number
  4         ,num2 number);
  5     type tbl_t is table of rec_t
  6        index by pls_integer;
  7     l_tbl tbl_t;
  8     l_idx pls_integer;
  9  begin
 10     for i in 1..3
 11     loop
 12        l_tbl := tbl_t (i => rec_t (num1 => i
 13                                   ,num2 => i));
 14     end loop;
 15     --
 16     l_idx := l_tbl.first;
 17     while l_idx is not null
 18     loop
 19        sys.dbms_output.put_line (to_char (l_tbl(l_idx).num1)
 20                                 ||' - '||
 21                                 to_char (l_tbl(l_idx).num2)
 22                                 );
 23        l_idx := l_tbl.next (l_idx);
 24     end loop;
 25  end;
 26  /
   
On line 2 - 4 a Record Type is declared with two numeric attributes. Line 5 and 6 define an Associative Array of the Record Type.
The Qualified Expression is on lines 12 and 13.
In the loop we assign the loop index variable to the Record Type using the Qualified Expression syntax.
To view the content of the Associative Array, lines 16 through 24 are used. When running this block of code the output is:
1 - 1
2 - 2
3 - 3
   
No surprises here, it works as expected. Well, actually it is surprising take a look at the follow-up blog.

Cursor For Loop

The previous example used a Numeric For Loop, but I observed strange things when using a Cursor For Loop.

  SQL> drop table t purge
  2  /



Table T dropped.

SQL> create table t
  2  (id number
  3  ,yes_no varchar2(1)
  4  )
  5  /



Table T created.

SQL> insert into t values (1, 'Y');



1 row inserted.

SQL> insert into t values (2, 'N');



1 row inserted.

SQL> insert into t values (3, 'Y');



1 row inserted.

SQL> commit;



Commit complete.
  
The table contains three records with different Yes/No-indicators. Instead of showing the complete block of code I will only show the changes that I made using the previous example.
 10     for r in (select * from t)
 11     loop
 12           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 13                                                    ,num2 => r.id));
 14     end loop;
  
The Numeric For Loop is removed and a Cursor For Loop is inserted in the code block. Instead of using the index-variable from the first code example, I am using the current count of the Associative Array and add one to that (l_tbl.count + 1).
The results are the same as the first code block.
1 - 1
2 - 2
3 - 3
   

...but you said Strange Things?

Yes, I did... but so far, it seems normal.
The strange thing happens when a condition is added inside the For-Loop.

 10     for r in (select * from t)
 11     loop
 12        if r.yes_no = 'Y'
 13        then
 14           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 15                                                    ,num2 => r.id));
 16        else
 17           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 18                                                    ,num2 => r.id));
 19  
 20        end if;
 21     end loop;
  
The table has a Yes_No column and inside the For Loop, the value of this column is used.
When you examine the code above, you will notice that it will assign a value to the collection regardless of the value of the column. The assignment is exactly the same in lines 14-15 and 17-18. The content of the Associative Array after the loop should be the same as before, but this is not the case.
The result looks like this:
  1 - 1
  3 - 3
  
What happened to the second record?... Your guess is as good as mine.

I've tried several variations, like using a local counter to assign instead of the collection.count + 1, but that doesn't make a difference.
Like said before: I welcome your comments for more insight into this strange behavior.

The same is reproducable on Oracle 19...

  SQL> select banner 
  2    from v$version
  3  /

BANNER 
_________________________________________________________________________ 
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production  
  

Links