Bug #4082 Integer lengths cause truncation with DISTINCT CONCAT and InnoDB
Submitted: 9 Jun 2004 22:13 Modified: 9 May 2005 2:23
Reporter: Dean Ellis
Status: Closed
Category:Server Severity:S2 (Serious)
Version:4.0.21 and 4.1.3 OS:Linux (Linux)
Assigned to: Ramil Kalimullin Target Version:

[9 Jun 2004 22: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 12: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 18: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 19:12] [ name withheld ] 
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 15: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 23: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 16:48] Marko Mäkelä 
Sergei, could you please have a look at this?

Regards, Heikki
[25 Sep 2004 15: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 9:26] Ramil Kalimullin 
fixed in 4.1.12
[9 May 2005 2:23] Paul DuBois 
Noted in 4.1.12 changelog.