php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #20510 All spaces on a right padded string getting chopped
Submitted: 2002-11-19 19:22 UTC Modified: 2002-11-20 13:35 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: jrust@php.net Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.3.0RC1 OS: Linux
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: jrust@php.net
New email:
PHP Version: OS:

 

 [2002-11-19 19:22 UTC] jrust@php.net
The following mysql query should return the data padded with spaces on the right:
<?php
$query = '
SELECT
    RPAD(t1.f_field1,15,\' \'),
    RPAD(t2.f_field2, 15, \' \')
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);
}
?>

However, the all spaces to the right of the string are chopped off, as if PHP (or maybe the MySQL api?) is doing an rtrim on the data.  

Some details:
* The exact same query run from the MySQL console returns the correctly padded data.
* If the ORDER BY is commented out the data comes back padded correctly.
* If the query does not contain a JOIN the data comes back padded correctly (even if there is an ORDER BY)
* It doesn't matter what the data is for table1 and  table2 so long as the two tables can be joined and have some data in the fields
* I am using MySQL version 3.23.52

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-11-19 19:38 UTC] nicos@php.net
I really don't think that can be PHP related... Post a MySQL bug instead? Did you tried that with MySQL 4 (C API)?

Anyway if someone feels that I'm wrong, open it again.

Thank you for the report.
 [2002-11-19 20:02 UTC] philip@php.net
The posted details make it sound like a bug to me so unless you truly know the answer nicos I don't think this should be closed.  jrust, maybe it'd be helpful if you provide a small db schema that'll go along with a query so others can easily test it.
 [2002-11-19 20:07 UTC] nicos@php.net
I never said it's not a bug. It is, the fact is that it's not PHP related but MySQL related, so bugs mysql.com
 [2002-11-19 20:27 UTC] nicos@php.net
<Zeev> it *seems* like a MySQL issue, but it could be some obscure PHP issue.

Lets see.

C API should be tested with MySQL 3 and MySQL4.
Same for the PHP extension.

I will do some tests and give feedbacks.
 [2002-11-20 07:34 UTC] derick@php.net
Read the 2nd paragraph on:

http://www.mysql.com/doc/en/CHAR.html
"When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed. "

Not a bug -> bogus
 [2002-11-20 11:47 UTC] jrust@php.net
Ok, here is the dump schema of the tables.  My hunch now is that it is a MySQL problem in general, because a similar happens from the MySQL command line.  

Anyhow, I've put in a thread about it over at mysql.com.  However, I don't think as a bug it's Bogus, because I'm not talking about putting data into a VARCHAR field, but rather padding any type of field on the fly on it's way out.  And the data as you'll see if you run the test, is padded without the ORDER BY, but not padded when the ORDER BY is present.   Oh, and one more wierd caveat I discovered.  The query has to be joining on more than one row, if it only joins on one row it works fine....wierd.  So, the dump schema:

# 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');
 [2002-11-20 12:09 UTC] derick@php.net
Works fine here:

[derick@kossu derick]$ mysqldump  -u root -p bug20510
Enter password: 
-- MySQL dump 8.21
--
-- Host: localhost    Database: bug20510
---------------------------------------------------------
-- Server version	3.23.49a

--
-- 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');


[derick@kossu derick]$ cat bug20510.php 
<?php
mysql_connect('localhost', 'root', 'mypassword');
mysql_select_db('bug20510');
$res = mysql_query ("SELECT RPAD(t1.field1,15, ' '), RPAD(t2.field2, 15, ' ') FROM table1 as t1  LEFT JOIN table2 AS t2 ON t1.record=t2.joinID ORDER BY t1.record DESC");
while ($row = mysql_fetch_row($res)) {
	var_dump ($row);
}
?>


[derick@kossu derick]$ php -v
PHP 4.3.0-dev (cli) (built: Nov 19 2002 21:39:05)
Copyright (c) 1997-2002 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2002 Zend Technologies


[derick@kossu derick]$ php bug20510.php 
array(2) {
  [0]=>
  string(15) "test           "
  [1]=>
  string(15) "join one       "
}
array(2) {
  [0]=>
  string(15) "test           "
  [1]=>
  string(15) "join two       "
}



Compiled against the bundled library, using mysql 3.23.49a as server.

Which versions do you use?

Derick
 [2002-11-20 12:16 UTC] jrust@php.net
Hmm...wierd.  Here's my info.  Same problem happens with php 4.2.1 with MySQL compiled as dynamic module.

<jrust:rhun dir="jrust"/>$ /usr/local/bin/php -v
PHP 4.3.0-pre2 (cli), Copyright (c) 1997-2002 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2002 Zend Technologies

<jrust:rhun dir="jrust"/>$ /usr/local/bin/php -m
[PHP Modules]
xml
tokenizer
standard
session
posix
pcre
overload
mysql
mbstring
ctype

[Zend Modules]

<jrust:rhun dir="jrust"/>$ rpm -qa|grep mysql
mysqlclient9-3.23.22-6
mysql-3.23.41-1
mysql-server-3.23.41-1
mysql-devel-3.23.41-1

<jrust:rhun dir="jrust"/>$ /usr/local/bin/php foo.php
array(2) {
  [0]=>
  string(4) "test"
  [1]=>
  string(8) "join two"
}
array(2) {
  [0]=>
  string(4) "test"
  [1]=>
  string(8) "join one"
}

Data without ORDER BY:
array(2) {
  [0]=>
  string(15) "test           "
  [1]=>
  string(15) "join one       "
}
array(2) {
  [0]=>
  string(15) "test           "
  [1]=>
  string(15) "join two       "
}

 [2002-11-20 13:35 UTC] georg@php.net
Couldn't verify this with MySQL-Versions 3.23.52, 3.23.53, 4.0.4 and 4.1.

Be sure, that you don't use a buggy distribution rpm (Redhat 7 and Mandrake 8 rpm's are buggy). Always use the actual original MySQL AB rpm's/binaries.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Nov 06 01:01:30 2024 UTC