MySQL Bugs: #39169: Unexpected behaviour when using GROUP BY and/or WHERE clauses

Bug #39169	Unexpected behaviour when using GROUP BY and/or WHERE clauses
Submitted:	1 Sep 2008 22:17	Modified:	10 Oct 2008 8:06
Reporter:	Giovanni R.	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	6.0.4alpha, 6.0.6alpha	OS:	Windows (Windows XP Home)
Assigned to:		CPU Architecture:	Any
Tags:	empty set, GROUP BY, where

Description:

Take a look at these queries (tables comes from a fresh Wordpress v2.6.1 installation, with just one account other than admin):

mysql> SELECT user_id, user_login FROM wp_users, wp_usermeta WHERE wp_users.ID = wp_usermeta.user_id AND meta_key = 'wp_capabilities';
+---------+------------+
| user_id | user_login |
+---------+------------+
|       1 | admin      |
|       2 | staff      |
+---------+------------+
2 rows in set (0.00 sec)

-- Adding GROUP BY clause to the SQL query...

mysql> SELECT user_id, user_login FROM wp_users, wp_usermeta WHERE wp_users.ID = wp_usermeta.user_id AND meta_key = 'wp_capabilities' ORDER BY wp_usermeta.user_id;
Empty set (0.00 sec)

-- Removing the second WHERE condition...

mysql> SELECT user_id, user_login FROM wp_users, wp_usermeta WHERE wp_users.ID = wp_usermeta.user_id ORDER BY wp_usermeta.user_id;
+---------+------------+
| user_id | user_login |
+---------+------------+
|       1 | admin      |
|       1 | admin      |
|       1 | admin      |
|       1 | admin      |
|       1 | admin      |
|       1 | admin      |
|       2 | staff      |
|       2 | staff      |
|       2 | staff      |
|       2 | staff      |
|       2 | staff      |
+---------+------------+
11 rows in set (0.00 sec)

mysql>

This means that there's something weird when using ORDER BY and a WHERE condition.

All this has been tested on MySQL v6.0.4-alpha-community.

When tested on MySQL v5.0.67-community, it gives the expected results (ie, using GROUP BY still returns two rows).

Hope this is not an already submitted bug.

Greetings, Giovanni

ps. same behaviour using "INNER JOIN", and no difference between ORDER BY ... ASC and ORDER BY ... DESC.

How to repeat:

Install Wordpress ;), create a new account (well, maybe it's not needed), then try to execute those queries.

Suggested fix:
No suggested fix.

Thank you for the bug report. Could you please try with latest released version 6.0.6. Thanks in advance.

Same behaviour with v6.0.6alpha.

Greetings, Giovanni

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE for tables wp_users and wp_usermeta

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".