[PEAR-BUG] Bug #19962 [Com]: Cannot insert or update when part of a reference in multi-key case is NULL

Edit report at http://pear.php.net/bugs/bug.php?id=19962&edit=1

 ID:               19962
 Comment by:       jpt
 Reported By:      jpt at nimbit dot com
 Summary:          Cannot insert or update when part of a reference in
                   multi-key  case is NULL
 Status:           Closed
 Type:             Bug
 Package:          DB_DataObject
 Operating System: Any tested on OpenBSD 5.3
 Package Version:  1.11.2
 PHP Version:      5.3.21
 Assigned To:      alan_k
 New Comment:

Perhaps the identical (===) operator is more well suited for comparing
the integer value to the empty string since 0 == ''.

In the example that follows, we'd expect a new record with a value of
zero for v.  Instead we have a value of one and incur a "No Data
specified for query" error.  When updates include other fields, the
problem is silent...


class Test extends DB_DataObject {
  // mandatory - set the table
  var $_database_dsn = "pgsql://username:password@localhost/database";
  // create table test(id serial, v int); -- serial is auto_increment in
pg parlance
  var $__table = "test";
  function table() {
    return array('id' => 1, 'v'  => 1);
  }
  function keys() {
    return array('id');
  }
}

$t = new Test;
$t->v = 1;
$t->insert();
$t->v = 0;
$t->update();


Previous Comments:
------------------------------------------------------------------------

[2013-07-04 14:31:54] alan_k

<div id="changeset">
<span class="removed">-Status: Assigned</span>
<span class="added">+Status: Closed</span>
</div>This bug has been fixed in SVN.

If this was a documentation problem, the fix will appear on pear.php.net
by the end of next Sunday (CET).

If this was a problem with the pear.php.net website, the change should
be live shortly.

Otherwise, the fix will appear in the package's next release.

Thank you for the report and for helping us make PEAR better.

------------------------------------------------------------------------

[2013-06-29 07:51:41] doconnor

<div id="changeset">
<span class="removed">-Assigned To:</span>
<span class="added">+Assigned To: alan_k</span>
</div>

------------------------------------------------------------------------

[2013-05-28 23:20:17] vtamara

The attached patch allow insertions and updates as expected.

------------------------------------------------------------------------

[2013-05-28 23:19:21] vtamara

Added #patch
bug:19962;patch:allows-null-in-int-multi-key-references;revision:1369779561;.

------------------------------------------------------------------------

[2013-05-28 23:05:49] vtamara

Description:
------------
Cannot insert or update 



Test script:
---------------
Suppose a Database base with 3 tables, departamento, municipio and
ubicacion:

base.ini includes

[departamento]
id = 129
[departamento__keys]
id = K
[municipio]
id = 129
id_departamento = 129
[municipio__keys]
id = K
id_departamento = K
[ubicacion]
id = 129
id_municipio = 1
id_departamento = 1
[ubicacion__keys]
id = K

Regardin relationsships, a municipio belongs to a department, an
ubication can be either a department or a municipio:

base.links.ini includes
[municipio]
id_departamento = departamento:id
[ubicacion]
id_departamento = departamento:id
id_municipio,id_departamento = municipio:id,id_departamento

I wish to be able to insert an object ubicacion that references only a
departamento but not a municipio:

do->id=22;
do->sitio='';
do->id_departamento=25;
do->id_municipio=null;
do->insert();

I also wish to be able to update with null in id_municipio.

Expected result:
----------------
It should allow to insert or update such records.  In SQL the 
insertion is possible (tested with PostgreSQL) with:
INSERT INTO ubicacion (id , sitio , id_municipio , 
id_departamento ) VALUES ( 22 , '' , NULL , 25);


Actual result:
--------------
Insert of main record failed - Error from DataObject: DB 
Error: constraint violation INSERT INTO ubicacion (id , sitio , 
id_municipio , id_departamento ) VALUES ( 22 , '' , 0 , 25) 
[nativecode=ERROR: insert or update on table "ubicacion" 
violates foreign key constraint "ubicacion_id_municipio_fkey" 
DETAIL: Key (id_municipio, id_departamento)=(0, 25) is not 
present in table "municipio".]

Notice that DB_DataObject changes NULL in id_municipio for 0 
and with that change the insertion is not possible.

------------------------------------------------------------------------


-- 
Edit this bug report at http://pear.php.net/bugs/bug.php?id=19962&edit=1