Logical Query Processing- Fuzzy Query with Order By Clause

Today it happened, I was called by my fellow programmer, he saw me a piece of T-SQL Code and asks few question on it, I will try to replicate the same code here.

There was a query somehow like this

select sub_group,sub_group,name,name from #temp

where sub_group = 'Furniture'

order by sub_group

And the query was failing, and asked me to justify the why it failing. That’s good, I find it interesting to explain the logic behind the scene through Logical Query Processing.  Yeah ;) I didn’t ask tea this time for this: P

Let’s formulate the same scenario by creating a sample table.

create table #temp

(id int identity(1,1),

sub_group varchar(10),

name varchar(10))

Command(s) completed successfully.

Lets insert few records in it.

insert into #temp

select 'Furniture','chair' union all

select 'Vechile','Maruti' union all

select 'Furniture','Desk' union all

select 'Furniture','Dine' union all

select 'Vechile','Honda' union all

select 'H.Vechile','Mahindra'

Now we can have the same Select query. Lets fire the query and see  the output.

select sub_group,sub_group,name,name from #temp

where sub_group = 'Furniture'

order by sub_group

Msg 209, Level 16, State 1, Line 32

Ambiguous column name 'sub_group'.

Now lets take the error, and dig the background of it. Now it says Ambiguous Column name.

If you see in the select list, we have called this column two times, now if we go through logical query processing, it says for this query.

1^st from clause gets executed i.e #temp and record set will build up say RS1.

2^nd where by clause gets executed which will filter the data and remain with the data which has ‘Furniture’ as sub_group and create a new record set say RS2

3^rd Now the Select clause will gets executed with form one more column of same name as sub_group. So the new record set will have this many column “sub_group, sub_group, name, name” say this record set as RS3

4^th Now Oder By clause gets the chance, it operate on RS3 record set, now if you see in query, the order by clause is on sub_group, so when it tries to order the RS3 data, it will found two column with same name sub_group, and optimizer find it difficult which column to pick up, thus it throws Ambiguous column error.

Solution to this problem is: make an alias to one of the sub_group column.

select sub_group,sub_group SB ,name,name from #temp

where sub_group = 'Furniture'

order by sub_group

Run the query and you will get your result.

sub_group  SB         name       name

---------- ---------- ---------- ----------

Furniture  Furniture  chair      chair

Furniture  Furniture  Desk       Desk

Furniture  Furniture  Dine       Dine

You just have to make time for your coding and keep it balance J

 

Post Reference: Vikram Aristocratic Elfin Share