Home Dashboard Directory Help
Search

[Datadude] Derived table with the same alias as one of the columns in that derived table incorrectly causes a warning by Jamie Thomson


Status: 

Closed
 as Deferred Help for as Deferred


5
0
Sign in
to vote
Type: Bug
ID: 635058
Opened: 1/7/2011 6:14:07 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

Hi,
Discovered an annoying little bug in Datadude. If the following conditions are met:
a) a SQL statement contains a derived table
b) the alias of that derived table is the same as one of the columns (or an alias of one of the columns) in that derived table
c) the derived table alias is used in the SELECT clause of the outer query

then you'll get a SQL04151 warning:
SQL04151: Procedure: [dbo].[Procedure1] contains an unresolved reference to an object.
Details
Sign in to post a comment.
Posted by Microsoft on 1/20/2011 at 5:08 PM
Jamie/Grant,

Thank you for submitting this issue. We have reproduced this issue and have added it to our product backlog. It will be considered for a service release of Visual Studio Database Tools.

Nice catch. Yes, it appears our interpreter has an issue when the there is an ambiguous alias between column and table; and column source is an expression.


Thanks,

Barclay Hill
Sr. Program Manager | Visual Studio Product Team (Data Tools) | Microsoft Corporation
Posted by Microsoft on 1/9/2011 at 7:35 PM
Thanks for your feedback.

We are rerouting this issue to the appropriate group within the Visual Studio Product Team for triage and resolution. These specialized experts will follow-up with your issue.

Posted by Jamie Thomson on 1/7/2011 at 7:53 AM
More on this, discussin offline with Grant caused me to investigate further. This does not cause a warning:
CREATE PROCEDURE [dbo].[Procedure1]
AS
SELECT [c].[c]
FROM (
    SELECT column_1 AS [c]
    FROM dbo.Table1
) [c]

but this does:
CREATE PROCEDURE [dbo].[Procedure1]
AS
SELECT [c].[c]
FROM (
    SELECT column_1 + 1 AS [c]
    FROM dbo.Table1
) [c]


Weird!!
Posted by Grant Fritchey on 1/7/2011 at 7:50 AM
Using the repro script, I could generate the error. But when I used this script:
CREATE PROCEDURE [dbo].[Procedure1]
    
AS
    SELECT [myId].[myId] FROM (SELECT [s].ID AS [myId] FROM dbo.StatsTest AS s) AS [myId]
RETURN 0

I didn't get an error. Until I changed this script to this:

CREATE PROCEDURE [dbo].[Procedure1]
    
AS
    SELECT [myId].[myId] FROM (SELECT CAST(1 AS INT) AS [myId] FROM dbo.StatsTest AS s) AS [myId]
RETURN 0

Then I got the error again.
Posted by Microsoft on 1/7/2011 at 6:58 AM
Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(http://support.microsoft.com)
Sign in to post a workaround.