Re: Conglomerate could not be created?
On 20/06/2012 15:25, Kristian Waagan wrote: Logged as DERBY-5823 [1]. The bug is data dependent in that it requires a certain number of rows to be inserted, so that may be what's causing your repro to fail triggering the bug. OK, many thanks. -- John English
Re: Conglomerate could not be created?
On 19.06.2012 09:45, John English wrote: On 05/06/2012 18:45, Dag H. Wanvik wrote: Looks like a bug to me. The error XSCH4 Conglomerate could not be created isn't being tested for in the Derby tests as far as I can see. I also searched the bug tracker (https://issues.apache.org/jira/browse/DERBY) in vain for this particular error. Looking in the code where the error is reported in the source code, it looks like it shouldn't happen. Could you post a JIRA bug report for this error, please? It you are able to make a stand-alone repro that will increase your chances of having it fixed. After trying to boil this down and after much head-scratching, I've realised that I do the insert using a method in a DB utility class. There are two methods: dbInsert() and dbUpdate(). The difference is that doInsert() uses Statement.RETURN_GENERATED_KEYS when creating the prepared statement, and then calls stat.getGeneratedKeys() after the insertion. My problem seems to arise because I used doInsert to execute the insert even though there isn't a generated key. If I use dbUpdate() instead, the problem goes away. My attempt at a standalone repro tries to mimic this behaviour like so: AUTOCOMMIT OFF; INSERT INTO resources ...; INSERT INTO resource_usage ...; PREPARE p AS ...; EXECUTE p USING ...; VALUES IDENTITY_VAL_LOCAL(); COMMIT; but this works. So I suspect that IDENTITY_VAL_LOCAL is implemented differently, and the problem is due to Statement.RETURN_GENERATED_KEYS being used with a statement that doesn't generate a key. The stack trace I posted earlier began: java.sql.SQLException: Conglomerate could not be created. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source) at utils.DataManager.dbInsert(DataManager.java:1056) dbInsert lines 1056/1057 look like this: stat.executeUpdate(); ResultSet r = stat.getGeneratedKeys(); so the failure happens when the prepared statement is executed, just before the call to getGeneratedKeys(). Does this suggest a cause for the Conglomerate could not be created error to anyone? Hi John, I believe your assumptions are correct. This is a bug in Derby where the temporary row holder, used to hold generated keys, overflows and a temporary backing conglomerate is attempted created with a zero-length row template. The bug can easily be reproduced using JDBC. Unless someone beats me to it, I'll log a JIRA and attach a regression test for the bug. Regards, -- Kristian TIA,
Re: Conglomerate could not be created?
On 20.06.2012 13:05, John English wrote: On 20/06/2012 11:45, Kristian Waagan wrote: Hi John, I believe your assumptions are correct. This is a bug in Derby where the temporary row holder, used to hold generated keys, overflows and a temporary backing conglomerate is attempted created with a zero-length row template. The bug can easily be reproduced using JDBC. Unless someone beats me to it, I'll log a JIRA and attach a regression test for the bug. Excellent, many thanks. I tried a JDBC version of my IJ script but it still didn't reproduce it, so if you've managed to do it that's great. Logged as DERBY-5823 [1]. The bug is data dependent in that it requires a certain number of rows to be inserted, so that may be what's causing your repro to fail triggering the bug. -- Kristian [1] https://issues.apache.org/jira/browse/DERBY-5823
Re: Conglomerate could not be created?
On 05/06/2012 18:45, Dag H. Wanvik wrote: Looks like a bug to me. The error XSCH4 Conglomerate could not be created isn't being tested for in the Derby tests as far as I can see. I also searched the bug tracker (https://issues.apache.org/jira/browse/DERBY) in vain for this particular error. Looking in the code where the error is reported in the source code, it looks like it shouldn't happen. Could you post a JIRA bug report for this error, please? It you are able to make a stand-alone repro that will increase your chances of having it fixed. After trying to boil this down and after much head-scratching, I've realised that I do the insert using a method in a DB utility class. There are two methods: dbInsert() and dbUpdate(). The difference is that doInsert() uses Statement.RETURN_GENERATED_KEYS when creating the prepared statement, and then calls stat.getGeneratedKeys() after the insertion. My problem seems to arise because I used doInsert to execute the insert even though there isn't a generated key. If I use dbUpdate() instead, the problem goes away. My attempt at a standalone repro tries to mimic this behaviour like so: AUTOCOMMIT OFF; INSERT INTO resources ...; INSERT INTO resource_usage ...; PREPARE p AS ...; EXECUTE p USING ...; VALUES IDENTITY_VAL_LOCAL(); COMMIT; but this works. So I suspect that IDENTITY_VAL_LOCAL is implemented differently, and the problem is due to Statement.RETURN_GENERATED_KEYS being used with a statement that doesn't generate a key. The stack trace I posted earlier began: java.sql.SQLException: Conglomerate could not be created. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source) at utils.DataManager.dbInsert(DataManager.java:1056) dbInsert lines 1056/1057 look like this: stat.executeUpdate(); ResultSet r = stat.getGeneratedKeys(); so the failure happens when the prepared statement is executed, just before the call to getGeneratedKeys(). Does this suggest a cause for the Conglomerate could not be created error to anyone? TIA, -- John English
Re: Conglomerate could not be created?
John English john.fore...@gmail.com writes: dbInsert lines 1056/1057 look like this: stat.executeUpdate(); ResultSet r = stat.getGeneratedKeys(); so the failure happens when the prepared statement is executed, just before the call to getGeneratedKeys(). I tried this sample, but saw no problem: Statement s = c.createStatement(); s.executeUpdate(create table t(c varchar(3))); PreparedStatement ps = c.prepareStatement(insert into t values ?, Statement.RETURN_GENERATED_KEYS); ps.setString(1, foo); ps.executeUpdate(); ResultSet rs = ps.getGeneratedKeys(); rs.next(); System.out.println(rs.getString(1)); // prints null but then again, you see the error already on the execute.. Maybe you can tweak you case into a small repro like this. Sorry I can't be of more help. Good luck!
Re: Conglomerate could not be created?
On 05/06/2012 18:45, Dag H. Wanvik wrote: Looks like a bug to me. The error XSCH4 Conglomerate could not be created isn't being tested for in the Derby tests as far as I can see. I also searched the bug tracker (https://issues.apache.org/jira/browse/DERBY) in vain for this particular error. Looking in the code where the error is reported in the source code, it looks like it shouldn't happen. Could you post a JIRA bug report for this error, please? It you are able to make a stand-alone repro that will increase your chances of having it fixed. OK, many thanks, will do. -- John English
Re: Conglomerate could not be created?
On 04/06/2012 19:57, Katherine Marsden wrote: On 6/4/2012 9:29 AM, John English wrote: I'm on 10.8, and I only have about a dozen matching rows at the moment. No stack trace that goes further than my own code; all I have is stuff like this: Check the derby.log for the error and full stack trace. If the derby.log doesn't show the error, try setting derby.infolog.append=true in the derby.properties or system property temporarily, so you don't lose the log when the database reboots. The problem was that the original exception info was being masked by my rethrowing it with the parameter information. Full version info from the log: Apache Derby - 10.8.1.2 - (1095077) Here's an example of a failed query and parameters: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND resid NOT IN (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)) Query parameters: 1='24' 2='final' 3='18' 4='temp' 5='24' 6='final'): Here's the resulting stack trace from the point just before I rethrow: java.sql.SQLException: Conglomerate could not be created. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source) at utils.DataManager.dbInsert(DataManager.java:1056) at actions.EditAction.copyResources(EditAction.java:1638) at actions.EditAction.doRelease(EditAction.java:1171) at actions.EditAction.processUpdates(EditAction.java:537) at servlets.BaseServlet.doGet(BaseServlet.java:190) at servlets.BaseServlet.doPost(BaseServlet.java:304) at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) [... snip ...] at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: Conglomerate could not be created. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 45 more Caused by: ERROR XSCH4: Conglomerate could not be created. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.store.access.heap.Heap.create(Unknown Source) at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(Unknown Source) at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(Unknown Source) at org.apache.derby.impl.sql.execute.TemporaryRowHolderImpl.insert(Unknown Source) at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source) at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source) ... 39 more -- John English
Re: Conglomerate could not be created?
Looks like a bug to me. The error XSCH4 Conglomerate could not be created isn't being tested for in the Derby tests as far as I can see. I also searched the bug tracker (https://issues.apache.org/jira/browse/DERBY) in vain for this particular error. Looking in the code where the error is reported in the source code, it looks like it shouldn't happen. Could you post a JIRA bug report for this error, please? It you are able to make a stand-alone repro that will increase your chances of having it fixed. Thanks, Dag
Re: Conglomerate could not be created?
On 6/2/12 3:18 AM, John English wrote: On 01/06/2012 15:19, John English wrote: I'm having trouble with the following error: Conglomerate could not be created. It happens when I do this: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); I also get the same error from this, which looks fairly innocuous to me: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,20,'final' FROM resource_usage WHERE itemid=? AND itemtype=?); Is it perhaps because I'm inserting into the same table I'm selecting from? Hi John, Do you have a stack trace for this error? Thanks -Rick -- John English If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one. — John Barrow, /Pi in the Sky /
Re: Conglomerate could not be created?
On 04.06.12 14:52, Rick Hillegas wrote: On 6/2/12 3:18 AM, John English wrote: On 01/06/2012 15:19, John English wrote: I'm having trouble with the following error: Conglomerate could not be created. It happens when I do this: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); I also get the same error from this, which looks fairly innocuous to me: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,20,'final' FROM resource_usage WHERE itemid=? AND itemtype=?); Is it perhaps because I'm inserting into the same table I'm selecting from? Hi John, Do you have a stack trace for this error? My extremely simple attempt at reproducing this didn't succeed on trunk (10.10), and I could duplicate the matching rows (only changing the itemtype column). Do you also have the schema and example of data that triggers the problem? Are there many matching rows for the select? Thanks, -- Kristian Thanks -Rick -- John English If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one. — John Barrow, /Pi in the Sky /
Re: Conglomerate could not be created?
On 04/06/2012 16:35, Kristian Waagan wrote: On 04.06.12 14:52, Rick Hillegas wrote: On 6/2/12 3:18 AM, John English wrote: On 01/06/2012 15:19, John English wrote: I'm having trouble with the following error: Conglomerate could not be created. It happens when I do this: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); I also get the same error from this, which looks fairly innocuous to me: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,20,'final' FROM resource_usage WHERE itemid=? AND itemtype=?); Is it perhaps because I'm inserting into the same table I'm selecting from? Hi John, Do you have a stack trace for this error? My extremely simple attempt at reproducing this didn't succeed on trunk (10.10), and I could duplicate the matching rows (only changing the itemtype column). The itemid also changes; only resid stays the same. Do you also have the schema and example of data that triggers the problem? Are there many matching rows for the select? I'm on 10.8, and I only have about a dozen matching rows at the moment. No stack trace that goes further than my own code; all I have is stuff like this: javax.servlet.ServletException: Conglomerate could not be created. Query parameters: 1='2' 2='final' 3='9' 4='temp' 5='2' 6='tests' at servlets.BaseServlet.doGet(BaseServlet.java:282) at servlets.BaseServlet.doPost(BaseServlet.java:304) at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) ... (and so on) Caused by: java.sql.SQLException: Conglomerate could not be created. Query parameters: 1='2' 2='final' 3='9' 4='temp' 5='2' 6='tests' at utils.DataManager.dbInsert(DataManager.java:1067) at actions.EditAction.copyResources(EditAction.java:1617) at actions.EditAction.doRelease(EditAction.java:1171) at actions.EditAction.processUpdates(EditAction.java:537) at servlets.BaseServlet.doGet(BaseServlet.java:190) ... 31 more which is from my own exception handler that catches the exception, appends the query parameters to the message and then rethrows it. The table is defined like so: CREATE TABLE resource_usage ( resid INTEGER NOT NULL, itemid INTEGER NOT NULL, itemtypeVARCHAR(16) NOT NULL, CONSTRAINT resusage_1 FOREIGN KEY (resid) REFERENCES resources(id) ON DELETE CASCADE, CONSTRAINT resusage_2 UNIQUE(resid,itemid,itemtype) ); There is more detail (including example data) in my original post. Meanwhile, in desperation, I have broken it down into code to select the resource IDs followed by a loop to insert them one by one, which works but leaves me feeling deeply offended! I'll try and boil it down to a simple example as soon as I have time and post more soon. Thanks for your help meanwhile! -- John English If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one. — John Barrow, /Pi in the Sky /
Re: Conglomerate could not be created?
On 6/4/2012 9:29 AM, John English wrote: I'm on 10.8, and I only have about a dozen matching rows at the moment. No stack trace that goes further than my own code; all I have is stuff like this: Check the derby.log for the error and full stack trace. If the derby.log doesn't show the error, try setting derby.infolog.append=true in the derby.properties or system property temporarily, so you don't lose the log when the database reboots.
Re: Conglomerate could not be created?
On 01/06/2012 16:36, Knut Anders Hatlen wrote: John Englishcheckpoint...@gmail.com writes: I'm having trouble with the following error: Conglomerate could not be created. It happens when I do this: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); I think Derby doesn't allow untyped parameters in the select list. For example, this fails: ij prepare ps as 'select ? from sysibm.sysdummy1'; ERROR 42X34: There is a ? parameter in the select list. This is not allowed. Whereas this works: ij prepare ps as 'select cast(? as int) from sysibm.sysdummy1'; ij execute ps using 'values 1'; 1 --- 1 Thanks for the suggestion, but unfortunately that isn't the solution. I tried what you said and got the same error. I also turned on statement logging and both versions (with and without cast) compile, but they fail when executed: Begin compiling prepared statement: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM ... End compiling prepared statement: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM ... Executing prepared statement: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM ... I also tried using a MessageFormat to sub the values into the query to eliminate the first two parameters, and again got the same thing. So I'm still thrashing around in search of a solution... -- John English If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one. --- John Barrow, /Pi in the Sky /
Re: Conglomerate could not be created?
On 01/06/2012 15:19, John English wrote: I'm having trouble with the following error: Conglomerate could not be created. It happens when I do this: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); I also get the same error from this, which looks fairly innocuous to me: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,20,'final' FROM resource_usage WHERE itemid=? AND itemtype=?); Is it perhaps because I'm inserting into the same table I'm selecting from? -- John English If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one. --- John Barrow, /Pi in the Sky /
Re: Conglomerate could not be created?
On Fri, 2012-06-01 at 15:19 +0300, John English wrote: I'm having trouble with the following error: Conglomerate could not be created. It happens when I do this: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); The first two and last two parameters are the same; the idea is that an item uses a set of resources, and I want to copy the item from one table where it's being edited to another where it is publicly visible. To do this I need to duplicate the set of resource IDs so that they appear for the item being edited (which has an ID and an item type of 'temp') and the released version (another ID and type 'final'), but without creating duplicate rows if the same resources were already listed prior to the editing process. For example: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,55,'final' FROM resource_usage WHERE itemid=11 AND itemtype='temp' AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=55 AND itemtype='final')); The resource_usage table is defined like this: CREATE TABLE resource_usage ( resid INTEGER NOT NULL, itemid INTEGER NOT NULL, itemtypeVARCHAR(16) NOT NULL, CONSTRAINT resusage_1 FOREIGN KEY (resid) REFERENCES resources(id) ON DELETE CASCADE, CONSTRAINT resusage_2 UNIQUE(resid,itemid,itemtype) ); So to create a new item I insert a copy of the item from the editing table into the final destinationtable, then try to do the above insert on resource_usage for the newly created item, and get Conglomerate could not be created. If I stop after copying the item to the public table but before the insert above and then try each of the selects in turn manually, I get this: SELECT resid FROM resource_usage WHERE itemid=55 AND itemtype='final'; -- 0 rows selected SELECT resid,55,'final' FROM resource_usage WHERE itemid=11 AND itemtype='temp' AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=55 AND itemtype='final'); -- 4 rows selected: RESID | ITEMID | ITEMTYPE ++--- 29 | 11 | temp 30 | 11 | temp 31 | 11 | temp 32 | 11 | temp SELECT resid,55,'final' FROM resource_usage WHERE itemid=11 AND itemtype='temp' AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=55 AND itemtype='final'); -- 4 rows selected: RESID | 2 | 3 +---+--- 29 | 55| final 30 | 55| final 31 | 55| final 32 | 55| final On the other hand I can do the insert manually: INSERT INTO resource_usage (resid,itemid,itemtype) VALUES (29,55,'final'),(30,55,'final'),(31,55,'final'),(32,55,'final'); -- 4 rows inserted Can anybody throw any light on what's going on here please? Don't have an exact answer but perhaps this will help. According to the description of the SYSCONGLOMERATES table, A conglomerate is a unit of storage and is either a table or an index. There may be additional exceptions in the chain that might reveal more contextual information. Try iterating over displaying the SQLException.getNextException() chain as well as each exception's getCause() chain. signature.asc Description: This is a digitally signed message part
Re: Conglomerate could not be created?
John English checkpoint...@gmail.com writes: I'm having trouble with the following error: Conglomerate could not be created. It happens when I do this: INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid,?,? FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); I think Derby doesn't allow untyped parameters in the select list. For example, this fails: ij prepare ps as 'select ? from sysibm.sysdummy1'; ERROR 42X34: There is a ? parameter in the select list. This is not allowed. Whereas this works: ij prepare ps as 'select cast(? as int) from sysibm.sysdummy1'; ij execute ps using 'values 1'; 1 --- 1 1 row selected Does the insert statement work if you rewrite it to INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid, CAST(? AS INT), CAST(? AS VARCHAR(32672)) FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); ? Of course, it's a bug that the compiler doesn't catch this and report a syntax error. It would be worth filing a bug report to get this improved. -- Knut Anders
Re: Conglomerate could not be created?
On 01/06/2012 16:36, Knut Anders Hatlen wrote: Does the insert statement work if you rewrite it to INSERT INTO resource_usage (resid,itemid,itemtype) (SELECT resid, CAST(? AS INT), CAST(? AS VARCHAR(32672)) FROM resource_usage WHERE itemid=? AND itemtype=? AND NOT EXISTS (SELECT resid FROM resource_usage WHERE itemid=? AND itemtype=?)); ? Alas, no. Same error. Of course, it's a bug that the compiler doesn't catch this and report a syntax error. It would be worth filing a bug report to get this improved. As soon as I fix this I will look into this further! -- John English If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one. --- John Barrow, /Pi in the Sky /
