List:General Discussion« Previous MessageNext Message »
From:<sinisa Date:August 25 1999 11:47am
Subject:IF(), ISNULL(), and COUNT() problem
View as plain text  
churo@stripped writes:
 > >Description:
 > 
 > It seems that IF() is not evaluating ISNULL() well
 > when ISNULL() is evaluating an indexed and left-joined column,
 >  and the returning expression is returned by COUNT().
 > 
 > >How-To-Repeat:
 > 
 > 1. When tr.pcode is not indexed:
 > 
 > mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
 > FROM tp
 > LEFT JOIN tr ON tp.pcode = tr.pcode
 > GROUP BY tp.pcode;
 > 
 > +---------+-------+
 > | pcode   | count |
 > +---------+-------+
 > | kvw2000 |    28 |
 > | kvw2001 |     0 |
 > +---------+-------+ (as expected)
 > 
 > 2. When tr.pcode is indexed:
 > 
 > mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
 > FROM tp
 > LEFT JOIN tr ON tp.pcode = tr.pcode
 > GROUP BY tp.pcode;
 > 
 > +---------+-------+
 > | pcode   | count |
 > +---------+-------+
 > | kvw2000 |    28 |
 > | kvw2001 |     1 |
 > +---------+-------+ (not as expected)
 > 
 > 3. When tr.pcode is indexed, but COUNT() is not used:
 > 
 > mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, 1) AS count
 > FROM tp
 > LEFT JOIN tr ON tp.pcode = tr.pcode
 > GROUP BY tp.pcode;
 > 
 > +---------+-------+
 > | pcode   | count |
 > +---------+-------+
 > | kvw2000 |     1 |
 > | kvw2001 |     0 |
 > +---------+-------+ (as expected)
 > 
 > >Fix:
 > 	
 > 
 > >Submitter-Id:	<submitter ID>
 > >Originator:	
 > >Organization:
 >  
 > >MySQL support: [none | licence | email support | extended email support ]
 > >Synopsis:	IF(), ISNULL(), and COUNT() problem
 > >Severity:	
 > >Priority:	
 > >Category:	mysql
 > >Class:		
 > >Release:	mysql-3.22.25 (Source distribution)
 > >Server: /usr/local/bin/mysqladmin  Ver 7.11 Distrib 3.22.25, for pc-linux-gnu on
i686
 > TCX Datakonsult AB, by Monty
 > 
 > Server version		3.22.25
 > Protocol version	10
 > Connection		Localhost via UNIX socket
 > UNIX socket		/tmp/mysql.sock
 > Uptime:			29 days 11 hours 16 min 33 sec
 > 
 > Threads: 8  Questions: 31041  Slow queries: 0  Opens: 49  Flush tables: 1  Open
tables: 18
 > >Environment:
 > 	
 > System: Linux kepler.snu.ac.kr 2.2.5-15 #1 Mon Apr 19 23:00:46 EDT 1999 i686 unknown
 > Architecture: i686
 > 
 > Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
 > GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
 > gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
 > Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
 > Configure command: ./configure  --with-charset=euc_kr --localstatedir=/home/data
 > Perl: This is perl, version 5.005_03 built for i386-linux
 > 
 > 


Hi!

Everything is OK in your queries.


A field can not be NULL and be indexed. At least not until 3.23.xx

So, everything is OK.

Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped|
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+
Thread
IF(), ISNULL(), and COUNT() problemchuro25 Aug
  • PHP3 & MySQL simple questionBarry25 Aug
    • Re: PHP3 & MySQL simple questionPaul DuBois25 Aug
      • Re: PHP3 & MySQL simple questionBarry25 Aug
  • IF(), ISNULL(), and COUNT() problemsinisa25 Aug
    • Re: IF(), ISNULL(), and COUNT() problemChuro Park25 Aug
  • Re: IF(), ISNULL(), and COUNT() problemMartin Ramsch13 Sep