Thanks for logging the jira, Rick.
On Wed, Jul 9, 2014 at 5:39 AM, Rick Hillegas <[email protected]> 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 >>> >>> >> >> >
