Bug #27118 Query doesnt return the same results each and every time

• Submitted: 14 Mar 2007 8:36
• Modified: 26 Mar 2007 12:30
• Reporter: Christoff Truter
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.033, 5.1 BK, 5.2-falcon
• OS: Windows (Windows XP Pro, Linux)
• Assigned to: Martin Hansson
• CPU Architecture: Any

[14 Mar 2007 8:36] Christoff Truter

Description:
When running this Query

SELECT f.forumID, t.topicID, f.categoryID, 
	f.subject AS forumsubject, f.description, 
	u.username, p.subject AS postsubject, count(t.topicID) as posts, 
	count(distinct(t.topicID)) as topics
FROM forum f
	LEFT JOIN topics t ON f.forumID = t.forumID
	LEFT JOIN posts p ON p.topicID = t.topicID
	LEFT JOIN user u ON u.userID = p.userID
WHERE f.categoryID = 2
	GROUP BY forumsubject

It doesnt always return the same results....

How to repeat:
Create a table, and run these queries to create tables/insert data etc

CREATE TABLE `category` (
  `categoryID` tinyint(4) NOT NULL auto_increment,
  `subject` varchar(50) collate latin1_general_ci NOT NULL,
  `description` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`categoryID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

INSERT INTO `category` VALUES (1, 'General', 'Talk about absolutely anything ');
INSERT INTO `category` VALUES (2, 'Open Source', 'Talk about open source languages');
INSERT INTO `category` VALUES (3, 'Microsoft', 'Talk about microsoft languages');
INSERT INTO `category` VALUES (4, 'Borland', 'Talk about borland languages');

CREATE TABLE `forum` (
  `forumID` int(11) NOT NULL auto_increment,
  `categoryID` tinyint(4) NOT NULL,
  `subject` varchar(50) collate latin1_general_ci NOT NULL,
  `description` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`forumID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;

INSERT INTO `forum` VALUES (1, 2, 'PHP', 'General discussion about PHP');
INSERT INTO `forum` VALUES (2, 3, 'C# / VB.Net', 'General discussion about .Net languages');
INSERT INTO `forum` VALUES (3, 1, 'Javascript / Ajax', 'General discussion about Javascript and Ajax');
INSERT INTO `forum` VALUES (4, 4, 'Delphi', 'General discussion about Delphi');
INSERT INTO `forum` VALUES (5, 1, 'Downloads', 'Discussions about downloads available on this site');

CREATE TABLE `posts` (
  `postID` bigint(20) NOT NULL auto_increment,
  `userID` int(11) NOT NULL,
  `date` date NOT NULL,
  `subject` varchar(50) collate latin1_general_ci NOT NULL,
  `body` text collate latin1_general_ci NOT NULL,
  `topicID` int(11) NOT NULL,
  PRIMARY KEY  (`postID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

INSERT INTO `posts` VALUES (1, 1, '2007-03-12', 'test', 'test', 1);
INSERT INTO `posts` VALUES (2, 2, '2007-03-12', 'test2', 'testy', 1);

CREATE TABLE `profile` (
  `profileID` int(11) NOT NULL auto_increment,
  `userID` int(11) NOT NULL,
  `firstname` varchar(100) collate latin1_general_ci NOT NULL,
  `lastname` varchar(100) collate latin1_general_ci NOT NULL,
  `email` varchar(255) collate latin1_general_ci NOT NULL,
  `countrycode` char(2) collate latin1_general_ci NOT NULL,
  `timezone` int(11) NOT NULL,
  `joindate` datetime NOT NULL,
  `lastlogin` datetime NOT NULL,
  PRIMARY KEY  (`profileID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

CREATE TABLE `topics` (
  `topicID` int(11) NOT NULL auto_increment,
  `forumID` int(11) NOT NULL,
  `postID` bigint(20) NOT NULL,
  PRIMARY KEY  (`topicID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

INSERT INTO `topics` VALUES (1, 1, 1);

CREATE TABLE `user` (
  `userID` int(11) NOT NULL auto_increment,
  `username` varchar(20) collate latin1_general_ci NOT NULL,
  `password` varchar(16) collate latin1_general_ci NOT NULL,
  `rights` tinyint(4) NOT NULL,
  PRIMARY KEY  (`userID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

INSERT INTO `user` VALUES (1, 'admin', '12345678', 1);
INSERT INTO `user` VALUES (2, 'dude', '12345678', 1);
------------------------------------------------------------------------

Run this query on it:
SELECT f.forumID, t.topicID, f.categoryID, 
	f.subject AS forumsubject, f.description, 
	u.username, p.subject AS postsubject, count(t.topicID) as posts, 
	count(distinct(t.topicID)) as topics
FROM forum f
	LEFT JOIN topics t ON f.forumID = t.forumID
	LEFT JOIN posts p ON p.topicID = t.topicID
	LEFT JOIN user u ON u.userID = p.userID
WHERE f.categoryID = 2
	GROUP BY forumsubject

You will find it sometimes returns admin sometimes dude as the username

[14 Mar 2007 10:11] Sveta Smirnova

Thank you for the report.

Verified as described on Linux using last 5.1 and 5.2-falcon development sources. With 5.0 and 4.1 in my environment bug is not repeatable.

[26 Mar 2007 12:30] Martin Hansson

This is expected behavior in MySQL. Since MySQL allows hidden fields in a GROUP BY clause, it is the user's responsibility to ensure that these fields are constant in the group. Please refer to the MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-fields.html

In the example query u.username is not constant in the query and neither is it included in the GROUP BY clause, so the server is free to return any value from the username column. If this behaviour is not accepted, you can set the variable sql_mode like so: SET sql_mode='ONLY_FULL_GROUP_BY';

See below:

mysql> SELECT f.forumID, t.topicID, f.categoryID,
    -> f.subject AS forumsubject, f.description,
    -> u.username, p.subject AS postsubject, count(t.topicID) as posts,
    -> count(distinct(t.topicID)) as topics
    -> FROM forum f
    -> LEFT JOIN topics t ON f.forumID = t.forumID
    -> LEFT JOIN posts p ON p.topicID = t.topicID
    -> LEFT JOIN user u ON u.userID = p.userID
    -> WHERE f.categoryID = 2
    -> GROUP BY forumsubject;
ERROR 1055 (42000): 'bug27118.f.forumID' isn't in GROUP BY