Everything

php

PostgreSQL driver handles boolean values incorrectly...

php.internals

Sean Chittenden

21 years ago

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

Michael Sims

21 years ago

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...

Sean Chittenden

21 years ago

>> 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...

*nods*  It's a pretty evil behavior, IMHO.  It's all too common to have
code that essentially does:

$r = pg_query($DB, "select FALSE::BOOL");
$row = pg_fetch_row($r);
var_dump($row);
if ($row)
echo "$row\n";
else
echo "not run\n";

Instead all instances of 'if ($row)' have to be converted to something
like:

if ($row == 'f')

.. just stayed up all night adding various == '[tf]' snippets to some
100K lines of web goo.  *sighs*  Moved a large database schema from
INTs to BOOLs, and unpleasantly discovered that PHP returns a string
that evaluates true, even for false values.  Qt did the right thing,
but PHP bombed in the worst of ways.  :(

This has got to be considered an egregious oversight by the driver
authors and is a sharp disconnect from any interpretation of POLS
(principle of least surprise).

>> 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:

*blush*  Yeah, I f-'ed up and misinterpreted the code... this was from
PHP's null constant to the generated SQL, not the other way around.
The problem with the booleans is still the same, however.  I figured
there would be only one type map from that would be used when
sending/receiving variant data out of the database.  -sc

--
Sean Chittenden

Christian Schneider

21 years ago

Sean Chittenden wrote:

> *nods*  It's a pretty evil behavior, IMHO.  It's all too common to have

It has been like that for ages as far as I know and plenty of code
relies on it, so it can't be changed really.

> 100K lines of web goo.  *sighs*  Moved a large database schema from INTs
> to BOOLs, and unpleasantly discovered that PHP returns a string that

May I ask why you changed it in the first place? (-:C
You mentined the principle of leat surprise, I answer with "if it ain't
broken, don't fix it" ;-)

- Chris

Sean Chittenden

21 years ago

>> *nods*  It's a pretty evil behavior, IMHO.  It's all too common to
>> have
>
> It has been like that for ages as far as I know and plenty of code
> relies on it, so it can't be changed really.

Better late than never.  I've got a ton of code depending on it too,
but believe me when I say doing:

find . -type f -name '*.php' -exec perl -pi -e
's#\s*==\s*[']([tf])[']##g' {} \;

will make me very happy.

>> 100K lines of web goo.  *sighs*  Moved a large database schema from
>> INTs to BOOLs, and unpleasantly discovered that PHP returns a string
>> that
>
> May I ask why you changed it in the first place? (-:C

Yeah, the booleans were being stored as INTs.  When you've got 20-50M
records and a dozen booleans stored as integers, the space savings gets
to be huge.  Nevermind that there's a 20-30% speed difference using
booleans vs INTs.

> You mentined the principle of leat surprise, I answer with "if it
> ain't broken, don't fix it" ;-)

It is broken though.  Release a big errata note and upgrade notice, but
it should be fixed.  I haven't heard one person who thinks that the
current behavior is the correct behavior.  PHP5 hasn't come to rule the
day yet and many people are still using PHP4.  Update it now while its
still at the beginning of the 5 release cycle, otherwise it'll have to
wait till 6.  :(  -sc

--
Sean Chittenden

Thomas Seifert

21 years ago

On Sun, 07 Nov 2004 11:59:09 -0800, Sean Chittenden wrote:

>>> *nods*  It's a pretty evil behavior, IMHO.  It's all too common to
>>> have
>>
>> It has been like that for ages as far as I know and plenty of code
>> relies on it, so it can't be changed really.
>
> Better late than never.  I've got a ton of code depending on it too,
> but believe me when I say doing:
>
> find . -type f -name '*.php' -exec perl -pi -e
> 's#\s*==\s*[']([tf])[']##g' {} \;
>
> will make me very happy.

Oh and you will change that for all the other developers out there?
Thats great, thank you ;).

What Christian wanted to say is this: if it is changed now it will break
backward compatibility (BC) so it will not happen!

thomas

David Kingma - Jool.nl

21 years ago

Hi All,

BC is a good thing when you want to keep maintenance down and developers happy
but sometimes stalls progression. When there are bugs in the code that have been
there for a while, chances are that they will stay there forever for BC
reasons. The latest example is the postgreSQL driver, but there are many many
more reports where the answer is something like "we should keep it this way for
BC".

Shoundn't we make something like a BC framework?

For example: two function bc_version(versionnr) and bc_bug(bugnumber) that
allows you to go 'back in time' to the behaviour before the bug was fixed or to
the behaviour of a specific PHP version. It's comparable to de ZE switch in the
php.ini, but only for bug fixes / function changes. In this way a developer kan
state someting like bc_version("5.0.0") at the beginning of his script and his
script will work the same for every next version of PHP 5. The bc_bug() allows
only to change the behaviour of the specific bugreport to before the fix. (so a
bc_version() can also be accomplished with multiple bc_bug()'s)

Together with some good documentation on the php website this can enable
progression while maintaining BC.

Wheter or not it has to be supported accross big version bumps (ie. PHP 5 to 6)
should be considered, but you might not want to do that to keep the source
clean from all the 'BC if structures'.

Hope to hear some comments :)

Kind regards,

David

Quoting Thomas Seifert <thomas-lists@mysnip.de>:

> On Sun, 07 Nov 2004 11:59:09 -0800, Sean Chittenden wrote:
>
> >>> *nods*  It's a pretty evil behavior, IMHO.  It's all too common to
> >>> have
> >>
> >> It has been like that for ages as far as I know and plenty of code
> >> relies on it, so it can't be changed really.
> >
> > Better late than never.  I've got a ton of code depending on it too,
> > but believe me when I say doing:
> >
> > find . -type f -name '*.php' -exec perl -pi -e
> > 's#\s*==\s*[']([tf])[']##g' {} \;
> >
> > will make me very happy.
>
> Oh and you will change that for all the other developers out there?
> Thats great, thank you ;).
>
> What Christian wanted to say is this: if it is changed now it will break
> backward compatibility (BC) so it will not happen!
>
>
>
> thomas
>
> --
> PHP Internals - PHP Runtime Development Mailing List
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Daniel Convissor

21 years ago

On Sun, Nov 07, 2004 at 12:38:06PM -0600, Michael Sims wrote:

> 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

Sean Chittenden

21 years ago

>>> 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

Daniel Convissor

21 years ago

Hi Sean:

On Mon, Nov 08, 2004 at 10:32:57AM -0800, Sean Chittenden wrote:

>
> 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 :)

--
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