I just noticed something in Teradata. We can define a table in the WHERE clause. For example the below query run without any error from the p****r: -
select * from personnel where personnel1.user_id = 1;
Where personnel and personnel1 are two different tables.
The result seems to be the cartisan product of table personnel and subquery (select * from personnel1 where user_id = 1) and in the output it displays only the columns of the table defined in the from clause (i.e. table personnel here).
Is this a known thing in Teradata. If yes than why it is so. Is there any advantage by this.
This is something we should be very careful while writing queries. what happened in the query is personnel1 is considered as an alias for personnel and it works with out error. similarly if we have a query like
select e.name, d.department_name from employee e, department d where employee.dept_id = department.dept_id
the above query will work but it will run into issues because of the wrong alias. It is a known thing but we learned it the hard way
I would not say this is bad but just a timesaving feature. To prevent this from happening again, try changing your defaultdatabase to a database contains nothing. The default database is the where Teradata trying to locate the table and if it could not find one, it will fail the query instead.