Left Outer Join does Right Outer Join

[craig322]

I was trying to do a left outer join and kept getting the wrong row count. I did an explain plan on the query and
found out that for some reason the parser was doing a right outer join. I have attached the sql and corresponding step of the explain plan. Am I doing something wrong in my syntax?

TIA

Craig

explain INSERT INTO dss_tables.sales2 (
date_loaded,
branch_id,
customer_no,
sim_mfr_no,
product_code,
product_category,
shipment_type,
outside_sales_no,
sales,
cost,
region_id,
group_id,
parent_mfr,
mfr_name,
industry_code,
country_code,
bridge_code)
SELECT s.date_loaded,
s.branch_id,
s.customer_no,
s.sim_mfr_no,
s.product_code,
s.product_category,
s.shipment_type,
s.outside_sales_no,
s.sales,
s.cost,
s.region_id,
s.group_id,
p.mfg_desc,
s.mfr_name,
s.industry_code,
s.country_code,
s.bridge_code
FROM dss_tables.sales s
LEFT OUTER JOIN dss_tables.pmna_supplier p ON
s.country_code = p.country_code
AND s.sim_mfr_no = p.mfg_num;

6) We do an all-AMPs JOIN step from dss_tables.p by way of a RowHash
match scan with no residual conditions, which is joined to Spool 2
(Last Use). dss_tables.p and Spool 2 are right outer joined using
a merge join, with a join condition of ("(sim_mfr_no =
dss_tables.p.mfg_num) AND (country_code =
dss_tables.p.country_code)&quot. The result goes into Spool 1, which
is redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 1 by row hash. The size of Spool 1 is estimated with
low confidence to be 551,513 rows. The estimated time for this
step is 20 minutes and 41 seconds.

 

[tdatgod]

Hi,
I would suggest you open an incident with the Teradata GSC however that requires a customer support ID. What version of the software are you using.

sel * from dbc.dbcinfo;

Is this the DEMO? WE theoreticaly don't support the Demo, but I figure since the DEMO is a real version of teradata that if it is BUG in the DEMO it is a bug in the real product.

if you are using the V2r4 version of the demo we have another Qualifier like explain.

Show DML
<query>;

could you send that to me and the full explain text from the above.

tdatgod@aol.com

( this email address is given out here for resolution of this problem and this problem only. Use of this email address for any other purpose is a violation of SPAM laws and will be dealt with accordingly )

 

[tdatgod]

Hi,
Thanks for the email. I talked to our GSC and they mentioned you had opened an incident. I forwarded them all the information you sent me. That is what they get paid for.

I talked to one the optimizer people and he mentioned that a

LEFT OUTER JOIN S to P

should be identical to the rows returned by a

RIGHT OUTER JOIN P to S


It is just that given your data the optimizer decided that it would have to read less datablocks if it reversed the conditions based upon the statistics, or Random amp samples that you may have collected.

He said if it is returning the wrong count there is a BUG and it needs to be fixed but just looking at the explain he didn't see any reason.

I told him that you had opened an incident and he said he would let the GSC handle it and if they needed to they could contact him for help.

Thanks for the information.