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.