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 <[email protected]> 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 <[email protected]> > 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 >>>>> >>>>> >>>> >>>> >>> >>> >> >
