MySQL Bugs: #3946: Error in LPAD() when padstring is longer than 1 character

Bug #3946	Error in LPAD() when padstring is longer than 1 character
Submitted:	1 Jun 2004 12:26	Modified:	7 Jun 2004 16:57
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.2	OS:	Windows (Windows 2000, FreeBSD 5.1)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
In 4.1.2 and 5.0.0 a padstring of multiple characters will not be contained through the whole resultset and give an unwanted output.

mysql> SELECT Name, LPAD(Population, 12, '-o-/') FROM Country LIMIT 7;
+----------------------+------------------------------+
| Name                 | LPAD(Population, 12, '-o-/') |
+----------------------+------------------------------+
| Aruba                | -o-/-o103000                 |
| Afghanistan          | -o-o22720000                 |
| Angola               | -o-o12878000                 |
| Anguilla             | -o-o-o-o8000                 |
| Albania              | -o-o-3401200                 |
| Andorra              | -------78000                 |
| Netherlands Antilles | ------217000                 |
+----------------------+------------------------------+
7 rows in set (0.00 sec)

As shown it keeps the pattern for the first row returned, at the second we've already lost a bit of the pattern that we supplied and down at the sixth row we only have the first character of the pattern we supplied left, and that's how it continues to the last row if you would remove the LIMIT.

Tried on both Windows and FreeBSD.
It works like it should on 4.0.20, but neither in the 4.1.2 release or 5.0.0.
Storage Engine doesn't matter, the error is present in MyISAM, InnoDB and MEMORY.

The error happens at the same place if the same query is repeated, but I haven't been able to find a pattern in different queries.

How to repeat:
Should be really easy to repeat, but the query above uses the world example database.

trying to repeat...

I was able to verify this on RedHat 9:
Linux booty 2.4.21 #12 SMP Thu Aug 14 00:49:40 EDT 2003 i686 i686 i386 GNU/Linux

with both 4.1.2 and 5.0.0.

bk commit - 4.1 tree (gluh:1.1896) BUG#3946

Fixed in 4.1 source tree