List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 28 1999 9:02pm
Subject:Problem of IF(), ISNULL(), and COUNT()
View as plain text  
>>>>> "churo" == churo  <churo@stripped> writes:

>> Description:
churo> It seems that IF() is not evaluating ISNULL() well
churo> when ISNULL() is evaluating an indexed and left-joined column,
churo>  and the returning expression is returned by COUNT().

>> How-To-Repeat:

churo> 1. When tr.pcode is not indexed:

mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
churo> FROM tp
churo> LEFT JOIN tr ON tp.pcode = tr.pcode
churo> GROUP BY tp.pcode;

churo> +---------+-------+
churo> | pcode   | count |
churo> +---------+-------+
churo> | kvw2000 |    28 |
churo> | kvw2001 |     0 |
churo> +---------+-------+ (as expected)

churo> 2. When tr.pcode is indexed:

mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
churo> FROM tp
churo> LEFT JOIN tr ON tp.pcode = tr.pcode
churo> GROUP BY tp.pcode;

churo> +---------+-------+
churo> | pcode   | count |
churo> +---------+-------+
churo> | kvw2000 |    28 |
churo> | kvw2001 |     1 |
churo> +---------+-------+ (not as expected)

churo> 3. When tr.pcode is indexed, but COUNT() is not used:

mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, 1) AS count
churo> FROM tp
churo> LEFT JOIN tr ON tp.pcode = tr.pcode
churo> GROUP BY tp.pcode;

churo> +---------+-------+
churo> | pcode   | count |
churo> +---------+-------+
churo> | kvw2000 |     1 |
churo> | kvw2001 |     0 |
churo> +---------+-------+ (as expected)

Hi!

I tried to simulate this, but I didn't succeed:

drop table if exists t1,t2;
create table t1 (a int not null);
create table t2 (a int not null, index(a));
insert into t1 values(1),(2),(3),(4);
insert into t2 values (2),(3);
explain select t1.a,if(isnull(t2.a),0,count(*)) from t1 left join t2 on t1.a=t2.a group by
t1.a;
select t1.a,if(isnull(t2.a),0,count(*)) from t1 left join t2 on t1.a=t2.a group by t1.a;

The last 2 lines returned:

explain select t1.a,if(isnull(t2.a),0,count(*)) from t1 left join t2 on t1.a=t2.a group by
t1.a
--------------

table   type    possible_keys   key     key_len ref     rows    Extra
t1      ALL     NULL    NULL    NULL    NULL    4
t2      index   a       a       4       NULL    2       Using index
--------------
select t1.a,if(isnull(t2.a),0,count(*)) from t1 left join t2 on t1.a=t2.a group by t1.a
--------------

a       if(isnull(t2.a),0,count(*))
1       0
2       1
3       1
4       0

and this looks ok to me.

Any change you can modify my test to get your behaveour ?

Regards,
Monty

Thread
Problem of IF(), ISNULL(), and COUNT()churo24 Aug
  • Problem of IF(), ISNULL(), and COUNT()Michael Widenius29 Aug
    • Re: Problem of IF(), ISNULL(), and COUNT()Churo Park30 Aug
      • Re: Problem of IF(), ISNULL(), and COUNT()Michael Widenius31 Aug