Teiid 8.1 - left outer join issue

[guidocampopiano]

Hi to all,

I'm facing an issue regarding a left outer join performed between two tables contained in the same view model. I can't provide actual table names / data in this post, I'll try to reproduce tables' structure in order to illustrate the issue.

T1 has the primary key PK1 composed by columns A, B, C and other columns, let's say D, E;

T2 has the primary key PK2 composed by columns A, B, C with a foreign key on PK1, and other columns, let's say F, G.

Columns A, B, C in both T1 e T2 are respectively of the same datatypes and lengths (fixed).

T2.F and T2.G MUST contain not - null values for some known values of A - B - C but, performing a left outer join between T1 and T2 executing following query

SELECT T1.*, T2.F, T2.G

FROM T1 LEFT OUTER JOIN T2 ON

(T1.A = T2.A AND T1.B = T2.B AND T1.C = T2.C)

null values are returned for T2.F and T2.G.

Follows setup on my PC:

- JBoss Teiid 8.1;

- JBoss AS 7.1.1;

- JDK 1.6.025;

- OS Windows 7 (Ubuntu Server 12.04 in future production environment);

- Teiid Designer 7.7 (as provided in JBoss Developer Studio's latest version).

On my PC Teiid is running in standalone mode, it will run in domain mode in production environment.

I'll provide some additional info based on your requests in order to address the resolution.

Thanks in advance.

Guido

[guidocampopiano]

Any help?

[shawkins]

You'll need to verify the query plan as the next step.  You can do that in designer or by issuing a "SET SHOWPLAN ON" or "SET SHOWPLAN DEBUG" - see https://docs.jboss.org/author/display/teiid81final/SET+Statement and https://docs.jboss.org/author/display/teiid81final/SHOW+Statement

The short form of the final plan is the PLAN_TEXT, which should be looked at first.  If it contains any obvious error, then log an issue.  The DEBUG_LOG can be useful at spotting exactly where an issue was introduced and may be something we'll want to see if there was a planning issue that is not redily reproducible from the query and final query plan.

If there's nothing obvious, then you'll need to reproduce your scenario or redact the query/query plan so that we can have a better idea of what is going on.  You can also engage Red Hat support services about a support contract if you need a non-public channel.

Steve

[guidocampopiano]

Hi Steven,

first of all thanks for your reply.

Transformation of my T2 table in virtual model is the union of several tables belonging to different physical source models in my project; all those tables haven't the same number of columns so, to make UNION work, I have to provide the missing columns via sql. For example, let's assume I have following source tables that compose my T2 table in the example above

STX composed by columns AX (string), BX (string), CX (integer), FX, GX

STY composed by columns AY (string), BY (string), FY, GY

When performing union of tables in T2's transformation query, I've to write something like the following fragment

SELECT AX as A, BX as B, CX as C, FX as F, GX as G

FROM STX

UNION

SELECT AY as A, BY as B, 0 as C, FY as F, GY as G

FROM STY

A, B, C are the primary key of virtual table T2 in example in my first post.

I got PLAN_TEXT and I see that queries (shown in the children AccessNodes of a UnionAllNode) generated on physical source models' tables do not contain the corresponding of "0 as C" but they select only the columns actually existing: I don't know if this aspect is really relevant but, since column C is used to perform the left outer join, it sounds strange.

I hope this could help.

Thanks in advance

Guido

[shawkins]

> I got PLAN_TEXT and I see that queries (shown in the children AccessNodes of a UnionAllNode) generated on physical source models' tables do not contain the corresponding of "0 as C" but they select only the columns actually existing: I don't know if this aspect is really relevant but, since column C is used to perform the left outer join, it sounds strange.

The access node performs a local optimization to remove duplicate and constant columns projected from the source query.  I don't believe the plan is showing that information.  I'll update that.  Once the tuples are projected out of the access node, the additional values are added back in.

Steve