Prepared Statements fail after independent DDL statements (and sometimes spontaneously)

[monetdb-team]

Date: 2011-07-08 10:47:53 +0200
From: Simon Brodt <<simon.brodt>>
To: clients devs <>
Version: 11.7.5 (Dec2011) [obsolete]

Last updated: 2012-03-02 13:35:13 +0100

Comment 15913

Date: 2011-07-08 10:47:53 +0200
From: Simon Brodt <<simon.brodt>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:5.0) Gecko/20100101 Firefox/5.0
Build Identifier:

After executing any DDL statement any PreparedStatement that was created before becomes invalid. In case of a SELECT statement this results in a SQLException "Query did not produce a result set" when calling the executeQuery() method.
In case of a UPDATE/DELETE/INSERT statement the statement seems to be executed correctly, but ist has no effect in the database and the returned update count is -1.
Sometimes both errors also occur spontaneously, i.e. without executing a DDL before.

Reproducible: Always

Steps to Reproduce:

1.Prepare any DML statement
2. Execute any DDL statement
3. Execute previously prepared DML statement
Example are included in the attached JUnit files (2.-4. test method).

Actual Results:

SELECT statement: SQLException "Query did not produce a result set"
UPDATE/DELETE/INSERT statement: update count -1 ; no effect in the database

Expected Results:

SELECT statement: ResultSet
UPDATE/DELETE/INSERT statement: update count >= 0 ; effect in the database if update count > 0

See the attached file

Comment 15914

Date: 2011-07-08 10:49:19 +0200
From: Simon Brodt <<simon.brodt>>

Created attachment 65
JUnit Test for reproducing the bug

Test methods 2-4 reproduce the bug.

Attached file: MonetDB.java (text/plain, 3726 bytes)
Description: JUnit Test for reproducing the bug

Comment 15924

Date: 2011-07-08 12:35:07 +0200
From: @grobian

This is expected behaviour. DDL statements invalidate all prepared statements (because their correctness cannot be guaranteed). The bug here is that the server doesn't tell you your prepared handle is invalid.

Comment 15926

Date: 2011-07-08 12:49:56 +0200
From: Simon Brodt <<simon.brodt>>

(In reply to comment 2)

This is expected behaviour. DDL statements invalidate all prepared statements
(because their correctness cannot be guaranteed). The bug here is that the
server doesn't tell you your prepared handle is invalid.

That's fine with me, too.
Probably calling any execute method on the prepared statement should result in an exception then.
After fixing this issue, could you plese tell me how to recognize such an exception? (e.g. SQLState)

Comment 16016

Date: 2011-07-29 10:57:51 +0200
From: @sjoerdmullender

Apr2011-SP2 has been released.

Comment 16257

Date: 2011-09-16 15:07:28 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Comment 16469

Date: 2011-10-26 14:09:46 +0200
From: @grobian

*** Bug #2899 has been marked as a duplicate of this bug. ***

Comment 16470

Date: 2011-10-26 14:28:24 +0200
From: @grobian

Changeset a9ec95c08325 made by Fabian Groffen fabian@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=a9ec95c08325

Changeset description:

query cache: keep last ID when renewing the cache

For bug #2834 (and 2899), make sure that we do not return the same id
on a connection to a client twice.  When a scheme update is performed,
the query cache and prepared statements are destroyed.  This is done for
all connected clients.  Since clients cannot know that someone else
invalidated their prepared statements, the client must have a means to
figure out that the handle is invalid (or in the worst case, doesn't
belong to the query it thinks it belongs to).

We simply keep the id before we empty the cache, and put it back in
place again, such that the client always sees an incrementing counter.