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.
Rhino
----- Original Message -----
From: "fabsk" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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".
Fabien
Le lundi 23 janvier 2006 à 20:54 +0000, [EMAIL PROTECTED] a écrit :
>From the MySQL 4.1 manual
12.10.1. GROUP BY (Aggregate) Functions
COUNT(expr)
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.
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.
Keith
In theory, theory and practice are the same;
In practice they are not.
On Mon, 23 Jan 2006, fabsk wrote:
> To: mysql@lists.mysql.com
> From: fabsk <[EMAIL PROTECTED]>
> 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),
MySQL
> 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
records.
> 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
should
> 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:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21/236 - Release Date:
20/01/2006
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
