on 2010 Apr 09 9:50 PM
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
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
@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.
User | Count |
---|---|
79 | |
10 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.