diff options
| -rw-r--r-- | mysql-test/main/func_gconcat.result | 53 | ||||
| -rw-r--r-- | mysql-test/main/func_gconcat.test | 7 | ||||
| -rw-r--r-- | sql/item_sum.cc | 40 | ||||
| -rw-r--r-- | sql/item_sum.h | 3 |
4 files changed, 65 insertions, 38 deletions
diff --git a/mysql-test/main/func_gconcat.result b/mysql-test/main/func_gconcat.result index e0da659f750..01bf45056c3 100644 --- a/mysql-test/main/func_gconcat.result +++ b/mysql-test/main/func_gconcat.result @@ -363,8 +363,8 @@ bb,ccc,a,bb,ccc BB,CCC,A,BB,CCC select group_concat(distinct b) from t1 group by a; group_concat(distinct b) -bb,ccc,a -BB,CCC,A +a,bb,ccc +A,BB,CCC select group_concat(b order by b) from t1 group by a; group_concat(b order by b) a,bb,bb,ccc,ccc @@ -383,11 +383,11 @@ Warning 1260 Row 2 was cut by GROUP_CONCAT() Warning 1260 Row 4 was cut by GROUP_CONCAT() select group_concat(distinct b) from t1 group by a; group_concat(distinct b) -bb,c -BB,C +a,bb +A,BB Warnings: -Warning 1260 Row 2 was cut by GROUP_CONCAT() -Warning 1260 Row 4 was cut by GROUP_CONCAT() +Warning 1260 Row 3 was cut by GROUP_CONCAT() +Warning 1260 Row 6 was cut by GROUP_CONCAT() select group_concat(b order by b) from t1 group by a; group_concat(b order by b) a,bb @@ -413,8 +413,8 @@ bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111 BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 select group_concat(distinct b) from t1 group by a; group_concat(distinct b) -bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 -BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,ccc +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,CCC select group_concat(b order by b) from t1 group by a; group_concat(b order by b) 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,bb,ccc,ccc @@ -433,11 +433,11 @@ Warning 1260 Row 7 was cut by GROUP_CONCAT() Warning 1260 Row 14 was cut by GROUP_CONCAT() select group_concat(distinct b) from t1 group by a; group_concat(distinct b) -bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 Warnings: -Warning 1260 Row 5 was cut by GROUP_CONCAT() -Warning 1260 Row 10 was cut by GROUP_CONCAT() +Warning 1260 Row 2 was cut by GROUP_CONCAT() +Warning 1260 Row 4 was cut by GROUP_CONCAT() select group_concat(b order by b) from t1 group by a; group_concat(b order by b) 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 @@ -520,9 +520,9 @@ a group_concat(b) NULL 3,4,2,1,2,7,3,3 select a, group_concat(distinct b) from t1 group by a with rollup; a group_concat(distinct b) -1 3,4,2,1 -2 7,3 -NULL 3,4,2,1,7 +1 1,2,3,4 +2 3,7 +NULL 1,2,3,4,7 select a, group_concat(b order by b) from t1 group by a with rollup; a group_concat(b order by b) 1 1,2,2,3,4 @@ -745,10 +745,10 @@ CREATE TABLE t1(a TEXT, b CHAR(20)); INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3"); SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1; GROUP_CONCAT(DISTINCT UCASE(a)) -ONE.1,TWO.2,ONE.3 +ONE.1,ONE.3,TWO.2 SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; GROUP_CONCAT(DISTINCT UCASE(b)) -ONE.1,TWO.2,ONE.3 +ONE.1,ONE.3,TWO.2 DROP TABLE t1; CREATE TABLE t1( a VARCHAR( 10 ), b INT ); INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), @@ -847,7 +847,7 @@ create table t1(a bit(2) not null); insert into t1 values (1), (0), (0), (3), (1); select group_concat(distinct a) from t1; group_concat(distinct a) -1,0,3 +0,1,3 select group_concat(distinct a order by a) from t1; group_concat(distinct a order by a) 0,1,3 @@ -860,13 +860,13 @@ insert into t1 values (1, 'a', 0), (0, 'b', 1), (0, 'c', 0), (3, 'd', 1), (1, 'e', 1), (3, 'f', 1), (0, 'g', 1); select group_concat(distinct a, c) from t1; group_concat(distinct a, c) -10,01,00,31,11 +00,01,10,11,31 select group_concat(distinct a, c order by a) from t1; group_concat(distinct a, c order by a) 00,01,11,10,31 select group_concat(distinct a, c) from t1; group_concat(distinct a, c) -10,01,00,31,11 +00,01,10,11,31 select group_concat(distinct a, c order by a, c) from t1; group_concat(distinct a, c order by a, c) 00,01,10,11,31 @@ -1333,8 +1333,8 @@ select grp,group_concat(c limit 5.5...' at line 1 select grp,group_concat(distinct c limit 1,10 ) from t1 group by grp; grp group_concat(distinct c limit 1,10 ) 1 c -2 b -3 C,D +2 c +3 D,E select grp,group_concat(c order by a) from t1 group by grp; grp group_concat(c order by a) 1 b,c @@ -1370,6 +1370,15 @@ grp group_concat(c order by c desc limit 2) 1 c,b 2 c,b 3 E,E +# +# Empty results for group concat as offset is greater than the rows +# for a group +# +select grp,group_concat(distinct c limit 10,1 ) from t1 group by grp; +grp group_concat(distinct c limit 10,1 ) +1 +2 +3 drop table t1; create table t2 (a int, b varchar(10)); insert into t2 values(1,'a'),(1,'b'),(NULL,'c'),(2,'x'),(2,'y'); diff --git a/mysql-test/main/func_gconcat.test b/mysql-test/main/func_gconcat.test index a76568bdae3..2ab856e0edd 100644 --- a/mysql-test/main/func_gconcat.test +++ b/mysql-test/main/func_gconcat.test @@ -986,6 +986,13 @@ select grp,group_concat(c order by c limit 2) from t1 group by grp; select grp,group_concat(c order by c desc) from t1 group by grp; select grp,group_concat(c order by c desc limit 2) from t1 group by grp; +--echo # +--echo # Empty results for group concat as offset is greater than the rows +--echo # for a group +--echo # + +select grp,group_concat(distinct c limit 10,1 ) from t1 group by grp; + drop table t1; create table t2 (a int, b varchar(10)); diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 86c0f4700bb..d9c971b9919 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -3632,23 +3632,26 @@ int dump_leaf_key(void* key_arg, element_count count __attribute__((unused)), ulonglong *offset_limit= &item->copy_offset_limit; ulonglong *row_limit = &item->copy_row_limit; if (item->limit_clause && !(*row_limit)) + { + item->result_finalized= true; return 1; - - if (item->no_appended) - item->no_appended= FALSE; - else - result->append(*item->separator); + } tmp.length(0); if (item->limit_clause && (*offset_limit)) { item->row_count++; - item->no_appended= TRUE; (*offset_limit)--; return 0; } + if (!item->result_finalized) + item->result_finalized= true; + else + result->append(*item->separator); + + for (; arg < arg_end; arg++) { String *res; @@ -3904,7 +3907,7 @@ void Item_func_group_concat::clear() result.copy(); null_value= TRUE; warning_for_row= FALSE; - no_appended= TRUE; + result_finalized= false; if (offset_limit) copy_offset_limit= offset_limit->val_int(); if (row_limit) @@ -4040,12 +4043,10 @@ bool Item_func_group_concat::add(bool exclude_nulls) tree_len+= row_str_len; } /* - If the row is not a duplicate (el->count == 1) - we can dump the row here in case of GROUP_CONCAT(DISTINCT...) - instead of doing tree traverse later. + In case of GROUP_CONCAT with DISTINCT or ORDER BY (or both) don't dump the + row to the output buffer here. That will be done in val_str. */ - if (row_eligible && !warning_for_row && - (!tree || (el->count == 1 && distinct && !arg_count_order))) + if (row_eligible && !warning_for_row && (!tree && !distinct)) dump_leaf_key(table->record[0] + table->s->null_bytes, 1, this); return 0; @@ -4278,9 +4279,18 @@ String* Item_func_group_concat::val_str(String* str) DBUG_ASSERT(fixed == 1); if (null_value) return 0; - if (no_appended && tree) - /* Tree is used for sorting as in ORDER BY */ - tree_walk(tree, &dump_leaf_key, this, left_root_right); + + if (!result_finalized) // Result yet to be written. + { + if (tree != NULL) // order by + tree_walk(tree, &dump_leaf_key, this, left_root_right); + else if (distinct) // distinct (and no order by). + unique_filter->walk(table, &dump_leaf_key, this); + else if (row_limit && copy_row_limit == (ulonglong)row_limit->val_int()) + return &result; + else + DBUG_ASSERT(false); // Can't happen + } if (table && table->blob_storage && table->blob_storage->is_truncated_value()) diff --git a/sql/item_sum.h b/sql/item_sum.h index d1b9303d3d6..b1dff9acd4a 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -1887,7 +1887,8 @@ protected: bool warning_for_row; bool always_null; bool force_copy_fields; - bool no_appended; + /** True if entire result of GROUP_CONCAT has been written to output buffer. */ + bool result_finalized; /** Limits the rows in the result */ Item *row_limit; /** Skips a particular number of rows in from the result*/ |