BLR of Stored procedure different though Sourcecode is equal [CORE4672]

[firebird-automations]

Submitted by: Helmut Steinberger (helste)

I have a stored procedure that has to return a result depending on paramaters passed to the SP. It allways returns 0. When I recompile the SP it returns a correct value. In the SP there is no computed field or other SP used.

I made a dataexport from the metadata before and after recompiling. So if you like I can send yo the files. I used this statement: select rdb$procedure_blr, rdb$procedure_source from rdb$procedures Then I got a lob file with the blr of the SP. I compared the files exported before and after the recompile and they are different. The ddl of the SP is the same.

That issue came up several times in the last few month and it is a little bit scary to see SPs working wrong because of wrong BLR data. I created the Sp a few weeks ago and it worked well. Suddenly it did not and I only had to recompile it to get it to work again.

I cannot send the whole databasem because it's more than 6GB. All I could do to show the issue is make a video clip.

So please take a look at this issue and tell me, if that is allready a known issue and what to do or what could be the reason for this problem. Kind regards Helmut Steinberger

[firebird-automations]

Commented by: @dyemanov

Downloading 6GB is not a problem, just make it available on some http/ftp. But before that, could you please attach different BLRs (before and after) in text representation?

[firebird-automations]

Commented by: Helmut Steinberger (helste)

I uploaded the 2 files that hold the blr before and after recompile.

here is the link. http://www.oe4hds.net/download/Demo.zip

I could make a backup and zip the fbk file and upload it.

The problem is, that it is a real data from a customer. So it's confidential.

I could try to reduce the database to just the necessary data by removing all unnecessary, but this could be a hard task, because of the cross references in the metadata.

I will do the best to provide you with all you need for finding this bug.

[firebird-automations]

Commented by: @asfernandes

What is this??

INSERT INTO RDB$PROCEDURES (RDB$PROCEDURE_BLR, RDB$PROCEDURE_SOURCE) VALUES (:h00000000_000004AA, :h000004AA_00000A9E);

And what you mean by 'recompile' ?

[firebird-automations]

Commented by: Helmut Steinberger (helste)

Sorry, this was the wrong file. I wanted to attach the .lob files.

I just uploaded the corect files. Please just use the same link.

[firebird-automations]

Commented by: Helmut Steinberger (helste)

The files wee created with the export function of IBExpert

[firebird-automations]

Commented by: @asfernandes

Please connect with isql and do:

set blob all; select rdb$procedure_blr, rdb$procedure_source from rdb$procedures where rdb$procedure_name = 'YOUR_PROCEDURE';

And save the BLR as text for us.

[firebird-automations]

Commented by: Helmut Steinberger (helste)

O.k., will do it, but will take some time. Before recompiling, I made a copy of the database file. Now I used this copy and there is no error. Very strange. 2 hours ago, I could reproduce it with just returning to the copy od the database. Probably it is a chaching issue? I don't think so, because restarting the firebird server did not change anything.

Now I have to restore from the origina fbk file. That is a longer task with this big file. So I try to get the error reproducable and then make the export of the blr.

I'll be back later this afternoon.

[firebird-automations]

Commented by: Helmut Steinberger (helste)

So, I got it to show the error again.

I connected to the db using isql. Then I did:

set blob all; set blobdisplay off; select rdb$procedure_blr, rdb$procedure_source from rdb$procedures where rdb$procedure_name = 'GET_ZUGANG_PREIS_VB_MAP'; blobdump 1a:a1a0 source.txt; blobdump 1a:a1a2 blr.txt;

I hope that's ok.

Just use the same link. I updated Demo.zip to hold the new files.

If that doesn't help, I will try to remove as much data as possible from the database and put it on my server so that you can download it and use it for testing.

[firebird-automations]

Commented by: @asfernandes

You didn't did what I want, the BLR as text, as I instructed you.

[firebird-automations]

Commented by: Helmut Steinberger (helste)

So, now the same problem as before. The error doesn't show up.

I have to try to get the error again.

At the end of this comment is what I got now. Hopefully that is what you are looking for. Only problem: at the moent the error doesn't show up. So I will wait for the restore of the original backup file to try it.

I think it must be some kind of caching issue. Now there is no error, no matter what database file I use. I guess, when I restart fbserver, the error will be back. Have to wait for the restore to finish, before I can restart the fbserver though.

