Thread: Aggregate function: Different results with jdbc and psql
Hello,
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.
The table "user_worksheet_grades" is actually a view which gets its data from
two other views, one of which is defined as follows:
SELECT
wks.id AS worksheet_id,
ann.the_user AS user_id,
count(ann.ann_type) AS num_corrected,
sum(ref_wks_prb.points * ann.score) AS grade
FROM
latest_worksheets wks, refs_worksheet_generic_problem ref_wks_prb,
anns_user_worksheet_generic_problem ann
WHERE ann.ref = ref_wks_prb.id
AND ref_wks_prb.from_doc = wks.id AND ann.ann_type = 2
GROUP BY wks.id, ann.the_user;
Thus, "grade" is defined as a sum of products.
I use PostgreSQL 8.2.3 on Linux.
Any help will be appreciated!
Tilman
On Tuesday 07 October 2008 14:21, you wrote: > Tilman Rassy <rassy@math.TU-Berlin.DE> writes: > > I have the following problem: A certain query, i.e., > > ... > > when I issue it in psql. When I use the same query from JDBC in Java, the > > column "grade" is SQL NULL. > > It's hard to believe it's really the "same" query in both cases. Yes, it is :-) But as I stated in the last mail, I have the query literally in the logs, and when I paste it to psql, the result is different from the result in Java ...
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
Tilman Rassy <rassy 'at' math.TU-Berlin.DE> writes:
> public ResultSet queryUserWorksheetGrade (int userId, int worksheetId)
> throws SQLException
> {
> final String METHOD_NAME = "queryUserWorksheetGrade";
> this.logDebug
> (METHOD_NAME + " 1/3: " + "Started" + ". " +
that's not your question, but you should really use a proper
logger (like, log4j, for example).
--
Guillaume Cottenceau
On Tuesday 07 October 2008 14:42, Guillaume Cottenceau wrote: > > that's not your question, but you should really use a proper > logger (like, log4j, for example). > Hm... I use the logger that comes with Cocoon 2.1.8, which is logkit. So far, I'm quite satisfied. Anyway, in the newest version of Cocoon they use log4j, so I will switch to log4j when I upgrade.
Hello,
maybe you try to retrieve the value of grade with an inappropriate
method ?
What are the data types returned with psql ?
HTH,
Marc Mamin
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Tilman Rassy
Sent: Tuesday, October 07, 2008 1:27 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Aggregate function: Different results with jdbc and psql
Hello,
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.
The table "user_worksheet_grades" is actually a view which gets its data
from two other views, one of which is defined as follows:
SELECT
wks.id AS worksheet_id,
ann.the_user AS user_id,
count(ann.ann_type) AS num_corrected,
sum(ref_wks_prb.points * ann.score) AS grade FROM latest_worksheets
wks, refs_worksheet_generic_problem ref_wks_prb,
anns_user_worksheet_generic_problem ann WHERE ann.ref = ref_wks_prb.id
AND ref_wks_prb.from_doc = wks.id AND ann.ann_type = 2 GROUP BY wks.id,
ann.the_user;
Thus, "grade" is defined as a sum of products.
I use PostgreSQL 8.2.3 on Linux.
Any help will be appreciated!
Tilman
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On Tuesday 07 October 2008 14:48, Marc Mamin wrote:
> maybe you try to retrieve the value of grade with an inappropriate
> method ?
I use the getFoat Method. Here is the code:
// Query grade data:
float result = 0;
ResultSet gradeData = dbHelper.queryUserWorksheetGrade(userId, worksheetId);
if ( !gradeData.next() )
this.logDebug("### DEBUG 1 ### Result set empty");
else
{
result = gradeData.getFloat(DbColumn.GRADE);
if ( gradeData.wasNull() )
this.logDebug("### DEBUG 2 ### Column was SQL NULL");
}
In the logs I see: "### DEBUG 2 ### Column was SQL NULL"
In earlier tries, I experimented with different types for "result" (double,
String). I also casted on the SQL side to "double precision" before
multiplying und summing. No effect.
Can you try modify your SQL to explicitely set the returned data type: SELECT worksheet_id, user_id, num_edited, num_corrected, grade::float FROM user_worksheet_grades WHERE user_id = 23 AND worksheet_id = 105; If this doesn't work too, than you can at least exclude a casting issue ... cheers, Marc
Hello, sorry, sorry , sorry - the problem is solved, and it had nothing to do with JDBC or Postgres. Rather, its origin was a wierd Cocoon problem. At the time Java queried the database, a certain column was not filled yet. When I later sent the query via psql, it had been filled meanwhile. This confused me. Sorry again, and thanks for your help! Best regards Tilman
Tilman Rassy <rassy 'at' math.TU-Berlin.DE> writes: > Hello, > > 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)? Are you sure you connect to the database with the same user/password with psql and JDBC? -- Guillaume Cottenceau
Tilman Rassy <rassy@math.TU-Berlin.DE> writes:
> I have the following problem: A certain query, i.e.,
> ...
> when I issue it in psql. When I use the same query from JDBC in Java, the
> column "grade" is SQL NULL.
It's hard to believe it's really the "same" query in both cases. Maybe
you're using parameters in the JDBC case and they're not quite right?
Maybe the Java user has a different search_path or something?
regards, tom lane