Teiid 9.1 Translator issue - Connecting to SQL 2014 for transformation query with 'union all'

[cm.kumar]

Issue: VDB view which has 'Union All' in the transformation query causes issue or does not return data as expected. Happens only when the VDB connects to SQL 2014 behind. It does work, when connecting to SQL 2008 R2 DB.

Details:

Teiid version : 9.1 used to connect to VDB (Teiid 9.1 jars attached, zip)

Connection URL : jdbc:teiid:<Name of VDB>@mm://<ip of server>:31000

Editors tried for querying: Squirrel 3.7.1 (with same teiid jars attached as drivers) and Teiid 11.0 designer (Database development plugin) editor.

View Definition: PPDMTx.ddl (attached)

Command run: SELECT * FROM PPDMTx.PARENT_WELL_COMPL_PERCENT ORDER BY PARENT_WELL_COMPL_ID OFFSET 1 ROWS FETCH NEXT 100 ROWS ONLY

Teiid Error:

TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PPDMSrc: 102 TEIID11008:TEIID11004 Error executing statement(s):

[Prepared Values: [] SQL: SELECT OFFSET 1 ROWS FETCH NEXT 100 ROWS ONLY * FROM (SELECT g_7."PARENT_ID" AS c_0, g_7."WELL_ID" AS c_1, g_10."PARENT" AS c_2,

g_9."WELL_NAME" AS c_3g_7."WELL_COMPONENT_ID" AS c_20 FROM "SQL2014DB"."dbo"."WELL_COMPONENT" g_7

INNER JOIN "SQL2014DB"."dbo"."R_WELL_COMPONENT_TYPE" g_8 ON g_7."WELL_COMPONENT_TYPE" = g_8."R_WELL_COMPONENT_TYPE_ID"

INNER JOIN "SQL2014DB"."dbo"."WELL" g_9 ON g_7."WELL_ID" = g_9."WELL_ID"

INNER JOIN "SQL2014DB"."dbo"."PARENT" g_10 ON g_7."PARENT_ID" = g_10."PARENT_ID"

WHERE g_7."ACTIVE_IND" = 'Y' AND g_8."WELL_COMPONENT_TYPE" = 'PARENT' AND g_9."ACTIVE_IND" = 'Y' AND g_10."ACTIVE_IND" = 'Y'

UNION ALL

SELECT g_1."PARENT_ID" AS c_0, g_0."COMPLETION_ID" AS c_1, g_3."PARENT" AS c_2, g_2."WELL_COMPLETION" AS c_3,

g_0."COMPL_PARENT_PERCENT_ID" AS c_20 FROM "SQL2014DB"."dbo"."COMPL_PARENT_PERCENT" g_0

INNER JOIN "SQL2014DB"."dbo"."COMPL_PARENT_PERCENT_COMP" g_1 ON g_0."COMPL_PARENT_PERCENT_ID" = g_1."COMPL_PARENT_PERCENT_ID"

INNER JOIN "SQL2014DB"."dbo"."WELL_COMPLETION" g_2 ON g_0."COMPLETION_ID" = g_2."WELL_COMPLETION_ID"

INNER JOIN "SQL2014DB"."dbo"."PARENT" g_3 ON g_1."PARENT_ID" = g_3."PARENT_ID"

  WHERE g_0."ACTIVE_IND" = 'Y' AND g_0."IS_ACTIVE" = 'Y' AND g_1."ACTIVE_IND" = 'Y' AND g_2."ACTIVE_IND" = 'Y' AND g_3."ACTIVE_IND" = 'Y') AS X ORDER BY c_20]

Problem is the same ddl view works fine when connected behind with SQL 2008. Issue happens only when connected to SQL 2014 and

when the view uses 'UNION ALL' in the transformation. The select statement has Offset information appended at the beginning. 

[rareddy]

You want to capture the source query that is being sent to SQLServer 2014 (you can turn on DEBUG logging to see this), then issue it manually and make sure it is generating right query and SQLServer 2014 is returning the right results. See the where the anomaly is? If Teiid is generating wrong query, then you need to post the query plan for further debugging. If it returning wrong results, then might want to investigate why? Depending upon what you find out, we may have further suggestions.

Ramesh..

[cm.kumar]

Attached are the queries which got prepared between SQL 2014 and SQL2008. I got this by turning on debug message. Both are prepared differently. But when i run the SQL 2008 prepared query in 2014 DB, it returns results.

[rareddy]

The difference is in 2008 version a ROW_NUMBER construct is being used over the resulset to apply the LIMIT, where as in 2014 version that capability of LIMIT is supported without the extra ROW_NUMBER wrapping. Can you see if take the row number wrapping out in 2008, and "OFFSET 1 ROWS FETCH NEXT 100 ROWS ONLY" wrapping in 2014 they generate same results?

If that is true, then something else in processing is wrong.

Ramesh..

[shawkins]

This is a bug with the SQLServer 2012+ conversion logic when there is a limit with an offset and an order by specified.  Please log a jira for this.

[shawkins]

I went ahead and logged this as [TEIID-4816] SQL Server 2012+ offset order by issue - JBoss Issue Tracker

[cm.kumar]

Is there any work around for this issue?

Currently for an ETL activity we are restricting the query to pick 1000 records per one pull, so we had to order the dataset and trying to offset and fetch. So that the same record is not duplicated. But if we have the restriction of not using Order by and Offset, is there any suggested alternative, will the row_number() option work out? Will it be equivalent perform-ant to do a row_number() on data set and pull 1000 rows at once, will that be equivalent to order by offset fetch next?

[shawkins]

The first workaround would be to manually set the translator property databaseVersion to 10.0 so that the bug will not be hit.

[cm.kumar]

is it setting up of database compatibility version of DB to SQL 2008 (100)? Or is it different?

[shawkins]

Yes, version 10.0 is SQL Server 2008.