row_count() blues

MySQL’s row_count(), I think, should be in every MySQL developer’s toolbox. I’ve been happily using it with MySQL 5.1. Then some odd things started happening with my newly upgraded CentOS 7 box, and I investigated, of course with MySQL Workbench. But I got totally confused along the way. After many WTF’s, I finally found out what the problem was. Well, it was my fault, to be exact, for not reading the docs very carefully.

My project uses C# and for MySQL Connector/Net. Somehow the same insert statement seemed to give different results for my C# program and for MySQL Workbench test. Why? Let’s read the docs carefully.

Originally I read 5.1’s row_count(). Then for 5.5, the behavior changed drastically for certain cases:

If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values” Which means that CLINET_FOUND_ROWS is not default. Indeed that was the case when I experimented with MySQL Workbench.

Well, only if you specify the flag, which for backward compatibility should be off by default.

Now I searched for an equivalent option for MySQL Connector/net. Of course it should be in the connection string, and there it was

Use Affected Rows, UseAffectedRows
default: false
When true, the connection reports changed rows instead of found rows. This option was added in Connector/Net version 5.2.6.

I first glanced over it, and repeated my WTF’s for about ten minutes. Wait, what? The default is false? The default is as if the flag CLIENT_FOUND_ROWS is given? The default is NOT compatible with 5.1’s behavior?

Why?

MySQL’s Assignment Operator and Queries

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.