Misleading stack-trace for CHECK constraint

[Gili]

Hi,

I'd like your opinion on the following bug/RFE for H2 1.3.171:

1. Given this table:

CREATE TABLE connections (id IDENTITY, call_id BIGINT NOT NULL,

from_participant_id BIGINT NOT NULL, to_participant_id BIGINT NOT NULL,

creation_time TIMESTAMP NOT NULL, messages_md5 VARCHAR(32) NOT NULL,

messages_last_modified TIMESTAMP NOT NULL, expired BOOLEAN NOT NULL,

UNIQUE (from_participant_id, to_participant_id),

  FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE CASCADE,

  FOREIGN KEY (from_participant_id) REFERENCES participants(id) ON DELETE CASCADE,

FOREIGN KEY (to_participant_id) REFERENCES participants(id) ON DELETE CASCADE,

CHECK (SELECT participants.call_id FROM calls, participants WHERE

participants.id IN (from_participant_id, to_participant_id)) = call_id);

2. When I insert into this table, it fails with the following stack-trace:

com.mysema.query.QueryException: Caught JdbcSQLException for insert into CONNECTIONS (CALL_ID, FROM_PARTICIPANT_ID, TO_PARTICIPANT_ID, CREATION_TIME, MESSAGES_MD5, MESSAGES_LAST_MODIFIED, EXPIRED)

values (?, ?, ?, ?, ?, ?, ?)

at com.mysema.query.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:294) ~[querydsl-sql-3.2.0.jar:na]

at com.mysema.query.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:171) ~[querydsl-sql-3.2.0.jar:na]

at com.mysema.query.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:163) ~[querydsl-sql-3.2.0.jar:na]

at com.mycompany.sql.row.Connection.insert(Connection.java:56) ~[server.service-1.0-SNAPSHOT.jar:na]

at com.mycompany.web.resource.CallResource.createConnection(CallResource.java:332) ~[classes/:na]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_21]

at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) ~[jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) [jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) [jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) [jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) [jersey-server-1.17.1.jar:1.17.1]

at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) [jersey-servlet-1.17.1.jar:1.17.1]

at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) [jersey-servlet-1.17.1.jar:1.17.1]

at com.sun.jersey.spi.container.servlet.ServletContainer.doFilter(ServletContainer.java:910) [jersey-servlet-1.17.1.jar:1.17.1]

at com.sun.jersey.spi.container.servlet.ServletContainer.doFilter(ServletContainer.java:858) [jersey-servlet-1.17.1.jar:1.17.1]

at com.sun.jersey.spi.container.servlet.ServletContainer.doFilter(ServletContainer.java:812) [jersey-servlet-1.17.1.jar:1.17.1]

at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163) [guice-servlet-3.0.jar:na]

at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) [guice-servlet-3.0.jar:na]

at com.mycompany.web.SessionFilter.doFilter(SessionFilter.java:58) [classes/:na]

at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163) [guice-servlet-3.0.jar:na]

at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58) [guice-servlet-3.0.jar:na]

at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) [guice-servlet-3.0.jar:na]

at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) [guice-servlet-3.0.jar:na]

at org.glassfish.grizzly.servlet.FilterChainImpl.doFilter(FilterChainImpl.java:137) [grizzly-http-servlet-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.servlet.FilterChainImpl.invokeFilterChain(FilterChainImpl.java:106) [grizzly-http-servlet-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.servlet.ServletHandler.doServletService(ServletHandler.java:252) [grizzly-http-servlet-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.servlet.ServletHandler.service(ServletHandler.java:188) [grizzly-http-servlet-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:164) [grizzly-http-server-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.http.server.HttpHandlerChain.service(HttpHandlerChain.java:196) [grizzly-http-server-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:164) [grizzly-http-server-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:175) [grizzly-http-server-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:265) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:134) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:78) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:815) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:567) [grizzly-framework-2.2.16.jar:2.2.16]

at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:547) [grizzly-framework-2.2.16.jar:2.2.16]

at java.lang.Thread.run(Thread.java:722) [na:1.7.0_21]

Caused by: org.h2.jdbc.JdbcSQLException: Scalar subquery contains more than one row; SQL statement:

insert into CONNECTIONS (CALL_ID, FROM_PARTICIPANT_ID, TO_PARTICIPANT_ID, CREATION_TIME, MESSAGES_MD5, MESSAGES_LAST_MODIFIED, EXPIRED)

values (?, ?, ?, ?, ?, ?, ?) [90053-171]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2-1.3.171.jar:1.3.171]

at org.h2.message.DbException.get(DbException.java:169) ~[h2-1.3.171.jar:1.3.171]

at org.h2.message.DbException.get(DbException.java:146) ~[h2-1.3.171.jar:1.3.171]

at org.h2.message.DbException.get(DbException.java:135) ~[h2-1.3.171.jar:1.3.171]

at org.h2.expression.Subquery.getValue(Subquery.java:40) ~[h2-1.3.171.jar:1.3.171]

at org.h2.expression.Comparison.getValue(Comparison.java:206) ~[h2-1.3.171.jar:1.3.171]

at org.h2.constraint.ConstraintCheck.checkRow(ConstraintCheck.java:91) ~[h2-1.3.171.jar:1.3.171]

at org.h2.table.Table.fireConstraints(Table.java:873) ~[h2-1.3.171.jar:1.3.171]

at org.h2.table.Table.fireBeforeRow(Table.java:863) ~[h2-1.3.171.jar:1.3.171]

