Edit report at https://bugs.php.net/bug.php?id=69967&edit=1
ID: 69967
User updated by: buschmann at nidsa dot net
Reported by: buschmann at nidsa dot net
Summary: char columns always return maximum length
Status: Wont fix
Type: Bug
Package: PostgreSQL related
Operating System: Windows 8.1 x64
PHP Version: 7.0.0alpha2
Block user comment: N
Private report: N
New Comment:
I don't agree that this behavior is to be expected.
PHP should respect the semantics of the database.
I have included length and concatenation operations from the database which clearly show that the
database (Postgres) treats char columns as right trimmed in these operations. When we retrieve these
values through PHP, the semantics of the database should be preserved.
The problem arose by migrating an application from MySQL to Postgres. The Mysql interface (the old
one) handles the strings with right trim according to the database semantic.
The consequences of handling the same column type from different databases in a different way in PHP
are very surprising: e.g. the comparison of two retreived values from two different char columns
with differing maximum length give not the expected result of equality, and this is depending from
the underlying database interface.
I was forced to eliminate almost all char columns in the application. I know the advantages of
varchar or text columns.
To my knowledge, Oracle has the same semantics as Mysql and Postgres, which is to use all values as
right trimmed strings in SQL expressions.
To clarify, we should follow the whole paragraph from postgres documentation:
<citation>
Values of type character are physically padded with spaces to the specified width n, and are stored
and displayed that way. However, trailing spaces are treated as semantically insignificant and
disregarded when comparing two values of type character. In collations where whitespace is
significant, this behavior can produce unexpected results, e.g. SELECT 'a '::CHAR(2)
collate "C" < 'a\n'::CHAR(2) returns true. Trailing spaces are removed when
converting a character value to one of the other string types. Note that trailing spaces are
semantically significant in character varying and text values, and when using pattern matching, e.g.
LIKE, regular expressions.
</citation>
Previous Comments:
------------------------------------------------------------------------
[2015-07-01 05:31:53] yohgaki@php.net
I think we shouldn't trim result returned from PostgreSQL. So it cannot be fixed.
BTW, PostgreSQL works better(faster) with TEXT than CHAR().
------------------------------------------------------------------------
[2015-06-30 15:52:16] cmb@php.net
The behavior is to be expected. The PostgreSQL documentation[1]
explains:
| Values of type character are physically padded with spaces to
| the specified width n, and are stored and displayed that way.
The PHP pgsql extension simply uses the value retrieved by
PQgetvalue()[2] and there doesn't appear to be a way to get the
actual string length (PQgetlength() returns n for char(n) columns)
(except for explicitly querying the length()).
[1] <http://www.postgresql.org/docs/8.2/static/datatype-character.html>
[2] <https://github.com/php/php-src/blob/php-5.6.10/ext/pgsql/pgsql.c#L2759>
------------------------------------------------------------------------
[2015-06-30 12:57:59] buschmann at nidsa dot net
Description:
------------
/* ################# ERROR Results in PHP ##############################
// the ERROR is that char strings retrieved by pg_fetch_assoc (or similar calls) always return the
maximum length of the column
// i.E when we select the fix string, the database always returns the correct length (pgflen) but
php always gives 6 (phpflen)!!!!
// for better illustration, in phpfixreplace all blanks returned are replaced with #
// this has been tested with php 5.6.10 and php 7 alpha 2 and seems to be similar on other versions
and platforms (not tested)
// platform: windows 8.1 64 bit, Apache 64bit 2.4.12 VC14, php TS in 64 bit,
// module extension=php_pgsql.dll
below the output:
v-- ERROR v-- ERROR
cfix = pgfix = ## pgflen = 0 phpflen = 6 phpfix = [ ] phpfixreplace = [######]
Test script:
---------------
<?php
$conexion=pg_connect('CONNECTION_STRING');
$sqlIns="select
length(cfix) as pgflen, cfix, '#'||cfix||'#' as pgfix
,length(cvar) as pgvlen, cvar, '#'||cvar||'#' as pgvar
from ctest";
$rS=pg_query($sqlIns);
while($fs=pg_fetch_assoc($rS)){
echo "<table>";
echo "<tr><td> cfix = ".$fs['cfix']." pgfix =
".$fs['pgfix']." pgflen = ".$fs['pgflen']." phpflen =
".strlen($fs['cfix']).
" phpfix = [".$fs['cfix']."] phpfixreplace = [".str_replace('
','#',$fs['cfix'])."]</td></tr>"
;
echo "<tr><td> cvar = ".$fs['cvar']." pgvar =
".$fs['pgvar']." pgvlen = ".$fs['pgvlen']." phpvlen =
".strlen($fs['cvar']).
" phpvar = [".$fs['cvar']."] phpvarreplace = [".str_replace('
','#',$fs['cvar'])."]</td></tr>";
;
echo "</table>";
}
?>
Expected result:
----------------
/*
// create the table and fill with test strings in Postgres
create table ctest (
cfix char(6),
cvar varchar (6)
);
insert into ctest values
('',''),
('f2','v2'),
('f6f6f6','v6v6v6')
;
// select in psql
db=# select
db-# length(cfix) as flen, cfix, '#'||cfix||'#' as pgfix
db-# ,length(cvar) as vlen, cvar, '#'||cvar||'#' as pgvar
db-# from ctest
db-# ;
flen | cfix | pgfix | vlen | cvar | pgvar
------+--------+----------+------+--------+----------
0 | | ## | 0 | | ##
2 | f2 | #f2# | 2 | v2 | #v2#
6 | f6f6f6 | #f6f6f6# | 6 | v6v6v6 | #v6v6v6#
(3 Zeilen)
*/
Actual result:
--------------
below the output:
v-- ERROR v-- ERROR
cfix = pgfix = ## pgflen = 0 phpflen = 6 phpfix = [ ] phpfixreplace = [######]
cvar = pgvar = ## pgvlen = 0 phpvlen = 0 phpvar = [] phpvarreplace = []
cfix = f2 pgfix = #f2# pgflen = 2 phpflen = 6 phpfix = [f2 ] phpfixreplace = [f2####]
cvar = v2 pgvar = #v2# pgvlen = 2 phpvlen = 2 phpvar = [v2] phpvarreplace = [v2]
cfix = f6f6f6 pgfix = #f6f6f6# pgflen = 6 phpflen = 6 phpfix = [f6f6f6] phpfixreplace = [f6f6f6]
cvar = v6v6v6 pgvar = #v6v6v6# pgvlen = 6 phpvlen = 6 phpvar = [v6v6v6] phpvarreplace = [v6v6v6]
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=69967&edit=1