Return-Path: 
 <derby-dev-return-107885-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
X-Original-To: apmail-db-derby-dev-archive@www.apache.org
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 5B33410C32
	for <apmail-db-derby-dev-archive@www.apache.org>;
 Sat, 30 Nov 2013 01:49:36 +0000 (UTC)
Received: (qmail 60151 invoked by uid 500); 30 Nov 2013 01:49:35 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 60110 invoked by uid 500); 30 Nov 2013 01:49:35 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 60102 invoked by uid 99); 30 Nov 2013 01:49:35 -0000
Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 30 Nov 2013 01:49:35 +0000
Date: Sat, 30 Nov 2013 01:49:35 +0000 (UTC)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12313704.1124846280000.48458.1385776175649@arcas>
In-Reply-To: <JIRA.12313704.1124846280000@arcas>
References: <JIRA.12313704.1124846280000@arcas>
Subject: [jira] [Comment Edited] (DERBY-532) Support deferrable constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


    [ https://issues.apache.org/jira/browse/DERBY-532?page=3Dcom.atlassian.=
jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D13835=
590#comment-13835590 ]=20

Dag H. Wanvik edited comment on DERBY-532 at 11/30/13 1:48 AM:
---------------------------------------------------------------

Here are the rest of the failures and errors I saw running the JUnit tests.=
 I'll be analyzing them to see if they are to be expected or not.

- LangProcedureTest (lock difference)
- LangScripts update (lock difference)
- LojReorderTest (different execution plan)
- InListMultiProbeTest (assertTrue(rtsp.usedIndexScan()))
- CollationTest2 (java.sql.SQLIntegrityConstraintViolationException:
  The statement was aborted because it would have caused a duplicate
  key value in a unique or primary key constraint or unique index
  identified by 'SQL131129120107383' defined on 'DERBY_5367'.)
- ConglomerateSharingTest (number of physical conglomerates that exist
  for the received table: Expected: >3< Found: >4<
  To be expected? (check): deferrable doesn't share index
- NullableUniqueConstraintTest (junit.framework.ComparisonFailure:
  Unexpected SQL state. expected:<[23505]> but was:<[XJ001]>)
  if (SanityManager.DEBUG) { // deferrable: we use a non-unique index
     SanityManager.ASSERT(
       insertStatus !=3D ConglomerateController.ROWISDUPLICATE); <=3D=3D=3D=
=3D=3D!!
  }
  ++ more

- UniqueConstraintSetNullTest
  (java.sql.SQLIntegrityConstraintViolationException: The statement
  was aborted because it would have caused a duplicate key value in a
  unique or primary key constraint or unique index identified by
  'U_CON' defined on 'CONSTRAINTEST'.)
  ++ more

- UniqueConstraintMultiThreadedTest
  (junit.framework.AssertionFailedError: isolation levels: 1 1)

- XplainStatisticsTest (=09 expected rows:=20
=09=09[[COUNTRIES_UNQ_NM, C, BTREE, RC, 1, 1, 1, SH, R, 2, ALL]]
=09 actual result:=20
=09=09[[COUNTRIES_UNQ_NM, C, BTREE, RC, 2, 1, 1, IS, R, 2, ALL]])

- dml019(org.apache.derbyTesting.functionTests.tests.nist.NistScripts)
  failed: junit.framework.ComparisonFailure: Output at line 85=20
  expected:<E1 |P[1 |4]0 > but was:<E1 |P[2 |2]0 >

- UpdateStatisticsTest (junit.framework.AssertionFailedError: failed
  to get statistics for table TEST_TAB_1 (#expected=3D0, timeout=3D0)
  Index statistics for TEST_TAB_1 1:
  {tableId=3D2f2b17ef-0142-a377-c079-0000766f8c2f, tableName=3DTEST_TAB_1,
  indexName=3DSQL131129120747740, lcols=3D1, rows=3D2, unique/card=3D2,
  created=3D2013-11-29 12:07:47.742} expected:<0> but was:<1>)

(Note: this run included the proposed patch for DERBY-6419 as well)


was (Author: dagw):
Here are the rest of the failures and errors I saw running the JUnit tests.=
 I'll be analyzing them to see if they are to be expected or not.

- LangProcedureTest (lock difference)
- LangScripts update (lock difference)
- LojReorderTest (different execution plan)
- InListMultiProbeTest (assertTrue(rtsp.usedIndexScan()))
- CollationTest2 (java.sql.SQLIntegrityConstraintViolationException:
  The statement was aborted because it would have caused a duplicate
  key value in a unique or primary key constraint or unique index
  identified by 'SQL131129120107383' defined on 'DERBY_5367'.)
- ConglomerateSharingTest (number of physical conglomerates that exist
  for the received table: Expected: >3< Found: >4<
  To be expected? (check): deferrable doesn't share index
- NullableUniqueConstraintTest (junit.framework.ComparisonFailure:
  Unexpected SQL state. expected:<[23505]> but was:<[XJ001]>)

  if (SanityManager.DEBUG) { // deferrable: we use a non-unique index
     SanityManager.ASSERT(
       insertStatus !=3D ConglomerateController.ROWISDUPLICATE); <=3D=3D=3D=
=3D=3D!!
  }

  ++ more

- UniqueConstraintSetNullTest
  (java.sql.SQLIntegrityConstraintViolationException: The statement
  was aborted because it would have caused a duplicate key value in a
  unique or primary key constraint or unique index identified by
  'U_CON' defined on 'CONSTRAINTEST'.)
  ++ more

- UniqueConstraintMultiThreadedTest
  (junit.framework.AssertionFailedError: isolation levels: 1 1)

- XplainStatisticsTest (=09 expected rows:=20
=09=09[[COUNTRIES_UNQ_NM, C, BTREE, RC, 1, 1, 1, SH, R, 2, ALL]]
=09 actual result:=20
=09=09[[COUNTRIES_UNQ_NM, C, BTREE, RC, 2, 1, 1, IS, R, 2, ALL]])

- dml019(org.apache.derbyTesting.functionTests.tests.nist.NistScripts)
  failed: junit.framework.ComparisonFailure: Output at line 85=20
  expected:<E1 |P[1 |4]0 > but was:<E1 |P[2 |2]0 >

- UpdateStatisticsTest (junit.framework.AssertionFailedError: failed
  to get statistics for table TEST_TAB_1 (#expected=3D0, timeout=3D0)
  Index statistics for TEST_TAB_1 1:
  {tableId=3D2f2b17ef-0142-a377-c079-0000766f8c2f, tableName=3DTEST_TAB_1,
  indexName=3DSQL131129120747740, lcols=3D1, rows=3D2, unique/card=3D2,
  created=3D2013-11-29 12:07:47.742} expected:<0> but was:<1>)


> Support deferrable constraints
> ------------------------------
>
>                 Key: DERBY-532
>                 URL: https://issues.apache.org/jira/browse/DERBY-532
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: J=C3=B6rg von Frantzius
>            Assignee: Dag H. Wanvik
>              Labels: derby_triage10_11
>         Attachments: IndexDescriptor.html, IndexDescriptorImpl.html, Inde=
xRowGenerator.html, SortObserver.html, deferredConstraints.html, deferredCo=
nstraints.html, deferredConstraints.html, deferredConstraints.html, deferre=
dConstraints.html, derby-532-fix-metadata-1.diff, derby-532-fix-metadata-1.=
status, derby-532-import-1.diff, derby-532-import-1.status, derby-532-impor=
t-2.diff, derby-532-import-3.diff, derby-532-import-3.status, derby-532-mor=
e-tests-1.diff, derby-532-more-tests-1.stat, derby-532-post-scan-1.diff, de=
rby-532-post-scan-1.stat, derby-532-post-scan-2.diff, derby-532-post-scan-2=
.stat, derby-532-post-scan-3.diff, derby-532-post-scan-3.stat, derby-532-po=
st-scan-4.diff, derby-532-post-scan-4.stat, derby-532-serializable-scan-1.d=
iff, derby-532-serializable-scan-2.diff, derby-532-serializable-scan-2.stat=
, derby-532-syntax-binding-dict-1.diff, derby-532-syntax-binding-dict-1.sta=
tus, derby-532-syntax-binding-dict-2.diff, derby-532-syntax-binding-dict-2.=
status, derby-532-syntax-binding-dict-all-1.diff, derby-532-test-with-defau=
lt-deferrable-all-over.diff, derby-532-testAlterConstraintInvalidation.diff=
, derby-532-testAlterConstraintInvalidation.status, derby-532-unique-pk-1.d=
iff, derby-532-unique-pk-1.status, derby-532-unique-pk-2.diff, derby-532-un=
ique-pk-3.diff, derby-532-unique-pk-3.status, derby-532-xa-1.diff, derby-53=
2-xa-2.diff, derby-532-xa-3.diff, derby-532-xa-3.status
>
>
> In many situations it is desirable to have constraints checking taking pl=
ace only at transaction commit time, and not before. If e.g. there is a cha=
in of foreign key constraints between tables, insert statements have to be =
ordered to avoid constraint violations. If foreign key references are circu=
lar, the DML has to be split into insert statements and subsequent update s=
tatements by the user.
> In other words, with deferred constraints checking, life is much easier f=
or the user. Also it can create problems with softwares such as object-rela=
tional mapping tools that are not prepared for statement ordering and thus =
depend on deferred constraints checking.



--
This message was sent by Atlassian JIRA
(v6.1#6144)