From: Markus KARG <markus.karg_at_gmx.net>
Date: Sat, 14 Oct 2006 07:51:14 +0200

Wayne,

> Though I admit, it seems a bit odd that MaxDB would throw an error on
> what is essentially an identity parameter in a select statement.
In fact, the error thrown is "-7008: Updates of this table is not
allowed". The MaxDB manual says, there can be the following reasons:

1. The table specified in an SQL statement with CURRENT OF and result
table name
<http://dev.mysql.com/doc/maxdb/en/8b/5fe53ab04e11d2a97100a0c9449261/content.htm>
is not the table indicated in the FROM clause
<http://dev.mysql.com/doc/maxdb/en/0f/486fae2f9611d3a98100a0c9449261/content.htm>
of the QUERY statement
<http://dev.mysql.com/doc/maxdb/en/0f/486f992f9611d3a98100a0c9449261/content.htm>
which generated the result table.
2. The SQL statement contains a correlated subquery
<http://dev.mysql.com/doc/maxdb/en/60/515f3eca2a11d2a97100a0c9449261/content.htm>
which uses the table to be updated.
3. The QUERY expression
<http://dev.mysql.com/doc/maxdb/en/0f/486fa22f9611d3a98100a0c9449261/content.htm>
was specified with FOR UPDATE. However, the QUERY expression contains
statement parts which cannot be used together with a FOR UPDATE
specification (DISTINCT, GROUP CLAUSE, UNION, INTERSECT, EXCEPT, SUBQUERY).
4. In the DB2 SQL mode, the QUERY expression of an SQL statement with
CURRENT OF <result_table_name> contains a FOR FETCH ONLY statement.

I think that in our case, reason is #2: We have used a correlated
subquery wich uses the table to be updated.

> Does this also fail on MaxDB? I just tried on Oracle 10.1.0.4.0 and
> both were no issue...
> select emp_id from cmp3_salary where 1=1;
> select a.emp_id from cmp3_salary a, cmp3_salary b where a.emp_id =
> b.emp_id;
Both statements work well on MaxDB, since they are read only but do not
update the table (see reason #2 above).
> I won't claim to be a SQL spec guru, but it seems like this should be
> OK... I'd expect the identity params would be internally removed by
> the sql planning tools etc before the select is performed against the
> table, so in effect, they would have no effect at all on the sql
> results.
The problem is not the "1=1" but the DELETE command. SQL does not
mandate that it must be possible to delete from a table that is used in
a subquery. I could imagine that the MaxDB query manager is keeping the
table in memory while executing the subquery but does not update it by
the delete. As a result, it must be forbidden to delete to not run into
the need to update that cache. Just an assumption. It runs well with
SELECT since no updates could happen there anyways.

Thanks
Markus