List:Commits« Previous MessageNext Message »
From:Georgi Kodinov Date:May 11 2011 11:13am
Subject:bzr commit into mysql-5.5 branch (Georgi.Kodinov:3502) Bug#11744875
View as plain text  
#At file:///Users/kgeorge/mysql/work/B11744875-5.5/ based on revid:georgi.kodinov@stripped

 3502 Georgi Kodinov	2011-05-11
      Bug #11744875:  4082: integer lengths cause truncation with distinct concat 
      and innodb
      
      The 5.5 version of the patch.
      
      The server doesn't restrict the data that can be inserted into integer columns 
      with explicitly specified length that's smaller than what the type can handle,
      e.g. 1234 can be inserted into an INT(2) column just fine.
      Thus, when calcualting the maximum width of expressions involving such 
      restricted integer columns we need to use the implicit maximum width of 
      the field instead of the explicitly speficied one.
      Fixed the server to use the implicit maximum in such cases and made sure 
      the implicit maximum is addjusted the same way as the explicit one wrt
      signedness.
      
      Fixed several test case results (ctype_*.result, metadata.result and 
      type_ranges.result) to reflect the extended column widths.
      
      Added a regression test case in distinct.test.
      
      Note : this is the behavior preserving fix that makes 5.5 behave as 5.1 and 
      earlier. In the mysql trunk we'll add a insert time check for the explict 
      maximum size.

    modified:
      mysql-test/r/ctype_binary.result
      mysql-test/r/ctype_cp1251.result
      mysql-test/r/ctype_latin1.result
      mysql-test/r/ctype_ucs.result
      mysql-test/r/ctype_utf8.result
      mysql-test/r/distinct.result
      mysql-test/r/metadata.result
      mysql-test/r/type_ranges.result
      mysql-test/t/distinct.test
      sql/item.cc
=== modified file 'mysql-test/r/ctype_binary.result'
--- a/mysql-test/r/ctype_binary.result	2011-04-08 13:15:23 +0000
+++ b/mysql-test/r/ctype_binary.result	2011-05-11 11:11:57 +0000
@@ -2046,7 +2046,7 @@ create table t2 as select concat(a) from
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `concat(a)` varbinary(2) DEFAULT NULL
+  `concat(a)` varbinary(4) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2;
 create table t1 (a year);
@@ -2355,7 +2355,7 @@ insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
 Field	Type	Null	Key	Default	Extra
-a	varbinary(2)	YES		NULL	
+a	varbinary(4)	YES		NULL	
 select hex(a) from v1;
 hex(a)
 3031

=== modified file 'mysql-test/r/ctype_cp1251.result'
--- a/mysql-test/r/ctype_cp1251.result	2011-04-08 13:15:23 +0000
+++ b/mysql-test/r/ctype_cp1251.result	2011-05-11 11:11:57 +0000
@@ -2438,7 +2438,7 @@ create table t2 as select concat(a) from
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `concat(a)` varchar(2) CHARACTER SET cp1251 DEFAULT NULL
+  `concat(a)` varchar(4) CHARACTER SET cp1251 DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2;
 create table t1 (a year);
@@ -2747,7 +2747,7 @@ insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
 Field	Type	Null	Key	Default	Extra
-a	varchar(2)	YES		NULL	
+a	varchar(4)	YES		NULL	
 select hex(a) from v1;
 hex(a)
 3031

=== modified file 'mysql-test/r/ctype_latin1.result'
--- a/mysql-test/r/ctype_latin1.result	2011-04-08 13:15:23 +0000
+++ b/mysql-test/r/ctype_latin1.result	2011-05-11 11:11:57 +0000
@@ -2465,7 +2465,7 @@ create table t2 as select concat(a) from
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `concat(a)` varchar(2) DEFAULT NULL
+  `concat(a)` varchar(4) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2;
 create table t1 (a year);
@@ -2774,7 +2774,7 @@ insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
 Field	Type	Null	Key	Default	Extra
-a	varchar(2)	YES		NULL	
+a	varchar(4)	YES		NULL	
 select hex(a) from v1;
 hex(a)
 3031

=== modified file 'mysql-test/r/ctype_ucs.result'
--- a/mysql-test/r/ctype_ucs.result	2011-04-08 13:15:23 +0000
+++ b/mysql-test/r/ctype_ucs.result	2011-05-11 11:11:57 +0000
@@ -3299,7 +3299,7 @@ create table t2 as select concat(a) from
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `concat(a)` varchar(2) CHARACTER SET ucs2 DEFAULT NULL
+  `concat(a)` varchar(4) CHARACTER SET ucs2 DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2;
 create table t1 (a year);
@@ -3608,7 +3608,7 @@ insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
 Field	Type	Null	Key	Default	Extra
-a	varchar(2)	YES		NULL	
+a	varchar(4)	YES		NULL	
 select hex(a) from v1;
 hex(a)
 00300031

=== modified file 'mysql-test/r/ctype_utf8.result'
--- a/mysql-test/r/ctype_utf8.result	2011-04-08 13:15:23 +0000
+++ b/mysql-test/r/ctype_utf8.result	2011-05-11 11:11:57 +0000
@@ -4177,7 +4177,7 @@ create table t2 as select concat(a) from
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `concat(a)` varchar(2) CHARACTER SET utf8 DEFAULT NULL
+  `concat(a)` varchar(4) CHARACTER SET utf8 DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2;
 create table t1 (a year);
@@ -4486,7 +4486,7 @@ insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
 Field	Type	Null	Key	Default	Extra
-a	varchar(2)	YES		NULL	
+a	varchar(4)	YES		NULL	
 select hex(a) from v1;
 hex(a)
 3031

