>>>>> "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
