[teiid-issues] [JBoss JIRA] (TEIID-3681) Subquery field added in wrong place in generated JDBC SQL

[Mark Tawk (JIRA)]

Mark Tawk created TEIID-3681:
--------------------------------

             Summary: Subquery field added in wrong place in generated JDBC SQL
                 Key: TEIID-3681
                 URL: https://issues.jboss.org/browse/TEIID-3681
             Project: Teiid
          Issue Type: Bug
            Reporter: Mark Tawk
            Assignee: Steven Hawkins


I'm using  Teiid 8.11.3 and h2 translator:
I have a query that contains a subquery field in the select statement and a join over another subquery. In the JDBC query executed, i found that the subquery field was added into the subquery join which is giving an error in execution.

Here is my query:

SELECT
	"Profile_fqS_030915"."CID" as "Profile_fqS_030915_CID"
	,
	(
		SELECT
			MAX("Trx_fil_030915_sub"."TransactionDate") as "Trx_fil_030915_TransactionDa"
		FROM
			"implify_data_BanksModel"."implify_data_Banks"."Trx_fil_030915" 
			"Trx_fil_030915_sub"
		WHERE
			("Profile_fqS_030915"."CID" = "Trx_fil_030915_sub"."CID") ) as "SubQuery Trx"	,

	TIMESTAMPDIFF(SQL_TSI_DAY,	"Trx"."TransactionDate", PARSETIMESTAMP( '2015-04-02 00:00:00.000',	'yyyy-MM-dd HH:mm:ss.SSS' )) as "CalculatedField2"
	,
	"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField12" as 
	"Trxmo3U_CalculatedField1"
	,
	"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField3" as 
	"Trxmo3U_CalculatedField"
	,
	"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField11" as 
	"Trxmo3U_CalculatedField11"
	,
	"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField2" as 
	"Trxmo3U_CalculatedField2"
	,
	"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField1" as 
	"Trxmo3U_CalculatedField12"
	,
	"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField" as 
	"Trxmo3U_CalculatedField3"
FROM
	"implify_data_BanksModel"."implify_data_Banks"."Profile_fqS_030915" 
	"Profile_fqS_030915"
		LEFT JOIN
		"implify_data_BanksModel"."implify_data_Banks"."Trx_fil_030915" "Trx"
		ON
		"Profile_fqS_030915"."CID" = "Trx"."CID"
		LEFT JOIN
		(
			SELECT
				"Trxmo3U_sub"."Trx_fil_030915_CID" as "Trxmo3U_Trx_fil_030915_CID"
				,
				MIN("Trxmo3U_sub"."CalculatedField1") as "Trxmo3U_CalculatedField1"
				,
				MIN("Trxmo3U_sub"."CalculatedField") as "Trxmo3U_CalculatedField"
				,
				MAX("Trxmo3U_sub"."CalculatedField") as "Trxmo3U_CalculatedField2"
				,
				MAX("Trxmo3U_sub"."CalculatedField1") as "Trxmo3U_CalculatedField11"
				,
				AVG(1.0 * "Trxmo3U_sub"."CalculatedField") as "Trxmo3U_CalculatedField3"
				,
				AVG(1.0 * "Trxmo3U_sub"."CalculatedField1") as "Trxmo3U_CalculatedField12"
			FROM
				"Domain1BusinessModel"."Trxmo3U" "Trxmo3U_sub"
			WHERE
				( ( ("Trxmo3U_sub"."Trx_fil_030915_Channel" IN ('ATM'
				,
				'Branch'
				,
				'P.O.S.') ) ) )
			GROUP BY
				"Trxmo3U_sub"."Trx_fil_030915_CID") "Trxmo3UMEoebyXTVyguosX_Sub" ON
		"Profile_fqS_030915"."CID" = 
		"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_Trx_fil_030915_CID" LIMIT 0, 10


Here is the executed JDBC query:

SELECT
	g_0."CID" AS c_0,
	(
	SELECT
		MAX(g_4."TransactionDate")
	FROM
		"implify_data_Banks"."Trx_fil_030915" AS g_4
	WHERE
		g_4."CID" = g_0."CID") AS c_1	,
	g_1."TransactionDate" AS c_2,
	v_0.c_2 AS c_3,
	v_0.c_3 AS c_4,
	v_0.c_4 AS c_5,
	v_0.c_5 AS c_6,
	v_0.c_6 AS c_7,
	v_0.c_7 AS c_8
FROM
	("implify_data_Banks"."Profile_fqS_030915" AS g_0
		LEFT OUTER JOIN
		"implify_data_Banks"."Trx_fil_030915" AS g_1
		ON
		g_0."CID" = g_1."CID")
		LEFT OUTER JOIN
		(
			SELECT
				g_2."Trx_fil_030915_CID" AS c_0
				,
				*_(
					SELECT
						MAX(g_3."TransactionDate")
					FROM
						"implify_data_Banks"."Trx_fil_030915" AS g_3
					WHERE
						g_3."CID" = g_0."CID") AS c_1_*				,
				AVG((1.0 * cast(g_2."CalculatedField1" AS decimal))) AS c_2	,
				AVG((1.0 * g_2."CalculatedField")) AS c_3	,
				MAX(g_2."CalculatedField1") AS c_4	,
				MAX(g_2."CalculatedField") AS c_5	,
				MIN(g_2."CalculatedField1") AS c_6	,
				MIN(g_2."CalculatedField") AS c_7	
			FROM
				"implify_data_Banks"."Trxmo3U" AS g_2
			WHERE
				g_2."Trx_fil_030915_Channel" IN ('ATM',	'Branch', 'P.O.S.')
			GROUP BY
				g_2."Trx_fil_030915_CID") AS v_0	ON	g_0."CID" = v_0.c_0 LIMIT 10

Error:
Please try again! TEIID30504 implify_data_BanksModelVDB: 42122 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] 


Note that if i remove TIMESTAMPDIFF from the main query, the query executes without a problem and the subquery field is no longer added in the join subquery



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)