=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2009-09-05 20:42:17 +0000
+++ b/mysql-test/r/distinct.result	2011-05-11 11:11:57 +0000
@@ -794,3 +794,14 @@ DROP TABLE t1;
 SET @@sort_buffer_size = @old_sort_buffer_size;
 SET @@max_heap_table_size = @old_max_heap_table_size;
 End of 5.1 tests
+#
+# Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb
+#
+CREATE TABLE t1 (a INT(1), b INT(1));
+INSERT INTO t1 VALUES (1111, 2222), (3333, 4444);
+SELECT DISTINCT CONCAT(a,b) AS c FROM t1 ORDER BY 1;
+c
+11112222
+33334444
+DROP TABLE t1;
+End of 5.5 tests

=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result	2010-03-24 15:03:44 +0000
+++ b/mysql-test/r/metadata.result	2011-05-11 11:11:57 +0000
@@ -126,7 +126,7 @@ renamed
 1
 select * from v3 where renamed=1 group by renamed;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def		v3	v3	renamed	renamed	8	11	0	Y	32896	0	63
+def		v3	v3	renamed	renamed	8	12	0	Y	32896	0	63
 renamed
 drop table t1;
 drop view v1,v2,v3;

=== modified file 'mysql-test/r/type_ranges.result'
--- a/mysql-test/r/type_ranges.result	2010-02-27 07:43:32 +0000
+++ b/mysql-test/r/type_ranges.result	2011-05-11 11:11:57 +0000
@@ -271,7 +271,7 @@ drop table t2;
 create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
 show full columns from t2;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-auto	int(6) unsigned	NULL	NO	PRI	0		#	
+auto	int(11) unsigned	NULL	NO	PRI	0		#	
 t1	int(1)	NULL	NO		0		#	
 t2	varchar(1)	latin1_swedish_ci	NO				#	
 t3	varchar(256)	latin1_swedish_ci	NO				#	

=== modified file 'mysql-test/t/distinct.test'
--- a/mysql-test/t/distinct.test	2009-09-05 20:42:17 +0000
+++ b/mysql-test/t/distinct.test	2011-05-11 11:11:57 +0000
@@ -614,3 +614,16 @@ SET @@sort_buffer_size = @old_sort_buffe
 SET @@max_heap_table_size = @old_max_heap_table_size;
 
 --echo End of 5.1 tests
+
+
+--echo #
+--echo # Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb
+--echo #
+
+CREATE TABLE t1 (a INT(1), b INT(1));
+INSERT INTO t1 VALUES (1111, 2222), (3333, 4444);
+SELECT DISTINCT CONCAT(a,b) AS c FROM t1 ORDER BY 1;
+DROP TABLE t1;
+
+
+--echo End of 5.5 tests

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2011-05-09 08:29:23 +0000
+++ b/sql/item.cc	2011-05-11 11:11:57 +0000
@@ -2011,6 +2011,61 @@ Item_field::Item_field(THD *thd, Item_fi
   collation.set(DERIVATION_IMPLICIT);
 }
 
+
+/**
+  Calculate the max column length not taking into account the
+  limitations over integer types.
+
+  When storing data into fields the server currently just ignores the
+  limits specified on integer types, e.g. 1234 can safely be stored in
+  an int(2) and will not cause an error.
+  Thus when creating temporary tables and doing transformations
+  we must adjust the maximum field length to reflect this fact.
+  We take the un-restricted maximum length and adjust it similarly to
+  how the declared length is adjusted wrt unsignedness etc.
+  TODO: this all needs to go when we disable storing 1234 in int(2).
+
+  @param field_par   Original field the use to calculate the lengths
+  @param max_length  Item's calculated explicit max length
+  @return            The adjusted max length
+*/
+
+inline static uint32
+adjust_max_effective_column_length(Field *field_par, uint32 max_length)
+{
+  uint32 new_max_length= field_par->max_display_length();
+  uint32 sign_length= (field_par->flags & UNSIGNED_FLAG) ? 0 : 1;
+
+  switch (field_par->type())
+  {
+  case MYSQL_TYPE_INT24:
+    /*
+      Compensate for MAX_MEDIUMINT_WIDTH being 1 too long (8)
+      compared to the actual number of digits that can fit into
+      the column.
+    */
+    new_max_length+= 1;
+    /* fall through */
+  case MYSQL_TYPE_LONG:
+  case MYSQL_TYPE_TINY:
+  case MYSQL_TYPE_SHORT:
+
+    /* Take out the sign and add a conditional sign */
+    new_max_length= new_max_length - 1 + sign_length;
+    break;
+
+  /* BINGINT is always 20 no matter the sign */
+  case MYSQL_TYPE_LONGLONG:
+  /* make gcc happy */
+  default:
+    break;
+  }
+
+  /* Adjust only if the actual precision based one is bigger than specified */
+  return new_max_length > max_length ? new_max_length : max_length;
+}
+
+
 void Item_field::set_field(Field *field_par)
 {
   field=result_field=field_par;			// for easy coding with fields
@@ -2024,6 +2079,9 @@ void Item_field::set_field(Field *field_
   collation.set(field_par->charset(), field_par->derivation(),
                 field_par->repertoire());
   fix_char_length(field_par->char_length());
+
+  max_length= adjust_max_effective_column_length(field_par, max_length);
+
   fixed= 1;
   if (field->table->s->tmp_table == SYSTEM_TMP_TABLE)
     any_privileges= 0;


Attachment: [text/bzr-bundle] bzr/georgi.kodinov@oracle.com-20110511111157-r63bnyd0ddbby68b.bundle
Thread
bzr commit into mysql-5.5 branch (Georgi.Kodinov:3502) Bug#11744875Georgi Kodinov11 May