[#MDEV-28899] group_concat result are NOT the same after enclosing by VIEW object in some condition

Found group_concat result are NOT the same after enclosing by VIEW object in some condition. It is not critical issue in our case because we are not that syntax. But it worth to make them the same result after enclosing by VIEW. Seems the problem is the extra "SELECT" between group_concat and trim functions. Below is the test case.

MariaDB [testdb1]> -- good case - result are the same after enclosing by VIEW object

MariaDB [testdb1]> select trim( both ',' from ( group_concat(c2) ) ) AS output from t1 ;

+--------+

| output |

+--------+

| A,B    |

+--------+

1 row in set (0.001 sec)

MariaDB [testdb1]> create or replace view v1 AS select trim( both ',' from ( group_concat(c2) ) ) AS output from t1 ;

Query OK, 0 rows affected (0.004 sec)

MariaDB [testdb1]> select * from v1 ;

+--------+

| output |

+--------+

| A,B    |

+--------+

1 row in set (0.006 sec)

MariaDB [testdb1]> -- bad case - result are NOT the same after enclosing by VIEW object

MariaDB [testdb1]> select trim( both ',' from (select group_concat(c2) ) ) AS output from t1 ;

+--------+

| output |

+--------+

| A,B    |

+--------+

1 row in set (0.001 sec)

MariaDB [testdb1]> create or replace view v2 AS select trim( both ',' from (select group_concat(c2) ) ) AS output from t1 ;

Query OK, 0 rows affected (0.004 sec)

MariaDB [testdb1]> select * from v2 ;

+--------+

| output |

+--------+

| A      |

| B      |

+--------+

2 rows in set (0.001 sec)

[MDEV-28899] group_concat result are NOT the same after enclosing by VIEW object in some condition Created: 2022-06-20 Updated: 2023-04-27
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.6.7
Fix Version/s:	10.6

[MDEV-28899] group_concat result are NOT the same after enclosing by VIEW object in some condition Created: 2022-06-20 Updated: 2023-04-27