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.

9 files changed, 36 insertions, 12 deletions

diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 291dcfdf92a..a80c52ebf9d 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -2046,7 +2046,7 @@ create table t2 as select concat(a) from t1;
 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
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index 58f023b2b79..cff4f6b7442 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -2438,7 +2438,7 @@ create table t2 as select concat(a) from t1;
 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
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index 2ecb9d6aeba..12a76302397 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -2465,7 +2465,7 @@ create table t2 as select concat(a) from t1;
 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
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index e687822c235..dc922f8490a 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -3299,7 +3299,7 @@ create table t2 as select concat(a) from t1;
 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
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index db981fbe298..cfbf6cee3a2 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -4177,7 +4177,7 @@ create table t2 as select concat(a) from t1;
 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
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index b1cb70fa43c..74f2c19c8fe 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -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
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index 480cec792c0..3418348854f 100644
--- a/mysql-test/r/metadata.result
+++ b/mysql-test/r/metadata.result
@@ -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;
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index ac3d52b9ead..d99c2363d62 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -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				#	
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index bf4c23562cf..84073d15109 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -614,3 +614,16 @@ SET @@sort_buffer_size = @old_sort_buffer_size;
 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