Bug #63514 select count(*), column_name from table gives inconsistant result

• Submitted: 1 Dec 2011 5:52
• Modified: 1 Dec 2011 5:57
• Reporter: Sagar Thoravat
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.5.31
• OS: Windows
• CPU Architecture: Any

[1 Dec 2011 5:52] Sagar Thoravat

Description:
In mysql 5.5.31, i tried following thing:
-------------------------------------------

mysql> create database sagar
    -> ;
Query OK, 1 row affected (0.57 sec)

mysql> use sagar
Database changed
mysql> create table tab(c1 int);
Query OK, 0 rows affected (2.43 sec)

mysql> insert into tab values(10);
Query OK, 1 row affected (0.18 sec)

mysql> insert into tab values(20);
Query OK, 1 row affected (0.09 sec)

mysql> insert into tab values(30);
Query OK, 1 row affected (0.03 sec)

mysql> insert into tab values(40);
Query OK, 1 row affected (0.11 sec)

mysql> insert into tab values(40);
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab values(40);
Query OK, 1 row affected (0.03 sec)

mysql> insert into tab values(30);
Query OK, 1 row affected (0.04 sec)

mysql> select * from tab;
+------+
| c1   |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
|   40 |
|   40 |
|   30 |
+------+
7 rows in set (0.10 sec)

mysql> select count(*) from tab;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*),c1 from tab;
+----------+------+
| count(*) | c1   |
+----------+------+
|        7 |   10 |
+----------+------+
1 row in set (0.04 sec)

This should fail as we syntax is not correct.

How to repeat:
mysql> create database sagar
    -> ;
Query OK, 1 row affected (0.57 sec)

mysql> use sagar
Database changed
mysql> create table tab(c1 int);
Query OK, 0 rows affected (2.43 sec)

mysql> insert into tab values(10);
Query OK, 1 row affected (0.18 sec)

mysql> insert into tab values(20);
Query OK, 1 row affected (0.09 sec)

mysql> insert into tab values(30);
Query OK, 1 row affected (0.03 sec)

mysql> insert into tab values(40);
Query OK, 1 row affected (0.11 sec)

mysql> insert into tab values(40);
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab values(40);
Query OK, 1 row affected (0.03 sec)

mysql> insert into tab values(30);
Query OK, 1 row affected (0.04 sec)

mysql> select * from tab;
+------+
| c1   |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
|   40 |
|   40 |
|   30 |
+------+
7 rows in set (0.10 sec)

mysql> select count(*) from tab;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*),c1 from tab;
+----------+------+
| count(*) | c1   |
+----------+------+
|        7 |   10 |
+----------+------+
1 row in set (0.04 sec)

Suggested fix:
This one should throw error. try comparing with microsoft sql client.

select count(*),column_name from table group by column_name is the right syntax.

[1 Dec 2011 5:57] Valeriy Kravchuk

This is not a bug. Read http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html.