So, here is the blr as text. Hopefully that's the right one.

RDB$PROCEDURE_BLR RDB$PROCEDURE_SOURCE ================= ==================== 1a:a1a0 1a:a1a2

RDB$PROCEDURE_BLR:
blr_version5, blr_begin, blr_message, 0, 6,0, blr_long, 0, blr_short, 0, blr_int64, 254, blr_short, 0, blr_int64, 254, blr_short, 0, blr_message, 1, 3,0, blr_int64, 254, blr_short, 0, blr_short, 0, blr_receive, 0, blr_begin, blr_declare, 0,0, blr_int64, 254, blr_assignment, blr_null, blr_variable, 0,0, blr_declare, 1,0, blr_short, 0, blr_assignment, blr_null, blr_variable, 1,0, blr_declare, 2,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 2,0, blr_declare, 3,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 3,0, blr_declare, 4,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 4,0, blr_declare, 5,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 5,0, blr_declare, 6,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 6,0, blr_declare, 7,0, blr_double, blr_assignment, blr_null, blr_variable, 7,0, blr_declare, 8,0, blr_double, blr_assignment, blr_null, blr_variable, 8,0, blr_declare, 9,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 9,0, blr_declare, 10,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 10,0, blr_declare, 11,0, blr_long, 0, blr_assignment, blr_null, blr_variable, 11,0, blr_declare, 12,0, blr_double, blr_assignment, blr_null, blr_variable, 12,0, blr_declare, 13,0, blr_double, blr_assignment, blr_null, blr_variable, 13,0, blr_stall, blr_label, 0, blr_begin, blr_begin, blr_assignment, blr_cast, blr_int64, 254, blr_value_if, blr_missing, blr_parameter2, 0, 2,0, 3,0, blr_literal, blr_long, 0, 0,0,0,0, blr_parameter2, 0, 2,0, 3,0, blr_parameter2, 0, 2,0, 3,0, blr_assignment, blr_cast, blr_int64, 254, blr_value_if, blr_missing, blr_parameter2, 0, 4,0, 5,0, blr_literal, blr_long, 0, 0,0,0,0, blr_parameter2, 0, 4,0, 5,0, blr_parameter2, 0, 4,0, 5,0, blr_assignment, blr_parameter2, 0, 2,0, 3,0, blr_variable, 0,0, blr_if, blr_and, blr_and, blr_not, blr_missing, blr_parameter2, 0, 0,0, 1,0, blr_gtr, blr_parameter2, 0, 2,0, 3,0, blr_literal, blr_long, 0, 0,0,0,0, blr_gtr, blr_parameter2, 0, 4,0, 5,0, blr_literal, blr_long, 0, 0,0,0,0, blr_begin, blr_begin, blr_assignment, blr_literal, blr_long, 0, 0,0,0,0, blr_variable, 1,0, blr_assignment, blr_parameter2, 0, 2,0, 3,0, blr_variable, 13,0, blr_for, blr_singular, blr_rse, 1, blr_rs_stream, 2, blr_relation, 3, 'M','A','P', 0, blr_relation, 3, 'M','A','T', 1, blr_boolean, blr_eql, blr_field, 1, 6, 'M','A','T','_','I','D', blr_field, 0, 6, 'M','A','T','_','I','D', blr_end, blr_boolean, blr_eql, blr_field, 0, 6, 'M','A','P','_','I','D', blr_parameter2, 0, 0,0, 1,0, blr_end, blr_begin, blr_assignment, blr_field, 1, 3, 'A','R','T', blr_variable, 2,0, blr_assignment, blr_field, 1, 7, 'S','U','B','_','A','R','T', blr_variable, 3,0, blr_assignment, blr_field, 0, 6, 'E','K','_','D','I','M', blr_variable, 4,0, blr_assignment, blr_field, 0, 9, 'E','K','_','P','R','_','D','I','M', blr_variable, 5,0, blr_assignment, blr_field, 0, 6, 'V','B','_','D','I','M', blr_variable, 6,0, blr_assignment, blr_field, 1, 5, 'E','K','2','V','B', blr_variable, 12,0, blr_assignment, blr_field, 1, 12, 'V','K','_','P','R','E','I','S','_','P','E','R', blr_variable, 11,0, blr_assignment, blr_cast, blr_int64, 254, blr_value_if, blr_missing, blr_field, 1, 6, 'N','R','_','G','E','W', blr_literal, blr_long, 0, 0,0,0,0, blr_field, 1, 6, 'N','R','_','G','E','W', blr_variable, 7,0, blr_assignment, blr_cast, blr_long, 254, blr_value_if, blr_missing, blr_field, 1, 7, 'G','E','W','I','C','H','T', blr_literal, blr_long, 0, 0,0,0,0, blr_field, 1, 7, 'G','E','W','I','C','H','T', blr_variable, 8,0, blr_assignment, blr_cast, blr_long, 0, blr_value_if, blr_missing, blr_field, 1, 6, 'B','R','E','I','T','E', blr_literal, blr_long, 0, 0,0,0,0, blr_field, 1, 6, 'B','R','E','I','T','E', blr_variable, 9,0, blr_assignment, blr_cast, blr_long, 0, blr_value_if, blr_missing, blr_field, 1, 5, 'H','O','E','H','E', blr_literal, blr_long, 0, 0,0,0,0, blr_field, 1, 5, 'H','O','E','H','E', blr_variable, 10,0, blr_end, blr_if, blr_gtr, blr_cast, blr_long, 0, blr_value_if, blr_missing, blr_variable, 11,0, blr_literal, blr_long, 0, 0,0,0,0, blr_variable, 11,0, blr_literal, blr_long, 0, 0,0,0,0, blr_begin, blr_begin, blr_if, blr_and, blr_and, blr_eql, blr_variable, 2,0, blr_literal, blr_long, 0, 0,0,0,0, blr_or, blr_or, blr_or, blr_eql, blr_variable, 4,0, blr_literal, blr_long, 0, 0,0,0,0, blr_eql, blr_variable, 4,0, blr_literal, blr_long, 0, 1,0,0,0, blr_eql, blr_variable, 4,0, blr_literal, blr_long, 0, 2,0,0,0, blr_eql, blr_variable, 4,0, blr_literal, blr_long, 0, 3,0,0,0, blr_or, blr_eql, blr_variable, 5,0, blr_literal, blr_long, 0, 5,0,0,0, blr_eql, blr_variable, 5,0, blr_literal, blr_long, 0, 6,0,0,0, blr_begin, blr_begin, blr_if, blr_eql, blr_variable, 5,0, blr_literal, blr_long, 0, 5,0,0,0, blr_begin, blr_begin, blr_if, blr_gtr, blr_variable, 7,0, blr_literal, blr_long, 0, 0,0,0,0, blr_assignment, blr_divide, blr_divide, blr_multiply, blr_multiply, blr_variable, 7,0, blr_variable, 11,0, blr_parameter2, 0, 2,0, 3,0, blr_parameter2, 0, 4,0, 5,0, blr_literal, blr_long, 251, 0,-31,-11,5, blr_variable, 13,0, blr_assignment, blr_divide, blr_divide, blr_multiply, blr_multiply, blr_multiply, blr_multiply, blr_variable, 9,0, blr_variable, 10,0, blr_variable, 8,0, blr_variable, 11,0, blr_parameter2, 0, 2,0, 3,0, blr_parameter2, 0, 4,0, 5,0, blr_literal, blr_int64, 251, 0,64,'z',16,-13,'Z',0,0, blr_variable, 13,0, blr_assignment, blr_literal, blr_long, 0, 1,0,0,0, blr_variable, 1,0, blr_end, blr_end, blr_end, blr_if, blr_eql, blr_variable, 5,0, blr_literal, blr_long, 0, 6,0,0,0, blr_begin, blr_begin, blr_assignment, blr_divide, blr_divide, blr_multiply, blr_multiply, blr_multiply, blr_variable, 9,0, blr_variable, 10,0, blr_variable, 11,0, blr_parameter2, 0, 2,0, 3,0, blr_parameter2, 0, 4,0, 5,0, blr_literal, blr_int64, 251, 0,-24,'v','H',23,0,0,0, blr_variable, 13,0, blr_assignment, blr_literal, blr _long, 0, 1,0,0,0, blr_variable, 1,0, blr_end, blr_end, blr_end, blr_end, blr_end, blr_end, blr_if, blr_and, blr_or, blr_or, blr_and, blr_and, blr_and, blr_eql, blr_variable, 2,0, blr_literal, blr_long, 0, 1,0,0,0, blr_eql, blr_variable, 3,0, blr_literal, blr_long, 0, 1,0,0,0, blr_eql, blr_variable, 4,0, blr_literal, blr_long, 0, 4,0,0,0, blr_neq, blr_variable, 6,0, blr_literal, blr_long, 0, 4,0,0,0, blr_and, blr_and, blr_or, blr_eql, blr_variable, 2,0, blr_literal, blr_long, 0, 2,0,0,0, blr_eql, blr_variable, 2,0, blr_literal, blr_long, 0, 3,0,0,0, blr_eql, blr_variable, 4,0, blr_literal, blr_long, 0, 12,0,0,0, blr_neq, blr_variable, 6,0, blr_literal, blr_long, 0, 12,0,0,0, blr_and, blr_or, blr_eql, blr_variable, 2,0, blr_literal, blr_long, 0, 4,0,0,0, blr_eql, blr_variable, 2,0, blr_literal, blr_long, 0, 5,0,0,0, blr_neq, blr_variable, 4,0, blr_variable, 6,0, blr_gtr, blr_variable, 12,0, blr_literal, blr_long, 0, 0,0,0,0, blr_begin, blr_begin, blr_assignment, blr_divide, blr_divide, blr_multiply, blr_parameter2, 0, 2,0, 3,0, blr_variable, 11,0, blr_variable, 12,0, blr_parameter2, 0, 4,0, 5,0, blr_variable, 13,0, blr_assignment, blr_literal, blr_long, 0, 1,0,0,0, blr_variable, 1,0, blr_end, blr_end, blr_end, blr_if, blr_and, blr_eql, blr_variable, 1,0, blr_literal, blr_long, 0, 0,0,0,0, blr_neq, blr_parameter2, 0, 4,0, 5,0, blr_variable, 11,0, blr_assignment, blr_multiply, blr_divide, blr_variable, 13,0, blr_parameter2, 0, 4,0, 5,0, blr_variable, 11,0, blr_variable, 0,0, blr_assignment, blr_variable, 13,0, blr_variable, 0,0, blr_end, blr_end, blr_end, blr_end, blr_end, blr_end, blr_begin, blr_send, 1, blr_begin, blr_assignment, blr_variable, 0,0, blr_parameter2, 1, 0,0, 1,0, blr_assignment, blr_literal, blr_short, 0, 1,0, blr_parameter, 1, 2,0, blr_end, blr_stall, blr_end, blr_end, blr_end, blr_end, blr_send, 1, blr_begin, blr_assignment, blr_variable, 0,0, blr_parameter2, 1, 0,0, 1,0, blr_assignment, blr_literal, blr_short, 0, 0,0, blr_parameter, 1, 2,0, blr_end, blr_end, blr_eoc

