Bug in SQL generation of CMP
Folks,
I dont know if this is a known issue, but I came across what seems like a fundamental bug in SQL generation logic of CMP module in JBoss.
I have a CMP bean called Timesheet representing a TIMESHEET table in Sql Server 2000 database. The table has about 4000 rows of data, 8 columns 6 of which are part of the compound primary key.
My bean has EmpName, DivName, ProjName, TaskName, TaskDesc, WorkDate, WorkHours, Billable as fields with first 6 of them being part of a primary key class.
My bean has a finder method called findByEmpName(String empname);
This finder method has an EJBQL like SELECT OBJECT(a) from Timesheet a where a.empName = ?1.
When I call this finder method I get an 'Unspecified error' from the Sql Server.
After turning on the CMP debugging, I came to know that JBoss is generating a huge SQL to be executed by SQL Server. Since SQL Server does not know how to execute this SQL, it was failing.
Upon further debugging, here is what I found.
Using the specified EJBQL, JBoss is first finding the primary key fields which in my case are 6 fields. This select correctly executed and returned about 500 or so rows.
Now using this data for primary key fields, it generated another SQL which tries to retrieve all the fields of my bean (6 PK fields and 2 non-PK fields). For this SQL it uses a where condition based on the 500 rows of the data retrieved by the earlier SQL. This where condition looks something like this:
where (div_name = ? AND emp_name = ? AND .....) OR
(div_name = ? AND emp_name = ? AND .....) OR
.......
This OR continues for all the rows retrieved by the previous select. This now becomes a huge select. SQL Server has no clue as to how to execute it.
This logic seems fundementally wrong to me.
First of all, we end up executing 2 SQL statements instead of 1 that could have sufficed.
Second, this second SQL is not practical to generate. What if the first select returns a million rows. We are not going to be able to generate a SQL that includes million rows of data in the where condition.
Does anyone else shed some light on this? Will someone be fixing this, or should I be looking to fix this myself?
Thanks,
Krishna.
