-
1. Re: Teiid 8.1 - left outer join issue
guidocampopiano Oct 30, 2012 7:40 AM (in response to guidocampopiano)Any help?
-
2. Re: Teiid 8.1 - left outer join issue
shawkins Oct 30, 2012 8:39 AM (in response to guidocampopiano)1 of 1 people found this helpfulYou'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
-
3. Re: Teiid 8.1 - left outer join issue
guidocampopiano Oct 30, 2012 2:17 PM (in response to shawkins)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 fragmentSELECT 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
-
4. Re: Teiid 8.1 - left outer join issue
shawkins Oct 31, 2012 3:17 PM (in response to guidocampopiano)> 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