MySQL Bugs: #4082: Integer lengths cause truncation with DISTINCT CONCAT and InnoDB

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

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

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

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.

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

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

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

Sergei, could you please have a look at this?

Regards, Heikki

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.

fixed in 4.1.12

Noted in 4.1.12 changelog.

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)

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;

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   |
+------+

Noted in 5.5.13, 5.6.3 changelogs.