Cross Database join with filter field is null

[mtawk]

Hello,

I'm executing the below query using Teiid; I'm using 2 different databases over the same MSSQL server with a condition where field is Null

After comparing Teiid execution result with direct execution over mssql with mssql management studio, I found that Teiid is returning more records, as if it is not taking into consideration the where clause  where field is Null

select table1."field1" as "field1"

from  "DB1"."dbo".table1 "Table1"

LEFT OUTER JOIN "DB2"."dbo".Table2 "Table2" ON Table1.ID = Table2.ID

WHERE ( ( (Table2."field2" is null ) ) )

Any idea of the problem?

Thanks in advance

[rareddy]

Mark,

You can turn on command logging to see what exactly is the query that Teiid is sending to the source. Turn on logging for "org.teiid.COMMAND_LOG" to "DEBUG". Also since the join tables are both from same database I would have expected the query to be pushed to source as is. Command log will show you that. May be you configured the translator name wrong on the VDB?

Ramesh..

[mtawk]

Thanks for the quick reply.

I guess in this case the query is not push as is.

here is the executed query, with the execution plan of Teiid

select MSP_PROJECTS."PROJ_NAME" as "MSP_PROJECTS_PROJ_NAME",MSP_PROJECTS."PROJ_PROP_TITLE" as "MSP_PROJECTS_PROJ_PROP_TITLE",MSP_PROJECTS."CREATED_DATE" as "MSP_PROJECTS_CREATED_DATE",MSP_PROJECTS."MOD_DATE" as "MSP_PROJECTS_MOD_DATE"

from "ProjectServer_DraftModel"."ProjectServer_Draft"."dbo".MSP_PROJECTS "MSP_PROJECTS"

LEFT OUTER JOIN "ProjectServer_ReportingModel"."ProjectServer_Reporting"."dbo".MSP_EpmProject "MSP_EpmProject" ON MSP_PROJECTS.PROJ_UID = MSP_EpmProject.ProjectUID

where ( ( (MSP_EpmProject."ProjectUID" IS null ) ) )

order by MSP_PROJECTS."PROJ_NAME" ASC LIMIT 0 , 30

