MySQL Bugs: #11412: Views and group

Bug #11412	Views and group_concat
Submitted:	17 Jun 2005 11:36	Modified:	3 Aug 2005 17:16
Reporter:	Tom Chantry	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.7	OS:	Windows (Win2k)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
There seems to be a problem using group_concat with Views - I assume this is allowed ?

It seems to only return one row when group_concat is used 

How to repeat:
CREATE TABLE `test`.`temptable` (
  `_aid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `_firstname` VARCHAR(45) NOT NULL,
  `_lastname` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`_aid`)
)
ENGINE = MYISAM;

CREATE TABLE `test`.`temptable2` (
  `_aid` INTEGER UNSIGNED NOT NULL,
  `_pid` INTEGER UNSIGNED NOT NULL
)
ENGINE = MYISAM;

mysql> insert into temptable values(1,'a','b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into temptable values(2,'c','d');
Query OK, 1 row affected (0.00 sec)

mysql> insert into temptable2 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into temptable2 values (2,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into temptable2 values (2,2);
Query OK, 1 row affected (0.00 sec)

mysql> create view simpleview as select t1.*,t2._pid from temptable t1, temptabl
e2 t2 where t1._aid = t2._aid;
Query OK, 0 rows affected (0.01 sec)

mysql> select _pid,group_concat(concat(_firstname,' ',_lastname)) from simplevie
w group by _pid;
+------+------------------------------------------------+
| _pid | group_concat(concat(_firstname,' ',_lastname)) |
+------+------------------------------------------------+
|    2 | a b,c d,c d                                    |
+------+------------------------------------------------+
1 row in set (0.01 sec)

What it should be:

mysql> select _pid,group_concat(concat(_firstname,' ',_lastname)) from temptable
 t1, temptable2 t2 where t1._aid = t2._aid group by _pid;
+------+------------------------------------------------+
| _pid | group_concat(concat(_firstname,' ',_lastname)) |
+------+------------------------------------------------+
|    1 | a b,c d                                        |
|    2 | c d                                            |
+------+------------------------------------------------+

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/27563

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/27603

ChangeSet
  1.1895 05/07/25 12:57:23 igor@rurik.mysql.com +5 -0
  sql_select.cc:
    Fixed bug #11412.
    Reversed the patch of cs 1.1934 for the function 
    create_tmp_table. Modified the function to support
    Item_ref objects created for view fields.
  item_buff.cc:
    Fixed bug #11412.
    Modified implementation of new_Cached_item to support
    cacheing of view fields.
  item.h:
    Fixed bug #11412.
    Changed implementation of Item_ref::get_tmp_table_field and
    added Item_ref::get_tmp_table_item to support Item_ref objects
    created for view fields.
  view.test, view.result:
    Added a test case for bug #11412.

ChangeSet
  1.1892 05/07/26 08:20:42 igor@rurik.mysql.com +1 -0
  item.h:
    Post review change in Item_ref::get_tmp_table_field (bug #11412).

The fix will appear in 5.0.11.

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 5.0.11 changelog; closed.