List:General Discussion« Previous MessageNext Message »
From:fabsk Date:January 24 2006 8:12am
Subject:Re: count(*) send a wrong value
View as plain text  
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" <fabsk+mysql@stripped>
> To: <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".
> >
> > Fabien
> >
> > 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
> >>
> >> 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@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),
> > 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/mysql?unsub=1
> >> >
> >> >
> >>
> >
> >
> >
> > -- 
> > 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: 
> > 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
> 
> 

Thread
count(*) send a wrong valuefabsk23 Jan
  • Re: count(*) send a wrong valuemysql23 Jan
    • Re: count(*) send a wrong valueFabien SK23 Jan
    • Re: count(*) send a wrong valuefabsk23 Jan
    • Re: count(*) send a wrong valueMichael Stassen23 Jan
  • Re: count(*) send a wrong valueRhino23 Jan
    • Re: count(*) send a wrong valuefabsk24 Jan
  • Re: count(*) send a wrong valueHank24 Jan
  • Re: count(*) send a wrong valueMartijn Tonies24 Jan
    • Re: count(*) send a wrong valuefabsk24 Jan
      • Re: count(*) send a wrong valueFabien SK24 Jan
        • Re: count(*) send a wrong valueFabien SK24 Jan