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.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1113) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:682) ... 9 more
