openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Georgi Naplatanov (JIRA)" <j...@apache.org>
Subject [jira] Updated: (OPENJPA-1846) Generates wrong SQL when subqueries are used
Date Wed, 20 Oct 2010 10:59:22 GMT

     [ 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.


Mime
View raw message