The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I have a procedure that contains the following code that works perfectly in ISQL. Essentially, I SET a random number to each record, then SET another value (panel_code) based on that random number. It works great... with one pass through the table.

    UPDATE cheetah.mailing_pool mp
        SET 
            random_nbr = dba.f_rand(record_id) , 
            panel_code =  
            ( CASE 
              WHEN random_nbr >= @panel_a_lower_threshold and random_nbr <  @panel_b_lower_threshold   then 'A'
              WHEN random_nbr >= @panel_b_lower_threshold and random_nbr <  @panel_c_lower_threshold   then 'B'
              WHEN random_nbr >= @panel_c_lower_threshold and random_nbr <  @panel_d_lower_threshold   then 'C'
              WHEN random_nbr >= @panel_d_lower_threshold and random_nbr <= 1   then 'D'

ELSE '!!!'  -- error, this shouldn't happen
              END )
        WHERE mp.mailing_id = @mailing_id;

However, the problem is that when I run it in my application, it fails. The table is updated with a reasonable random number, but the CASE expression drop through to the ELSE every time.

So I tried this...

    UPDATE cheetah.mailing_pool mp
        SET 
            random_nbr = dba.f_rand(record_id)     
     WHERE mp.mailing_id = @mailing_id;

UPDATE cheetah.mailing_pool mp
        SET 
            panel_code =  
            ( CASE 
              WHEN random_nbr >= @panel_a_lower_threshold and random_nbr <  @panel_b_lower_threshold   then 'A'
              WHEN random_nbr >= @panel_b_lower_threshold and random_nbr <  @panel_c_lower_threshold   then 'B'
              WHEN random_nbr >= @panel_c_lower_threshold and random_nbr <  @panel_d_lower_threshold   then 'C'
              WHEN random_nbr >= @panel_d_lower_threshold and random_nbr <= 1   then 'D'

ELSE '!!!'  -- error, this shouldn't happen
              END )
        WHERE mp.mailing_id = @mailing_id;

And guess what? It works perfectly every time. It never drops though to the ELSE. So what's different?

I've tested the input parameters -- I know all the variables have the same value in the code leading up to the UPDATE... so that left me looking for Connection differences.

The only thing different I can find is the Isolation level -- ISQL comes in at Isolation level 0, and the application (where the one-pass code doesn't work) uses Isolation level 1.

So.. is this a reasonable outcome of using Isolation level 1, or do I have a differnt problem.

Mods... please edit the question. I had no idea how to phrase the question!

Thanks!

I'm using 9.0.2.3850

asked 09 Apr '10, 20:50

Ron%20Hiner's gravatar image

Ron Hiner
880202427
accept rate: 9%


The issue is that the query is using the original (column) value of random_nbr in the case expression to determine which case sub-expression to use to assign to panel_code.

To do it in one pass, you need to do is compute the new set of random numbers and get your case expression to use the new random number for each row.

For example:

UPDATE cheetah.mailing_pool mp
   SET 
       random_nbr = new_rand,
       panel_code =  
            ( CASE 
              WHEN new_rand >= @panel_a_lower_threshold 
               and new_rand < panel_b_lower_threshold
              THEN 'A'
              WHEN new_rand >= @panel_b_lower_threshold
               and new_rand <  @panel_c_lower_threshold
              THEN 'B'
              WHEN new_rand >= @panel_c_lower_threshold
               and new_rand <  @panel_d_lower_threshold
              THEN'C'
              WHEN new_rand >= @panel_d_lower_threshold
               and new_rand <= 1
              THEN 'D'
              ELSE '!!!'  -- error, this shouldn't happen
              END )
  FROM ( select dba.f_rand(record_id) as new_rand,
                mailing_id
           from cheetah.mailing_pool
          where mailing_id = @mailing_id
       ) dt
 WHERE mp.mailing_id = dt.mailing_id
   and mp.mailing_id = @mailing_id;

The above may be more (or less) than what you need - i.e. I do not know how many rows in mailing_pool are going to be matched by your predicate mp.mailing_id = @mailing_id? If it matches more than one row, then you are going to need to add additional conditions between mp & dt so that each row gets a different random number from the derived table dt.

Note: I am assume that your f_rand function has been declared not deterministic so that it returns different random numbers for every row that is being updated? E.g.

CREATE FUNCTION f_rand()
RETURNS double
NOT DETERMINISTIC
BEGIN
    RETURN rand();
END;

FWIW: I tested the above using

create table foo(
     i int default autoincrement,
     r double,
     s varchar(100),
     primary key( i )
);

insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
commit;

select * from foo;

update foo f
   set r = new_rand,
       s = ( case
         when new_rand >= 0 and new_rand < 0.5 then 'a'
         when new_rand >= 0.5 and new_rand < 0.75 then 'b'
         when new_rand >= 0.75 and new_rand <= 1 then 'c'
         else '!'
         end )
 from ( select f_rand() as new_rand, * from foo ) dt
  where dt.i = f.i

select * from foo;
permanent link

answered 09 Apr '10, 23:29

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262
accept rate: 40%

Thanks Mark -- you wrote "The issue is that the query is using the original (column) value of random_nbr in the case expression to determine which case sub-expression to use to assign to panel_code."

Does that behavior change with Isolation level? Is my diagnosis correct that it's the isolation level that determines if either the original value or the new value of random_nbr is used to process the CASE sub-expression?

(10 Apr '10, 14:44) Ron Hiner

@Ron: I do not believe that isolation level has any direct effect on the behaviour of how the expressions are evaluated. I tried my test (see bottom of post) with different isolation levels and did not see any difference in behaviour. I'm not sure why you saw a difference? Perhaps you ran your statement twice? Note that you need to be careful when using dbisql - depending on settings, it will sometimes run your statement twice under-the-covers. FWIW I used dbisqlc during my tests.

(10 Apr '10, 20:05) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×27
×10

question asked: 09 Apr '10, 20:50

question was seen: 724 times

last updated: 09 Apr '10, 23:29