MySQL Bugs: #12922: wrong results with GROUP BY on VIEW

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

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;

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

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

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

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

Noted in 5.0.13 changelog.