Bug #12922 wrong results with GROUP BY on VIEW
Submitted: 1 Sep 2005 1:57 Modified: 8 Sep 2005 19:15
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.11 OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[1 Sep 2005 1:57] Kolbe Kegel
Description:
In some situations, a query using GROUP BY on a VIEW can cause wrong results to be returned.

How to repeat:

DROP TABLE IF EXISTS items_viewtest_t;
CREATE TABLE `items_viewtest_t` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(32) NOT NULL,
  `plural` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `items_viewtest_t` VALUES (1,'1_n',NULL),(2,'2_n',NULL),(3,'3_n','3_p_t'),(4,'4_n',NULL),(5,'5_n','5_p_t'),(6,'6_n',NULL),(7,'7_n',NULL),(8,'8_n','8_p_t'),(9,'9_n',NULL),(10,'10_n',NULL),(11,'11_n',NULL),(12,'12_n','12_p_t'),(13,'13_n',NULL),(14,'14_n',NULL),(15,'15_n',NULL);

DROP TABLE IF EXISTS actions_viewtest;
CREATE TABLE `actions_viewtest` (
  `id` int(10) unsigned NOT NULL default '0',
  `item` int(10) unsigned NOT NULL default '0',
  `quantity` int(11) NOT NULL default '1'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `actions_viewtest` VALUES (1,1,1),(2,1,1),(3,1,1),(4,2,1),(5,2,2),(6,3,1),(7,3,1),(8,4,1),(9,5,2),(10,5,3),(11,6,1),(12,6,2),(13,7,1),(14,7,2);

DROP TABLE IF EXISTS items_viewtest_v;
DROP VIEW IF EXISTS items_viewtest_v;
CREATE VIEW items_viewtest_v AS select id, name, ifnull(plural,concat(name,'_s')) AS plural from items_viewtest_t;

/* SELECT from table works as expected */
SELECT sum(a.quantity) sum, i.id, i.name, i.plural, if(sum(a.quantity)>1,ifnull(plural,concat(name,'_s')),name) chosen FROM actions_viewtest a, items_viewtest_t i WHERE a.item=i.id group by i.id, i.plural, i.name;

/* SELECT from view, should produce the same results for column "chosen" as previous query */
SELECT sum(a.quantity) sum, i.id, i.name, i.plural, if(sum(a.quantity)>1,plural,name) chosen FROM actions_viewtest a, items_viewtest_v i WHERE a.item=i.id group by i.id, i.plural, i.name;

/* The following queries are simply to show that the problem lies in VIEWs and not some other portion of the parser or optimizer */

drop table if exists items_viewtest_x;
create table items_viewtest_x select * from items_viewtest_v;

/* SELECT from table created from the view works as expected*/
SELECT sum(a.quantity) sum, i.id, i.name, i.plural, if(sum(a.quantity)>1,plural,name) chosen FROM actions_viewtest a, items_viewtest_x i WHERE a.item=i.id group by i.id, i.plural, i.name;

/* SELECT from derived table works as expected*/ 
SELECT sum(a.quantity) sum, i.id, i.name, i.plural, if(sum(a.quantity)>1,plural,name) chosen FROM actions_viewtest a, (select `viewtest`.`items_viewtest_t`.`id` AS `id`,`viewtest`.`items_viewtest_t`.`name` AS `name`,ifnull(`viewtest`.`items_viewtest_t`.`plural`,concat(`viewtest`.`items_viewtest_t`.`name`,_utf8'_s')) AS `plural` from `viewtest`.`items_viewtest_t`) i WHERE a.item=i.id group by i.id, i.plural, i.name;
[6 Sep 2005 19:38] 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/internals/29393
[7 Sep 2005 17:49] 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/internals/29449
[7 Sep 2005 18:39] 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/internals/29454
[7 Sep 2005 20:44] Evgeny Potemkin
Fields of view represented by Item_direct_view_ref. When complex expression
such as if(sum()>...,...) is split to simpler parts refs was ignored.
Beside this direct ref doesn't use it's result_field and thus can't store
it's result in tmp table which is needed for sum() ... group.
All this results in reported bug.

Fixed in 5.0.13, cset 1.1957.4.1
[8 Sep 2005 19:15] Paul Dubois
Noted in 5.0.13 changelog.