Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL), 11.1(EOL)
-
None
Description
Wrong message on second execution of prepare statement for GROUP_CONCAT() function with optimizer_switch='outer_join_with_cache=off' (with 'outer_join_with_cache=on' messages are correct ) and left join:
SET optimizer_switch='outer_join_with_cache=off'; |
|
|
set group_concat_max_len=5; |
create table t1 (a int, b varchar(20)); |
create table t2 (a int, c varchar(20)); |
insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb"); |
insert into t2 values (1,"cccccccccc"),(2,"dddddddddd"); |
|
|
prepare stmt1 from "select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a"; |
|
|
execute stmt1; |
execute stmt1; |
|
|
deallocate prepare stmt1; |
|
|
drop table t1, t2; |
set group_concat_max_len=default; |
Actual result:
execute stmt1; |
group_concat(t1.b,t2.c)
|
aaaaa
|
bbbbb
|
Warnings:
|
Warning 1260 Row 1 was cut by GROUP_CONCAT() |
Warning 1260 Row 2 was cut by GROUP_CONCAT() |
execute stmt1; |
group_concat(t1.b,t2.c)
|
aaaaa
|
bbbbb
|
Warnings:
|
Warning 1260 Row 3 was cut by GROUP_CONCAT() |
Warning 1260 Row 4 was cut by GROUP_CONCAT() |
deallocate prepare stmt1; |
Expected result:
execute stmt1; |
group_concat(t1.b,t2.c)
|
aaaaa
|
bbbbb
|
Warnings:
|
Warning 1260 Row 1 was cut by GROUP_CONCAT() |
Warning 1260 Row 2 was cut by GROUP_CONCAT() |
execute stmt1; |
group_concat(t1.b,t2.c)
|
aaaaa
|
bbbbb
|
Warnings:
|
Warning 1260 Row 1 was cut by GROUP_CONCAT() |
Warning 1260 Row 2 was cut by GROUP_CONCAT() |
deallocate prepare stmt1; |
Attachments
Issue Links
- relates to
-
MDEV-31003
Second execution for ps-protocol
-
- Closed
-
Activity
Note that when we do not allow to use join cache table t1 is first sorted and because group by list depends only on table t1 the aggregation can be performed when reading the rows from the sorted table and joining them with the rows from t2. No temporary table is needed in this case. When join cache is used the aggregation is performed after the join operation has been completed. In this case a temporary table is used. So the aggregation is performed over the columns of this temporary table rather than the columns of the base table. In this case the original Item_func_group_concat item created by the parser when the prepare command is executed cannot be used for aggregation. A variant of it bound to the temporary table must be used. In the current code such a Item_func_group_concat object is created for each execution of the PS and it has Item_func_group_concat::row_count set to 0. In the case when join cache is not used the server continues using the Item_func_group_concat created by prepare command and no code resets
Item_func_group_concat::row_count to 0 for each new execution of the prepared statement.
Please review this really simple patch. See my comments in the MDEV and the tree bb-10.5-igor.
A fix for this bug was pushed into 10.5. It has to be merged upstream as it is. It also can be cherry-picked into 10.4 if it's really needed there.
The same problem can be reproduced with the join query
if we do not allow to use join cache:
MariaDB [test]> set join_cache_level=0;Query OK, 0 rows affected (0.000 sec)MariaDB [test]> set group_concat_max_len=12;Query OK, 0 rows affected (0.001 sec)MariaDB [test]> explain select group_concat(t1.b,t2.c) from t1 join t2 on t1.a=t2.a group by t1.a;+------+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+----------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort || 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |+------+-------------+-------+------+---------------+------+---------+------+------+----------------+2 rows in set (0.002 sec)MariaDB [test]> prepare stmt from "select group_concat(t1.b,t2.c) from t1 join t2 on t1.a=t2.a group by t1.a";Query OK, 0 rows affected (0.001 sec)Statement preparedMariaDB [test]> execute stmt;+-------------------------+| group_concat(t1.b,t2.c) |+-------------------------+| aaaaaaaaaacc || bbbbbbbbbbdd |+-------------------------+2 rows in set, 2 warnings (0.002 sec)MariaDB [test]> show warnings;+---------+------+---------------------------------+| Level | Code | Message |+---------+------+---------------------------------+| Warning | 1260 | Row 1 was cut by GROUP_CONCAT() || Warning | 1260 | Row 2 was cut by GROUP_CONCAT() |+---------+------+---------------------------------+2 rows in set (0.000 sec)MariaDB [test]> execute stmt;+-------------------------+| group_concat(t1.b,t2.c) |+-------------------------+| aaaaaaaaaacc || bbbbbbbbbbdd |+-------------------------+2 rows in set, 2 warnings (0.001 sec)MariaDB [test]> show warnings;+---------+------+---------------------------------+| Level | Code | Message |+---------+------+---------------------------------+| Warning | 1260 | Row 3 was cut by GROUP_CONCAT() || Warning | 1260 | Row 4 was cut by GROUP_CONCAT() |+---------+------+---------------------------------+2 rows in set (0.000 sec)Let's add more records to table t2:
We see the same problem:
MariaDB [test]> set group_concat_max_len=36;Query OK, 0 rows affected (0.000 sec)MariaDB [test]> prepare stmt from "select group_concat(t1.b,t2.c) from t1 join t2 on t1.a=t2.a group by t1.a";Query OK, 0 rows affected (0.001 sec)Statement preparedMariaDB [test]> execute stmt;+--------------------------------------+| group_concat(t1.b,t2.c) |+--------------------------------------+| aaaaaaaaaacccccccccc,aaaaaaaaaaeeeee || bbbbbbbbbbdddddddddd,bbbbbbbbbbfffff |+--------------------------------------+2 rows in set, 2 warnings (0.003 sec)MariaDB [test]> show warnings;+---------+------+---------------------------------+| Level | Code | Message |+---------+------+---------------------------------+| Warning | 1260 | Row 2 was cut by GROUP_CONCAT() || Warning | 1260 | Row 4 was cut by GROUP_CONCAT() |+---------+------+---------------------------------+2 rows in set (0.000 sec)MariaDB [test]> execute stmt;+--------------------------------------+| group_concat(t1.b,t2.c) |+--------------------------------------+| aaaaaaaaaacccccccccc,aaaaaaaaaaeeeee || bbbbbbbbbbdddddddddd,bbbbbbbbbbfffff |+--------------------------------------+2 rows in set, 2 warnings (0.003 sec)MariaDB [test]> show warnings;+---------+------+---------------------------------+| Level | Code | Message |+---------+------+---------------------------------+| Warning | 1260 | Row 6 was cut by GROUP_CONCAT() || Warning | 1260 | Row 8 was cut by GROUP_CONCAT() |+---------+------+---------------------------------+2 rows in set (0.000 sec)If we allow to use join buffer then the warnings from the second execution of the prepared statement are the same as the warnings from the first execution.
MariaDB [test]> set join_cache_level=default;Query OK, 0 rows affected (0.000 sec)MariaDB [test]> explain select group_concat(t1.b,t2.c) from t1 join t2 on t1.a=t2.a group by t1.a;+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort || 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+2 rows in set (0.001 sec)MariaDB [test]> prepare stmt from "select group_concat(t1.b,t2.c) from t1 join t2 on t1.a=t2.a group by t1.a";Query OK, 0 rows affected (0.001 sec)Statement preparedMariaDB [test]> execute stmt;+--------------------------------------+| group_concat(t1.b,t2.c) |+--------------------------------------+| aaaaaaaaaacccccccccc,aaaaaaaaaaeeeee || bbbbbbbbbbdddddddddd,bbbbbbbbbbfffff |+--------------------------------------+2 rows in set, 2 warnings (0.003 sec)MariaDB [test]> show warnings;+---------+------+---------------------------------+| Level | Code | Message |+---------+------+---------------------------------+| Warning | 1260 | Row 2 was cut by GROUP_CONCAT() || Warning | 1260 | Row 4 was cut by GROUP_CONCAT() |+---------+------+---------------------------------+2 rows in set (0.000 sec)MariaDB [test]> execute stmt;+--------------------------------------+| group_concat(t1.b,t2.c) |+--------------------------------------+| aaaaaaaaaaeeeeeee,aaaaaaaaaacccccccc || bbbbbbbbbbdddddddddd,bbbbbbbbbbfffff |+--------------------------------------+2 rows in set, 2 warnings (0.003 sec)MariaDB [test]> show warnings;+---------+------+---------------------------------+| Level | Code | Message |+---------+------+---------------------------------+| Warning | 1260 | Row 2 was cut by GROUP_CONCAT() || Warning | 1260 | Row 4 was cut by GROUP_CONCAT() |+---------+------+---------------------------------+2 rows in set (0.000 sec)