Return-Path: 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 1F54010BFE for ; Wed, 9 Jul 2014 16:40:01 +0000 (UTC) Received: (qmail 36822 invoked by uid 500); 9 Jul 2014 16:40:00 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 36797 invoked by uid 500); 9 Jul 2014 16:40:00 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 36785 invoked by uid 99); 9 Jul 2014 16:40:00 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 09 Jul 2014 16:40:00 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of msatoor@gmail.com designates 74.125.82.182 as permitted sender) Received: from [74.125.82.182] (HELO mail-we0-f182.google.com) (74.125.82.182) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 09 Jul 2014 16:39:56 +0000 Received: by mail-we0-f182.google.com with SMTP id q59so7821733wes.27 for ; Wed, 09 Jul 2014 09:39:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=38yz70x+a4WqLSqekzVaaF8CNuyJc+EvND83MCAGs7E=; b=Y3hor7wPUwERLVMjsAKZnJddpuujjVhKVv87VhPv0WLm0KOmRNP7F2ecyqlPvTSIls bKLqQbJq+GaUEf1P0pmQLa208L3yvIKzxYKm2ZMtiB/qt/3cOR4wMem2xgtM7SCinQ37 hDUd+OLSR+JH7RO7bj26pEfiPuUzLte+EupQ2/qAS1IrQV13paoHOrc66o5wSTWfejHU sq5h3RpvoE+tGHHIz1LPObaNIra9UzLB2DMKIaxQAglUjC5HTz15kCQQVqmpQ7e19KQG NrWPfM0B3hO3hO1LurgEb9rMggKFytc0owFvZe8Aig2Ov77vkKsiFbvyiCkD86YKI1wG MZeQ== MIME-Version: 1.0 X-Received: by 10.194.77.177 with SMTP id t17mr47970720wjw.55.1404923974696; Wed, 09 Jul 2014 09:39:34 -0700 (PDT) Received: by 10.217.124.146 with HTTP; Wed, 9 Jul 2014 09:39:34 -0700 (PDT) In-Reply-To: <53BD380F.7020205@oracle.com> References: <53BD3513.3020503@oracle.com> <53BD380F.7020205@oracle.com> Date: Wed, 9 Jul 2014 09:39:34 -0700 Message-ID: Subject: Re: some testing of MERGE feature From: Mamta Satoor To: Derby Development Content-Type: multipart/alternative; boundary=047d7bfd0bf672f31304fdc55ac4 X-Virus-Checked: Checked by ClamAV on apache.org --047d7bfd0bf672f31304fdc55ac4 Content-Type: text/plain; charset=UTF-8 Thanks for logging the jira, Rick. On Wed, Jul 9, 2014 at 5:39 AM, Rick Hillegas wrote: > On 7/9/14 5:26 AM, Rick Hillegas wrote: > >> Hi Mamta, >> >> An assertion isn't expected, so this is a bug I need to look at. >> > I've logged DERBY-6652 to track this. > > Thanks, > -Rick > > >> Thanks, >> -Rick >> >> On 7/7/14 4:25 PM, Mamta Satoor wrote: >> >>> Hi Rick, >>> I did some basic testing of MERGE feature and observed a behavior that I >>> wanted to run by you to see if it the expected behavior. >>> I have 2 really simple tables with views defined on them. >>> CREATE TABLE employee ( >>> employee_id int, >>> first_name VARCHAR(20), >>> last_name VARCHAR(20), >>> dept_no int, >>> salary int); >>> create view v1employee as select * from employee; >>> INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000); >>> INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000); >>> INSERT INTO employee VALUES (3, 'Elizabeth', 'Scott', 20, 50000); >>> INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000); >>> INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 30000); >>> INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 70000); >>> INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000); >>> CREATE TABLE bonuses ( >>> employee_id int, bonus int DEFAULT 100); >>> create view v2bonuses as select * from bonuses; >>> INSERT INTO bonuses (employee_id) VALUES (1); >>> INSERT INTO bonuses (employee_id) VALUES (2); >>> INSERT INTO bonuses (employee_id) VALUES (4); >>> INSERT INTO bonuses (employee_id) VALUES (6); >>> INSERT INTO bonuses (employee_id) VALUES (7); >>> I tried using the view as the source in the MERGE statement and got >>> following error. May be I misunderstood it, but I thought views are allowed >>> for source. I tried this on trunk using classes last week. thanks. >>> MERGE INTO bonuses B >>> USING v1employee E >>> ON B.employee_id = E.employee_id >>> WHEN MATCHED AND E.dept_no=20 THEN >>> UPDATE SET B.bonus = E.salary * 0.1 >>> WHEN NOT MATCHED AND dept_no=20 THEN >>> INSERT (employee_id, bonus) >>> VALUES (E.employee_id, E.salary * 0.05); >>> >>> ERROR XJ001: Java exception: 'ASSERT FAILED Column EMPLOYEE.SALARY has >>> illegal MERGE table id: 0: org.apache.derby.shared. >>> common.sanity.AssertFailure'. >>> >>> java.sql.SQLException: Java exception: 'ASSERT FAILED Column >>> EMPLOYEE.SALARY has illegal MERGE table id: 0: org.apache.derby.shared. >>> common.sanity.AssertFailure'. >>> >>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException( >>> SQLExceptionFactory.java:107) >>> >>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException( >>> SQLExceptionFactory.java:133) >>> >>> at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:255) >>> >>> at org.apache.derby.impl.jdbc.Util.javaException(Util.java:277) >>> >>> at org.apache.derby.impl.jdbc.TransactionResourceImpl. >>> wrapInSQLException(TransactionResourceImpl.java:437) >>> >>> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException( >>> TransactionResourceImpl.java:353) >>> >>> at org.apache.derby.impl.jdbc.EmbedConnection.handleException( >>> EmbedConnection.java:2396) >>> >>> at org.apache.derby.impl.jdbc.ConnectionChild.handleException( >>> ConnectionChild.java:82) >>> >>> at org.apache.derby.impl.jdbc.EmbedStatement.execute( >>> EmbedStatement.java:691) >>> >>> at org.apache.derby.impl.jdbc.EmbedStatement.execute( >>> EmbedStatement.java:631) >>> >>> at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367) >>> >>> at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:527) >>> >>> at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts( >>> utilMain.java:369) >>> >>> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245) >>> >>> at org.apache.derby.impl.tools.ij.Main.go(Main.java:229) >>> >>> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184) >>> >>> at org.apache.derby.impl.tools.ij.Main.main(Main.java:75) >>> >>> at org.apache.derby.tools.ij.main(ij.java:59) >>> >>> Caused by: ERROR XJ001: Java exception: 'ASSERT FAILED Column >>> EMPLOYEE.SALARY has illegal MERGE table id: 0: org.apache.derby.shared. >>> common.sanity.AssertFailure'. >>> >>> at org.apache.derby.iapi.error.StandardException.newException( >>> StandardException.java:290) >>> >>> at org.apache.derby.impl.jdbc.SQLExceptionFactory. >>> wrapArgsForTransportAcrossDRDA(SQLExceptionFactory.java:162) >>> >>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException( >>> SQLExceptionFactory.java:73) >>> >>> ... 17 more >>> >>> Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT >>> FAILED Column EMPLOYEE.SALARY has illegal MERGE table id: 0 >>> >>> at org.apache.derby.shared.common.sanity.SanityManager. >>> ASSERT(SanityManager.java:120) >>> >>> at org.apache.derby.impl.sql.compile.MatchingClauseNode.getMergeTableID( >>> MatchingClauseNode.java:1495) >>> >>> at org.apache.derby.impl.sql.compile.MatchingClauseNode. >>> getSelectListOffset(MatchingClauseNode.java:1444) >>> >>> at org.apache.derby.impl.sql.compile.MatchingClauseNode. >>> useGeneratedScan(MatchingClauseNode.java:1423) >>> >>> at org.apache.derby.impl.sql.compile.MatchingClauseNode. >>> adjustThenColumns(MatchingClauseNode.java:1380) >>> >>> at org.apache.derby.impl.sql.compile.MatchingClauseNode. >>> generateInsertUpdateRow(MatchingClauseNode.java:1330) >>> >>> at org.apache.derby.impl.sql.compile.MatchingClauseNode. >>> generate(MatchingClauseNode.java:1237) >>> >>> at org.apache.derby.impl.sql.compile.MergeNode.generate( >>> MergeNode.java:1013) >>> >>> at org.apache.derby.impl.sql.compile.StatementNode. >>> generate(StatementNode.java:317) >>> >>> at org.apache.derby.impl.sql.GenericStatement.prepMinion( >>> GenericStatement.java:549) >>> >>> at org.apache.derby.impl.sql.GenericStatement.prepare( >>> GenericStatement.java:99) >>> >>> at org.apache.derby.impl.sql.conn.GenericLanguageConnectionConte >>> xt.prepareInternalStatement(GenericLanguageConnectionContext.java:1113) >>> >>> at org.apache.derby.impl.jdbc.EmbedStatement.execute( >>> EmbedStatement.java:682) >>> >>> ... 9 more >>> >>> >> >> > --047d7bfd0bf672f31304fdc55ac4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thanks for logging the jira, Rick.


