Negative numeric values returned from query

[schultcd]

I used iReport to build up a report from scratch, slowly adding more complexity to my SQL query to gather data. At some point, two of my (numeric) columns started giving me all negative numbers (the values were correct, just the sign was off).

 

The only thing I could think of was to wrap each $F{num} in Math.abs() to get the right value.

 

I figured I must have hosed something in the field, so I re-created it several times, but it looks like the value coming from the database is actually negative.

 

When copy-and-pasting the query into the MySQL sql interpreter (minus a WHERE clause that uses jasper reports parameters), the query runs as expected.

 

While gathering information for this post, I found that the problem was in the query where I had comments like this:

 

SELECT ...,

--

-- Here is a comment.

--

(SELECT ...),

FROM

...

 

It appears that the two columns going negative had comments such as these just before them in the query. I'm trying to create a report that showcases the problem, but I have been so far unable to replicate it outside of the report in question (which I can't publish).

 

This may be a problem with the MySQL driver itself, so I'm reticent to attempt to log a bug for this. On the other hand, iReport (and JasperReports itself) makes it easy to enter multi-line SQL queries, so perhaps a warning could be issued, or comments could be removed entirely before the query is issued. It might save someone a lot of head scratching.

 

Thanks!

[lucianc]

JasperReports' processing of the query string only consists of replacing parameter placeholders by prepared statement parameters. Therefore I suspect the problem lies somewhere in the JDBC driver. You could run your query via a JDBC prepared statement and see whether the issue is replicated.

 

If you think detecting comments in the SQL would be usefull, you can log a feature request for this. However, SQL parsing is not something we are keen on doing in JR (detecting single line comments is not trivial as the double dash could also appear in String literals and so on).

 

Regards,

Lucian

[schultcd]

I was finally able to reproduce the error. Apparently, I had been using a query like this:

 

SELECT

---

--- This is a comment.

---

1 AS num

 

This query executes correctly in the MySQL command-line utility (with a result of "1"), but going through the JDBC driver (as we both had suspected) results in the "-1" result.

 

I have logged a bug with MySQL regarding this discrepancy (http://bugs.mysql.com/bug.php?id=30215). That bug has links to MySQL documentation describing why this issue might exist.

 

Thanks for the comments, Lucian.