Discrepency!! (count and select)

[e2ka]

Suppose I have a database with table A and column A.c1 I do the following

SELECT COUNT(*) FROM A;

and get: 800

but if I do:

SELECT * FROM A;

I get 400 rows returned.

and if I do

SELECT COUNT(c1) FROM A;

I get 400.

So it seems like there are 400 rows of data... so why does COUNT(*) tell me 800??

-e2ka

[nicc777]

http://www.mysql.com/doc/en/Group_by...s.html#IDX1366

[e2ka]

Ah yes, I should have mentioned the fact that I understand that the 400-row discrepency is due to NULL rows - judging from the difference in COUNT(*) vs. COUNT(A.c1). I guess what my real question is is how did I get 400 null rows in the first place and how do I get rid of them?

DELETE * FROM A WHERE IS NULL;

was my guess but this did not seem to work.

-e2ka

[nicc777]

Ragarding the entry of NULL values - it's hard to say. I would inspect the application code as the database just executes SQL commands.

As far as getting rid of the NULL entries is concerned:

DELETE FROM A WHERE cl IS NULL;

Cheers

[e2ka]

Originally posted by nicc777
Ragarding the entry of NULL values - it's hard to say. I would inspect the application code as the database just executes SQL commands.

As far as getting rid of the NULL entries is concerned:

DELETE FROM A WHERE cl IS NULL;

Cheers

Actually, none of the columns in A (c1, c2, c3 ... cN) have any NULL values (all 400 rows are filled). This is what is so confusing to me. I can't see where these nulls are. I am sure A has no nulls I did the following:

mysql -u user -p -h host -D database < cmd | grep NULL

where cmd contains "SELECT * FROM A;"

and I recieved no output. = no NULLS

Is there some other NULL that is adding up in count(*)?

Something terrible happened to this database, and I suspect foul play (someone might have gotten the password and decided to delete everything). The database is supposed to have 400 records. We came to it today and it was empty. I refilled the database, and then discovered this count(*) discrepency and became very confused.


And still am!
but thanks for your help though

-e2ka

[nicc777]

Mmm... the plot thickens

Try:

mysqldump --databases databasename -u username -p | grep NULL

This should give you an idea of where all the NULL values are.

Note: The above command works on *nix. For Windows, you should pipe to a file, and then examine the file in your fav text editor.

Also, when restoring the data, make sure you first drop the old table, then recreate it before restoring the data. The mysqldump command should already add the drop table and create table SQL for you - if you used that to do the backup previously.

BTW: the command with the drop table bit:

mysqldump --databases databasename -u username -p --add-drop-table

Hope this helps somewhat... more

Cheers