Multi-table dynamic query joining on array element returns the wrong results when executed against DataServer for MS SQL Server

Multi-table dynamic query joining on array element returns the wrong results when executed against DataServer for Microsoft SQL Server

Mar 7, 2016
Knowledge

Title

URL Name

Multi-table-dynamic-query-joining-on-array-element-returns-the-wrong-results-when-executed-against-DataServer-for-MS-SQL-Server

Article Number

000118291

Environment

Product: OpenEdge Version: 11.3.x and later OS: Windows Other: DataServer for Microsoft SQL Server

Question/Problem Description

Multi-table dynamic query joining on array element returns the wrong results when executed against DataServer for MS SQL Server.

Too many records are returned when running a dynamic query joining on array element against DataServer for MS SQL Server.

Affected query joins on an array element. E.g. the following (or similar) appears in the WHERE clause of the ABL query:

table1.arrayField[10] = '10'

Both static and dynamic queries generate server-side joins (Cursor Type= Join by Server) but dynamic queries omit the clause featuring the array field.

The generated SQL query omits this from its WHERE clause:

/* Fail */
WHERE ((T0."imp" = ?) AND (T0."po_type_char" = ?) AND (T0."parent_code" = ?) AND (T0."uds_type" = ?) AND (T1."imp" = ?) AND (T0."owner_code" = T1."mt_int_code") AND (T1."mt_type" = ?) AND (T1."file_status" = ?)) ORDER BY T1."imp", T1."description"

Running the same as a static query, or as a FOR EACH generates the expected result. The clause that references the array element is included in the SQL query:

/* Success */
WHERE ((T0."imp" = ?) AND (T0."po_type_char" = ?) AND (T0."parent_code" = ?) AND (T0."arrayField##10" = ?) AND (T0."uds_type" = ?) AND (T1."imp" = ?) AND (T0."owner_code" = T1."mt_int_code") AND (T1."mt_type" = ?) AND (T1."file_status" = ?)) ORDER BY T1."imp", T1."description"

Running the same dynamic query against an OpenEdge database works as expected.

Steps to Reproduce

Clarifying Information

Sample ABL query:

FOR EACH table1 FIELDS (owner-code arrayField) 
		WHERE table1.imp = '' AND 
			  table1.po-type-char = 'c' AND 
			  table1.parent-code = 'ctmg' AND 
			  table1.arrayField[10] = '10' AND 
			  table1.uds-type = 'xyz', 
	EACH table2 FIELDS (mt-int-code matter-code) 
		WHERE table2.imp = '' AND 
		 	  table2.mt-int-code = table1.owner-code AND 
			  table2.mt-type = 'ctmg' AND 
			  table2.file-status = '' 
		BY table2.imp BY table2.description

Error Message

Defect Number

Defect PSC00345639

Enhancement Number

Cause

The exact cause is not known at this time.

Resolution

Until the fix is available, use the workaround below.

Workaround

Option #1
Add the QUERY-TUNING(NO-JOIN-BY-SQLDB) clause to the ABL query.
This forces a client-side join and avoids the issue.

Option #2
Start the session with the -nojoinbysqldb parameter to disable server-side joins for all queries.
This will avoid the issue but could have a performance impact since all joins will be performed by the client.

Option #3
Convert the dynamic query to a static query.

Notes

Keyword Phrase

Last Modified Date

4/14/2016 3:25 PM

Multi-table dynamic query joining on array element returns the wrong results when executed against DataServer for MS SQL Server

Files(0)

Was this article helpful?

Was this article helpful?

Multi-table dynamic query joining on array element returns the wrong results when executed against DataServer for MS SQL Server

Information

Related Articles