Buggy Multi-Select Query Value for Adhoc Report

[jojopaderes]

I just upgraded to Jasper Server 3.1 and bumped into some problems using the multi-select input control with an adhoc report. Running the adhoc report will always end up with an SQL exception. The exception has something to do with the mismatched data types used in the WHERE IN sql clause. This only happens after with JS 3.1. The adhoc report with multi-select query works well in JS 2.1

 

Using the sample FoodMart sample that comes with JS 3.1, I modified the SuperMart Products adhoc topic and added a new multi-select input control that fetches the list of product_class_id values from product_class table. I also modified the query string of the topic by using a $X{IN, column_name, collection} function that will take in the multi-select query values. After completing all the necessary configurations for the adhoc topic, I launched the adhoc report page, selected some entries in the multi-select input control, added a few columns at the adhoc report canvass then clicked on "Run" button. Instead of getting the report display, I got an exception page instead. Please refer to the attachment for the stack trace.

 

One thing I noticed with the problem I encountered is that the exception only happens when I select multiple entries from the multi-select input control. Another interesting observation is the changing object type inside the input control collection when fed to the Jasper Report template. Before running the adhoc report (that's after selecting some entries from the multi-select input control), this is the log output:

 

 

WHERE 1=1
     AND product_class.product_class_id IN (1, 2, 3, 4, 5)
 

DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #1 (ProductClass[0] of type java.lang.Integer): 5
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #2 (ProductClass[1] of type java.lang.Integer): 4
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #3 (ProductClass[2] of type java.lang.Integer): 3
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #4 (ProductClass[3] of type java.lang.Integer): 2
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #5 (ProductClass[4] of type java.lang.Integer): 1
 

Running the adhoc report, this is the log output now:

 

 

WHERE 1=1
     AND product_class.product_class_id IN ('1', '2', '3', '4', '5')

 

 

DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #1 (ProductClass[0] of type java.lang.String): 5
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #2 (ProductClass[1] of type java.lang.String): 4
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #3 (ProductClass[2] of type java.lang.String): 3
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #4 (ProductClass[3] of type java.lang.String): 2
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #5 (ProductClass[4] of type java.lang.String): 1


Take note of the changes both with the generated sql where clause query and the object type of the parameters inside the collection. From Numeric/Integer type, these were changed to Strings. This will then cause the following exception in the database:

 

org.postgresql.util.PSQLException: ERROR: IN types integer and character varying cannot be matched

Post Edited by Jojo Paderes at 02/11/09 17:44

[jojopaderes]

For some reason the forum site is not allowing the attachment to be uploaded. The attachment is a zip file containing logs and xml data.

[swood]

Looks like a bug - we will look into it.

 

Sherman

Jaspersoft

[andrewsok]

 I tried to reproduce your issue and I cound not.

So let me describe what I did. I took SuperMartProductsTopic.jrxml as a base, added a new parameter:

 

<parameter name="productClassId" class="java.util.Collection" isForPrompting="true">

<defaultValueExpression><![CDATA[java.util.Arrays.asList(new java.lang.Integer[] { new java.lang.Integer( 1 ) })]]></defaultValueExpression>

</parameter>

 

and then added extra expression in WHERE part of SQL query:

 

     AND ( $X{IN, product_class.product_class_id, productClassId} )

 

The I created a new JasperReport in /adhoc/topics folder, uploaded JRXML, added bundle and two InputControls:

1) single select query, name:productFamily, sql:"select distinct product_family from product_class"

2) multi select query, name:productClassId, sql:"select product_class_id from product_class"

 

Then I was able to run it as report, use it as topic in Adhoc, and run report from Adhoc, with no problem.

 

[jojopaderes]

Thanks andrewsok for providing the sample report. I was still able to replicate the problem that I had before using the sample you gave. I'm attaching a storyboard on how I was able to replicate the problem.

 

Here's the where clause snippet that was captured when I ran the adhoc report. Looking at the IN parameters, it's obvious that the values are specified as character/text types (enclosed by the single quotes) which is clearly a mismatch with the product_class_id integer column:

 

WHERE 1=1
     AND ( product_class.product_family = 'Drink' OR 1 = 0 )
     AND ( product_class.product_class_id IN ('5', '4', '3', '2', '1') )
 

 

Post Edited by Jojo Paderes at 03/09/09 16:19

Post Edited by Jojo Paderes at 03/09/09 16:21

[andrewsok]

It might be PostgreSQL related, works fine on MySQL. I'll try it on PostgreSQL and let you know..

[jojopaderes]

Thank you andrewsok for helping out checking this issue!

 