On Wed, Jul 9, 2014 at 5:39 AM,= Rick Hillegas <rick.hillegas@oracle.com> wrote:
On 7/9/14 5:26 AM, Rick Hillegas wrote:=
Hi Mamta,

An assertion isn't expected, so this is a bug I need to look at.
I've logged DERBY-6652 to track this.

Thanks,
-Rick


Thanks,
-Rick

On 7/7/14 4:25 PM, Mamta Satoor wrote:
Hi Rick,
I did some basic testing of MERGE feature and observed a behavior that I wa= nted to run by you to see if it the expected behavior.
I have 2 really simple tables with views defined on them.
CREATE TABLE employee (
employee_id int,
first_name =C2=A0VARCHAR(20),
last_name =C2=A0 VARCHAR(20),
dept_no =C2=A0 =C2=A0 int,
salary =C2=A0 =C2=A0 =C2=A0int);
create view v1employee as select * from employee;
INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000= );
INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000= );
INSERT INTO employee VALUES (3, 'Elizabeth', 'Scott', 20, 5= 0000);
INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 400= 00);
INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 300= 00);
INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 70000= );
INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 9000= 0);
CREATE TABLE bonuses (
employee_id int, bonus int DEFAULT 100);
create view v2bonuses as select * from bonuses;
INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
I tried using the view as the source in the MERGE statement and got followi= ng error. May be I misunderstood it, but I thought views are allowed for so= urce. I tried this on trunk using classes last week. thanks.
MERGE INTO bonuses B
USING v1employee E
ON B.employee_id =3D E.employee_id
WHEN MATCHED AND E.dept_no=3D20 THEN
=C2=A0 UPDATE SET B.bonus =3D E.salary * 0.1
WHEN NOT MATCHED AND dept_no=3D20 THEN
=C2=A0 INSERT (employee_id, bonus)
=C2=A0 VALUES (E.employee_id, E.salary * 0.05);