==============================================================================

RDB$PROCEDURE_SOURCE:
/* Zugangsmenge in vb_dim */

declare variable umgerechnet smallint;

declare variable art integer;

declare variable sub_art integer;

declare variable ek_dim integer;

declare variable ek_pr_dim integer;

declare variable vb_dim integer;

declare variable nr_gew double precision;

declare variable gewicht double precision;

declare variable breite integer;

declare variable hoehe integer;

declare variable vk_preis_per integer;

declare variable ek2vb double precision;

declare variable vk_preis double precision;

begin

preis = coalesce (:preis, 0);

per = coalesce (:per, 0);

ergebnis = :preis;

if (:map_id is not null and

:preis \> 0 and

:per \> 0\) then

begin

umgerechnet = 0;

vk_preis = :preis;

select mat.art, mat.sub_art, map.ek_dim, map.ek_pr_dim, map.vb_dim, mat.ek2vb, mat.vk_preis_per,

      coalesce \(mat\.nr\_gew, 0\), coalesce \(mat\.gewicht, 0\), coalesce \(mat\.breite, 0\), coalesce \(mat\.hoehe, 0\)

  from map

  inner join mat on \(mat\.mat\_id = map\.mat\_id\)

  where map\_id = :map\_id

  into :art, :sub\_art, :ek\_dim, :ek\_pr\_dim, :vb\_dim, :ek2vb, :vk\_preis\_per, :nr\_gew, :gewicht, :breite, :hoehe;

