Bug #72474 Query crash when selecting fixed value column on group by
Submitted: 28 Apr 2014 17:42 Modified: 29 Apr 2014 14:00
Reporter: Allan Souza Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.5.35 OS:Linux (Debian 5.5.35-0+wheezy1-log)
Assigned to: CPU Architecture:Any
Tags: error, Fixed Column, GROUP BY, query

[28 Apr 2014 17:42] Allan Souza
Description:
I was trying to check if an error of an PHP application I was working on had happened and made the following query (changed names to english to make it more readable):

SELECT p.description, t.type testtype, b.name boardname, c.name candidatename, g.grade 
FROM exam_process p 
INNER JOIN exam_test t ON t.idprocess=p.id 
INNER JOIN exam_board b ON b.idprocess=p.id 
INNER JOIN exam_candidate c ON c.idprocess=p.id 
  LEFT JOIN exam_grade g ON g.idprocess=p.id 
  AND g.testtype=t.type 
  AND g.idboard=b.id 
  AND g.idcandidate=c.id 
WHERE p.id= /*?*/1 
AND g.grade IS NULL 
AND t.aplied='Y' 
AND NOT EXISTS (
  SELECT 1 
  FROM exam_test t2 
  INNER JOIN exam_board b2 ON b2.idprocess=t2.idprocess 
  INNER JOIN exam_candidate c2 ON c2.idprocess=t2.idprocess 
  INNER JOIN exam_grade n2 ON n2.idprocess=t2.idprocess 
  AND n2.testtype=t2.type 
  AND n2.idboard=b2.id 
  AND n2.idcandidate=c2.id 
  WHERE t2.idprocess=t.idprocess 
  AND t2.order<t.order 
  AND t2.aplied='Y' 
  AND t2.eliminatory='Y'
  GROUP BY t2.idprocess, t2.type, c2.id
  HAVING AVG(n2.grade)<t2.minimumgrade 
) 
ORDER BY t.order, c.name;

then I got this error:

ERROR 1054 (42S22): Unknown column 't2.minimumgrade' in 'having clause'

With all this context in mind I tried to figure out what was going wrong, without success. First I isolated the nested query, placing fixed values on the where clauses this way:

  SELECT 1 
  FROM exam_test t2 
  INNER JOIN exam_board b2 ON b2.idprocess=t2.idprocess 
  INNER JOIN exam_candidate c2 ON c2.idprocess=t2.idprocess 
  INNER JOIN exam_grade n2 ON n2.idprocess=t2.idprocess 
  AND n2.testtype=t2.type 
  AND n2.idboard=b2.id 
  AND n2.idcandidate=c2.id 
  WHERE t2.idprocess=1
  AND t2.order<5 
  AND t2.aplied='Y' 
  AND t2.eliminatory='Y'
  GROUP BY t2.idprocess, t2.type, c2.id
  HAVING AVG(n2.grade)<t2.minimumgrade;

getting the same error. It didn't seemed right for me something so simple go wrong, I just wanted to check if that condition existed, no the data on it. Even thinking that way i changed the select statement column to the error column to see if the error message changed, and magically the error was gone and the data was retrieved! With this change:

  SELECT /*changed here >*/ t2.minimumgrade /* < changed here*/ 
  FROM exam_test t2 
  INNER JOIN exam_board b2 ON b2.idprocess=t2.idprocess 
  INNER JOIN exam_candidate c2 ON c2.idprocess=t2.idprocess 
  INNER JOIN exam_grade n2 ON n2.idprocess=t2.idprocess 
  AND n2.testtype=t2.type 
  AND n2.idboard=b2.id 
  AND n2.idcandidate=c2.id 
  WHERE t2.idprocess=1
  AND t2.order<5 
  AND t2.aplied='Y' 
  AND t2.eliminatory='Y'
  GROUP BY t2.idprocess, t2.type, c2.id
  HAVING AVG(n2.grade)<t2.minimumgrade;

