|
[
Permalink
| « Hide
]
David Welch added a comment - 28/Jul/06 02:05 PM
Liferay Portal Professional 4.0.0 (Cowper / Build 3081 / July 7, 2006)
Liferay Portal Professional 4.0.0 (Cowper / Build 3081 / July 7, 2006)
This bug happens on Oracle/Jboss as well.
I think I narrowed down the problem, here is a link to forum post describing the exception and SQL query that fails: http://www.liferay.com/web/guest/devzone/forums/message_boards/message/19212 The problem is in com.liferay.portal.service.persistence.GroupFinder method findByC_N_D class, the closing bracket is in the wrong place. This is a part of core functionality and the fix is pretty trivial (move the closing bracket to right place). I would fix it myself, but I am not sure how it works here (I've installed Liferay from sourcecode and have it on my computer). forgot to mention, I am using version 4.1.1
The problem is that Oracle doesn't like this query:
select * from ( ( SELECT Group_.groupId AS groupId, Group_.name AS groupName FROM Group_ WHERE (Group_.companyId = ?) AND (Group_.className IS NULL OR Group_.className = '') AND (Group_.classPK IS NULL OR Group_.classPK = '') AND (lower(Group_.name) LIKE ? OR ? IS NULL) AND (lower(Group_.description) LIKE ? OR ? IS NULL) ) ORDER BY groupName ASC ) where rownum <= ? However this query works fine: select * from ( select * from ( SELECT Group_.groupId AS groupId, Group_.name AS groupName FROM Group_ WHERE (Group_.companyId = ?) AND (Group_.className IS NULL OR Group_.className = '') AND (Group_.classPK IS NULL OR Group_.classPK = '') AND (lower(Group_.name) LIKE ? OR ? IS NULL) AND (lower(Group_.description) LIKE ? OR ? IS NULL) ) ORDER BY groupName ASC ) where rownum <= ? So changing code in com.liferay.portal.service.persistence.GroupFinder method findByC_N_D From this: sql = "("; sql += CustomSQLUtil.get(FIND_BY_C_N_D); sql = StringUtil.replace(sql, "[$JOIN$]", _getJoin(params1)); sql += ")"; To this: sql = "select * from ("; sql += CustomSQLUtil.get(FIND_BY_C_N_D); sql = StringUtil.replace(sql, "[$JOIN$]", _getJoin(params1)); sql += ")"; helped to solve the problem. Weird. I just tried it with SVN trunk on oracle 10, that part of the code hasn't changed since 4.1.0 so should be the same.
I can no longer reproduce this in 4.1.1. I did not test with 4.1.0, so possibly it was fixed in 4.1.0.
I am using MySQL, so, cannot confirm the Oracle issue. You need to configure liferay properly to handle DB2 for custom queries.
What have you done? We have this working in our environment so I will leave this issue closed.
Hi,
Our log shows a series of similar SQL statements that causes DB2 exceptions. We conclude that the SQL statement generated in com.liferay.portal.service.persistence.GroupFinder.findByC_N_D(GroupFinder.java:334) does not work well with Hibernate's DB2Dialect. Appreciate if there are workarounds for this problem. [4/3/07 18:50:04:922 SGT] 00000047 SystemOut O Hibernate: ( select * from ( select rownumber() over(ORDER BY groupName ASC) as rownumber_, Group_.groupId AS groupId, Group_.name AS groupName FROM Group_ INNER JOIN Users_Groups ON (Users_Groups.userId = ?) WHERE (Users_Groups.groupId = Group_.groupId) AND (Group_.companyId = ?) AND (Group_.className IS NULL OR Group_.className = '') AND (Group_.classPK IS NULL OR Group_.classPK = '') AND (lower(Group_.name) LIKE ? OR CAST(? AS VARCHAR(32672)) IS NULL) AND (lower(Group_.description) LIKE ? OR CAST(? AS VARCHAR(32672)) IS NULL) ) UNION ( SELECT Group_.groupId AS groupId, Group_.name AS groupName FROM Group_ INNER JOIN Groups_Orgs ON (Groups_Orgs.groupId = Group_.groupId) INNER JOIN Users_Orgs ON (Users_Orgs.organizationId = Groups_Orgs.organizationId) WHERE (Users_Orgs.userId = ?) AND (Group_.companyId = ?) AND (Group_.className IS NULL OR Group_.className = '') AND (Group_.classPK IS NULL OR Group_.classPK = '') AND (lower(Group_.name) LIKE ? OR CAST(? AS VARCHAR(32672)) IS NULL) AND (lower(Group_.description) LIKE ? OR CAST(? AS VARCHAR(32672)) IS NULL) ) UNION ( SELECT Group_.groupId AS groupId, Group_.name AS groupName FROM Group_ INNER JOIN Groups_UserGroups ON (Groups_UserGroups.groupId = Group_.groupId) INNER JOIN Users_UserGroups ON (Users_UserGroups.userGroupId = Groups_UserGroups.userGroupId) WHERE (Users_UserGroups.userId = ?) AND (Group_.companyId = ?) AND (Group_.className IS NULL OR Group_.className = '') AND (Group_.classPK IS NULL OR Group_.classPK = '') AND (lower(Group_.name) LIKE ? OR CAST(? AS VARCHAR(32672)) IS NULL) AND (lower(Group_.description) LIKE ? OR CAST(? AS VARCHAR(32672)) IS NULL) ) ORDER BY groupName ASC ) as temp_ where rownumber_ <= ? [4/3/07 18:50:04:938 SGT] 00000047 JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -104, SQLState: 42601 [4/3/07 18:50:05:078 SGT] 00000047 JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions An unexpected token "( select * from ( select rownumber() over(O" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<from>". [4/3/07 18:50:05:094 SGT] 00000047 JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -727, SQLState: 56098 See portal.properties for specific DB2 set up. Again we have this working in our environment.
I'm experiencing the same problem with DB2 8.2. If it is working in your environment, can you please elaborate on version etc. Last test i did was on the 4.2.2 release.
Hi Rob,
Just to let you know that we currently overcome the problem by modifying GroupFinder : In the method findByC_N_D, //sb.append("("); sb.append("Select groupId, groupName from ( ("); ... //sb.append(" ORDER BY groupName ASC"); sb.append(") AS tempA_ ORDER BY groupName ASC"); The solution is very similar to what Roman Hoyenko has suggested above. We had to fix the GroupFinder for 4.1.2 as we do now for 4.2.2 using DB 2 8.2.
What is the workaround needed to get this to work in 4.3, we will gladly check this in.
I'm trying to get Liferay 4.3 RC 1 to work at the moment. If everything is running (with or without the patch) I'll report back.
The GroupFinder.findByC_N_D() creates following SQL code for "available communities":
(SELECT Group_.groupId AS groupId, Group_.name AS groupName FROM Group_ WHERE clause) ORDER BY groupName ASC For limiting the result to a subset Hibernate modifies the statement and adds an additional temporary column to it: ( select * from ( select rownumber() over(ORDER BY groupName ASC) as rownumber_, Group_.groupId AS groupId, Group_.name AS groupName FROM Group_ WHERE clause) ORDER BY groupName ASC ) as temp_ where rownumber_ <= ? And this statement does not work at all. The solution is to wrap the unmodified statement except the order by clause in an additional select: SELECT liferay_tmp.* FROM (orig statement) AS liferay_tmp ORDER BY groupName ASC. Therefore the first string to be appended to StringMaker has to be the outer select (around line 283 in Liferay 4.3 RC 1): try { session = HibernateUtil.openSession(); StringMaker sm = new StringMaker(); sm.append("SELECT liferay_tmp.* FROM ("); // this line has to be added sm.append("("); sm.append(CustomSQLUtil.get(FIND_BY_C_N_D)); String sql = sm.toString(); and before the order by clause the temporary table name has to be added (around line 330 in Liferay 4.3 RC 1): sm.append(sql); sm.append(")"); } sm.append(") AS liferay_tmp"); // this line has to be added sm.append(" ORDER BY groupName ASC"); sql = sm.toString(); SQLQuery q = session.createSQLQuery(sql); If you need an actual patch you have to wait another few hours. But it should be easy enough to add the two lines. That's the patch for 4.2.2, but it can't be reapplied to 4.3 RC 1 as is since StringBuffer has been replaced with StringMaker:
Index: GroupFinder.java =================================================================== RCS file: /var/cvs/TAbt/dcpf/ra_portal/src/com/liferay/portal/service/persistence/GroupFinder.java,v retrieving revision 1.4 retrieving revision 1.5 diff -u -r1.4 -r1.5 --- GroupFinder.java 12 Jun 2007 14:17:18 -0000 1.4 +++ GroupFinder.java 12 Jun 2007 14:20:31 -0000 1.5 @@ -264,6 +264,7 @@ StringBuffer sb = new StringBuffer(); + sb.append("SELECT ourtemp.* FROM ("); sb.append("("); sb.append(CustomSQLUtil.get(FIND_BY_C_N_D)); @@ -309,6 +310,7 @@ sb.append(")"); } + sb.append(") AS ourtemp "); sb.append(" ORDER BY groupName ASC"); sql = sb.toString(); Would you mind supplying a patch against RC1. And also, will this patch work across other databases?
Sorry, I promised it, but forgot it ... here it is (against revision 6935).
About the consequences: Simply from the SQL there is nothing wrong with it. Actually it wraps a select * from xyz into another select: select * from (select * from xyz) as tmp But of course ... you never know what might happen ... I don't have other databases to test it, especially since Hibernate is involved and was the actual one breaking the original SQL expression. The patch works also with the second code path (there is a "if (Validator.isNotNull(userId))" inside of it). This code path ends in (select * from xyz) UNION (select * from xyz) UNION (select * from xyz) order by column Now that changes to select * from ((select * from xyz) UNION (select * from xyz) UNION (select * from xyz)) as tmp order by column I'm not that deep in SQL, but I wonder if the original one was correctly sorted and not only the last UNION expression. At least it's clear now with the additional wrapping. Hi, to fix it you just need to use the correct dialect for DB2 in your portal.properties:
org.hibernate.dialect.DB2400Dialect This means the selection process in DynamicDialect or more exact Hibernate's DialectFactory at the end is insufficient?
Hi Jorg, I don't remember the reason for that, maybe because there are at least 3 dialects for db2, so you need to choose it explicity, but I will look for the ticket and make sure this dialect is always chose for db2. Did you tested with DB2400Dialect?
By the way, do you know how to rename a column name in db2? I got conlicting answers, some places say it is not possible other say it works in 9.1. I need to figure out this to fix the upgrade proccess to 4.3 using db2. Hi Bruno,
I have not tested it yet. There are times I'm sleeping as well :) I only wondered about the reasoning, if it was intuition or something like that ;) No, I don't know a way to rename a column in DB2. But neither really searched for it. Because of We also have a db migration tool which only alters the table when columns are appended. As soon as there is some renaming it renames the table by prefixing it with TMP_, then it creates the new table and creates and optionally executes a "insert into NEW_TABLE select * from TMP_TABLE". This means whom's ever knowledge is coded into that tool probably did not know a way to rename a column as well. But it might be 10 years old, was written against DB2 5.x and has probably never been adapted since then. Joerg I can confirm it works since Hibernate changes the SQL statement now in a different way:
( SELECT * FROM Group_ WHERE ...) ORDER BY groupName ASC *fetch first 20 rows only* Thanks, Bruno |
||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Closed
Major
Community Portlet Non-Functional
