Description:
When trying to run a query with a field from a temporary table in the HAVING clause, it returns a "ERROR 1054: Unknown column '<column_name>' in 'having clause'"
How to repeat:
This is the two queries used (one creates the temporary table, the other is the actual SELECT statement containing HAVING):
mysql> create temporary table cert_aux1
-> select cert_id, and_grp, count(*) as target
-> from cert_data group by cert_id, and_grp;
Query OK, 11 rows affected (0.92 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from cert_aux1;
+---------+---------+--------+
| cert_id | and_grp | target |
+---------+---------+--------+
| 6 | 0 | 3 |
| 6 | 1 | 3 |
| 6 | 2 | 3 |
| 7 | 0 | 1 |
| 7 | 2 | 1 |
| 7 | 3 | 1 |
| 7 | 4 | 1 |
| 8 | 0 | 1 |
| 8 | 1 | 1 |
| 8 | 2 | 1 |
| 8 | 4 | 1 |
+---------+---------+--------+
11 rows in set (1.35 sec)
mysql> select p.name, crt.name cert, cd.and_grp, (count(t.course_id) - target) as score
-> from (((training t inner join courses c using (course_id))
-> inner join cert_data cd using (course_id)
-> inner join certificates crt using (cert_id))
-> inner join persons p on p.person_id = t.person_id)
-> inner join cert_aux1 a1 on cd.cert_id = a1.cert_id and cd.and_grp = a1.and_grp
-> where
-> t.person_id = 1230
-> and t.date_certified < now()
-> and (c.expiry is null or date_add(t.date_certified, interval c.expiry month) > now())
-> and t.course_id in (select course_id from cert_data cd1
-> where cd1.cert_id = cd.cert_id and cd1.and_grp = cd.and_grp)
-> group by p.name, crt.name, cd.cert_id, cd.and_grp, target
-> having (count(t.course_id) - target) >= 0;
ERROR 1054: Unknown column 'target' in 'having clause'
mysql>
I've also tried the combinations a1.target, cert_aux1.target, and target, to no avail.
Server is 4.1.0-alpha-max-nt-log, tables are InnoDB.
The same query without a HAVING clause returns the following:
mysql> select p.name, crt.name cert, cd.and_grp, (count(t.course_id) - target) as score
-> from (((training t inner join courses c using (course_id))
-> inner join cert_data cd using (course_id)
-> inner join certificates crt using (cert_id))
-> inner join persons p on p.person_id = t.person_id)
-> inner join cert_aux1 a1 on cd.cert_id = a1.cert_id and cd.and_grp = a1.and_grp
-> where
-> t.person_id = 1230
-> and t.date_certified < now()
-> and (c.expiry is null or date_add(t.date_certified, interval c.expiry month) > now())
-> and t.course_id in (select course_id from cert_data cd1
-> where cd1.cert_id = cd.cert_id and cd1.and_grp = cd.and_grp)
-> group by p.name, crt.name, cd.cert_id, cd.and_grp, target;
+-------------+-----------------------+---------+-------+
| name | cert | and_grp | score |
+-------------+-----------------------+---------+-------+
| Diego Berge | Driver | 0 | -1 |
| Diego Berge | Driver | 1 | 0 |
| Diego Berge | Driver | 2 | -1 |
| Diego Berge | Driver Terrain Skills | 4 | 0 |
| Diego Berge | Driver Vehicle Skills | 3 | 0 |
+-------------+-----------------------+---------+-------+
5 rows in set (1.74 sec)
...and the idea is to filter out the rows with negative scores.
Here's the schema for all those tables (if necessary, a small dataset can be provided):
mysql> show create table training \G
*************************** 1. row ***************************
Table: training
Create Table: CREATE TABLE `training` (
`course_id` int(11) NOT NULL default '0',
`person_id` int(11) NOT NULL default '0',
`date_certified` date NOT NULL default '0000-00-00',
`remarks` char(255) character set latin1 default NULL,
`user` char(32) character set latin1 NOT NULL default '',
`ts_update` timestamp NOT NULL,
PRIMARY KEY (`course_id`,`person_id`,`date_certified`),
KEY `person_id` (`person_id`),
FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`person_id`) REFERENCES `persons` (`person_id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB
1 row in set (1.37 sec)
mysql> show create table courses \G
*************************** 1. row ***************************
Table: courses
Create Table: CREATE TABLE `courses` (
`course_id` int(11) NOT NULL auto_increment,
`name` char(64) character set latin1 NOT NULL default '',
`code` char(4) character set latin1 NOT NULL default '',
`description` char(255) character set latin1 NOT NULL default '',
`expiry` int(11) default NULL,
`user` char(32) character set latin1 NOT NULL default '',
`ts_update` timestamp NOT NULL,
PRIMARY KEY (`course_id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `code` (`code`)
) TYPE=InnoDB
1 row in set (1.35 sec)
mysql> show create table cert_data \G
*************************** 1. row ***************************
Table: cert_data
Create Table: CREATE TABLE `cert_data` (
`cert_id` int(11) NOT NULL default '0',
`and_grp` int(11) NOT NULL default '0',
`course_id` int(11) NOT NULL default '0',
PRIMARY KEY (`cert_id`,`and_grp`,`course_id`),
KEY `course_id` (`course_id`),
FOREIGN KEY (`cert_id`) REFERENCES `certificates` (`cert_id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB
1 row in set (1.58 sec)
mysql> show create table certificates \G
*************************** 1. row ***************************
Table: certificates
Create Table: CREATE TABLE `certificates` (
`cert_id` int(11) NOT NULL auto_increment,
`name` char(64) character set latin1 NOT NULL default '',
`description` char(255) character set latin1 NOT NULL default '',
`user` char(32) character set latin1 NOT NULL default '',
`ts_update` timestamp NOT NULL,
PRIMARY KEY (`cert_id`),
UNIQUE KEY `name` (`name`)
) TYPE=InnoDB
1 row in set (1.41 sec)
mysql> show create table persons \G
*************************** 1. row ***************************
Table: persons
Create Table: CREATE TABLE `persons` (
`person_id` int(11) NOT NULL auto_increment,
`name` char(64) character set latin1 NOT NULL default '',
`emp_no` int(11) NOT NULL default '0',
`company_id` int(11) NOT NULL default '0',
`job_title` char(64) character set latin1 NOT NULL default '',
`location_id` int(11) NOT NULL default '0',
`ldap_dn` char(128) character set latin1 default NULL,
`status` enum('Active','Inactive','Blacklisted') character set latin1 NOT NULL default 'Active',
`remarks` char(255) character set latin1 default NULL,
`user` char(32) character set latin1 NOT NULL default '',
`ts_update` timestamp NOT NULL,
PRIMARY KEY (`person_id`),
UNIQUE KEY `company_id` (`company_id`,`emp_no`),
KEY `location_id` (`location_id`),
FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB
1 row in set (1.42 sec)
mysql> show create table cert_aux1 \G
*************************** 1. row ***************************
Table: cert_aux1
Create Table: CREATE TEMPORARY TABLE `cert_aux1` (
`cert_id` int(11) NOT NULL default '0',
`and_grp` int(11) NOT NULL default '0',
`target` bigint(21) NOT NULL default '0'
) TYPE=MyISAM CHARSET=latin1
1 row in set (1.60 sec)
mysql>
Also tried changing the type of cert_aux1 to InnoDB via
create temporary table cert_aux1 type=innodb
select cert_id, and_grp, count(*) as target
from cert_data group by cert_id, and_grp;
but it didn't make any difference.
Regards,
Diego Berge.
Description: When trying to run a query with a field from a temporary table in the HAVING clause, it returns a "ERROR 1054: Unknown column '<column_name>' in 'having clause'" How to repeat: This is the two queries used (one creates the temporary table, the other is the actual SELECT statement containing HAVING): mysql> create temporary table cert_aux1 -> select cert_id, and_grp, count(*) as target -> from cert_data group by cert_id, and_grp; Query OK, 11 rows affected (0.92 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from cert_aux1; +---------+---------+--------+ | cert_id | and_grp | target | +---------+---------+--------+ | 6 | 0 | 3 | | 6 | 1 | 3 | | 6 | 2 | 3 | | 7 | 0 | 1 | | 7 | 2 | 1 | | 7 | 3 | 1 | | 7 | 4 | 1 | | 8 | 0 | 1 | | 8 | 1 | 1 | | 8 | 2 | 1 | | 8 | 4 | 1 | +---------+---------+--------+ 11 rows in set (1.35 sec) mysql> select p.name, crt.name cert, cd.and_grp, (count(t.course_id) - target) as score -> from (((training t inner join courses c using (course_id)) -> inner join cert_data cd using (course_id) -> inner join certificates crt using (cert_id)) -> inner join persons p on p.person_id = t.person_id) -> inner join cert_aux1 a1 on cd.cert_id = a1.cert_id and cd.and_grp = a1.and_grp -> where -> t.person_id = 1230 -> and t.date_certified < now() -> and (c.expiry is null or date_add(t.date_certified, interval c.expiry month) > now()) -> and t.course_id in (select course_id from cert_data cd1 -> where cd1.cert_id = cd.cert_id and cd1.and_grp = cd.and_grp) -> group by p.name, crt.name, cd.cert_id, cd.and_grp, target -> having (count(t.course_id) - target) >= 0; ERROR 1054: Unknown column 'target' in 'having clause' mysql> I've also tried the combinations a1.target, cert_aux1.target, and target, to no avail. Server is 4.1.0-alpha-max-nt-log, tables are InnoDB. The same query without a HAVING clause returns the following: mysql> select p.name, crt.name cert, cd.and_grp, (count(t.course_id) - target) as score -> from (((training t inner join courses c using (course_id)) -> inner join cert_data cd using (course_id) -> inner join certificates crt using (cert_id)) -> inner join persons p on p.person_id = t.person_id) -> inner join cert_aux1 a1 on cd.cert_id = a1.cert_id and cd.and_grp = a1.and_grp -> where -> t.person_id = 1230 -> and t.date_certified < now() -> and (c.expiry is null or date_add(t.date_certified, interval c.expiry month) > now()) -> and t.course_id in (select course_id from cert_data cd1 -> where cd1.cert_id = cd.cert_id and cd1.and_grp = cd.and_grp) -> group by p.name, crt.name, cd.cert_id, cd.and_grp, target; +-------------+-----------------------+---------+-------+ | name | cert | and_grp | score | +-------------+-----------------------+---------+-------+ | Diego Berge | Driver | 0 | -1 | | Diego Berge | Driver | 1 | 0 | | Diego Berge | Driver | 2 | -1 | | Diego Berge | Driver Terrain Skills | 4 | 0 | | Diego Berge | Driver Vehicle Skills | 3 | 0 | +-------------+-----------------------+---------+-------+ 5 rows in set (1.74 sec) ...and the idea is to filter out the rows with negative scores. Here's the schema for all those tables (if necessary, a small dataset can be provided): mysql> show create table training \G *************************** 1. row *************************** Table: training Create Table: CREATE TABLE `training` ( `course_id` int(11) NOT NULL default '0', `person_id` int(11) NOT NULL default '0', `date_certified` date NOT NULL default '0000-00-00', `remarks` char(255) character set latin1 default NULL, `user` char(32) character set latin1 NOT NULL default '', `ts_update` timestamp NOT NULL, PRIMARY KEY (`course_id`,`person_id`,`date_certified`), KEY `person_id` (`person_id`), FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`person_id`) REFERENCES `persons` (`person_id`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB 1 row in set (1.37 sec) mysql> show create table courses \G *************************** 1. row *************************** Table: courses Create Table: CREATE TABLE `courses` ( `course_id` int(11) NOT NULL auto_increment, `name` char(64) character set latin1 NOT NULL default '', `code` char(4) character set latin1 NOT NULL default '', `description` char(255) character set latin1 NOT NULL default '', `expiry` int(11) default NULL, `user` char(32) character set latin1 NOT NULL default '', `ts_update` timestamp NOT NULL, PRIMARY KEY (`course_id`), UNIQUE KEY `name` (`name`), UNIQUE KEY `code` (`code`) ) TYPE=InnoDB 1 row in set (1.35 sec) mysql> show create table cert_data \G *************************** 1. row *************************** Table: cert_data Create Table: CREATE TABLE `cert_data` ( `cert_id` int(11) NOT NULL default '0', `and_grp` int(11) NOT NULL default '0', `course_id` int(11) NOT NULL default '0', PRIMARY KEY (`cert_id`,`and_grp`,`course_id`), KEY `course_id` (`course_id`), FOREIGN KEY (`cert_id`) REFERENCES `certificates` (`cert_id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB 1 row in set (1.58 sec) mysql> show create table certificates \G *************************** 1. row *************************** Table: certificates Create Table: CREATE TABLE `certificates` ( `cert_id` int(11) NOT NULL auto_increment, `name` char(64) character set latin1 NOT NULL default '', `description` char(255) character set latin1 NOT NULL default '', `user` char(32) character set latin1 NOT NULL default '', `ts_update` timestamp NOT NULL, PRIMARY KEY (`cert_id`), UNIQUE KEY `name` (`name`) ) TYPE=InnoDB 1 row in set (1.41 sec) mysql> show create table persons \G *************************** 1. row *************************** Table: persons Create Table: CREATE TABLE `persons` ( `person_id` int(11) NOT NULL auto_increment, `name` char(64) character set latin1 NOT NULL default '', `emp_no` int(11) NOT NULL default '0', `company_id` int(11) NOT NULL default '0', `job_title` char(64) character set latin1 NOT NULL default '', `location_id` int(11) NOT NULL default '0', `ldap_dn` char(128) character set latin1 default NULL, `status` enum('Active','Inactive','Blacklisted') character set latin1 NOT NULL default 'Active', `remarks` char(255) character set latin1 default NULL, `user` char(32) character set latin1 NOT NULL default '', `ts_update` timestamp NOT NULL, PRIMARY KEY (`person_id`), UNIQUE KEY `company_id` (`company_id`,`emp_no`), KEY `location_id` (`location_id`), FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB 1 row in set (1.42 sec) mysql> show create table cert_aux1 \G *************************** 1. row *************************** Table: cert_aux1 Create Table: CREATE TEMPORARY TABLE `cert_aux1` ( `cert_id` int(11) NOT NULL default '0', `and_grp` int(11) NOT NULL default '0', `target` bigint(21) NOT NULL default '0' ) TYPE=MyISAM CHARSET=latin1 1 row in set (1.60 sec) mysql> Also tried changing the type of cert_aux1 to InnoDB via create temporary table cert_aux1 type=innodb select cert_id, and_grp, count(*) as target from cert_data group by cert_id, and_grp; but it didn't make any difference. Regards, Diego Berge.