How to repeat:
-- MySQL dump 10.13  Distrib 5.5.35, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: temp
-- ------------------------------------------------------
-- Server version	5.5.35-0+wheezy1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `exam_board`
--

DROP TABLE IF EXISTS `exam_board`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exam_board` (
  `id` int(10) unsigned NOT NULL,
  `idprocess` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exam_board`
--

LOCK TABLES `exam_board` WRITE;
/*!40000 ALTER TABLE `exam_board` DISABLE KEYS */;
INSERT INTO `exam_board` VALUES (1,1,'Board Member1'),(2,1,'Board Member2'),(3,1,'Board Member3'),(4,1,'Board Member4'),(5,1,'Board Member5');
/*!40000 ALTER TABLE `exam_board` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `exam_candidate`
--

DROP TABLE IF EXISTS `exam_candidate`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exam_candidate` (
  `id` int(10) unsigned NOT NULL,
  `idprocess` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `status` enum('A','R') NOT NULL DEFAULT 'A'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exam_candidate`
--

LOCK TABLES `exam_candidate` WRITE;
/*!40000 ALTER TABLE `exam_candidate` DISABLE KEYS */;
INSERT INTO `exam_candidate` VALUES (1,1,'Candidate 1','R'),(2,1,'Candidate 2','A'),(3,1,'Candidate 3','A'),(4,1,'Candidate 4','R');
/*!40000 ALTER TABLE `exam_candidate` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `exam_grade`
--

DROP TABLE IF EXISTS `exam_grade`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exam_grade` (
  `idprocess` int(10) unsigned NOT NULL,
  `idboard` int(10) unsigned NOT NULL,
  `idcandidate` int(10) unsigned NOT NULL,
  `testtype` enum('W','S','T','D','A') NOT NULL,
  `grade` decimal(4,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exam_grade`
--

LOCK TABLES `exam_grade` WRITE;
/*!40000 ALTER TABLE `exam_grade` DISABLE KEYS */;
INSERT INTO `exam_grade` VALUES (1,1,1,'W',6.50),(1,1,2,'W',7.40),(1,1,2,'S',7.00),(1,1,2,'T',7.90),(1,1,2,'D',7.50),(1,1,2,'A',7.80),(1,1,3,'W',7.60),(1,1,3,'S',7.80),(1,1,3,'T',7.20),(1,1,3,'D',7.40),(1,1,3,'A',7.30),(1,1,4,'W',7.10),(1,1,4,'S',6.80),(1,2,1,'W',6.50),(1,2,2,'W',7.40),(1,2,2,'S',7.00),(1,2,2,'T',7.90),(1,2,2,'D',7.50),(1,2,2,'A',7.80),(1,2,3,'W',7.60),(1,2,3,'S',7.80),(1,2,3,'T',7.20),(1,2,3,'D',7.40),(1,2,3,'A',7.30),(1,2,4,'W',7.10),(1,2,4,'S',6.80),(1,3,1,'W',6.50),(1,3,2,'W',7.40),(1,3,2,'S',7.00),(1,3,2,'T',7.90),(1,3,2,'D',7.50),(1,3,2,'A',7.80),(1,3,3,'W',7.60),(1,3,3,'S',7.80),(1,3,3,'T',7.20),(1,3,3,'D',7.40),(1,3,3,'A',7.30),(1,3,4,'W',7.10),(1,3,4,'S',6.80),(1,4,1,'W',6.50),(1,4,2,'W',7.40),(1,4,2,'S',7.00),(1,4,2,'T',7.90),(1,4,2,'D',7.50),(1,4,2,'A',7.80),(1,4,3,'W',7.60),(1,4,3,'S',7.80),(1,4,3,'T',7.20),(1,4,3,'D',7.40),(1,4,3,'A',7.30),(1,4,4,'W',7.10),(1,4,4,'S',6.80),(1,5,1,'W',6.50),(1,5,2,'W',7.40),(1,5,2,'S',7.00),(1,5,2,'T',7.90),(1,5,2,'D',7.50),(1,5,2,'A',7.80),(1,5,3,'W',7.60),(1,5,3,'S',7.80),(1,5,3,'T',7.20),(1,5,3,'D',7.40),(1,5,3,'A',7.30),(1,5,4,'W',7.10),(1,5,4,'S',6.80);
/*!40000 ALTER TABLE `exam_grade` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `exam_process`
--

DROP TABLE IF EXISTS `exam_process`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exam_process` (
  `id` int(10) unsigned NOT NULL,
  `description` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exam_process`
--

LOCK TABLES `exam_process` WRITE;
/*!40000 ALTER TABLE `exam_process` DISABLE KEYS */;
INSERT INTO `exam_process` VALUES (1,'num proc teste');
/*!40000 ALTER TABLE `exam_process` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `exam_test`
--

DROP TABLE IF EXISTS `exam_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exam_test` (
  `idprocess` int(10) unsigned NOT NULL,
  `type` enum('W','S','T','D','A') NOT NULL,
  `aplied` enum('Y','N') NOT NULL,
  `eliminatory` enum('Y','N') NOT NULL,
  `order` enum('1','2','3','4','5') NOT NULL,
  `minimumgrade` decimal(4,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exam_test`
--

LOCK TABLES `exam_test` WRITE;
/*!40000 ALTER TABLE `exam_test` DISABLE KEYS */;
INSERT INTO `exam_test` VALUES (1,'W','Y','Y','1',7.00),(1,'S','Y','Y','2',7.00),(1,'T','Y','N','4',0.00),(1,'D','Y','N','5',0.00),(1,'A','Y','N','3',0.00);
/*!40000 ALTER TABLE `exam_test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-04-28 13:28:13

Suggested fix:
Didn't know, I just want to report it because I didn't find anything similar on the other bug reports, just one with the same error message, but the fix was a sql change.
I'm sorry if it was covered on the manual, I was reluctant on report a bug because I though the error was my fault, but putting it on perspective I saw it was not.
[28 Apr 2014 19:29] MySQL Verification Team
Thank you for the bug report. What you meant with "Query crash.." the server crashed? or the error message for you is a crash?.
[28 Apr 2014 19:32] Allan Souza
Sorry, i meant that the query returned an error instead of just give me a result.
[28 Apr 2014 21:25] MySQL Verification Team
Thank you for the feedback. You got the correct server message: ERROR 1054 (42S22): Unknown column 't2.minimumgrade' in 'having clause' It does not contained in either aggregate function or GROUP BY clause. If you use the
sql_mode = ONLY_FULL_GROUP_BY you could see you query which returned rows
gets an error message:

mysql 5.5 > SET SQL_MODE = ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >  SELECT /*changed here >*/ t2.minimumgrade /* < changed here*/
    ->   FROM exam_test t2
    ->   INNER JOIN exam_board b2 ON b2.idprocess=t2.idprocess
    ->   INNER JOIN exam_candidate c2 ON c2.idprocess=t2.idprocess
    ->   INNER JOIN exam_grade n2 ON n2.idprocess=t2.idprocess
    ->   AND n2.testtype=t2.type
    ->   AND n2.idboard=b2.id
    ->   AND n2.idcandidate=c2.id
    ->   WHERE t2.idprocess=1
    ->   AND t2.order<5
    ->   AND t2.aplied='Y'
    ->   AND t2.eliminatory='Y'
    ->   GROUP BY t2.idprocess, t2.type, c2.id
    ->   HAVING AVG(n2.grade)<t2.minimumgrade;
ERROR 1055 (42000): 'xc1.t2.minimumgrade' isn't in GROUP BY
[29 Apr 2014 14:00] Allan Souza
I'm really sorry about this bug report. On my database the columns t2.idprocess t2.type are primary keys, and I though that if the primary keys of a table where on the group by clause the other columns of that particular table could be in the having clause the same way as in the select clause (no random value on each query execution). Thank you so much. I learned a huge lesson here today. Could I suggest one thing? Isn't the ONLY_FULL_GROUP_BY a great default value for the sql_mode?