Another thing that confuses me with this multi-select input control bug with adhoc reports is that I don't have this problem with non-adhoc reports. I think there's something else going on with the adhoc report that causes it to generate incorrect SQL IN parameter codes.

 

I came up with a workaround for the SQL IN parameter issue caused by the multi-select input control in the adhoc report. Instead of using the $X{} built-in function of JasperReports, I used a Java code snippet to generate a CSV value for the collection input values. Please see the code section for the snippet.

 

The code snippet works well for resolving the IN parameter sql code problem, both for adhoc and non-adhoc reports. The new problem I'm struggling right now is that when changing the report options for the adhoc report, the report data displayed does not get updated. This is not the case with non-adhoc reports.

Code:

...<import value="org.springframework.util.StringUtils" />...<parameter name="MultiSelectInputValues" class="java.util.Collection"/><parameter name="InputCsv" isForPrompting="false">    <defaultValueExpression><![CDATA[        $P{MultiSelectInputValues}.size() > 0 ? StringUtils.collectionToCommaDelimitedString($P{MultiSelectInputValues}) : "NULL"    ]]></defaultValueExpression></parameter>...<queryString><![CDATA[sELECT foo_columns FROM foo_tableWHERE int_column in ($P!{InputCsv})]]></queryString>

[jojopaderes]

I have managed to do a test using MySQL and there was no exception thrown. I don't see the JDBC exception complaining about the mismatch with the IN parameters and column data types, instead, JasperServer simply don't return any data results. Interestingly, I can execute the following sql code in MySQL and PostgreSQL without any problems (directly executed using a DB client app), but executing this query in JasperServer fails:

 

SELECT * FROM product WHERE product.product_class_id IN ('1','2');
 

Using andrewsok report jrxml example, launching the report designer will produce the following sql code and logs respectively:

 

WHERE 1=1
     AND ( product_class.product_family = 'Non-Consumable' OR 1 = 0 )
     AND product_class.product_class_id IN (1, 2, 3, 4, 5)
 

DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #1 (ProductClass[0] of type java.lang.Integer): 5
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #2 (ProductClass[1] of type java.lang.Integer): 4
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #3 (ProductClass[2] of type java.lang.Integer): 3
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #4 (ProductClass[3] of type java.lang.Integer): 2
DEBUG JRJdbcQueryExecuter,Thread-15:257 - Parameter #5 (ProductClass[4] of type java.lang.Integer): 1
 

So far so good. The ProductClass collection contains the correct object types which are of Integer class.

 

Running the adhoc report, this is the log output now:

 

WHERE 1=1
     AND ( product_class.product_family = 'Non-Consumable' OR 1 = 0 )
     AND product_class.product_class_id IN ('1', '2', '3', '4', '5')

 

DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #1 (ProductClass[0] of type java.lang.String): 5
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #2 (ProductClass[1] of type java.lang.String): 4
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #3 (ProductClass[2] of type java.lang.String): 3
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #4 (ProductClass[3] of type java.lang.String): 2
DEBUG JRJdbcQueryExecuter,Thread-16:257 - Parameter #5 (ProductClass[4] of type java.lang.String): 1

 

Again, take note that the collection now contains String objects instead of Integers. Using PostgreSQL database and driver an exception will be thrown from JasperServer. Upgrading the PostgreSQL driver does not help either. Back in JasperServer 2.1 the object types in the collection remain consistent, always Integer if the input parameters are of int type.

Post Edited by Jojo Paderes at 03/10/09 04:57

Post Edited by Jojo Paderes at 03/10/09 04:58

[andrewsok]

I am confirming that there is an issue there.

MySQL driver is nice about accepting String values for Integer field, but it is obviously wrong.

Bug is filed. Stay tunned..

[andrewsok]

Good news: bug is fixed and will be available in the next release (coming out very soon!).

"parameter" tag will have a new nestedType="java.lang.Integer" (or any other java type) attribute to determine the type of list elements.

[jojopaderes]

That's good news indeed! Thank you for working in this issue really fast!

 

There are also a couple of issues I found with the multi-select input control (when used in the adhoc report) even after applying the workaround I came up (see post #53103)

 

1. Changing the parameters using Report Options does not refresh the data displayed at the adhoc report preview.

 

2. Selected report parameters for multi-select input control does not get saved.

 

I hope these issues were also fixed with the upcoming release.

Post Edited by Jojo Paderes at 03/15/09 01:52

[jojopaderes]

Please ignore the issues I listed at post #53340. These behaviors were caused by activating the "Set these values as defaults when saving report." checkbox at the report options popup shown when creating a new adhoc report (see attachment).