PostgreSQL driver handles boolean values incorrectly...

20 years ago by Sean Chittenden — view source

unread

Howdy. Was doing some development with PHP and PostgreSQL and came
across a rather nasty surprise.

A boolean value is returned as the strings 't' and 'f', not the
constants true and false. This presents all kinds of interesting
oddities for code that does something like:

$r = pg_query("SELECT FALSE");
list($b) = pg_fetch_row($r);
if ($b)
echo "This gets displayed 100% of the time because %b is the string
'f'\n";
else
echo "This never gets displayed: $b is always true\n";

The same problem lies with the NULL value, which IMHO, should be mapped
to the constant NULL, not the string 'NULL'. I took a first stab at
handling this, but have been by and large unsuccessful, so I'm posting
here instead of submitting a complete solution. My patches can be
found at the URL below:

http://people.FreeBSD.org/~seanc/patches/php/

Any help/assistance is appreciated. I thought I was doing the right
thing, but am apparently not. Please advise. Thanks in advance. -sc

--
Sean Chittenden

20 years ago by Michael Sims — view source

unread

Sean Chittenden wrote:

A boolean value is returned as the strings 't' and 'f', not the
constants true and false. This presents all kinds of interesting
oddities for code that does something like:
[...]

You're probably already aware of this, but you can use a bit(1) field as a
boolean and this will map to PHP values that will allow you to check for
truth in conditionals. Personally I do that instead of using Postgres's
boolean since other database systems I've worked with don't have a boolean
type, but they all have a bit type. Although I agree that it would be nice
for PHP to map pg's boolean to PHP's boolean...

The same problem lies with the NULL value, which IMHO, should be
mapped to the constant NULL, not the string 'NULL'.

I was unable to reproduce this. The following code:

$r = pg_query($dbh, "create table test (field1 varchar(10))");
$r = pg_query($dbh, "insert into test values (NULL)");
$r = pg_query($dbh, "select field1 from test");

$row = pg_fetch_row($r);
var_dump($row);

$r = pg_query($dbh, "drop table test");

outputs:

array(1) {
[0]=>
NULL
}

On PHP 4.3.9, Postgresql 7.4.5, running on Linux (Debian Sarge). If you get
different results perhaps you've uncovered a bug that should be reported...

20 years ago by Daniel Convissor — view source

unread

Sean Chittenden wrote:

A boolean value is returned as the strings 't' and 'f', not the
constants true and false. This presents all kinds of interesting
oddities for code that does something like:

I suspect that behavior has nothing to do with PHP and everything to
do with PostgreSQL. Each DBMS outputs their information in their own
way.

Now, should PHP implement a new standard to convert that into a
standard format, well that's another question. The answer is "no."
That would cause compatibility problems.

Stuff like this will likely be handled by the experimental PDO
extension. When it becomes stable and fully develloped, things will
be very nice.

You're probably already aware of this, but you can use a bit(1)
field as a boolean

Most DBMS's don't support BIT column types. Second, some of those
that do don't allow NULL in them. NULL is a legit value for a BOOLEAN
column.

I'll be talking about compatibility issues like this at the
International PHP Conference this coming Wednesday at 13:30 during my
talk entitled "Building Truly Portable Database Applications in PHP."
I'll have the slides up later this week.

--Dan

--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

20 years ago by Sean Chittenden — view source

unread

A boolean value is returned as the strings 't' and 'f', not the
constants true and false. This presents all kinds of interesting
oddities for code that does something like:

I suspect that behavior has nothing to do with PHP and everything to
do with PostgreSQL. Each DBMS outputs their information in their own
way.

Now, should PHP implement a new standard to convert that into a
standard format, well that's another question. The answer is "no."
That would cause compatibility problems.

Bah, provide a backwards compatibility config option. As the following
poster suggests, PHP needs some way of advancing itself out of the
stone age as a programming language. The use of php.ini for register
globals sets a precedent to allow this to happen, IMHO. Something
like: pgsql_bool_return_string = true for the near future, then change
over to pgsql_bool_return_string = false at some later date. My other
cohorts on this contract had the exact same idea and to me it seems
more than rather plausible to me.

You're probably already aware of this, but you can use a bit(1)
field as a boolean

Most DBMS's don't support BIT column types. Second, some of those
that do don't allow NULL in them. NULL is a legit value for a BOOLEAN
column.

NULL can be a legit value for any column depending on the DDL. NULL
can also be an invalid value for any particular column. I don't see
how this makes any difference.

-sc

--
Sean Chittenden

20 years ago by Daniel Convissor — view source

unread

Hi Sean:

Bah, provide a backwards compatibility config option.

The PHP extension isn't a place for that. PDO or some database
abstraction layer written in PHP is.

Anyway, as mentioned, none of the DBMS's output a boolean data type in
PHP. So, why even bother? Or, why stop at PostgreSQL?

NULL can be a legit value for any column depending on the DDL. NULL
can also be an invalid value for any particular column. I don't see
how this makes any difference.

My point was BIT isn't a good mapping for proper boolean
functionality.

--Dan

Hi Steph :)