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] Created: (OPENJPA-1846) Generates wrong SQL when subqueries are used
Date Wed, 20 Oct 2010 10:57:22 GMT
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.


Mime
View raw message