Home » Database » DB » Bug #4423

[2005-05-25 04:42 UTC] karl at posmaster dot com dot au

Description:
------------
Environment:
PHP 5.0.4
PostgreSQL 7.4.6
DB 1.7.6

PHP Configure line:
'./configure' '--prefix=/usr' '--disable-static' '--with-apxs=/usr/sbin/apxs' '--sysconfdir=/etc' '--enable-discard-path' '--with-config-file-path=/etc/apache' '--enable-safe-mode' '--with-openssl' '--with-mhash' '--enable-bcmath' '--with-bz2' '--with-pic' '--enable-calendar' '--enable-ctype' '--with-gdbm' '--with-db3' '--enable-dbase' '--enable-ftp' '--with-iconv' '--with-exif' '--with-gd' '--enable-gd-native-ttf' '--with-jpeg-dir=/usr' '--with-png' '--with-gmp' '--with-gettext=shared,/usr' '--with-expat-dir=/usr' '--with-xml' '--enable-wddx' '--with-mm=/usr' '--enable-trans-sid' '--enable-shmop' '--enable-sockets' '--with-regex=php' '--enable-sysvsem' '--enable-sysvshm' '--enable-yp' '--enable-memory-limit' '--with-tsrm-pthreads' '--enable-shared' '--disable-debug' '--with-zlib=/usr' '--with-pgsql' '--with-mysql' '--with-mssql=/usr/local/freetds'

Reproduce code:
---------------
$db = DB::connect('pgsql://user:pass@localhost/db_name');
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$query = 'SELECT "Customer".* , "CustomerInfo".*
FROM "Customer"
LEFT JOIN "CustomerInfo" USING ("CustomerID")';

$res = $db->getAll($query);

Expected result:
----------------
CustomerID | CustomerName | CustomerInfo
1 | foo |
2 | bar | blah

Actual result:
--------------
CustomerID | CustomerName | CustomerInfo
| foo |
2 | bar | blah

In the cases where there is no matching record in the CustomerInfo table, the CustomerID in the results returned is blank (empty string), otherwise it is populated correctly
(when there is a matching record in the CustomerInfo table)
The rest of the details from the Customer table are populated correctly.

When this query is entered directly into the postgres shell, the CustomerID is populated correctly.

Note:
Changeing the first line of the query to:
SELECT "CustomerInfo".*, "Customer".*
or:
SELECT *
results in the CustomerID being populated correctly.

[2005-05-27 01:59 UTC] karl at posmaster dot com dot au

Hope this helps, the behaviour can be seen with this example

As before making the select query begin with 'SELECT *' shows the desired behaviour.

Apart from having to use quotes all over the place, what issues can delimited identifiers introduce?

Thanks for your input,
Karl.

<?php
require_once 'DB.php';

$db = DB::connect('pgsql://root:@localhost/db_name');
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$query = 'CREATE TABLE "Customer" (
"CustomerID" serial NOT NULL,
"CustomerName" varchar DEFAULT \'AName\' NOT NULL)';
$res = $db->query($query);
$query = 'CREATE TABLE "CustomerInfo" (
"CustomerID" serial NOT NULL,
"SomeInfo" varchar DEFAULT \'Info\' NOT NULL)';
$res = $db->query($query);

$query = 'INSERT INTO "Customer" VALUES (\'1\', \'Customer1 Name\')';
$res = $db->query($query);
$query = 'INSERT INTO "Customer" VALUES (\'2\', \'Customer2 Name\')';
$res = $db->query($query);
$query = 'INSERT INTO "CustomerInfo" VALUES (\'1\', \'Some Info\')';
$res = $db->query($query);

$query = 'SELECT "Customer".* , "CustomerInfo".*
FROM "Customer"
LEFT JOIN "CustomerInfo" USING ("CustomerID")';
$res = $db->getAll($query);

echo '<pre>';print_r($res);echo '</pre>';

$query = 'DROP TABLE "Customer"';
$res = $db->query($query);
$query = 'DROP TABLE "CustomerInfo"';
$res = $db->query($query);
?>