search

hamidhtc / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

Feature not supported: "FOR UPDATE && JOIN" #541

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
Enabling MV_STORE and MVCC yields an error when I execute the following query:

SELECT t1.x FROM t1, (SELECT x FROM t3 WHERE y = ? ORDER BY x) AS t2 WHERE t2.x 
= t1.x ORDER BY t1.x FOR UPDATE [50100-175]

        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
        at org.h2.message.DbException.get(DbException.java:172)
        at org.h2.message.DbException.get(DbException.java:149)
        at org.h2.message.DbException.getUnsupportedException(DbException.java:207)
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:615)
        at org.h2.command.dml.Query.query(Query.java:314)
        at org.h2.command.dml.Query.query(Query.java:284)
        at org.h2.command.dml.Query.query(Query.java:36)
        at org.h2.command.CommandContainer.query(CommandContainer.java:91)
        at org.h2.command.Command.executeQuery(Command.java:196)
        at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:106)

Original issue reported on code.google.com by g...@maginatics.com on 21 Jan 2014 at 12:03

GoogleCodeExporter commented 9 years ago 
This is not related to the MV_STORE. It also fails when you don't enable it.

When using FOR UPDATE, the database tries to convert the query to a UPDATE 
statement, but that's not so easy when using a join.

As a workaround, what you could do is write an UPDATE statement yourself, one 
that does a dummy update, for example SET X=X.

Original comment by thomas.t...@gmail.com on 21 Jan 2014 at 8:38

GoogleCodeExporter commented 9 years ago 
I'm a little confused by the last comment. This functionality works in 1.3.176 
but fails in 1.4.178.

1. What feature does not support FOR UPDATE and JOIN? Is it MVCC or MV_STORE?
2. Why is it harder to convert a query an UPDATE statement when one of these 
features is enabled?

Original comment by cow...@bbs.darktech.org on 5 May 2014 at 3:10

GoogleCodeExporter commented 9 years ago 
Thomas? Can you please answer the aforementioned questions?

Original comment by cow...@bbs.darktech.org on 21 Jun 2014 at 6:31

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Any progress on this topic?

We switched to the nightly build lately because of the deadlock issues in the 
stable version. We found a discussion 
http://h2-database.66688.n3.nabble.com/Deadlock-trying-to-lock-a-table-that-is-a
lready-locked-td4030281.html that exactly described our deadlock problem and 
the nightly build fixed that issue. But now we have this "feature not 
supported" issue instead.

Original comment by gsmits...@gmail.com on 18 Nov 2014 at 7:34

GoogleCodeExporter commented 9 years ago 
We don't support the combination of

(1) MVCC=true
(2) FOR UPDATE
(3) GROUP/DISTINCT/AGGREGATE/JOIN

Original comment by noelgrandin on 6 Jan 2015 at 7:34