Archives of the TeradataForum
Message Posted: Tue, 13 May 2008 @ 16:04:13 GMT
I am always happy to help if I have something to offer that seems appropriate.
I have run your queries under TD 12.0 and the non-joined rows with NULL are coming back as expected. As I mentioned previously, using an outer join to bring back only the missing non-joined rows uses more resources (spool, disk, and processor for holding joined rows and then throwing them away at the end) than necessary. That is why I suggested the NOT EXISTS which keeps only the non-joined rows. I suggested it instead of NOT IN to include any rows that might in real life have a NULL in column1, if that is possible.
Regardless, back to your question:
This is a correct assessment on your part.
I am not sure, but it sounds like you are interpretting this incorrectly. The SELECT with the count(*) showed correct counts with a count of 1 for 'Default' - when the 1 in column1 of table1 matched the 1 in column1 of table2 (regardless of the view). The ? (NULL) is the one with a count(*) of 2 - the non-matching rows from the outer join. As you mentioned, table2/view_tbl2 is the inner table. Whenever a row from the inner table is not found to match a row of the outer table, all column references from the inner table are "extended with NULL value."
As mentioned by a couple of people, the earlier releases had a bug regarding selecting a literal in the view and its evaluation when part of an outer join. The optimizer seemed to assume that a literal would never produce a NULL in the WHERE clause, even when it is coming from the inner table. This was a wrong assumption. As mentioned above, this bug is fixed in TD 12 and maybe later releases of V2R6. I am not sure when it came out and don't remember if Dieter mentioned which release.
Don't know that this adds much more value to what has already been discussed or entirely answers your concern.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|