I came upon the slide Programmatic Query by Shloml Noach. Shloml cleverly utilizes mysql’s case…when…end operator to sequentially execute assignment in query. I was excited since in many cases I need to retrieve columns at the same time as I update. But it doesn’t work very well at first. And along the way I found certain behaviors of mysql. I can roughly guess why mysql behaves in such a way.
This was the beginning:
set @c2:=1, @c1:=0;
select
case
when (@c3:=@c1+@c2) is null then null
when (@c1:=@c2) is null then null
when (@c2:=@c3) is null then null
else @c3
end
from
mysql.help_topic
limit 10;
which shows a Fibonacci sequence of course. Now here’s my task to which I’d like to apply this technique. I’d like to update a column c1 for a row with a given id, say 3, and to retrieve its column c2 at the same time.
I experimented a little bit with a puzzling result. So I tried to reduce the problem, and this was what I reached at:
set @tmp1=1, @tmp2=1;
select
case
when (@tmp1:=99) is null then null
when (@tmp2:=@tmp1) is null then null
else @tmp2
end;
select @tmp1, @tmp2;
Pretty simple, isn’t it? But the result was: 1 1. What? Somehow the two assignments inside case…when…end were not executed.
I’m not familiar with inner workings of mysql/mariadb. So I can only guess that mysql is smart enough to optimize the condition expressions away. My experiments with update and table seems to confirm this.
First, here’s the initial table setup:
drop table tbl_test if exists;
create table tbl_test (
id int(11) not null auto_increment,
c1 int(11) not null,
c2 int(11) not null,
primary key (id)
);
insert into tbl_test(c1, c2) values (1, 100), (2, 200), (3, 300);
Now update as follows:
set @tmp1:=99;
select c1 into @tmp2 from tbl_test where id=3;
update tbl_test
set c1 =
(case
when (@tmp1:=c1+1) is null then null
else @tmp1
end)
where id = 3;
select @tmp1, @tmp2, c1 from tbl_test where id=3;
which gives: 99 3 99. The assignment @tmp1:=c1+1 was not executed. Why? Because, I guess, c1 is not null and the condition in the first when clause is tautologically true. Even to mysql. Somehow mysql is smart enough to optimize this and the previous case away.
But it’s not so smart sometimes:
set @tmp1=1, @tmp2=2;
select
(case
when (@tmp1:=null) is not null then null
when (@tmp2:=@tmp1) is not null then null
else @tmp2
end);
select @tmp1, @tmp2;
gives: null null. ‘(@tmp1:=null) is not null’ is not tautologically false, at least to mysql.
So using ‘is null’ for obviously non-null expression, as suggested by Shloml doesn’t always work. I need another tautologically false expression attachable to almost any expression. What would that be?
This seems to work.
set @tmp1=1, @tmp2=1;
select ( case
when (@tmp1:=99) and false then false
when (@tmp2:=99) and false then false
else true
end );
select @tmp1, @tmp2;
‘and false,’ of course, guarantees that the result is always false.
Now for update, I can use:
set @tmp1=99;
select c1 into @tmp2 from tbl_test where id=3;
update tbl_test
set c1 =
(case
when (@tmp1:=c1+1) and false then false
when (@tmp2:=@tmp2*2) and false then false
when (@tmp3:=c2) and false then false
else 78
end)
where id = 3;
select @tmp1, @tmp2, @tmp3, c1 from tbl_test where id=3;
The result, as expected, is: 4 6 300 78.
case…when…end can be used with ‘and (case … else true end)’ in the where clause, as far as I can tell.
If anyone can find a counter-example, please let me know.