*Problem*
When using Jaspersoft 5.5 and 6.3 with MySql server 5.7, the data retrieved from the following query is different when running it from Jaspersoft MySQL.
*Steps to Reproduce*
1. Create the following tables on a schema of your choise:
drop table if exists table1;
drop table if exists table2;
create table table1 (
pkey varchar(256),
customvalue varchar(256)
);
create table table2 (
pkey varchar(256),
value varchar(256)
);
insert into table1 values('abc','2');
insert into table2 values('abc','example');
insert into table2 values('foo','bar');
2. Run the following select on your MySql server (Server version must be 5.7 in order to reproduce the error):
select
table3.val, customvalue, value
from
table1
inner join
table2 ON table2.pkey = table1.pkey
right join
(select
pkey, value as val
from
table2
where
table2.pkey = 'foo') table3 ON table3.pkey = 'foo'
where
table1.pkey = 'abc'
group by table1.pkey;
3. Create a jasper report using the same exact above query and output the results.
*Expected Results*
MySQL Result set:
val = bar
customvalue = 2
value = example
Jasper Report Result Set:
var = bar
customvalue = 2
value = example
*Acctual Results*
MySQL Result set:
val = bar
customvalue = 2
value = example
Jasper Report Result Set:
var = bar
customvalue = 2
value = bar
This problem only occurs with mysql server 5.7.
On mysql server 5.6 both the jasper report AND mysql query return the same exact results.
Recommended Comments