bzr push into mysql-5.1-bugteam branch (Georgi.Kodinov:3465 to 3466) Bug#55188

[Georgi Kodinov]

 3466 Georgi Kodinov	2010-07-30
      Bug #55188: GROUP BY, GROUP_CONCAT and TEXT - inconsistent results
      
      In order to be able to check if the set of the grouping fields in a 
      GROUP BY has changed (and thus to start a new group) the optimizer
      caches the current values of these fields in a set of Cached_item 
      derived objects.
      The Cached_item_str, used for caching varchar and TEXT columns,
      is limited in length by the max_sort_length variable.
      A String buffer to store the value with an alloced length of either
      the max length of the string or the value of max_sort_length 
      (whichever is smaller) in Cached_item_str's constructor.
      Then, at compare time the value of the string to compare to was 
      truncated to the alloced length of the string buffer inside 
      Cached_item_str.
      This is all fine and valid, but only if you're not assigning 
      values near or equal to the alloced length of this buffer.
      Because when assigning values like this the alloced length is 
      rounded up and as a result the next set of data will not match the
      group buffer, thus leading to wrong results because of the changed
      alloced_length.
      Fixed by preserving the original maximum length in the 
      Cached_item_str's constructor and using this instead of the 
      alloced_length to limit the string to compare to.
      Test case added.

    modified:
      mysql-test/r/group_by.result
      mysql-test/t/group_by.test
      sql/item.h
      sql/item_buff.cc
 3465 Davi Arnaut	2010-07-30 [merge]
      Merge of mysql-5.0-bugteam into mysql-5.1-bugteam.

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2010-05-12 16:10:33 +0000
+++ b/mysql-test/r/group_by.result	2010-07-30 13:35:06 +0000
@@ -1810,4 +1810,39 @@ MAX(t2.a)
 2
 DROP TABLE t1, t2;
 #
+# Bug#55188: GROUP BY, GROUP_CONCAT and TEXT - inconsistent results
+#
+CREATE TABLE t1 (a text, b varchar(10));
+INSERT INTO t1 VALUES (repeat('1', 1300),'one'), (repeat('1', 1300),'two');
+EXPLAIN 
+SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a;
+id	1
+select_type	SIMPLE
+table	t1
+type	ALL
+possible_keys	NULL
+key	NULL
+key_len	NULL
+ref	NULL
+rows	2
+Extra	Using filesort
+SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a;
+SUBSTRING(a,1,10)	LENGTH(a)	GROUP_CONCAT(b)
[phone]	one,two
+EXPLAIN 
+SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a;
+id	1
+select_type	SIMPLE
+table	t1
+type	ALL
+possible_keys	NULL
+key	NULL
+key_len	NULL
+ref	NULL
+rows	2
+Extra	Using temporary; Using filesort
+SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a;
+SUBSTRING(a,1,10)	LENGTH(a)
[phone]
+DROP TABLE t1;
 # End of 5.1 tests

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2010-05-12 16:10:33 +0000
+++ b/mysql-test/t/group_by.test	2010-07-30 13:35:06 +0000
@@ -1221,5 +1221,19 @@ DROP TABLE t1, t2;
 
 
 --echo #
+--echo # Bug#55188: GROUP BY, GROUP_CONCAT and TEXT - inconsistent results
+--echo #
+
+CREATE TABLE t1 (a text, b varchar(10));
+INSERT INTO t1 VALUES (repeat('1', 1300),'one'), (repeat('1', 1300),'two');
+
+query_vertical EXPLAIN 
+SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a;
+SELECT SUBSTRING(a,1,10), LENGTH(a), GROUP_CONCAT(b) FROM t1 GROUP BY a;
+query_vertical EXPLAIN 
+SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a;
+SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a;
+DROP TABLE t1;
+
 
 --echo # End of 5.1 tests

=== modified file 'sql/item.h'
--- a/sql/item.h	2010-05-11 14:39:51 +0000
+++ b/sql/item.h	2010-07-30 13:35:06 +0000
@@ -2740,6 +2740,7 @@ public:
 class Cached_item_str :public Cached_item
 {
   Item *item;
+  uint32 value_max_length;
   String value,tmp_value;
 public:
   Cached_item_str(THD *thd, Item *arg);

=== modified file 'sql/item_buff.cc'
--- a/sql/item_buff.cc	2007-10-11 17:29:09 +0000
+++ b/sql/item_buff.cc	2010-07-30 13:35:06 +0000
@@ -58,7 +58,9 @@ Cached_item::~Cached_item() {}
 */
 
 Cached_item_str::Cached_item_str(THD *thd, Item *arg)
-  :item(arg), value(min(arg->max_length, thd->variables.max_sort_length))
+  :item(arg),
+   value_max_length(min(arg->max_length, thd->variables.max_sort_length)),
+   value(value_max_length)
 {}
 
 bool Cached_item_str::cmp(void)
@@ -67,7 +69,7 @@ bool Cached_item_str::cmp(void)
   bool tmp;
 
   if ((res=item->val_str(&tmp_value)))
-    res->length(min(res->length(), value.alloced_length()));
+    res->length(min(res->length(), value_max_length));
   if (null_value != item->null_value)
   {
     if ((null_value= item->null_value))


Attachment: [text/bzr-bundle] bzr/[e-mail]