Generates wrong SQL when subqueries are used
--------------------------------------------
Key: OPENJPA-1846
URL: https://issues.apache.org/jira/browse/OPENJPA-1846
Project: OpenJPA
Issue Type: Bug
Affects Versions: 2.0.1, 2.1.0
Environment: PostgreSQL 8.3, Java 1.6
Reporter: Georgi Naplatanov
Priority: Blocker
The generated SQL declaring twice some tables in the "FROM" statement.
============================
JPQL
============================
SELECT
T_CmsDAOOfflineResources,
T_CmsDAOOfflineStructure,
T_CmsDAOOfflineResources.m_projectLastModified,
MAX(T_CmsDAOLog.m_logDate)
FROM
CmsDAOOfflineResources
T_CmsDAOOfflineResources,
CmsDAOOfflineStructure T_CmsDAOOfflineStructure,
CmsDAOLog T_CmsDAOLog
WHERE
20 < (
SELECT
T1_CmsDAOLog.m_logType
FROM
CmsDAOLog T1_CmsDAOLog
WHERE
T1_CmsDAOLog.m_userId = ?1
AND T1_CmsDAOLog.m_structureId =
T_CmsDAOLog.m_structureId
AND T1_CmsDAOLog.m_logDate = (
" SELECT
MAX(T2_CmsDAOLog.m_logDate)
" FROM CmsDAOLog
T2_CmsDAOLog
" WHERE
T2_CmsDAOLog.m_structureId = T1_CmsDAOLog.m_structureId
" )
)
AND T_CmsDAOLog.m_structureId=T_CmsDAOOfflineStructure.m_structureId
AND
T_CmsDAOOfflineStructure.m_resourceId=T_CmsDAOOfflineResources.m_resourceId
AND T_CmsDAOLog.m_userId = ?2
AND T_CmsDAOLog.m_structureId IS NOT NULL
GROUP BY
T_CmsDAOOfflineResources,
T_CmsDAOOfflineStructure,
T_CmsDAOOfflineResources.m_projectLastModified
============================
Generated wrong SQL
============================
SELECT
t6.resource_id,
t5.structure_id,
t6.project_lastmodified,
MAX(t4.log_date)
FROM
public.cms_log t1, --twice
public.cms_log t4,
public.cms_offline_structure t5,
public.cms_offline_resources t6
WHERE
(? < (
SELECT t0.log_type
FROM
public.cms_log t0,--twice
public.cms_log t3
WHERE (
t0.user_id = ?
AND t0.structure_id = t1.structure_id
AND t0.log_date = (
SELECT MAX(t2.log_date)
FROM public.cms_log t2
WHERE (
t2.structure_id =
t3.structure_id)
)
)
)
AND t4.structure_id = t5.structure_id
AND t5.resource_id = t6.resource_id
AND t4.user_id = ?
AND t4.structure_id IS NOT NULL
)
GROUP BY
t6.resource_id,
t5.structure_id,
t6.project_lastmodified
===============================
Corrected SQL
===============================
SELECT
t6.resource_id,
t5.structure_id,
t6.project_lastmodified,
MAX(t4.log_date)
FROM
public.cms_log t4,
public.cms_offline_structure t5,
public.cms_offline_resources t6
WHERE
(20 < (
SELECT t0.log_type
FROM
public.cms_log t0
WHERE (
t0.user_id = ?
AND t0.structure_id = t4.structure_id
AND t0.log_date = (
SELECT MAX(t2.log_date)
FROM public.cms_log t2
WHERE (
t2.structure_id =
t0.structure_id)
)
)
)
AND t4.structure_id = t5.structure_id
AND t5.resource_id = t6.resource_id
AND t4.user_id = ?
AND t4.structure_id IS NOT NULL
)
GROUP BY
t6.resource_id,
t5.structure_id,
t6.project_lastmodified
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