if (coalesce (:vk_preis_per, 0) > 0) then

  begin

  if \(:art = 0 and :ek\_dim in \(0, 1, 2, 3\) and :ek\_pr\_dim in \(5, 6\)\) then /\* Bogenpapier mit Preis kg, m² \*/

     begin

     if \(:ek\_pr\_dim = 5\) then  /\*  Bogen \-\> kg \*/

        begin

        if \(:nr\_gew \> 0\) then

           vk\_preis = :nr\_gew \* :vk\_preis\_per \* :preis / :per / 1000\.00000;

        else

           vk\_preis = :breite \* :hoehe \* :gewicht \* :vk\_preis\_per \* :preis / :per / 1000000000\.00000;

        umgerechnet = 1;

        end

     if \(:ek\_pr\_dim = 6\) then /\* Bogen \-\> m² \*/

        begin

        vk\_preis = :breite \* :hoehe \* :vk\_preis\_per \* :preis / :per / 1000000\.00000;

        umgerechnet = 1;

        end

     end

  if \(\(\(:art = 1 and sub\_art = 1 and :ek\_dim = 4 and :vb\_dim <\> 4\) or

       \(:art in \(2, 3\) and :ek\_dim = 12 and :vb\_dim <\> 12\) or

       \(:art in \(4, 5\) and :ek\_dim <\> :vb\_dim\)\) and :ek2vb \> 0\) then /\* Digital Rollenpapier in Rolle oder

                                                                        Farben und Lacke in Dosen oder

                                                                        Sonstiges Materialien \*/

     begin

     vk\_preis = :preis \* vk\_preis\_per / :ek2vb / :per;

     umgerechnet = 1;

     end

  if \(:umgerechnet = 0 and :per <\> :vk\_preis\_per\) then

     ergebnis = :vk\_preis / :per \* :vk\_preis\_per;

  else

     ergebnis = :vk\_preis;

  end

