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.