Bug #3089 SUBSTRING bug when mixed with CONCAT and ORDER BY

• Submitted: 7 Mar 2004 3:03
• Modified: 11 Mar 2004 2:07
• Reporter: [ name withheld ]
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.2
• OS: Linux (Linux)
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[7 Mar 2004 3:03] [ name withheld ]

Description:
The SUBSTRING operation works improperly in the example below.

Environment:
- MySQL server 4.1.2-alpha-nightly-20040229 on Linux. (Mandrake 8.1)

How to repeat:
drop table test1;
create table test1 (
  id int(1),
  str varchar(10)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
insert into test1 values (1,'aaaaaaaaaa');
insert into test1 values (2,'bbbbbbbbbb');
drop table test2;
create table test2 (
  id int(1),
  str varchar(10)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
insert into test2 values (1,'cccccccccc');
insert into test2 values (2,'ddcddddddd');
commit;
select
  substring(concat(t1.str,t2.str),1,10) "name"
from
  test1 t1,
  test2 t2
where
  t2.id=t1.id
order by
  name;
+------+
| name |
+------+
| aaa  |
| bbb  |
+------+
select
  substring(concat(t1.str,t2.str),1,20) "name"
from
  test1 t1,
  test2 t2
where
  t2.id=t1.id
order by
  name;
+--------+
| name   |
+--------+
| aaaaaa |
| bbbbbb |
+--------+
#if order by is missing, it works:
select
  substring(concat(t1.str,t2.str),1,20) "name"
from
  test1 t1,
  test2 t2
where
  t2.id=t1.id
+----------------------+
| name                 |
+----------------------+
| aaaaaaaaaacccccccccc |
| bbbbbbbbbbdddddddddd |
+----------------------+

[8 Mar 2004 8:39] Dean Ellis

Verified against 4.1.2/Linux; it is not InnoDB-specific.  Thank you.

[11 Mar 2004 2:07] Ramil Kalimullin

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html