Bug #4082 Integer lengths cause truncation with DISTINCT CONCAT and InnoDB
Submitted: 9 Jun 2004 20:13 Modified: 25 Jun 2011 0:39
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.5, 4.0.21 and 4.1.3 OS:Linux (Linux)
Assigned to: Alexander Barkov CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[9 Jun 2004 20:13] Dean Ellis
Description:
A combination of SELECT DISTINCT CONCAT(integer,integer) with integer length values results in truncation of data with an InnoDB table.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int(1), b int(1) ) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1111,2222);
SELECT DISTINCT CONCAT(a,b) as c FROM t1;
ALTER TABLE t1 MODIFY a int(2), MODIFY b int(2);
SELECT DISTINCT CONCAT(a,b) as c FROM t1;
ALTER TABLE t1 MODIFY a int, MODIFY b int;
SELECT DISTINCT CONCAT(a,b) as c FROM t1;
DROP TABLE t1;

Suggested fix:
n/a
[10 Jun 2004 10:14] Heikki Tuuri
Hi!

What does CONCAT(integer1, integer2) mean? Does it mean concatenating the binary representations of those integers?

I believe this bug is in MySQL, not in InnoDB, since InnoDB is unaware of the print length specification in an integer type.

Regards,

Heikki
[10 Jun 2004 16:53] Dean Ellis
CONCAT(integer1, integer2) means CONCAT(integer-column, integer-column) as in the test case.  I tried this against MyISAM, BDB and MEMORY, but it only occurs with InnoDB, hence the InnoDB target.
[11 Jun 2004 17:12] Ronald Jeremy
Until there is a fix, I was able to work around this problem doing something like this:

SELECT DISTINCT TRIM(CONCAT(a,b,REPEAT(' ',LENGTH(b)))) AS c FROM t1;

which returns 11112222 instead of 11
[14 Sep 2004 13:26] Heikki Tuuri
Dean,

can you check if this bug still exists? And find out why it happens. InnoDB is not aware of print length specifications. Therefore, this probably is not an InnoDB bug.

Thank you,

Heikki
[15 Sep 2004 21:51] Dean Ellis
It is still present in current (as of now) bk sources for 4.0.22 and 4.1.5.

The test above, with InnoDB, returns:

11
1111
11112222

Changing to any other storage engine yields:

11112222
11112222
11112222
[23 Sep 2004 14:48] Marko Mäkelä
Sergei, could you please have a look at this?

Regards, Heikki
[25 Sep 2004 13:54] Ramil Kalimullin
It's not an InnoDB bug, indeed.
As a workaround one can use 'select distinct a+0, b...'.
The issue will be fixed in the 4.1 tree.
[7 May 2005 7:26] Ramil Kalimullin
fixed in 4.1.12
[9 May 2005 0:23] Paul Dubois
Noted in 4.1.12 changelog.
[16 Apr 2010 18:34] Vasil Dimov
This bug has resurfaced in 5.5 (mysql-trunk):

in mysql-5.1:

mysql> create table t1(a int(1) , b int(1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('1111', '3333');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
| 1111 | 3333 |
+------+------+
1 row in set (0.03 sec)

mysql> select distinct concat(a, b) from t1;
+--------------+
| concat(a, b) |
+--------------+
| 11113333     |
+--------------+
1 row in set (0.04 sec)

In mysql-trunk:

mysql> create table t1(a int(1) , b int(1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('1111', '3333');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
| 1111 | 3333 |
+------+------+
1 row in set (0.00 sec)

mysql> select distinct concat(a, b) from t1;
+--------------+
| concat(a, b) |
+--------------+
| 11           |
+--------------+
1 row in set (0.00 sec)
[19 Apr 2010 8:08] Vasil Dimov
Reopening this bug since it has resurfaced in MySQL 5.5,

it was fixed in c1616.1904.1:

------------------------------------------------------------
revno: 1616.1904.1
revision-id: sp1r-ramil@ram-book.(none)-20050506151101-23450
parent: sp1r-bar@noter.(none)-20050506151100-23889
committer: ramil@ram-book.(none)
timestamp: Fri 2005-05-06 20:11:01 +0500
message:
  fix for bug #4082: Integer lengths cause truncation with DISTINCT CONCAT and InnoDB

 @ mysql-test/r/create.result
    fix for bug #4082: Integer lengths cause truncation with DISTINCT CONCAT and InnoDB

 @ mysql-test/r/innodb.result
    fix for bug #4082: Integer lengths cause truncation with DISTINCT CONCAT and InnoDB

 @ mysql-test/r/type_float.result
    fix for bug #4082: Integer lengths cause truncation with DISTINCT CONCAT and InnoDB

 @ mysql-test/t/innodb.test
    fix for bug #4082: Integer lengths cause truncation with DISTINCT CONCAT and InnoDB

 @ sql/item.cc
    fix for bug #4082: Integer lengths cause truncation with DISTINCT CONCAT and InnoDB

The fix is/was:

--- sql/item.cc	2005-05-05 08:55:09 +0000
+++ sql/item.cc	2005-05-06 15:11:01 +0000
@@ -551,13 +551,13 @@ Item_field::Item_field(THD *thd, Item_fi
 }
 
 void Item_field::set_field(Field *field_par)
 {
   field=result_field=field_par;			// for easy coding with fields
   maybe_null=field->maybe_null();
-  max_length=field_par->field_length;
+  max_length=field_par->max_length();
   decimals= field->decimals();
   table_name=field_par->table_name;
   field_name=field_par->field_name;

Now the code looks like:

void Item_field::set_field(Field *field_par)
{
  field=result_field=field_par;                 // for easy coding with fields
  maybe_null=field->maybe_null();
  decimals= field->decimals();
  table_name= *field_par->table_name;
  field_name= field_par->field_name;
[23 Aug 2010 11:08] Alexander Barkov
The above script is reproducible with ENGINE=ENNODB,
but works fine with ENGINE=MYISAM.

To reproduce the same problem with MYISAM, insert two rows:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int(1), b int(1)) engine=myisam;
INSERT INTO t1 VALUES (1111,2222),(1111,2222);
SELECT DISTINCT CONCAT(a,b) AS c FROM t1; 

+------+
| c    |
+------+
| 11   |
+------+
[25 Jun 2011 0:39] Paul Dubois
Noted in 5.5.13, 5.6.3 changelogs.