SQL Server incorrectly accepts prefixes in column list of INSERT statements

SQL Server incorrectly accepts prefixes in column list of INSERT statements by Erland Sommarskog

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	311881
Opened:	11/22/2007 2:06:47 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

If you add a prefix to a column in an INSERT list, SQL Server gladly accepts this,
although the prefix makes no sense.

Fixing this could potentially break existing code, so if this is addressed in SQL 2008,
it seems a good idea to permit it in compatibility mode 90.

At very least, the syntax should be deprecated.

I've tried this in SQL 2000, SQL 2005 and SQL 2008 and approve it.

Details (expand)

English

SQL Server 2008 November CTP

SQL Engine

Win2003 Enterprise Server (SP2)

US English

CREATE TABLE nisse(hult int NOT NULL)
go
INSERT nisse(ab.xx.hult)
VALUES(12)
go
DROP TABLE nisse

(1 row(s) affected)

Syntax error.

0 attachments

Posted by Erland Sommarskog on 8/10/2008 at 12:09 PM

This was close as fixed, but since the syntax is accepted in SQL 2008 RTM
in compat mode 100, I repoen it. I did not even get a deprecation event in Profiler.

Posted by Microsoft on 3/5/2008 at 6:50 PM

Hi Erland

As an update: We are planning on restricting the column names to simple column names as defined in the SQL-2006 standard for the MERGE statement and are considering to put the fully qualified names for column names for the normal INSERT statement on the deprecation path in the next release.

Thanks for your report. I will leave this comment open until we have finalized our deprecation approach.

Best regards
Michael

Posted by Microsoft on 1/17/2008 at 12:38 PM

Hi,

Thankyou for this bug report. The language parser skips over the prefix and ends up doing "the right thing"; but detecting and explicity rejecting that extra token would improve useability.

The bug is now assigned to the Development Team to fix.

Thanks,

Jim