Synapse Serverless Pool View defined using SELECT * on OPENROWSET query with columns defined causes SQL71501 Error
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
- SqlPackage or DacFx Version: 162.1.167.1
- .NET Framework (Windows-only) or .NET Core:
- Environment (local platform and source/target platforms):
**Steps to Reproduce:**
1. Consider this valid TSQL create for a serverless pool database
`CREATE VIEW dbo.foo
AS
SELECT
src.*
FROM
OPENROWSET(
BULK 'raw/scm6/dbo/foo/*.parquet',
DATA_SOURCE = 'dls',
FORMAT='PARQUET'
) with (databaseName varchar(128), ID int) as src`
2. Create a View on top of this
`CREATE VIEW dbo.bar AS SELECT databaseName, ID FROM dbo.foo;`
this will throw: Build error SQL71501: on both columns
3. adjusting the view to explicitly call the columns seems to work fine:
`CREATE VIEW dbo.foo
AS
SELECT
src.databaseName
,src.ID
FROM
OPENROWSET(
BULK 'raw/scm6/dbo/foo/*.parquet',
DATA_SOURCE = 'dls',
FORMAT='PARQUET'
) with (databaseName varchar(128), ID int) as src`
I get that select * is potentially an anti-pattern but the sql engine / parser seems to be able to sort this out and the code works fine on my serverless pool. Also notable is the fact that the columns are defined in the with statement I believe I have seen intellisense even suggest the column names when typing the fixed version of the view.
Extracting the code into a database project in either ADS or VS2022 fails to build due to SQL71501.
I have thousands of such errors due to the same issue. I'm not entirely sure this is a DacFx Error but hoping to get some clarification.
Happy to provide a database project file on github with a sample if that helps speed triage / fix.
**Did this occur in prior versions? If not - which version(s) did it work in?**
unsure
(DacFx/SqlPackage/SSMS/Azure Data Studio)
Contributor guide
Research direction
Start with the reproduced SQL views using OPENROWSET, an explicit WITH column list, and SELECT *; then build the database project to observe SQL71501 for the dependent view. Done means the valid serverless-pool view pattern builds without SQL71501 errors while retaining the defined columns.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100