php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #69967 char columns always return maximum length
Submitted: 2015-06-30 12:57 UTC Modified: 2016-05-02 17:10 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: buschmann at nidsa dot net Assigned:
Status: Wont fix Package: PostgreSQL related
PHP Version: 7.0.0alpha2 OS: Windows 8.1 x64
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2015-06-30 12:57 UTC] 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]



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-06-30 15:52 UTC] 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-07-01 05:31 UTC] 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-07-01 11:58 UTC] cmb@php.net
-Status: Open +Status: Wont fix
 [2015-07-01 14:42 UTC] buschmann at nidsa dot net
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>
 [2015-07-01 17:22 UTC] cmb@php.net
-Status: Wont fix +Status: Open
 [2016-05-02 17:10 UTC] ab@php.net
-Status: Open +Status: Wont fix
 [2016-05-02 17:10 UTC] ab@php.net
There is no PG APIs to make CHAR work similar to how fe MySQL does. One thing is what is being done in the DB internally, the access API is something else. Also, in Postgres CHAR has absolutely no advantage over VARCHAR and others, rather the exact opposite.

It is barely acceptable to touch the data delivered from the database. Same way, if CHAR has now advantage in Postgres, probably it makes no sense to push for that datatype just because of MySQL handles it differently.

Thanks.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Oct 21 19:03:35 2021 UTC