end

suspend;

end

[firebird-automations]

Commented by: @asfernandes

You said BLR changed, so we need to BLR listing, one of the wrong and one of the working copy.

[firebird-automations]

Commented by: Helmut Steinberger (helste)

I have a problem. After restore, the error does not exist any more.

The copy of the databasefile I made before recompiling the SP now also does not show the error. This is a very strange issue.

I hope to be able to get it in a state where the error is reproducable. Very difficult problem.

As soon as I got it, I'll be back here with some data.

[firebird-automations]

Commented by: Helmut Steinberger (helste)

o.k., that issue is driving me nuts.

After I could not reproduce it yesterday, today, when I run the SQL statement the error was back again. So I did an export of the blr as you instructed me yesterday.

After that, I wanted to recompile and make a after recompile export. To have the previous state of the database, I made a copy of the database file. After I copied the file, I wanted to recompile the SP, but before I run the SQL statement again, and surprise, surprise, it works well without the error.

So I think the blr will be the same. So this will not bring us any step nearer to the solution.

So it's not just a recompile thing. It's a "by chance" thing. I don't know when it works and when the error comes up, and that's the worst thing on that issue. I uploaded a screen print

There you see that I called the stored procedure with the same paramaters to the same database file twice. The first one was before I copied tha database file to safe it for further test and the second was after I copied it. But I did not use the copy. So all what happened between the 2 Queries was: Close the database, copy it, open it again.

Here we go: http://www.oe4hds.net/download/screenprint1.JPG

So please tell me, what I could do.

When just closing and opening a database influences the behavior of stored procedures, how confident could I be, that the database works as expected?

[firebird-automations]

Commented by: Helmut Steinberger (helste)

I am back with a note on this issue again. I did not work on that the last days. I am working on something totally different, but using the database where the issue occured. So today I opened IBExpert and in the SQL Editor there is still the Sztatment that I used to reproduce the issue. So I marked it and executed it. The result was, that the issue occured again. The Statment returned 0 as result. So I thought to me "great, you can reproduce it again." I closed the database and wanted to make a copy. Before making the copy, I reopened it, just to be sure, it is reproduceable. It is not. The statment returned the correct value. So I am very concerned about that. How can I rely on the stored procedure to prduce correct results, if it sometime does and sometime does not?

I really don't know what to do. Any thoughts from the experts?