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