Different result with * and explicit field list?

Question

I was exploring another question, when I hit this behaviour in Sql Server 2005. This query would exhaust the maximum recursion:

with foo(parent_id,child_id) as (
    select parent_id,child_id
    from #bar where parent_id in (1,3)
    union all
    select #bar.* -- Line that changed
    from #bar
    join foo on #bar.parent_id = foo.child_id
)
select * from foo

But this would work fine:

with foo(parent_id,child_id) as (
    select parent_id,child_id
    from #bar where parent_id in (1,3)
    union all
    select #bar.parent_id, #bar.child_id -- Line that changed
    from #bar
    join foo on #bar.parent_id = foo.child_id
)
select * from foo

Is this a bug in Sql Server, or am I overlooking something?

Here's the table definition:

if object_id('tempdb..#bar') is not null
    drop table #bar

create table #bar (
    child_id int,
    parent_id int
)

insert into #bar (parent_id,child_id) values (1,2)
insert into #bar (parent_id,child_id) values (1,5)
insert into #bar (parent_id,child_id) values (2,3)
insert into #bar (parent_id,child_id) values (2,6)
insert into #bar (parent_id,child_id) values (6,4)

Answer 1

Edit

I think I know what's going on and is a great example of why to avoid select * in the first place.

You defined your table with childId first then parentId, but the CTE Foo expects parentId then childId,

So essentially when you say select #bar.* your saying select childId, parentId but your putting that into parentId, child. This results in a n-level recursive expression as you go to join back on yourself.

So this is not a bug in SQL.

Moral of the lesson: Avoid Select * and save yourself headaches.

Answer 2

I would say it was probably a deliberate choice by the programmers as a safety value because you have a union and all parts of a union must have the same number of fields. I never use select * in a union so I can't say for sure. I tried on SQL Server 2008 and got this message: Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

That seems to support my theory.

Select * is a poor technique to use in any prodcution code, so I don't see why it would be a problem to simply specify the fields.