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