| Bug #44305 | Unknown column on having clause | ||
|---|---|---|---|
| Submitted: | 16 Apr 2009 6:37 | Modified: | 28 May 2009 9:41 |
| Reporter: | Paulo Cesar | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.30-community | OS: | Linux (Red Hat) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | column, Concat_WS, grave accent, having, UNKNOWN | ||
[16 Apr 2009 6:40]
Paulo Cesar
Reverse acute is actually grave accent sorry about that (my natural language is Portuguese)
[16 Apr 2009 6:49]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with test data. Please provide output of SHOW CREATE TABLE for all underlying tables.
[16 Apr 2009 7:14]
Paulo Cesar
CREATE TABLE `veiculos` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`label` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`renavam` varchar(9) COLLATE utf8_unicode_ci DEFAULT NULL,
`placa` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Letras e numeros',
`owner` int(10) unsigned DEFAULT NULL,
`modelo` tinyint(3) unsigned DEFAULT NULL COMMENT 'Na tabela ''modelos''',
`licensa` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Array no PHP',
`custom` tinyint(1) DEFAULT '0',
`last_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `RENAVAM` (`renavam`),
UNIQUE KEY `placa` (`placa`),
UNIQUE KEY `owner` (`label`,`owner`),
KEY `reowner` (`owner`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1
CREATE TABLE `condutores` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`owner` int(10) unsigned DEFAULT NULL,
`nome` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`nascimento` date DEFAULT NULL,
`experiencia` tinyint(4) DEFAULT NULL,
`carteira` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
`telefone` varchar(14) COLLATE utf8_unicode_ci DEFAULT NULL,
`celular` varchar(14) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`preferencias` set('tel','cel','email') COLLATE utf8_unicode_ci DEFAULT 'cel',
`nickname` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`sexo` tinyint(1) DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `carteira` (`carteira`),
UNIQUE KEY `owner` (`owner`,`nome`),
UNIQUE KEY `nickname` (`owner`,`nickname`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `modelos` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`capacidade` tinyint(4) DEFAULT NULL,
`marca` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`modelo` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`tipo` tinyint(4) DEFAULT NULL COMMENT 'Van, onibus, carro',
PRIMARY KEY (`id`),
KEY `NewIndex1` (`tipo`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `tipos` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`nome` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `tipo` (`nome`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `creditos` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`compra` date DEFAULT NULL,
`expira` date DEFAULT NULL,
`quantidade` smallint(5) DEFAULT '1',
`owner` int(10) unsigned DEFAULT NULL,
`status` enum('pendente','ativo') COLLATE utf8_unicode_ci DEFAULT 'pendente',
PRIMARY KEY (`id`),
KEY `owner` (`owner`),
KEY `expira` (`expira`),
KEY `NewIndex1` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `xcreditos` (
`credito_id` int(10) unsigned NOT NULL DEFAULT '0',
`transporte_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`credito_id`,`transporte_id`),
UNIQUE KEY `credito_id` (`transporte_id`,`credito_id`),
UNIQUE KEY `transporte_id` (`transporte_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `cidades` (
`id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`uf` tinyint(3) unsigned NOT NULL DEFAULT '0',
`nome` varchar(64) COLLATE utf8_unicode_ci DEFAULT '',
`capital` tinyint(1) DEFAULT '0',
`metropolitana` mediumint(8) unsigned DEFAULT '0',
PRIMARY KEY (`id`,`uf`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `turnos` (
`id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`nome` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `servicos` (
`id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Id do tipo de serviço',
`nome` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Nome do serviço',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `caracteristicas` (
`id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Id da característica',
`nome` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Nome da característica',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `locais` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nome` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`by` int(10) unsigned DEFAULT NULL,
`ip` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`date` datetime DEFAULT NULL,
`cidade` mediumint(8) unsigned DEFAULT NULL,
`estado` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cidade` (`cidade`,`estado`)
) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `bairros` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nome` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`cidade` mediumint(8) unsigned DEFAULT NULL,
`uf` tinyint(3) unsigned DEFAULT NULL,
`by` int(10) unsigned DEFAULT '0',
`data` date DEFAULT NULL,
`ip` varchar(16) COLLATE utf8_unicode_ci DEFAULT '0.0.0.0',
PRIMARY KEY (`id`),
KEY `cidade` (`cidade`,`uf`)
) ENGINE=MyISAM AUTO_INCREMENT=126 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Nome do evento',
`content` text COLLATE utf8_unicode_ci COMMENT 'Descrição do evento',
`date` datetime DEFAULT NULL COMMENT 'Data que acontecerá',
`termino` date DEFAULT NULL,
`visitas` int(10) unsigned DEFAULT '0' COMMENT 'Visitas na página do evento',
`cidade` mediumint(8) unsigned DEFAULT NULL,
`estado` tinyint(3) unsigned DEFAULT NULL,
`local` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`site` text COLLATE utf8_unicode_ci,
`by` int(10) unsigned DEFAULT NULL COMMENT 'Premio por ter sugerido evento',
`pic_id` int(10) unsigned DEFAULT NULL,
`tags` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `title` (`title`),
KEY `cidade` (`cidade`,`estado`),
KEY `date` (`date`),
KEY `pic_id` (`pic_id`),
KEY `NewIndex1` (`estado`)
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
[16 Apr 2009 7:41]
Sveta Smirnova
Thank you for the feedback. Please provide output of SHOW CREATE TABLE transportes
[17 Apr 2009 1:55]
Paulo Cesar
CREATE TABLE `transportes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`owner` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'From ''registered''',
`label` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`condutor` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'From ''condutores''',
`veiculo` mediumint(8) unsigned DEFAULT '0' COMMENT 'From ''veiculos''',
`extra` text COLLATE utf8_unicode_ci,
`visitas` int(10) unsigned DEFAULT '0',
`ultima` datetime DEFAULT NULL COMMENT 'Ultima visita a este transportador',
`status` set('pending','visible','only','auto') COLLATE utf8_unicode_ci DEFAULT 'pending',
`last_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `label` (`owner`,`label`),
KEY `condutor` (`condutor`),
KEY `veiculo` (`veiculo`),
KEY `owner` (`owner`,`condutor`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
[28 Apr 2009 9:40]
Sveta Smirnova
Thank you for the feedback. Please also provide output of SHOW CREATE TABLE estados and xcidades_estados
[28 May 2009 23:00]
Bugs System
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".
Description: I've stumbled upon a very weird bug. I was on one shared hosting and they had MYSQL 5.0. I recently changed to another host, with 5.1.30, and my query was unable to be compiled (was working fine before). After strugling a few hours with it, I noticed the reverse acute (`) on HAVING CONCAT_WS(" ",`alias` was giving me the error. It's a search feature on one my websites, it's a complicated query through a lot of joins, so I can't show exacly how to drop/create tables. Faulty code below, returns Error Code : 1054 Unknown column 'cidade' in 'having clause' How to repeat: SELECT DISTINCT `transportes`.`id` AS `id`, `transportes`.`extra` AS `extra`, (SELECT GROUP_CONCAT(`cidades`.`nome`," (", `estados`.`short`,")" SEPARATOR ", ") FROM `estados`,`cidades`,`xcidades_estados` WHERE `xcidades_estados`.`transporte_id` = `transportes`.`id` AND `cidades`.`id` = `xcidades_estados`.`cidade_id` AND `estados`.`id` = `xcidades_estados`.`estado_id`) AS `cidade`, (SELECT GROUP_CONCAT(`turnos`.`nome` SEPARATOR ", ") FROM `turnos`,`xturnos` WHERE `xturnos`.`transporte_id` = `transportes`.`id` AND `turnos`.`id` = `xturnos`.`turno_id`) AS `turnos`, (SELECT GROUP_CONCAT(`servicos`.`nome` SEPARATOR ", ") FROM `servicos`,`xservicos` WHERE `xservicos`.`transporte_id` = `transportes`.`id` AND `servicos`.`id` = `xservicos`.`servico_id`) AS `servicos`, (SELECT GROUP_CONCAT(`caracteristicas`.`nome` SEPARATOR ", ") FROM `caracteristicas`,`xcaracteristicas` WHERE `xcaracteristicas`.`transporte_id` = `transportes`.`id` AND `caracteristicas`.`id` = `xcaracteristicas`.`caracteristica_id`) AS `caracteristicas`, (SELECT GROUP_CONCAT(`locais`.`nome` SEPARATOR ", ") FROM `locais`,`xlocais` WHERE `xlocais`.`transporte_id` = `transportes`.`id` AND `locais`.`id` = `xlocais`.`locais_id`) AS `locais`, (SELECT GROUP_CONCAT(`bairros`.`nome` SEPARATOR ", ") FROM `bairros`,`xbairros` WHERE `xbairros`.`transporte_id` = `transportes`.`id` AND `bairros`.`id` = `xbairros`.`bairro_id`) AS `bairros`, (SELECT GROUP_CONCAT(`events`.`title` SEPARATOR ", ") FROM `events`,`xevents` WHERE `xevents`.`transporte_id` = `transportes`.`id` AND `events`.`id` = `xevents`.`evento_id` AND CURDATE() <= `events`.`termino`) AS `eventos`, (SELECT GROUP_CONCAT(`events`.`tags`," ",`events`.`local` SEPARATOR ",") FROM `events`,`xevents` WHERE `xevents`.`transporte_id` = `transportes`.`id` AND `events`.`id` = `xevents`.`evento_id` AND CURDATE() <= `events`.`termino`) AS `tags`, `condutores`.`nickname` AS `nome`, `modelos`.`marca` AS `marca`, `modelos`.`modelo` AS `modelo`, `modelos`.`capacidade` AS `capacidade`, `tipos`.`nome` AS `tiponome` FROM `transportes` INNER JOIN `veiculos` ON `transportes`.`veiculo`=`veiculos`.`id` INNER JOIN `condutores` ON `transportes`.`condutor`=`condutores`.`id` INNER JOIN `modelos` ON `veiculos`.`modelo`=`modelos`.`id` INNER JOIN `tipos` ON `modelos`.`tipo`=`tipos`.`id` INNER JOIN `creditos` ON `creditos`.`expira` >= NOW() AND `creditos`.`status`="ativo" INNER JOIN `xcreditos` ON `xcreditos`.`transporte_id`=`transportes`.`id` AND `creditos`.`id`=`xcreditos`.`credito_id` WHERE FIND_IN_SET("pending",`transportes`.`status`) = 0 AND FIND_IN_SET("visible",`transportes`.`status`) > 0 HAVING CONCAT_WS(" ", `cidade`,`tags`,`turnos`,`extra`,`servicos`,`caracteristicas`,`locais`,`bairros`,`modelo`,`marca`,`tiponome`,`eventos`) LIKE "%belo%" This was working fine before, but as soon the host changed, it started to choke. Notice the HAVING CONCAT_WS(" ", `cidade`,`tags`,`turnos`,`extra`,`servicos`,`caracteristicas`,`locais`,`bairros`,`modelo`,`marca`,`tiponome`,`eventos`) Suggested fix: SELECT DISTINCT `transportes`.`id` AS `id`, `transportes`.`extra` AS `extra`, (SELECT GROUP_CONCAT(`cidades`.`nome`," (", `estados`.`short`,")" SEPARATOR ", ") FROM `estados`,`cidades`,`xcidades_estados` WHERE `xcidades_estados`.`transporte_id` = `transportes`.`id` AND `cidades`.`id` = `xcidades_estados`.`cidade_id` AND `estados`.`id` = `xcidades_estados`.`estado_id`) AS `cidade`, (SELECT GROUP_CONCAT(`turnos`.`nome` SEPARATOR ", ") FROM `turnos`,`xturnos` WHERE `xturnos`.`transporte_id` = `transportes`.`id` AND `turnos`.`id` = `xturnos`.`turno_id`) AS `turnos`, (SELECT GROUP_CONCAT(`servicos`.`nome` SEPARATOR ", ") FROM `servicos`,`xservicos` WHERE `xservicos`.`transporte_id` = `transportes`.`id` AND `servicos`.`id` = `xservicos`.`servico_id`) AS `servicos`, (SELECT GROUP_CONCAT(`caracteristicas`.`nome` SEPARATOR ", ") FROM `caracteristicas`,`xcaracteristicas` WHERE `xcaracteristicas`.`transporte_id` = `transportes`.`id` AND `caracteristicas`.`id` = `xcaracteristicas`.`caracteristica_id`) AS `caracteristicas`, (SELECT GROUP_CONCAT(`locais`.`nome` SEPARATOR ", ") FROM `locais`,`xlocais` WHERE `xlocais`.`transporte_id` = `transportes`.`id` AND `locais`.`id` = `xlocais`.`locais_id`) AS `locais`, (SELECT GROUP_CONCAT(`bairros`.`nome` SEPARATOR ", ") FROM `bairros`,`xbairros` WHERE `xbairros`.`transporte_id` = `transportes`.`id` AND `bairros`.`id` = `xbairros`.`bairro_id`) AS `bairros`, (SELECT GROUP_CONCAT(`events`.`title` SEPARATOR ", ") FROM `events`,`xevents` WHERE `xevents`.`transporte_id` = `transportes`.`id` AND `events`.`id` = `xevents`.`evento_id` AND CURDATE() <= `events`.`termino`) AS `eventos`, (SELECT GROUP_CONCAT(`events`.`tags`," ",`events`.`local` SEPARATOR ",") FROM `events`,`xevents` WHERE `xevents`.`transporte_id` = `transportes`.`id` AND `events`.`id` = `xevents`.`evento_id` AND CURDATE() <= `events`.`termino`) AS `tags`, `condutores`.`nickname` AS `nome`, `modelos`.`marca` AS `marca`, `modelos`.`modelo` AS `modelo`, `modelos`.`capacidade` AS `capacidade`, `tipos`.`nome` AS `tiponome` FROM `transportes` INNER JOIN `veiculos` ON `transportes`.`veiculo`=`veiculos`.`id` INNER JOIN `condutores` ON `transportes`.`condutor`=`condutores`.`id` INNER JOIN `modelos` ON `veiculos`.`modelo`=`modelos`.`id` INNER JOIN `tipos` ON `modelos`.`tipo`=`tipos`.`id` INNER JOIN `creditos` ON `creditos`.`expira` >= NOW() AND `creditos`.`status`="ativo" INNER JOIN `xcreditos` ON `xcreditos`.`transporte_id`=`transportes`.`id` AND `creditos`.`id`=`xcreditos`.`credito_id` WHERE FIND_IN_SET("pending",`transportes`.`status`) = 0 AND FIND_IN_SET("visible",`transportes`.`status`) > 0 HAVING CONCAT_WS(" ", cidade,tags,turnos,extra,servicos,caracteristicas,locais,bairros,modelo,marca,tiponome,eventos) LIKE "%belo%" This code is working, without the ` between the aliases.