Description:
I have a view on a table, but on certain circumstances, (on a group by on the view), the result is mixed
How to repeat:
Here is the content of a sql script you just need to load :
--------------------------------------- TEST.sql----------------------------------
DROP TABLE IF EXISTS `traductions`;
CREATE TABLE `traductions` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`label_table` varchar(255) NOT NULL,
`label_champ` varchar(255) NOT NULL,
`ref` mediumint(8) unsigned NOT NULL,
`texte` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `ref` (`ref`),
KEY `label_table` (`label_table`),
KEY `label_champ` (`label_champ`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `ex_pays`;
CREATE TABLE `ex_pays` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`pays` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ex_pays` VALUES (1,'-----'),(6,'Allemagne'),(17,'Autriche'),(25,'Belgique'),(54,'Danemark'),(62,'Espagne'),(68,'France'),(109,'Italie'),(169,'Pays-Bas'),(186,'Royaume-Uni'),(206,'SuÚde'),(207,'Suisse');
DROP TABLE IF EXISTS `ex_portails`;
CREATE TABLE `ex_portails` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`ref_pays` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `ref_pays` (`ref_pays`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ex_portails` VALUES (1,1),(2,1),(6,6),(18,17),(15,25),(16,25),(17,25),(10,54),(5,62),(3,68),(9,109),(8,169),(4,186),(7,206),(11,207),(12,207),(13,207),(14,207);
DELIMITER ;;
CREATE TRIGGER `AD_ex_portails_utime` AFTER DELETE ON `ex_portails` FOR EACH ROW BEGIN REPLACE INTO `update_times` VALUES ('ex_portails', NOW()); END;;
DELIMITER ;
DROP VIEW IF EXISTS `__vue_ex_pays_fr`;
CREATE VIEW `__vue_ex_pays_fr` AS select ex_pays.id,if((`traduc1`.`texte` is not null),`traduc1`.`texte`,`ex_pays`.`pays`) AS `pays` from (`ex_pays` left join `traductions` `traduc1` on(((`traduc1`.`label_table` = _utf8'pays') and (`traduc1`.`label_champ` = _utf8'pays') and (`traduc1`.`ref` = `ex_pays`.`id`))));
-------------------------------TEST.sql---------------------------------
Now some queries:
To see what is in the table: (this result of this query is correct)
mysql> select pays.id, pays from ex_pays as pays, ex_portails where ex_portails.ref_pays=pays.id;
+-----+-------------+
| id | pays |
+-----+-------------+
| 1 | ----- |
| 1 | ----- |
| 6 | Allemagne |
| 17 | Autriche |
| 25 | Belgique |
| 25 | Belgique |
| 25 | Belgique |
| 54 | Danemark |
| 62 | Espagne |
| 68 | France |
| 109 | Italie |
| 169 | Pays-Bas |
| 186 | Royaume-Uni |
| 206 | SuÚde |
| 207 | Suisse |
| 207 | Suisse |
| 207 | Suisse |
| 207 | Suisse |
+-----+-------------+
Same query now, on the view, i get the same result, which is correct:
mysql> select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id;
+-----+-------------+
| id | pays |
+-----+-------------+
| 1 | ----- |
| 1 | ----- |
| 6 | Allemagne |
| 17 | Autriche |
| 25 | Belgique |
| 25 | Belgique |
| 25 | Belgique |
| 54 | Danemark |
| 62 | Espagne |
| 68 | France |
| 109 | Italie |
| 169 | Pays-Bas |
| 186 | Royaume-Uni |
| 206 | SuÚde |
| 207 | Suisse |
| 207 | Suisse |
| 207 | Suisse |
| 207 | Suisse |
+-----+-------------+
But now, i'd like to group this results to have distinct results, so i do this query, and the result is perfectly good:
mysql> select pays.id, pays from ex_pays as pays, ex_portails where ex_portails.ref_pays=pays.id group by ref_pays;
+-----+-------------+
| id | pays |
+-----+-------------+
| 1 | ----- |
| 6 | Allemagne |
| 17 | Autriche |
| 25 | Belgique |
| 54 | Danemark |
| 62 | Espagne |
| 68 | France |
| 109 | Italie |
| 169 | Pays-Bas |
| 186 | Royaume-Uni |
| 206 | SuÚde |
| 207 | Suisse |
+-----+-------------+
BUT, if i do the same thing on the viewn here is the result:
mysql> select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id group by ref_pays;
+-----+-------------+
| id | pays |
+-----+-------------+
| 1 | Allemagne |
| 6 | Autriche |
| 17 | Belgique |
| 25 | Danemark |
| 54 | Espagne |
| 62 | France |
| 68 | Italie |
| 109 | Pays-Bas |
| 169 | Royaume-Uni |
| 186 | SuÚde |
| 206 | Suisse |
| 207 | NULL |
+-----+-------------+
the result is completly mixed ! in fact a line in the field 'pays' has been dropped
Suggested fix:
The weird thing is to make this query work:
delete in the TEST.sql:
DELIMITER ;;
CREATE TRIGGER `AD_ex_portails_utime` AFTER DELETE ON `ex_portails` FOR EACH ROW BEGIN REPLACE INTO `update_times` VALUES ('ex_portails', NOW()); END;;
DELIMITER ;
than reload the TEST.sql, and all queries work fine !
For now, i can make the last query work, by doing the GROUP BY on the view, and not on the table ex_portails
query that does not work:
select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id group by ex_portails.ref_pays;
query that works:
select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id group by pays.id;
Description: I have a view on a table, but on certain circumstances, (on a group by on the view), the result is mixed How to repeat: Here is the content of a sql script you just need to load : --------------------------------------- TEST.sql---------------------------------- DROP TABLE IF EXISTS `traductions`; CREATE TABLE `traductions` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `label_table` varchar(255) NOT NULL, `label_champ` varchar(255) NOT NULL, `ref` mediumint(8) unsigned NOT NULL, `texte` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `ref` (`ref`), KEY `label_table` (`label_table`), KEY `label_champ` (`label_champ`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `ex_pays`; CREATE TABLE `ex_pays` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `pays` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `ex_pays` VALUES (1,'-----'),(6,'Allemagne'),(17,'Autriche'),(25,'Belgique'),(54,'Danemark'),(62,'Espagne'),(68,'France'),(109,'Italie'),(169,'Pays-Bas'),(186,'Royaume-Uni'),(206,'SuÚde'),(207,'Suisse'); DROP TABLE IF EXISTS `ex_portails`; CREATE TABLE `ex_portails` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `ref_pays` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `ref_pays` (`ref_pays`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `ex_portails` VALUES (1,1),(2,1),(6,6),(18,17),(15,25),(16,25),(17,25),(10,54),(5,62),(3,68),(9,109),(8,169),(4,186),(7,206),(11,207),(12,207),(13,207),(14,207); DELIMITER ;; CREATE TRIGGER `AD_ex_portails_utime` AFTER DELETE ON `ex_portails` FOR EACH ROW BEGIN REPLACE INTO `update_times` VALUES ('ex_portails', NOW()); END;; DELIMITER ; DROP VIEW IF EXISTS `__vue_ex_pays_fr`; CREATE VIEW `__vue_ex_pays_fr` AS select ex_pays.id,if((`traduc1`.`texte` is not null),`traduc1`.`texte`,`ex_pays`.`pays`) AS `pays` from (`ex_pays` left join `traductions` `traduc1` on(((`traduc1`.`label_table` = _utf8'pays') and (`traduc1`.`label_champ` = _utf8'pays') and (`traduc1`.`ref` = `ex_pays`.`id`)))); -------------------------------TEST.sql--------------------------------- Now some queries: To see what is in the table: (this result of this query is correct) mysql> select pays.id, pays from ex_pays as pays, ex_portails where ex_portails.ref_pays=pays.id; +-----+-------------+ | id | pays | +-----+-------------+ | 1 | ----- | | 1 | ----- | | 6 | Allemagne | | 17 | Autriche | | 25 | Belgique | | 25 | Belgique | | 25 | Belgique | | 54 | Danemark | | 62 | Espagne | | 68 | France | | 109 | Italie | | 169 | Pays-Bas | | 186 | Royaume-Uni | | 206 | SuÚde | | 207 | Suisse | | 207 | Suisse | | 207 | Suisse | | 207 | Suisse | +-----+-------------+ Same query now, on the view, i get the same result, which is correct: mysql> select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id; +-----+-------------+ | id | pays | +-----+-------------+ | 1 | ----- | | 1 | ----- | | 6 | Allemagne | | 17 | Autriche | | 25 | Belgique | | 25 | Belgique | | 25 | Belgique | | 54 | Danemark | | 62 | Espagne | | 68 | France | | 109 | Italie | | 169 | Pays-Bas | | 186 | Royaume-Uni | | 206 | SuÚde | | 207 | Suisse | | 207 | Suisse | | 207 | Suisse | | 207 | Suisse | +-----+-------------+ But now, i'd like to group this results to have distinct results, so i do this query, and the result is perfectly good: mysql> select pays.id, pays from ex_pays as pays, ex_portails where ex_portails.ref_pays=pays.id group by ref_pays; +-----+-------------+ | id | pays | +-----+-------------+ | 1 | ----- | | 6 | Allemagne | | 17 | Autriche | | 25 | Belgique | | 54 | Danemark | | 62 | Espagne | | 68 | France | | 109 | Italie | | 169 | Pays-Bas | | 186 | Royaume-Uni | | 206 | SuÚde | | 207 | Suisse | +-----+-------------+ BUT, if i do the same thing on the viewn here is the result: mysql> select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id group by ref_pays; +-----+-------------+ | id | pays | +-----+-------------+ | 1 | Allemagne | | 6 | Autriche | | 17 | Belgique | | 25 | Danemark | | 54 | Espagne | | 62 | France | | 68 | Italie | | 109 | Pays-Bas | | 169 | Royaume-Uni | | 186 | SuÚde | | 206 | Suisse | | 207 | NULL | +-----+-------------+ the result is completly mixed ! in fact a line in the field 'pays' has been dropped Suggested fix: The weird thing is to make this query work: delete in the TEST.sql: DELIMITER ;; CREATE TRIGGER `AD_ex_portails_utime` AFTER DELETE ON `ex_portails` FOR EACH ROW BEGIN REPLACE INTO `update_times` VALUES ('ex_portails', NOW()); END;; DELIMITER ; than reload the TEST.sql, and all queries work fine ! For now, i can make the last query work, by doing the GROUP BY on the view, and not on the table ex_portails query that does not work: select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id group by ex_portails.ref_pays; query that works: select pays.id, pays from __vue_ex_pays_fr as pays, ex_portails where ex_portails.ref_pays=pays.id group by pays.id;