Mailing-List: contact dev-help@geronimo.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@geronimo.apache.org
Message-ID: <1587166262.1129525785254.JavaMail.jira@ajax.apache.org>
Date: Mon, 17 Oct 2005 07:09:45 +0200 (CEST)
From: "Aaron Mulder (JIRA)" <dev@geronimo.apache.org>
To: dev@geronimo.apache.org
Subject: [jira] Created: (GERONIMO-1080) CMP generates invalid update SQL
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

CMP generates invalid update SQL
--------------------------------

         Key: GERONIMO-1080
         URL: http://issues.apache.org/jira/browse/GERONIMO-1080
     Project: Geronimo
        Type: Bug
  Components: OpenEJB  
    Versions: 1.0-M5    
    Reporter: Aaron Mulder


I have a group of about 5 CMP EJBs, with various relationships defined.  I'm using PostgreSQL 8.  I'm not able to update (well, at least, the two EJBs I've tried have both had the problem).  I get this error:

java.sql.SQLException: ERROR: multiple assignments to same column "test_id"

And indeed the SQL is:

UPDATE test_run SET test_id = CASE WHEN ? THEN ? ELSE test_id END, create_date = CASE WHEN ? THEN ? ELSE create_date END, name = CASE WHEN ? THEN ? ELSE name END, description = CASE WHEN ? THEN ? ELSE description END, test_id = CASE WHEN ? THEN ? ELSE test_id END WHERE id = ?

Likewise, for a different table:

UPDATE test_run_machine SET test_run_id = CASE WHEN ? THEN ? ELSE test_run_id END, machine_id = CASE WHEN ? THEN ? ELSE machine_id END, role = CASE WHEN ? THEN ? ELSE role END, time_offset_ms = CASE WHEN ? THEN ? ELSE time_offset_ms END, test_run_id = CASE WHEN ? THEN ? ELSE test_run_id END, machine_id = CASE WHEN ? THEN ? ELSE machine_id END WHERE id = ?

I speculate that it's noticing the columns once as CMP fields and once as CMR fields, or perhaps pulling them in twice if the EJB participates in two relationships (even though they wouldn't both use that field) or something.  In any case, we should always eliminate duplicates, as far as I can tell.

Also, the CASE..WHEN..THEN..ELSE..END statements seem pretty nasty to me.  Why don't we just do "update foo set bar=?, baz=?,..."?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira