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 Thu, 10 Jul 2014 17:07:18 GMT
Hi Rick,

Do we need to change the documentation now that views are disabled for
source? Also, should we add another jira to support use of view for source
if such use of views is allowed by SQL standards?

thanks,
Mamta


On Thu, Jul 10, 2014 at 5:40 AM, Rick Hillegas <rick.hillegas@oracle.com>
wrote:

> Hi Mamta,
>
> I have disabled the use of views as the source data sets of MERGE
> statements. Hope that this allows you to continue buddy-testing MERGE.
>
> Thanks,
> -Rick
>
>
> On 7/9/14 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
>>>>
>>>>
>>>
>>>
>>
>>
>

Mime
View raw message