Bug #5282 LPAD

• Submitted: 28 Aug 2004 17:35
• Modified: 29 Aug 2004 10:38
• Reporter: Marko Koski
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0 alpha
• OS: Linux (Fedora Core 1)
• CPU Architecture: Any

[28 Aug 2004 17:35] Marko Koski

Description:
when assigning shorter or exact width value to lpad it erases the string

How to repeat:
select lpad((111+111+11111),5,'0')

[29 Aug 2004 10:38] Alexander Keremidarski

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

mysql> select lpad((111+111+11111),5,'0');
+-----------------------------+
| lpad((111+111+11111),5,'0') |
+-----------------------------+
| 11333                       |
+-----------------------------+

I see nothing wrong in the result. As for trimming of value when len < LENGTH(str) this is expected and well documented behaviour. Quoting the manual:

LPAD(str,len,padstr)
...    
If str is longer than len, the return value is shortened to len characters.

[30 Aug 2004 8:31] Marko Koski

In my enviroment SELECT LPAD((11111+111+111),5,'0'); 
the result is

+-----------------------------+
| lpad((11111+111+111),5,'0') |
+-----------------------------+
|                             |
+-----------------------------+
1 row in set (0,17 sec)

and when you quit mysql-client it throws memory overflow.

to be more precise:
mysql: 5.0.0-alpha-standard
system: fedora core 1 with
Linux xxx.xxx.xxx 2.4.22-1.2174.nptl #1 Wed Feb 18 16:38:32 EST 2004 i6
86 i686 i386 GNU/Linux
Language Finnish

[30 Aug 2004 8:35] Marko Koski

I also found a workaround... I put lpad with one 0 too wide and read it with substr from 2 then the result is correct
in prev example:
SELECT SUBSTR(LPAD((11111+111+111),6,'0'),2);
result:
+---------------------------------------+
| SUBSTR(LPAD((11111+111+111),6,'0'),2) |
+---------------------------------------+
| 11333                                 |
+---------------------------------------+
1 row in set (0,00 sec)