at org.h2.command.dml.Insert.insertRows(Insert.java:121) ~[h2-1.3.171.jar:1.3.171]

at org.h2.command.dml.Insert.update(Insert.java:84) ~[h2-1.3.171.jar:1.3.171]

at org.h2.command.CommandContainer.update(CommandContainer.java:75) ~[h2-1.3.171.jar:1.3.171]

at org.h2.command.Command.executeUpdate(Command.java:230) ~[h2-1.3.171.jar:1.3.171]

at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156) ~[h2-1.3.171.jar:1.3.171]

at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142) ~[h2-1.3.171.jar:1.3.171]

at com.mysema.query.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:278) ~[querydsl-sql-3.2.0.jar:na]

... 63 common frames omitted

3. As you can see, the error message points to the INSERT statement but in fact the error is in the CHECK constraint.

4. Expected behavior: stack-trace should reference CHECK constraint, not INSERT statement.

Can I file a bug report for this?

Thanks,

Gili

[Noel Grandin]

On 2013-06-07 01:32, Gili wrote:
>
> 2. When I insert into this table, it fails with the following stack-trace:
>

> 3. As you can see, the error message points to the INSERT statement
> but in fact the error is in the CHECK constraint.
> 4. Expected behavior: stack-trace should reference CHECK constraint,
> not INSERT statement.
>

How exactly would we distinguish between an error with the CHECK and an
error with the INSERT?

We are executing the CHECK constraint when the INSERT occurs, and the
INSERT fails, so the error message points to the INSERT.

If you can come up with some foolproof method of distinguishing between
- a failure of a CHECK constraint because the constraint is broken
vs
- a failure in the CHECK constraint because the INSERT was broken
then, sure, we can try to fix it.

But I just don't see it.

This is one of those cases where we give you lots of flexibility, and
the price you pay for the flexibility is that weird errors can happen.

[cowwoc]

Hi Noel,

Before answering the question, could you please explain what "a
failure in the CHECK constraint because the INSERT was broken" actually
means? When/how would that occur?

Thanks,
Gili

[Thomas Mueller]

Hi,

What about changing the constraint:

CHECK (SELECT count(*) FROM calls, participants 

    WHERE participants.id IN (from_participant_id, to_participant_id) 

    and participants.call_id=call_id) > 0);

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

[cowwoc]

Hi Thomas,

    I actually ended up doing exactly that, but the original question remains: can we provide a better error message?

Thanks,
Gili

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/h3GLzmtTTIk/unsubscribe?hl=en-US.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Noel Grandin]

You are correct Gili, we should be able to. 

Can you create a self contained test case?

Thanks. 

[cowwoc]

Hi Noel,

    Here you go: https://bitbucket.org/cowwoc/h2invalidcheck

Gili

[Noel Grandin]

On 2013-06-08 05:55, cowwoc wrote:

Hi Noel,

    Here you go: https://bitbucket.org/cowwoc/h2invalidcheck
 

Sorry, that link doesn't take me anywhere useful.
What is it supposed to point to?

[cowwoc]

Hi Noel,

    You can download the testcase from https://bitbucket.org/cowwoc/h2invalidcheck/downloads (click on the "zip" link)

Gili

[Noel Grandin]

Nope, there is no zip link when I go to that page.

How about you just post the SQL script using email?

[cowwoc]

On 10/06/2013 12:21 PM, Noel Grandin wrote:
> Nope, there is no zip link when I go to that page.
>
> How about you just post the SQL script using email?

Sorry. Here is a direct link:
https://bitbucket.org/cowwoc/h2invalidcheck/get/default.zip

Gili

[Noel Grandin]

I see trigger stuff, but nothing about CHECK constraints.

If I don't see a simple self-contained SQL script in the next email, I
will stop trying to fix this.

[cowwoc]

Noel,

Sorry for the confusion. Here is the testcase (with embedded SQL):

package org.bitbucket.cowwoc.h2invalidcheck;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class App {

public static void main(String[] args) throws
ClassNotFoundException, SQLException {
Class.forName("org.h2.Driver");
Connection connection =
DriverManager.getConnection("jdbc:h2:mem:test1;DB_CLOSE_DELAY=120",
"sa", "");
Statement statement = connection.createStatement();
statement.executeUpdate("create table companies(id identity)");
statement.executeUpdate("create table departments(id identity, "
+ "company_id bigint not null, "
+ "foreign key(company_id) references companies(id))");
statement.executeUpdate("create table connections (id identity,
company_id bigint not null, "
+ "first bigint not null, second bigint not null, "
+ "foreign key (company_id) references companies(id), "
+ "foreign key (first) references departments(id), "
+ "foreign key (second) references departments(id), "
+ "check (select departments.company_id from
departments, companies where "
+ "departments.id in (first, second)) = company_id);");

statement.executeUpdate("insert into companies(id) values(1)");
statement.executeUpdate("insert into departments(id, company_id) "
+ "values(10, 1)");
statement.executeUpdate("insert into departments(id, company_id) "
+ "values(20, 1)");
statement.executeUpdate("insert into connections(id,
company_id, first, second) "
+ "values(100, 1, 10, 20)");
connection.commit();
}
}

Thanks,
Gili

[Noel Grandin]

On 2013-06-11 16:42, cowwoc wrote:
>
> Sorry for the confusion. Here is the testcase (with embedded SQL):

Excellent, thank you very much.
Change committed to SVN.