MySQL Bugs: #45321: count(*) behaving unpredictably

Bug #45321	count(*) behaving unpredictably
Submitted:	4 Jun 2009 7:29	Modified:	4 Jun 2009 17:35
Reporter:	Jayadevan M	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.30-community	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Hi,
I was just trying to create table, delete truncate etc and ran into this strange behaviour. I am pasting everything I did, here.

H:\>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.30-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table cry1(c text)
    -> ;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into crypt values('MYWORD');
ERROR 1146 (42S02): Table 'test.crypt' doesn't exist
mysql> insert into cry1 values('MYWORD');
Query OK, 1 row affected (0.02 sec)

mysql> select * from cry1;
+--------+
| c      |
+--------+
| MYWORD |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> truncate table cry1;
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> delete from cry1;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*)  from cry1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*)   from cry1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.30-community |
+------------------+
1 row in set (0.00 sec)

mysql>
---------
The table is being accessed only from this session. As you can see, the when I put 2 spaces after count(*), it seems to return the correct count. If there is only one space, it returns 1.

How to repeat:
Please see above

Thank you for the report.

This looks like bug #40386 which was fixed in version 5.1.32. Please try current version 5.1.34 in you environment to be sure problem is fixed.

Possibly it is the same bug. But the behaviour is not consistent. That is worrying. As I mentioned, select count(*) from and select count(*)  from behave differently - i.e. the number of spaces between "count(*)" and "from" seems to matter.

Got it. When I use 2 spaces, it is a 'new' query and hence the cache is not being used.

Below what I got following the same sequence with a current source server:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.36-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >create table cry1(c text)
    -> ;
Query OK, 0 rows affected (0.38 sec)

mysql 5.1 >insert into crypt values('MYWORD');
ERROR 1146 (42S02): Table 'test.crypt' doesn't exist
mysql 5.1 >insert into cry1 values('MYWORD');
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >select * from cry1;
+--------+
| c      |
+--------+
| MYWORD |
+--------+
1 row in set (0.00 sec)

mysql 5.1 >select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >truncate table cry1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >delete from cry1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 > commit;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >select count(*)   from cry1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >select count(*) from cry1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >select version();
+--------------------+
| version()          |
+--------------------+
| 5.1.36-Win X64-log |
+--------------------+
1 row in set (0.00 sec)

mysql 5.1 >

So, this is a duplicate of bug #40386.