db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: some testing of MERGE feature
Date Wed, 09 Jul 2014 16:39:34 GMT
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
>>> 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
>>>
>>>
>>
>>
>

Mime
View raw message