"Out of spool" error in Teradata

Question

Why do I get "Out of spool" error when I alias a table and quality with the real table name in a join condition?

For example; sel name,ID from emp_table E inner join dep_table D on emp_table.dep_no= D.dep_no

Answer 1

UPDATE : addressing follow-up question from comments

When you qualify with the "real" table name, Teradata does not assume you mean the same instance of the table as the one you aliased. So it's cross-joining another instance of emp_table (in your example), which must be generating too much data for your installation to handle. (And even if it ran to completion, it would get unexpected results because the logic simply doesn't say what you mean it to say; see below.)

If you think about it, the DBMS would be headed down a dangerous road to assume that emp_table.dep_no refers to the same table instance as would E.dep_no; how would it then be expected to deal with this:

SELECT e.id
  FROM            emp_table e
       inner join emp_table m
               on e.manager_id = m.id
 WHERE emp_table.dep_num = 37

But I don't like the way it behaves either. IMO it should throw an error, because your FROM clause doesn't specify any table to be addressed as emp_table. Alas, Teradata generally allows implicit joining to new table instances just by referring to them.

So I mentioned that you'll get unexpected results even if the query completes. Consider an example with small data sets:

EMP_TABLE
-----------------------------
EMP_ID     | DEPT_ID  | NAME
1          | A        | Sue
2          | B        | Bob

DEPT_TABLE
------------------------------
DEPT_ID   | NAME
A         | Engineering
B         | Sales
C         | Legal

Ok, so say you want to just list names of employees and their departments:

select e.name, d.name
  from            emp_table e
       inner join dept_table d
               on d.dept_id = e.dept_id

And that's fine, but now you decide to exclude employees in sales. But you qualify with the actual table name instead of d...

select e.name, d.name
  from            emp_table e
       inner join dept_table d
               on d.dept_id = e.dept_id
 where dept_table.name <> 'Sales'

Now you expect to just see Sue | Engineering. But actually you get four records: two that say Sue | Engineering and two that say Bob | Sales. So what gives?

Well, Teradata thinks your query means

select e.name, d.name
  from            emp_table e
       inner join dept_table d
               on d.dept_id = e.dept_id
       cross join dept_table x
 where x.name <> 'Sales'

Right off the bat the WHERE filter wasn't applied as expected. No restrictions are placed on rows from e and d other than their join condition, so that keeps both Sue and Bob in the results.

Worse, that extra relation x returned two rows, which were cross joined with the rest of the result set (which is why you got duplicates of each returned record).