Re: Aggregate function: Different results with jdbc and psql

Hello,

On Tuesday 07 October 2008 14:01, you wrote:
> Tilman Rassy <rassy 'at' math.TU-Berlin.DE> writes:
> > I have the following problem: A certain query, i.e.,
> >
> > SELECT * FROM user_worksheet_grades WHERE user_id = 23 AND worksheet_id =
> > 105;
> >
> > gives
> >
> > worksheet_id | user_id | num_edited | num_corrected | grade
> > --------------+---------+------------+---------------+-------
> > 105 | 23 | 1 | 1 | 4
> > (1 row)
> >
> > when I issue it in psql. When I use the same query from JDBC in Java, the
> > column "grade" is SQL NULL.
>
> Can you show the Java code (to the list)?

Yes, here it is:

public ResultSet queryUserWorksheetGrade (int userId, int worksheetId)
throws SQLException
{
final String METHOD_NAME = "queryUserWorksheetGrade";
this.logDebug
(METHOD_NAME + " 1/3: " + "Started" + ". " +
" userId = " + userId +
", worksheetId = " + worksheetId);
this.sqlComposer
.clear()
.addSELECT()
.addAsterisk()
.addFROM()
.addTable(DbTable.USER_WORKSHEET_GRADES)
.addWHERE()
.addColumn(DbColumn.USER_ID) .addEq() .addValue(userId)
.addAND()
.addColumn(DbColumn.WORKSHEET_ID) .addEq() .addValue(worksheetId);
String query = this.sqlComposer.getCode();
this.logDebug(METHOD_NAME + " 2/3: " + "query = " + query);
ResultSet resultSet =
this.connection.createStatement().executeQuery(query);
this.logDebug(METHOD_NAME + " 3/3: " + "Done");
return resultSet;
}

A few remarks: sqlComposer is an auxiliary object to compose SQL code. The SQL
is written to a log message before it is executed. When I copy the SQL from
the logs and paste it to psql, I get the result above. But in Java, "grade"
is NULL. I tested it with "wasNull".

I also tried variants of the above. In any case, all columns except "grade"
are correct. This is why a thought the problem is related to the "sum"
aggregate function. The use of views seems to have no influence. The problem
occurs even if no views are involved.

> Are you sure you connect to the database with the same
> user/password with psql and JDBC?

User is te same, password is not needed with psql.

Tilman