Bug #20466 SELECT with GROUP BY from view missing data when trigger defined on base table
Submitted: 14 Jun 2006 18:02 Modified: 7 Aug 2006 8:24
Reporter: Jrme Despatis (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22 OS:Linux (Linux 2.6.15 Debian Testing)
Assigned to: Georgi Kodinov CPU Architecture:Any

[14 Jun 2006 18:02] Jrme Despatis
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;
[21 Jul 2006 6:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9402
[21 Jul 2006 15:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9418
[21 Jul 2006 17:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9426
[2 Aug 2006 19:06] Evgeny Potemkin
When making a place to store field values at the start of each group
the real item (not the reference) must be used when deciding which column
to copy.

Fixed in 5.0.25, 5.1.12
[7 Aug 2006 8:24] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs.