What you're describing definitely sounds like a bug to me, assuming that you
are accurately reporting the query you've used and the data in your table.
In other words, if there really are 10 rows that have a cid value of 123 and
you really are doing "select * from table where cid = 123", then you should
definitely be getting a result of 10, not 2.
But that is a VERY strange error to be having! I've been writing SQL for
over 20 years on a variety of platforms and I can't remember EVER seeing a
count(*) give the wrong result. Any time the result was not what I expected,
it turned out that I'd written the query incorrectly or I was wrong about
what data was in the table. I'd also expect that the MySQL testing team
would have executed many tests to be sure that basic functionality like
count(*) works before ever releasing the product.
Please, retest everything VERY carefully once more and make VERY sure that
you aren't inadvertently writing the query incorrectly and that you really
DO have 10 rows with cid = 123. If you still get 2 as the result of your
query, I would recommend sending a bug report to MySQL.
----- Original Message -----
From: "fabsk" <fabsk+mysql@stripped>
Sent: Monday, January 23, 2006 5:32 PM
Subject: Re: count(*) send a wrong value
> Thank you for you answer, but I read many times and I did not found
> something to answer my question (well, I did not know about the NULL).
> In my case:
> - there is one table
> - htere is no "distinct"
> - there is a WHERE clause, so there is no optimisation
> - there is no other field and no "group by"
> If I do "count(cid)", I still get "2".
> Le lundi 23 janvier 2006 à 20:54 +0000, mysql@stripped a écrit :
>> >From the MySQL 4.1 manual
>> 12.10.1. GROUP BY (Aggregate) Functions
>> Returns a count of the number of non-NULL values in the rows
>> retrieved by a SELECT statement.
>> COUNT() returns 0 if there were no matching rows.
>> mysql> SELECT student.student_name,COUNT(*)
>> -> FROM student,course
>> -> WHERE student.student_id=course.student_id
>> -> GROUP BY student_name;
>> COUNT(*) is somewhat different in that it returns a count
>> of the number of rows retrieved, whether or not they contain
>> NULL values.
>> COUNT(*) is optimized to return very quickly if the SELECT
>> retrieves from one table, no other columns are retrieved,
>> and there is no WHERE clause. For example:
>> mysql> SELECT COUNT(*) FROM student;
>> This optimization applies only to MyISAM and ISAM tables
>> only, because an exact record count is stored for these
>> table types and can be accessed very quickly. For
>> transactional storage engines (InnoDB, BDB), storing an
>> exact row count is more problematic because multiple
>> transactions may be occurring, each of which may affect the
>> COUNT(DISTINCT expr,[expr...])
>> Returns a count of the number of different non-NULL values.
>> COUNT(DISTINCT) returns 0 if there were no matching rows.
>> mysql> SELECT COUNT(DISTINCT results) FROM student;
>> In MySQL, you can get the number of distinct expression
>> combinations that do not contain NULL by giving a list of
>> expressions. In standard SQL, you would have to do a
>> concatenation of all expressions inside COUNT(DISTINCT ...).
>> COUNT(DISTINCT ...) was added in MySQL 3.23.2.
>> In theory, theory and practice are the same;
>> In practice they are not.
>> On Mon, 23 Jan 2006, fabsk wrote:
>> > To: mysql@stripped
>> > From: fabsk <fabsk+mysql@stripped>
>> > Subject: count(*) send a wrong value
>> > Hi,
>> > I'm facing a strange problem. I am using a database at my Internet
>> > provider (Free, France). The type of table is MyISAM (no choice),
>> > 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
>> > The definition of my table is:
>> > - uid, int
>> > - cid, int
>> > - response, text
>> > - points, int (can be null)
>> > keys:
>> > - uid, cid
>> > - cid, response(4)
>> > - cid
>> > When I do "select * from my_table where cid=123", I get my 10
>> > But when I do "select count(*) from my_table where cid=123" I get
> "2". I
>> > also happens with many other values of "cid" and the bad result is
>> > always "2".
>> > I can't understand what's happen. It seems to simple, but there
>> > be something. Do you have an idea?
>> > Thank you for your attention
>> > Fabien
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.21/236 - Release Date:
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21/236 - Release Date: 20/01/2006