[SPARK-32101] The name in the with clause when it is same as table name. And when that table name is used in the other places, it is not taking the table, it is considering the with clause.

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Incomplete
• Affects Version/s: 2.2.1
• Component/s: Spark Core, SQL
• Labels: spark spark-sql sql
• Flags: Patch, Important
• Assignee: Unassigned
• Reporter: Pavan Kothamasu
• Created: 26/Jun/20 06:42
• Updated: 12/Dec/22 18:11
• Resolved: 28/Jul/20 08:27

Description

The name in the with clause when it is same as table name. And when that table name is used in the other places, it is not taking the table, it is considering the with clause sub table. The example is given below with explanation:

database1.sample structure:

columns: id,name

 

with sample as (

select id, 1 as cnt from database1.sample),

with amp as(

select name,id from database1sample)

select * from sample inner join amp on amp.id=sample.id

;

 

In this example, second alias will fail for missing "name" column, even though the database1.sample table has "name" column. Becuase it is referring column "name" from first alias sample. Because the tablename and alias name are same here and this error came.

The bug is it should not take the alias even i have given databasename along with tablename. It should consider from metadata of the table not from alias.

Here the solution is tablename should not be alias name. The first alias should be sample1 or some thing like that.