ERROR XJ001: Java exception: 'ASSERT FAILED Column EMPLOYEE.SALARY has = illegal MERGE table id: 0: org.apache.derby.shared.common.sanity.Ass= ertFailure'.

java.sql.SQLException: Java exception: 'ASSERT FAILED Column EMPLOYEE.S= ALARY has illegal MERGE table id: 0: org.apache.derby.shared.common.= sanity.AssertFailure'.

at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLExcep= tion(SQLExceptionFactory.java:107)

at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLExcep= tion(SQLExceptionFactory.java:133)

at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java= :255)

at org.apache.derby.impl.jdbc.Util.javaException(Util.java:27= 7)

at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInS= QLException(TransactionResourceImpl.java:437)

at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleE= xception(TransactionResourceImpl.java:353)

at org.apache.derby.impl.jdbc.EmbedConnection.handleException= (EmbedConnection.java:2396)

at org.apache.derby.impl.jdbc.ConnectionChild.handleException= (ConnectionChild.java:82)

at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedSta= tement.java:691)

at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedSta= tement.java:631)

at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java= :367)

at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.j= ava:527)

at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(util= Main.java:369)

at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:2= 45)

at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)

at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184= )

at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)

at org.apache.derby.tools.ij.main(ij.java:59)

Caused by: ERROR XJ001: Java exception: 'ASSERT FAILED Column EMPLOYEE.= SALARY has illegal MERGE table id: 0: org.apache.derby.shared.common= .sanity.AssertFailure'.

at org.apache.derby.iapi.error.StandardException.newException= (StandardException.java:290)

at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsFor= TransportAcrossDRDA(SQLExceptionFactory.java:162)

at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLExcep= tion(SQLExceptionFactory.java:73)

... 17 more

Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSE= RT FAILED Column EMPLOYEE.SALARY has illegal MERGE table id: 0

at org.apache.derby.shared.common.sanity.SanityManager.ASSERT= (SanityManager.java:120)

at org.apache.derby.impl.sql.compile.MatchingClauseNode.getMe= rgeTableID(MatchingClauseNode.java:1495)

at org.apache.derby.impl.sql.compile.MatchingClauseNode.getSe= lectListOffset(MatchingClauseNode.java:1444)

at org.apache.derby.impl.sql.compile.MatchingClauseNode.useGe= neratedScan(MatchingClauseNode.java:1423)

at org.apache.derby.impl.sql.compile.MatchingClauseNode.adjus= tThenColumns(MatchingClauseNode.java:1380)

at org.apache.derby.impl.sql.compile.MatchingClauseNode.gener= ateInsertUpdateRow(MatchingClauseNode.java:1330)

at org.apache.derby.impl.sql.compile.MatchingClauseNode.gener= ate(MatchingClauseNode.java:1237)

at org.apache.derby.impl.sql.compile.MergeNode.generate(Merge= Node.java:1013)

at org.apache.derby.impl.sql.compile.StatementNode.generate(S= tatementNode.java:317)

at org.apache.derby.impl.sql.GenericStatement.prepMinion(Gene= ricStatement.java:549)

at org.apache.derby.impl.sql.GenericStatement.prepare(Generic= Statement.java:99)

at org.apache.derby.impl.sql.conn.GenericLanguageConnectionCo= ntext.prepareInternalStatement(GenericLanguageConnectionConte= xt.java:1113)

at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedSta= tement.java:682)

... 9 more





--047d7bfd0bf672f31304fdc55ac4--