All spaces on a right padded string getting chopped

>Description:
When using RPAD to pad strings with spaces on the fly the spaces are getting chopped off under very
specific conditions. Those conditions are:
1. The query is a join
2. The join finds more than one row to join upon
3. The query is not using an ORDER BY
>How-To-Repeat:
Here's the code that shows the problem clearly from a PHP script. The same query from the mysql commandline
also produces the problem, though slightly different. From the PHP script the data will come back not padded
at all, from the command line it pads up to about 15 spaces, after that it chops them off. So, a pad of 50
will not be reflected.
I have tried this on another machine running 3.23.52 with the same results. I don't have access to MySQL 4, so
haven't tested there.

$query = '
SELECT
RPAD(t1.f_field1, 50, \' \'),
RPAD(t2.f_field2, 50, \' \')
FROM
test.table1 as t1
LEFT JOIN test.table2 AS t2 ON t1.record=t2.joinID
ORDER BY t1.record DESC';

$link = mysql_connect('localhost', 'root', 'password');
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
var_dump($row);
}
?>

Here is the dump schema for the two tables:
# start test table schema.

USE DATABASE test;

#
# Table structure for table `table1`
#

CREATE TABLE table1 (
record int(10) unsigned NOT NULL auto_increment,
field1 varchar(30) NOT NULL default '',
PRIMARY KEY (record)
) TYPE=MyISAM;

#
# Dumping data for table `table1`
#

INSERT INTO table1 VALUES (1, 'test');

#
# Table structure for table `table2`
#

CREATE TABLE table2 (
record int(10) unsigned NOT NULL auto_increment,
joinID int(10) unsigned NOT NULL default '0',
field2 varchar(30) NOT NULL default '',
PRIMARY KEY (record)
) TYPE=MyISAM;

#
# Dumping data for table `table2`
#

INSERT INTO table2 VALUES (1, 1, 'join one');
INSERT INTO table2 VALUES (3, 1, 'join two');
>Fix:
One thing that prevents the problem above from occuring is to take out
the ORDER BY line. If that happens all the data comes back padded correctly.
Additionally, if the JOIN only finds one row to join on the data comes back fine.
Lastly, if the pad character is not a space then it comes back padded correctly.
>Submitter-Id:
>Originator: Jason Rust
>Organization:
Benefit Software Inc.
>MySQL support: none
>Synopsis: All spaces on a right padded string getting chopped
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.41 (Source distribution)

>Environment:

System: Linux rhun.bsiweb.com 2.4.18-17.7.xsmp #1 SMP Tue Oct 8 12:37:04 EDT 2002 i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='c++' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Nov 8 11:14 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 1285884 Oct 10 10:19 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27338282 Oct 10 09:48 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Oct 10 09:48 /usr/lib/libc.so
lrwxrwxrwx 1 root root 10 Jan 26 2002 /usr/lib/libc-client.a -> c-client.a
Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --without-debug --without-readline --enable-shared --with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql --with-extra-charsets=all --disable-assember --with-berkeley-db --enable-large-files=yes --enable-largefile=yes --with-thread-safe-client --enable-assembler


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13052@lists.mysql.com
To unsubscribe, e-mail

Re: All spaces on a right padded string getting chopped

jrust@bsiweb.com writes:
> >Description:
> When using RPAD to pad strings with spaces on the fly the spaces are getting chopped off under very
> specific conditions. Those conditions are:
> 1. The query is a join
> 2. The join finds more than one row to join upon
> 3. The query is not using an ORDER BY
> >How-To-Repeat:
> Here's the code that shows the problem clearly from a PHP script. The same query from the mysql commandline
> also produces the problem, though slightly different. From the PHP script the data will come back not padded
> at all, from the command line it pads up to about 15 spaces, after that it chops them off. So, a pad of 50
> will not be reflected.
> I have tried this on another machine running 3.23.52 with the same results. I don't have access to MySQL 4, so
> haven't tested there.

[skip]

Hi!

Thank you for your bug report.

However, with latest 3.23, I get correct results.

Here is the output from the query, both with and without ORDER BY:

RPAD(t1.field1, 50, ' ') RPAD(t2.field2, 50, ' ')
test join one
test join two
RPAD(t1.field1, 50, ' ') RPAD(t2.field2, 50, ' ')
test join one
test join two

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13053@lists.mysql.com
To unsubscribe, e-mail

Re: All spaces on a right padded string getting chopped

Jason Rust writes:
> > Here it is again how it looks on my screen. First output is with ORDER
> > BY, second without. Both are the same.
> >
> > +----------------------------------------------------+------ ----------------------------------------------+
> > | RPAD(t1.field1, 50, ' ') | RPAD(t2.field2, 50, ' ') |
> > +----------------------------------------------------+------ ----------------------------------------------+
> > | test | join one |
> > | test | join two |
> > +----------------------------------------------------+------ ----------------------------------------------+
> > +----------------------------------------------------+------ ----------------------------------------------+
> > | RPAD(t1.field1, 50, ' ') | RPAD(t2.field2, 50, ' ') |
> > +----------------------------------------------------+------ ----------------------------------------------+
> > | test | join one |
> > | test | join two |
> > +----------------------------------------------------+------ ----------------------------------------------+
>
> Hmm. This is strange. Well, I changed my MySQL binaries to use the
> ones that are on the MySQL website. Now I have:
> # rpm -qa|grep -i mysql
> MySQL-shared-3.23.53a-1
> mysqlclient9-3.23.22-6
> MySQL-client-3.23.53a-1
> MySQL-devel-3.23.53a-1
> MySQL-3.23.53a-1
>
> But same results as before are yielded.
>
> My only thought is that there is a library that MySQL uses which is
> different/buggy on RedHat machines than the one you are using. I
> assume you're not using RedHat?
>
> -Jason
>
> --
> http://www.rustyparts.com/
> puttin' some rust into it!
>

Function itself does not use much of glibc, except (possibly) malloc,
which did not fail and memcpy().

No, I am not using Red Hat, I am using my own Linux distro, which I
call SinisaLinux.

It could be also issue with a shell or mysql monitor program.

Try saving the output to file or running:

mysql -t < commandfile

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13054@lists.mysql.com
To unsubscribe, e-mail

Re: All spaces on a right padded string getting chopped

Jason Rust writes:
> SELECT
> RPAD(t1.field1, 50, ' ') AS f2,
> RPAD(t2.field2, 50, ' ') AS f1
> FROM
> test.table1 as t1
> LEFT JOIN test.table2 AS t2 ON t1.record=t2.joinID;
>
>
> -Jason

Hi!

Thank you for your report and test case that has helped us solve this
bug.

This is a patch that fixes a problem:

===== sql/sql_select.cc 1.127 vs edited =====
*** /tmp/sql_select.cc-1.127-21715 Fri Nov 8 09:58:27 2002
--- edited/sql/sql_select.cc Sat Nov 23 15:38:11 2002
***************
*** 3289,3294 ****
--- 3289,3297 ----
if (item->max_length > 255)
new_field= new Field_blob(item->max_length,maybe_null,
item->name,table,item->binary);
+ else if (type == Item::FUNC_ITEM)
+ new_field= new Field_varstring(item->max_length,maybe_null,
+ item->name,table,item->binary);
else
new_field= new Field_string(item->max_length,maybe_null,
item->name,table,item->binary);


--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13088@lists.mysql.com
To unsubscribe, e-mail