How can I get EclipseLink to output valid Informix SQL for an UPDATE WHERE clause?

Question

We have a named query like this:

UPDATE Foo f SET f.x = 0 WHERE f.x = :invoiceId

Foo in this case is an entity with a superclass, using the table-per-class inheritance strategy.

The SQL that EclipseLink generates is:

UPDATE foo_subclass SET x = ?
 WHERE EXISTS(SELECT t0.id
                FROM foo_superclass t0, foo_subclass t1
               WHERE ((t1.x = ?) AND ((t1.id = t0.id) AND (t0.DTYPE = ?)))

(The ? slots are correctly filled in.)

On Informix 11.70, we get an error that the subquery cannot access the table being changed.

Here is the documentation that I was able to find on subquery restrictions on Informix: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_2005.htm

Other databases also feature restrictions on subqueries like this, so although this is manifesting as an Informix issue, I'm sure that if we ran this against, say, MySQL, we would get a similar error.

How can I get EclipseLink to honor these restrictions? Is there a better query I should be using?

Answer 1

Found the answer. Looks like EclipseLink had to handle this case for MySQL, which also has similar issues.

The answer is that in your InformixPlatform subclass, you need to override the following methods to solve this problem:

1. supportsLocalTemporaryTables(): this needs to return true
2. shouldAlwaysUseTempStorageForModifyAll(): this needs to return true
3. dontBindUpdateAllQueryUsingTempTables needs to return true
4. getCreateTempTableSqlPrefix(): this needs to return CREATE TEMP TABLE 
5. getCreateTempTableSqlSuffix(): this needs to return  WITH NO LOG
6. isInformixOuterJoin(): needs to return false

7. getTempTableForTable(DatabaseTable): this needs to do this:

   return new DatabaseTable("TL_" + table.getName(), "" /* no table qualifier */, table.shouldUseDelimiters(), this.getStartDelimiter(), this.getEndDelimiter());
   

In addition, you need to override the following methods as well for proper InformixPlatform behavior:

1. appendBoolean(Boolean, Writer): the stock Informix platform does not write out boolean literals properly. Yours needs to do this:

   if (Boolean.TRUE.equals(booleanValue)) {
     writer.write("'t'");
   } else {
     writer.write("'f'");
   }
   

2. You need to override writeUpdateOriginalFromTempTableSql so that it contains the same code as the H2Platform's override does:

   @Override
   public void writeUpdateOriginalFromTempTableSql(final Writer writer, final DatabaseTable table, final Collection pkFields, final Collection assignedFields) throws IOException {
     writer.write("UPDATE ");
     final String tableName = table.getQualifiedNameDelimited(this);    
     writer.write(tableName);
     writer.write(" SET ");
     final int size = assignedFields.size();
     if (size > 1) {
       writer.write("(");            
     }
     writeFieldsList(writer, assignedFields, this);
     if (size > 1) {
       writer.write(")");            
     }
     writer.write(" = (SELECT ");        
     writeFieldsList(writer, assignedFields, this);
     writer.write(" FROM ");
     final String tempTableName = this.getTempTableForTable(table).getQualifiedNameDelimited(this);
     writer.write(tempTableName);
     writeAutoJoinWhereClause(writer, null, tableName, pkFields, this);
     writer.write(") WHERE EXISTS(SELECT ");
     writer.write(((DatabaseField)pkFields.iterator().next()).getNameDelimited(this));
     writer.write(" FROM ");
     writer.write(tempTableName);
     writeAutoJoinWhereClause(writer, null, tableName, pkFields, this);
     writer.write(")");
   }
   

Lastly, your constructor needs to call this.setShouldBindLiterals(false).

With these changes, it seems that Informix is happy.