db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gurvan Le Guernic <glegu...@gmail.com>
Subject Re: Bug in Derby 10.5.1.1 or misuse ?
Date Mon, 25 May 2009 16:17:44 GMT
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 <gleguern@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 <gleguern@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
>>>>
>>>>
>>>>         
>>     


Mime
View raw message