Archives of the TeradataForum

Message Posted: Wed, 24 Jul 2002 @ 17:33:22 GMT

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

All:

I was having some issues with the application of Search Conditions in ON clauses, which were not returning the rows I expected. It turns out that while I found a work around (see last example), the original SQL structure does not work - and by everything I read - it should (and I could well have missed something - all deferences to the manual's tact of: just try it).

My understanding was that search clauses in an JOIN/ON clause were applied by the optimizer prior to any JOIN operations defined by *that* JOIN/ON clause (or at least applied as a condition during the join operation). Also that: 1) the definition of a JOIN/ON search clause forced the order of operations of multiple non-inner joins to the order defined in the SQL and 2) that any search conditions in a where clause were not associative with the search conditions in the objects of the JOIN/ON clauses.

In this case, I am presuming the Search Condition is never applied (no matter what combination of columns the constraint is applied to in the ON clause) since the same set is returned if the search condition is not applied. The explain shows that the optimizer converts the IN list to OR's in all cases I tried. Same results on V2R4.1 and Demo TNT V2R4.0

So - is this a bug, a feature, or a change in behavior? If a feature - what are the rules for application of search conditions in a join/on clause?

Michael

I have a query:

SELECT T1.C1, T1.C2, T1.C3... (some CASE stmts)...
FROM      T1
LEFT JOIN T2
ON        T1.C1 = T2.C1
AND       T1.C2 = T2.C2
AND       T1.C1 IN (N,N,N,N,N,N,N,N,N)
LEFT JOIN T3
ON        T1.C1 = T3.C1
AND       T3.C2 = T3.C2

I expected for the search clause (IN (N...N)) in the first ON clause to be applied prior to the join which would make the following select statement equivilent:

SELECT T1.C1, T1.C2, T1.C3... (some CASE stmts)...
FROM      (Select *
           from T1
           where T1.C1 IN (N,N,N,N,N,N,N,N,N,N)
          ) as T1
LEFT JOIN T2
ON        T1.C1 = T2.C1
AND       T1.C2 = T2.C2
LEFT JOIN T3
ON        T1.C1 = T3.C1
AND       T3.C2 = T3.C2

But the first query returns 68,723 rows, and the second 85.

Even when I code the query to constrain ALL equality columns, I receive the same 67K result set:

SELECT T1.C1, T1.C2, T1.C3... (some CASE stmts)...
FROM      T1
LEFT JOIN T2
ON        T1.C1 = T2.C1
AND       T1.C2 = T2.C2
AND       T1.C1 IN (N,N,N,N,N,N,N,N,N)
AND       T2.C1 IN (N,N,N,N,N,N,N,N,N)
LEFT JOIN T3
ON        T1.C1 = T3.C1
AND       T3.C2 = T3.C2
AND       T1.C1 IN (N,N,N,N,N,N,N,N,N)

This application of the Search Clause on the WHERE works, and returns the 85 rows: (explain shows that this constraint is associative)

SELECT T1.C1, T1.C2, T1.C3... (some CASE stmts)...
FROM      T1
LEFT JOIN T2
ON        T1.C1 = T2.C1
AND       T1.C2 = T2.C2
LEFT JOIN T3
ON        T1.C1 = T3.C1
AND       T3.C2 = T3.C2
WHERE     T1.C1 IN (N,N,N,N,N,N,N,N,N)

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