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:10:56 GMT
I also saw that you have created DERBY-6657 for fixing the docs. Thanks


On Thu, Jul 10, 2014 at 10:08 AM, Mamta Satoor <msatoor@gmail.com> wrote:

> I just noticed that you already created a jira DERBY-6656 for that. Thanks.
>
>
> On Thu, Jul 10, 2014 at 10:07 AM, Mamta Satoor <msatoor@gmail.com> wrote:
>
>> 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(GenericLanguageConnectionConte
>>>>>> xt.java:1113)
>>>>>>
>>>>>> at org.apache.derby.impl.jdbc.EmbedStatement.execute(
>>>>>> EmbedStatement.java:682)
>>>>>>
>>>>>> ... 9 more
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>

Mime
View raw message