[
https://issues.apache.org/jira/browse/OPENJPA-1846?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Georgi Naplatanov updated OPENJPA-1846:
---------------------------------------
Attachment: CmsDAOOfflineStructure.java
CmsDAOOfflineResources.java
CmsDAOLog.java
> 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
> Attachments: CmsDAOLog.java, CmsDAOOfflineResources.java,
> CmsDAOOfflineStructure.java
>
>
> 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.
