#At file:///mnt/sda7/work/52160-5.1-bugteam/ based on revid:bjorn.munch@stripped
3547 Gleb Shchepa 2010-10-26
Bug #52160: crash and inconsistent results when grouping
by a function and column
The bugreport reveals two different bugs about grouping
on a function:
1) grouping by the TIME_TO_SEC function result caused
a server crash or wrong results and
2) grouping by the function returning a blob caused
an unexpected "Duplicate entry" error and wrong
result.
Details for the 1st bug:
TIME_TO_SEC() returns NULL if its argument is invalid (empty
string for example). Thus its nullability depends not only
on the nullability of its arguments but also on their values.
Fixed by (overoptimistically) setting TIME_TO_SEC() to be
nullable despite the nullability of its arguments.
Details for the 2nd bug:
The server is unable to create indices on blobs without
explicit blob key part length. However, this fact was
ignored for blob function result fields of GROUP BY
intermediate tables.
Fixed by disabling GROUP BY index creation for blob
function result fields like regular blob fields.
@ mysql-test/r/func_time.result
Test case for bug #52160.
@ mysql-test/r/type_blob.result
Test case for bug #52160.
@ mysql-test/t/func_time.test
Test case for bug #52160.
@ mysql-test/t/type_blob.test
Test case for bug #52160.
@ sql/item_timefunc.h
Bug #52160: crash and inconsistent results when grouping
by a function and column
TIME_TO_SEC() returns NULL if its argument is invalid (empty
string for example). Thus its nullability depends not only
Fixed by (overoptimistically) setting TIME_TO_SEC() to be
nullable despite the nullability of its arguments.
@ sql/sql_select.cc
Bug #52160: crash and inconsistent results when grouping
by a function and column
The server is unable to create indices on blobs without
explicit blob key part length. However, this fact was
ignored for blob function result fields of GROUP BY
intermediate tables.
Fixed by disabling GROUP BY index creation for blob
function result fields like regular blob fields.
modified:
mysql-test/r/func_time.result
mysql-test/r/type_blob.result
mysql-test/t/func_time.test
mysql-test/t/type_blob.test
sql/item_timefunc.h
sql/sql_select.cc
=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result 2010-08-13 13:05:46 +0000
+++ b/mysql-test/r/func_time.result 2010-10-26 18:23:20 +0000
@@ -1343,4 +1343,18 @@ SELECT 1 FROM t1 ORDER BY @x:=makedate(a
1
1
DROP TABLE t1;
+#
+# Bug #52160: crash and inconsistent results when grouping
+# by a function and column
+#
+CREATE TABLE t1(a CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES (''),('');
+SELECT COUNT(*) FROM t1 GROUP BY TIME_TO_SEC(a);
+COUNT(*)
+2
+Warnings:
+Warning 1292 Truncated incorrect time value: ''
+Warning 1292 Truncated incorrect time value: ''
+Warning 1292 Truncated incorrect time value: ''
+DROP TABLE t1;
End of 5.1 tests
=== modified file 'mysql-test/r/type_blob.result'
--- a/mysql-test/r/type_blob.result 2010-02-09 10:30:50 +0000
+++ b/mysql-test/r/type_blob.result 2010-10-26 18:23:20 +0000
@@ -974,3 +974,14 @@ ERROR 42000: Display width out of range
explain select convert(1, binary(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
ERROR 42000: Display width out of range for column 'cast as char' (max = 4294967295)
End of 5.0 tests
+# Bug #52160: crash and inconsistent results when grouping
+# by a function and column
+CREATE FUNCTION f1() RETURNS TINYBLOB RETURN 1;
+CREATE TABLE t1(a CHAR(1));
+INSERT INTO t1 VALUES ('0'), ('0');
+SELECT COUNT(*) FROM t1 GROUP BY f1(), a;
+COUNT(*)
+2
+DROP FUNCTION f1;
+DROP TABLE t1;
+End of 5.1 tests
=== modified file 'mysql-test/t/func_time.test'
--- a/mysql-test/t/func_time.test 2010-08-13 13:05:46 +0000
+++ b/mysql-test/t/func_time.test 2010-10-26 18:23:20 +0000
@@ -849,4 +849,14 @@ INSERT INTO t1 VALUES (0),(9.216e-096);
SELECT 1 FROM t1 ORDER BY @x:=makedate(a,a);
DROP TABLE t1;
+--echo #
+--echo # Bug #52160: crash and inconsistent results when grouping
+--echo # by a function and column
+--echo #
+
+CREATE TABLE t1(a CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES (''),('');
+SELECT COUNT(*) FROM t1 GROUP BY TIME_TO_SEC(a);
+DROP TABLE t1;
+
--echo End of 5.1 tests
=== modified file 'mysql-test/t/type_blob.test'
--- a/mysql-test/t/type_blob.test 2008-05-30 09:12:07 +0000
+++ b/mysql-test/t/type_blob.test 2010-10-26 18:23:20 +0000
@@ -612,3 +612,19 @@ explain select convert(1, binary(4294967
explain select convert(1, binary(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999));
--echo End of 5.0 tests
+
+--echo # Bug #52160: crash and inconsistent results when grouping
+--echo # by a function and column
+
+CREATE FUNCTION f1() RETURNS TINYBLOB RETURN 1;
+
+CREATE TABLE t1(a CHAR(1));
+INSERT INTO t1 VALUES ('0'), ('0');
+
+SELECT COUNT(*) FROM t1 GROUP BY f1(), a;
+
+DROP FUNCTION f1;
+DROP TABLE t1;
+
+--echo End of 5.1 tests
+
=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h 2010-09-22 19:33:18 +0000
+++ b/sql/item_timefunc.h 2010-10-26 18:23:20 +0000
@@ -331,6 +331,7 @@ public:
const char *func_name() const { return "time_to_sec"; }
void fix_length_and_dec()
{
+ maybe_null= TRUE;
decimals=0;
max_length=10*MY_CHARSET_BIN_MB_MAXLEN;
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-10-05 08:11:56 +0000
+++ b/sql/sql_select.cc 2010-10-26 18:23:20 +0000
@@ -15198,6 +15198,8 @@ calc_group_buffer(JOIN *join,ORDER *grou
{
key_length+= 8;
}
+ else if (type == MYSQL_TYPE_BLOB)
+ key_length+= MAX_BLOB_WIDTH; // Can't be used as a key
else
{
/*
Attachment: [text/bzr-bundle] bzr/gleb.shchepa@oracle.com-20101026182320-3cnfkiz9gptdh988.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (gleb.shchepa:3547) Bug#52160 | Gleb Shchepa | 26 Oct |
