Hi, Thank you a lot for your answer. I checked very carefully. The structure of the table is (exported by phpMyAdmin):
CREATE TABLE `tp_participation` ( `uid` int(11) NOT NULL default '0', `challenge_id` int(11) NOT NULL default '0', `response` text collate latin1_general_ci NOT NULL, `points` int(11) default NULL, UNIQUE KEY `tp_id_part` (`uid`,`challenge_id`), KEY `tp_idx_part_solution` (`challenge_id`,`response`(4)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; I removed the key on challenge_id as suggested in another post. I do the following queries with phpMyAdmin (no direct access to mysql client): select * from tp_participation where challenge_id=20 > 10 records. select challenge_id from tp_participation where challenge_id=20 > _two_ records with the value "20". select count(challenge_id) from tp_participation where challenge_id=20 > One record with value "2" select count(*) from tp_participation where challenge_id=20 > One record with value "2" select count(uid) from tp_participation where challenge_id=20 > One record with value "10" select count(response) from tp_participation where challenge_id=20 > One record with value "10" select count(points) from tp_participation where challenge_id=20 > One record with value "select count(response) from tp_participation where challenge_id=20 > One record with value "0" (value of points is null for each field with challenge_id in the database, so it seems normal) Fabien Le lundi 23 janvier 2006 à 17:50 -0500, Rhino a écrit : > 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]
