Re: Performance Tuning Problem ?
Hi all, In an attempt to investigate the problem I mentioned in my previous post, I run additional tests and some interesting and weird results were appeared. May be someone who has good knowledge of Derby's internal operation can give some explanation. Any suggestion is welcomed. I describe what I have done: Fresh Start of ij to ensure that no cache is used: java -Dderby.language.maxMemoryPerTable=128 -Dderby.storage.pageCacheSize=4000 -Xms256m -Xmx256m -jar $DERBY_HOME/lib/derbyrun.jar ij prepare ps1 as 'select count(*) from ganest1.big inner join ganest1.bigref on ganest1.big.id=ganest1.bigref.bigid and ganest1.big.id 400 and ganest1.big.id 410'; ELAPSED TIME = 334 milliseconds ij execute ps1; 1 --- 10128 1 row selected ELAPSED TIME = 440 milliseconds From the query plan we can see that a nested loop join strategy is used and it opens 10128 index scans on the inner table. The situation looks similar to the one I described in my previous post, but the execution time is quite satisfactory! (milliseconds vs 20 seconds see below) I attach the query plan at the end of the post. (in my previous post I attached the DDL for the whole test) After that I run the query (after restarting ij in an attempt to prevent cache usage...): prepare ps3 as 'select count(*) from ganest1.big where ganest1.big.name like ''abc%'' '; ij ELAPSED TIME = 208 milliseconds ij execute ps3; 1 --- 2540 1 row selected ELAPSED TIME = 148 milliseconds Again quite satisfactory time !! And the problematic query: prepare ps2 as 'select count(*) from ganest1.big inner join ganest1.bigref on ganest1.big.id=ganest1.bigref.bigid and ganest1.big.name like ''abc%'' '; ELAPSED TIME = 51 milliseconds ij execute ps2; 1 --- 258 1 row selected ELAPSED TIME = 18072 milliseconds It takes 18 seconds !! ( I attach again the query plan) === Query Plans == Statement Name: null Statement Text: select count(*) from ganest1.big inner join ganest1.bigref on ganest1.big.id=ganest1.bigref.bigid and ganest1.big.id 400 and ganest1.big.id 410 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (6): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count:1.00 optimizer estimated cost:49737.71 Source result set: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 10128 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count:10126.00 optimizer estimated cost:49737.71 Index Key Optimization = false Source result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 10128 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count:10126.00 optimizer estimated cost:49737.71 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 10128 Rows seen from the right = 10128 Rows filtered = 0 Rows returned = 10128 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count:10126.00 optimizer estimated cost:49737.71 Left result set: Index Scan ResultSet for BIGREF using constraint SQL090522104305131 at read
Re: Performance Tuning Problem ?
ganest gan...@ced.tuc.gr writes: Hi all, In an attempt to investigate the problem I mentioned in my previous post, I run additional tests and some interesting and weird results were appeared. May be someone who has good knowledge of Derby's internal operation can give some explanation. Any suggestion is welcomed. I describe what I have done: Fresh Start of ij to ensure that no cache is used: java -Dderby.language.maxMemoryPerTable=128 -Dderby.storage.pageCacheSize=4000 -Xms256m -Xmx256m -jar $DERBY_HOME/lib/derbyrun.jar ij prepare ps1 as 'select count(*) from ganest1.big inner join ganest1.bigref on ganest1.big.id=ganest1.bigref.bigid and ganest1.big.id 400 and ganest1.big.id 410'; ELAPSED TIME = 334 milliseconds ij execute ps1; 1 --- 10128 1 row selected ELAPSED TIME = 440 milliseconds From the query plan we can see that a nested loop join strategy is used and it opens 10128 index scans on the inner table. The situation looks similar to the one I described in my previous post, but the execution time is quite satisfactory! (milliseconds vs 20 seconds see below) The accessed data has higher locality in this query than in the previous query, and therefore the page cache is used more efficiently. The previous query used the indexes on big.name and bigref.bigid, which are ordered differently. So even if the index on big.name was scanned sequentially, the index on bigref.bigid was accessed almost completely randomly, and a large number of pages all over the table had to be visited. In the latest query, the indexes on big.id and bigref.bigid are used. Those indexes are ordered the same way, and all the interesting rows are located consecutively on a relatively small number of pages. So even if the number of page accesses is higher, the number of different pages accessed is probably much lower. And since the rows in both indexes are accessed in the same order that they are stored, the requested page is found in the page cache most of the time. Another difference is that the previous query had to access the base table (big) to get the id column, whereas the latest query finds all the columns it needs in the indexes. You may want to try to add an extra index to speed up the previous query: CREATE UNIQUE INDEX BIGNAMEID ON BIG(NAME, ID) -- Knut Anders
Re: Performance Tuning Problem ?
Dean Knut, Thanks for answer, The accessed data has higher locality in this query than in the previous query, and therefore the page cache is used more efficiently. The previous query used the indexes on big.name and bigref.bigid, which are ordered differently. So even if the index on big.name was scanned sequentially, the index on bigref.bigid was accessed almost completely randomly, and a large number of pages all over the table had to be visited. In the latest query, the indexes on big.id and bigref.bigid are used. Those indexes are ordered the same way, and all the interesting rows are located consecutively on a relatively small number of pages. So even if the number of page accesses is higher, the number of different pages accessed is probably much lower. And since the rows in both indexes are accessed in the same order that they are stored, the requested page is found in the page cache most of the time. Another difference is that the previous query had to access the base table (big) to get the id column, whereas the latest query finds all the columns it needs in the indexes. You may want to try to add an extra index to speed up the previous query: CREATE UNIQUE INDEX BIGNAMEID ON BIG(NAME, ID) If I understand well, the creation of the new index (BIGNAMEID) will solve the second problem you mentioned, the id column of table big will be taken from the index BIGNAMEID and not from the base table, but the first issue the index on bigref.bigid was accessed almost completely randomly, and a large number of pages all over the table had to be visited. will remain? Is than correct ? For the record, the creation of the index you proposed speeds up the query in a spectacular way !! Is it correct to assume that the main source of the problem was the access of the base table (big) to get the id column and not the almost random access of the index on bigref.bigid ? Anyway, I would not like to become (more :-)) annoying, thanks a lot for your answers. Best regards, George
Re: Performance Tuning Problem ?
George Anestis gan...@ced.tuc.gr writes: Another difference is that the previous query had to access the base table (big) to get the id column, whereas the latest query finds all the columns it needs in the indexes. You may want to try to add an extra index to speed up the previous query: CREATE UNIQUE INDEX BIGNAMEID ON BIG(NAME, ID) If I understand well, the creation of the new index (BIGNAMEID) will solve the second problem you mentioned, the id column of table big will be taken from the index BIGNAMEID and not from the base table, but the first issue the index on bigref.bigid was accessed almost completely randomly, and a large number of pages all over the table had to be visited. will remain? Is than correct ? Correct. For the record, the creation of the index you proposed speeds up the query in a spectacular way !! That's great! :) Is it correct to assume that the main source of the problem was the access of the base table (big) to get the id column and not the almost random access of the index on bigref.bigid ? Yes, that sounds reasonable. The accesses to the base table are random too, since the base table and the BIGNAME index are ordered differently. The index on BIGREF.BIGID is much more compact than the BIG table, so the random reads there probably use the cache more efficiently and don't hurt the performance that much. -- Knut Anders
Re: Bug in Derby 10.5.1.1 or misuse ?
The NullPointer exception is thrown by the statement: ps = dbConnection.prepareStatement(sqlStr); ,with ps a PreparedStatement ans sqlStr a string equal to SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? I use similar code to prepare other statements and they work well. So I guess that if there is an error in my code, it lies in the SQL select query. Bryan Pendelton suggested filling a bug query. If I have no clue what happens this evening, I' ll do it. Peter Ondruška wrote: Can we see how you set parameters for this prepared statement? 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: Hi, I have a java.lang.NullPointerException when preparing the following statement: SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? with the command: dbConnection.prepareStatement(sqlStr); The tables involved are: CREATE TABLE types ( + id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name VARCHAR(128) NOT NULL UNIQUE, + shortName VARCHAR(64) NOT NULL + ) and CREATE TABLE methodParameters ( + method INT REFERENCES methods (id), + position INT, + parameter INT REFERENCES types (id) + ) SQL information for the exception are: SQL state: XJ001 Error code: 0 And the stack trace is: Message: Exception Java : ': java.lang.NullPointerException'. java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.javaException(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.EmbedPreparedStatement.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.thinkcollabs.jmbrowser.db.DAO.initSelect(DAO.java:370) at org.thinkcollabs.jmbrowser.db.DB_Derby$MethodDataLoader.run(DB_Derby.java:254) Caused by: java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 16 more Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source) at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ... 9 more I can't see what I am doing wrong, but I am not an SQL expert. So, I am doing something wrong or is there a problem with Derby 10.5.1.1? Thank you, Gurvan
Re: Bug in Derby 10.5.1.1 or misuse ?
Would you please try if there is any difference if you remove name IN (?). 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: The NullPointer exception is thrown by the statement: ps = dbConnection.prepareStatement(sqlStr); ,with ps a PreparedStatement ans sqlStr a string equal to SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? I use similar code to prepare other statements and they work well. So I guess that if there is an error in my code, it lies in the SQL select query. Bryan Pendelton suggested filling a bug query. If I have no clue what happens this evening, I' ll do it. Peter Ondruška wrote: Can we see how you set parameters for this prepared statement? 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: Hi, I have a java.lang.NullPointerException when preparing the following statement: SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? with the command: dbConnection.prepareStatement(sqlStr); The tables involved are: CREATE TABLE types ( + id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name VARCHAR(128) NOT NULL UNIQUE, + shortName VARCHAR(64) NOT NULL + ) and CREATE TABLE methodParameters ( + method INT REFERENCES methods (id), + position INT, + parameter INT REFERENCES types (id) + ) SQL information for the exception are: SQL state: XJ001 Error code: 0 And the stack trace is: Message: Exception Java : ': java.lang.NullPointerException'. java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.javaException(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.EmbedPreparedStatement.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.thinkcollabs.jmbrowser.db.DAO.initSelect(DAO.java:370) at org.thinkcollabs.jmbrowser.db.DB_Derby$MethodDataLoader.run(DB_Derby.java:254) Caused by: java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 16 more Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source) at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ... 9 more I can't see what I am doing wrong, but I am not an SQL expert. So, I am doing something wrong or is there a problem with
Re: Bug in Derby 10.5.1.1 or misuse ?
It really seems like a bug. I realized I have a similar query that I prepare. I modified my code so that the second query uses the query generating an exception as a substring. The two queries are: private static final String selectStr_simpleQueries_methodIdsHavingParameters = SELECT method + FROM methodParameters AS mp + INNER JOIN types ON mp.parameter = types.id + WHERE types.name IN (%s) + GROUP BY method + HAVING COUNT(method) = ?; private static final String selectStr_complexQueries_methodsFromSignature = SELECT m.id AS methodId, isConstructor, isStatic, rT.name AS result, cT.name AS class, m.name AS method, mp.position AS paramPos, pT.name AS parameter + FROM methods AS m + INNER JOIN types AS cT ON m.class = cT.id + INNER JOIN types AS rT ON m.returnType = rT.id + LEFT OUTER JOIN methodParameters AS mp ON m.id = mp.method + LEFT OUTER JOIN types AS pT ON mp.parameter = pT.id + WHERE m.id IN ( + ( + SELECT m.id + FROM methods AS m + INNER JOIN types ON m.returnType = types.id + WHERE types.name = ?+ ) + INTERSECT + ( + selectStr_simpleQueries_methodIdsHavingParameters + ) + ); selectStr_simpleQueries_methodIdsHavingParameters is a substring of selectStr_complexQueries_methodsFromSignature. They contain '%s' because I reuse them multiple time, replacing the '%s ' by multiple '?' For the first iteration, here are the strings I obtain: SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id WHERE types.name IN (?) GROUP BY method HAVING COUNT(method) = ? SELECT m.id AS methodId, isConstructor, isStatic, rT.name AS result, cT.name AS class, m.name AS method, mp.position AS paramPos, pT.name AS parameter FROM methods AS m INNER JOIN types AS cT ON m.class = cT.id INNER JOIN types AS rT ON m.returnType = rT.id LEFT OUTER JOIN methodParameters AS mp ON m.id = mp.method LEFT OUTER JOIN types AS pT ON mp.parameter = pT.id WHERE m.id IN ( ( SELECT m.id FROM methods AS m INNER JOIN types ON m.returnType = types.id WHERE types.name = ? ) INTERSECT (SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id WHERE types.name IN (?) GROUP BY method HAVING COUNT(method) = ? ) ) sqlStr = new String(selectStr_simpleQueries_methodIdsHavingParameters).replaceFirst(%s, replStr); System.out.println(sqlStr); ps = dbConnection.prepareStatement(sqlStr); Throws the NullPointer exception. BUT: sqlStr = new String(selectStr_complexQueries_methodsFromSignature).replaceFirst(%s, replStr); System.out.println(sqlStr); ps = dbConnection.prepareStatement(sqlStr); Works fine. Either selectStr_simpleQueries_methodIdsHavingParameters as a substring of selectStr_complexQueries_methodsFromSignature is not really prepared because of incompatibilities in selectStr_complexQueries_methodsFromSignature or there is a bug. 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: The NullPointer exception is thrown by the statement: ps = dbConnection.prepareStatement(sqlStr); ,with ps a PreparedStatement ans sqlStr a string equal to SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? I use similar code to prepare other statements and they work well. So I guess that if there is an error in my code, it lies in the SQL select query. Bryan Pendelton suggested filling a bug query. If I have no clue what happens this evening, I' ll do it. Peter Ondruška wrote: Can we see how you set parameters for this prepared statement? 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: Hi, I have a java.lang.NullPointerException when preparing the following statement: SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? with the command: dbConnection.prepareStatement(sqlStr); The tables involved are: CREATE TABLE types ( + id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name VARCHAR(128) NOT NULL UNIQUE, + shortName VARCHAR(64) NOT NULL + ) and CREATE TABLE methodParameters ( + method INT REFERENCES methods (id), + position INT, + parameter INT REFERENCES types (id) + ) SQL information for the exception are: SQL state: XJ001 Error code: 0 And the stack trace is: Message: Exception Java : ': java.lang.NullPointerException'. java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'.
Re: Bug in Derby 10.5.1.1 or misuse ?
The problem is with the IN statement. It does not like it if there is only one possibility (i.e. IN (?)). SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id WHERE types.name IN (?, ?) GROUP BY method HAVING COUNT(method) = ? does not generate the exception BUT SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id WHERE types.name IN (?) GROUP BY method HAVING COUNT(method) = ? does generate the null pointer exception Peter Ondruška wrote: Would you please try if there is any difference if you remove name IN (?). 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: The NullPointer exception is thrown by the statement: ps = dbConnection.prepareStatement(sqlStr); ,with ps a PreparedStatement ans sqlStr a string equal to SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? I use similar code to prepare other statements and they work well. So I guess that if there is an error in my code, it lies in the SQL select query. Bryan Pendelton suggested filling a bug query. If I have no clue what happens this evening, I' ll do it. Peter Ondruška wrote: Can we see how you set parameters for this prepared statement? 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: Hi, I have a java.lang.NullPointerException when preparing the following statement: SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? with the command: dbConnection.prepareStatement(sqlStr); The tables involved are: CREATE TABLE types ( + id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name VARCHAR(128) NOT NULL UNIQUE, + shortName VARCHAR(64) NOT NULL + ) and CREATE TABLE methodParameters ( + method INT REFERENCES methods (id), + position INT, + parameter INT REFERENCES types (id) + ) SQL information for the exception are: SQL state: XJ001 Error code: 0 And the stack trace is: Message: Exception Java : ': java.lang.NullPointerException'. java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.javaException(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.EmbedPreparedStatement.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.thinkcollabs.jmbrowser.db.DAO.initSelect(DAO.java:370) at org.thinkcollabs.jmbrowser.db.DB_Derby$MethodDataLoader.run(DB_Derby.java:254) Caused by: java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 16 more Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source) at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source) at
Re: Bug in Derby 10.5.1.1 or misuse ?
Well that is because you cannot bind parameter like you do: name IN (?). Therefore you get an exception. Try name = ? and I bet it will work. 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: If I remove the WHERE part, it works fine (i.e. no exception generated when preparing the statement). SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id GROUP BY method HAVING COUNT(method) = ? Peter Ondruška wrote: Would you please try if there is any difference if you remove name IN (?). 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: The NullPointer exception is thrown by the statement: ps = dbConnection.prepareStatement(sqlStr); ,with ps a PreparedStatement ans sqlStr a string equal to SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? I use similar code to prepare other statements and they work well. So I guess that if there is an error in my code, it lies in the SQL select query. Bryan Pendelton suggested filling a bug query. If I have no clue what happens this evening, I' ll do it. Peter Ondruška wrote: Can we see how you set parameters for this prepared statement? 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: Hi, I have a java.lang.NullPointerException when preparing the following statement: SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? with the command: dbConnection.prepareStatement(sqlStr); The tables involved are: CREATE TABLE types ( + id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name VARCHAR(128) NOT NULL UNIQUE, + shortName VARCHAR(64) NOT NULL + ) and CREATE TABLE methodParameters ( + method INT REFERENCES methods (id), + position INT, + parameter INT REFERENCES types (id) + ) SQL information for the exception are: SQL state: XJ001 Error code: 0 And the stack trace is: Message: Exception Java : ': java.lang.NullPointerException'. java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.javaException(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.EmbedPreparedStatement.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.thinkcollabs.jmbrowser.db.DAO.initSelect(DAO.java:370) at org.thinkcollabs.jmbrowser.db.DB_Derby$MethodDataLoader.run(DB_Derby.java:254) Caused by: java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 16 more Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source) at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source) at
Re: Bug in Derby 10.5.1.1 or misuse ?
It does not like the = ? version of IN (?) SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id WHERE types.name = ? GROUP BY method HAVING COUNT(method) = ? generates the same null pointer exception Gurvan Le Guernic wrote: The problem is with the IN statement. It does not like it if there is only one possibility (i.e. IN (?)). SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id WHERE types.name IN (?, ?) GROUP BY method HAVING COUNT(method) = ? does not generate the exception BUT SELECT method FROM methodParameters AS mp INNER JOIN types ON mp.parameter = types.id WHERE types.name IN (?) GROUP BY method HAVING COUNT(method) = ? does generate the null pointer exception Peter Ondruška wrote: Would you please try if there is any difference if you remove name IN (?). 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: The NullPointer exception is thrown by the statement: ps = dbConnection.prepareStatement(sqlStr); ,with ps a PreparedStatement ans sqlStr a string equal to SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? I use similar code to prepare other statements and they work well. So I guess that if there is an error in my code, it lies in the SQL select query. Bryan Pendelton suggested filling a bug query. If I have no clue what happens this evening, I' ll do it. Peter Ondruška wrote: Can we see how you set parameters for this prepared statement? 2009/5/25, Gurvan Le Guernic glegu...@gmail.com: Hi, I have a java.lang.NullPointerException when preparing the following statement: SELECT method, COUNT(method) FROM methodParameters INNER JOIN types ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method) = ? with the command: dbConnection.prepareStatement(sqlStr); The tables involved are: CREATE TABLE types ( + id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name VARCHAR(128) NOT NULL UNIQUE, + shortName VARCHAR(64) NOT NULL + ) and CREATE TABLE methodParameters ( + method INT REFERENCES methods (id), + position INT, + parameter INT REFERENCES types (id) + ) SQL information for the exception are: SQL state: XJ001 Error code: 0 And the stack trace is: Message: Exception Java : ': java.lang.NullPointerException'. java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.javaException(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.EmbedPreparedStatement.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.thinkcollabs.jmbrowser.db.DAO.initSelect(DAO.java:370) at org.thinkcollabs.jmbrowser.db.DB_Derby$MethodDataLoader.run(DB_Derby.java:254) Caused by: java.sql.SQLException: Exception Java : ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 16 more Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source) at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source) at
Re: Bug in Derby 10.5.1.1 or misuse ?
Bryan Pendleton bpendle...@amberpoint.com writes: I think this issue is quite possibly related to the work we did for DERBY-2526, so perhaps you could mention that as a possibility in the problem report. It looks like it started failing at revision 754579, with the fix for DERBY-4071. That fix has also been backported to the 10.4 branch. -- Knut Anders
