Organizational Research By

Surprising Reserch Topic

why do i need or null in mysql when counting rows with a condition


why do i need or null in mysql when counting rows with a condition  using -'mysql,theory'

There is a question about MySQL's COUNT() aggregate function that keeps popping into my head time to time. I would like to get some explanation to why it is working the way it is.

When I started working with MySQL I quickly learned that its COUNT(condition) seems only to work properly if condition also contains an OR NULL in the end. In case of more complicated COUNT conditions it was an empirical process to find out where to put it exactly. In MSSQL you do not need this OR NULL to get proper results, so I would like to know the explanation for it. So, here is an example.

Lets have a very basic table with the following structure and data:

CREATE TABLE test (
  `value` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO test (value) VALUES(1);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(5);
INSERT INTO test (value) VALUES(6);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(5);
INSERT INTO test (value) VALUES(2);
INSERT INTO test (value) VALUES(8);
INSERT INTO test (value) VALUES(1);


Scenario: I would like to count how many rows I have where the value = 4. An obvious solution would be to filter for it using a WHERE and do a COUNT(*) but I am interested in a COUNT(condition) based solution.

So, the solution that comes to my mind is:

SELECT COUNT(value=4)
  FROM test


The result is 10. This is obviously wrong.

Second attempt with OR NULL:

SELECT COUNT(value=4 OR NULL)
  FROM test


The result is 3. It is correct.

Can someone explain the logic behind this? Is this some bug in MySQL or is there a logical explanation why I need to add that strange-looking OR NULL to the end of the COUNT condition to get the correct result?
    
asked Oct 13, 2015 by nikhilapatil
0 votes
4 views



Related Hot Questions



Government Jobs Opening


...