<?xml version='1.0' encoding='UTF-8'?><node name="ProjectNode"><property name="Output Columns"><value>MSP_PROJECTS_PROJ_NAME (string)</value><value>MSP_PROJECTS_PROJ_PROP_TITLE (string)</value><value>MSP_PROJECTS_CREATED_DATE (timestamp)</value><value>MSP_PROJECTS_MOD_DATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 0</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 0</value><value>Node Blocks: 0</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 1.0</value></property><property name="Child 0"><node name="LimitNode"><property name="Output Columns"><value>PROJ_NAME (string)</value><value>PROJ_PROP_TITLE (string)</value><value>CREATED_DATE (timestamp)</value><value>MOD_DATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 0</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 0</value><value>Node Blocks: 0</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 1.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>PROJ_NAME (string)</value><value>PROJ_PROP_TITLE (string)</value><value>CREATED_DATE (timestamp)</value><value>MOD_DATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 0</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 0</value><value>Node Blocks: 0</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 1.0</value></property><property name="Child 0"><node name="SelectNode"><property name="Output Columns"><value>PROJ_NAME (string)</value><value>PROJ_PROP_TITLE (string)</value><value>CREATED_DATE (timestamp)</value><value>MOD_DATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 842</value><value>Node Next Batch Process Time: 32</value><value>Node Cumulative Next Batch Process Time: 16</value><value>Node Cumulative Process Time: 32</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 2</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>ProjectUID (string)</value><value>PROJ_NAME (string)</value><value>PROJ_PROP_TITLE (string)</value><value>CREATED_DATE (timestamp)</value><value>MOD_DATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 845</value><value>Node Next Batch Process Time: 16</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 16</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 845.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>PROJ_UID (string)</value><value>PROJ_NAME (string)</value><value>PROJ_PROP_TITLE (string)</value><value>CREATED_DATE (timestamp)</value><value>MOD_DATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 845</value><value>Node Next Batch Process Time: 16</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 16</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 845.0</value></property><property name="Query"><value>SELECT g_0.PROJ_UID AS c_0, g_0.PROJ_NAME AS c_1, g_0.PROJ_PROP_TITLE AS c_2, g_0.CREATED_DATE AS c_3, g_0.MOD_DATE AS c_4 FROM ProjectServer_DraftModel.ProjectServer_Draft.dbo.MSP_PROJECTS AS g_0 ORDER BY c_0</value></property><property name="Model Name"><value>ProjectServer_DraftModel</value></property></node></property><property name="Child 1"><node name="AccessNode"><property name="Output Columns"><value>ProjectUID (string)</value></property><property name="Statistics"><value>Node Output Rows: 813</value><value>Node Next Batch Process Time: 16</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 16</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 813.0</value></property><property name="Query"><value>SELECT g_0.ProjectUID AS c_0 FROM ProjectServer_ReportingModel.ProjectServer_Reporting.dbo.MSP_EpmProject AS g_0 ORDER BY c_0</value></property><property name="Model Name"><value>ProjectServer_ReportingModel</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>MSP_PROJECTS.PROJ_UID=MSP_EpmProject.ProjectUID</value></property></node></property><property name="Criteria"><value>MSP_EpmProject.ProjectUID IS NULL</value></property></node></property><property name="Sort Columns"><value>[MSP_PROJECTS.PROJ_NAME]</value></property><property name="Sort Mode"><value>SORT</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>30</value></property></node></property><property name="Select Columns"><value>MSP_PROJECTS.PROJ_NAME AS MSP_PROJECTS_PROJ_NAME</value><value>MSP_PROJECTS.PROJ_PROP_TITLE AS MSP_PROJECTS_PROJ_PROP_TITLE</value><value>MSP_PROJECTS.CREATED_DATE AS MSP_PROJECTS_CREATED_DATE</value><value>MSP_PROJECTS.MOD_DATE AS MSP_PROJECTS_MOD_DATE</value></property></node>

[rareddy]

Mark,

I would have expected "ProjectServer_ReportingModel" would have sent the "null" criteria to source. Can you attach "OPTION DEBUG" log to the discussion to see complete optimizer plan. Also the translator name is correctly set to "sqlserver" right?

Thanks

Ramesh..

[shawkins]

I need a couple of clarifications:

> I'm using 2 different databases over the same MSSQL server

> I guess in this case the query is not push as is

Since these are two different connection pools Teiid must process source requests against them individually.  Just to make sure, you weren't expecting Teiid to create a single query for what is cross source join?

> I found that Teiid is returning more records, as if it is not taking into consideration the where clause  where field is Null

Are you saying that you see incorrect top level results or that you expect less records to have pulled to form the final result?  From what I see of the log the is null criteria will be applied appropriately.

Also what version of Teiid are you on?

[mtawk]

My Teiid version is 8.6.0

My main concern is that the query result is wrong because the is null condition is not taken into consideration by Teiid.

When I run the same query directly over mssql, i'm getting a different result.

Otherwise, if i change the condition to where field = 'xxx', the result is correct.

I guess the problem is uniquely with the is null condition.

Is the xml log I sent enough or you need any additional log?

[shawkins]

> My main concern is that the query result is wrong because the is null condition is not taken into consideration by Teiid.

The is null condition is being taken into consideration, you can see it in the query plan.

> When I run the same query directly over mssql, i'm getting a different result.

How are you running a cross source query directly to mssql? 

> Is the xml log I sent enough or you need any additional log?

I'm still not sure yet what you are seeing.  Perhaps if you could characterize better if you are seeing duplicate rows, or rows that do not have null values, or rows that have null values that you don't expect should have null values.