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
>
>
