Archives of the TeradataForum

Message Posted: Mon, 12 May 2008 @ 20:45:54 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: View with Default and Null values

From:		Victor Sokovin

My problem is why the last SQL does not return any rows. My hypothesis is that since the view has a default defined, it assumes this field will have default value for all rows in the join, even though this is a left join and NOT ALL rows may have a match. Regardless, I think this is not ideal. Can the members confirm if this is not an acceptable behavior or a bug? Any workarounds (short of not using defaults in the view?)

Please ignore my previous posting. I was again watching TV while posting, and it looks like TV and SQL do not go well together. I should stop posting from the couch.

What you described is indeed a problem! Looks like the last SELECT rewrites the JOIN to

          > >   left outer join
          > > VIEW_tbl2 TB2 on
          > > ( TB1.Column1 = TB2.Column1
          > > and TB2.Column2 is NULL );

or something like that.

Could it be the same or very similar "ghost bug" we discussed in

www.teradataforum.com/teradata/20071212_102138.htm ?

The problem with these bugs is that it is difficult to reproduce them and the OP do not provide more details like their DB version, explain plans, stats collected or not, etc. It would be good to know all these details.

Victor


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference