"Lorry Astra" wrote:

> My System Environment:
> WIN2000 SP4
> SQL Server Version: SQL 2000 Standard Edition [8.00.194(RTM)]
> You could follow my way to know the bug.
> 1.In TempDB DataBase,
>
> CREATE TABLE Lorry1(
> ID INT NOT NULL,
> cInvcode NVarchar(20) NULL,
> cCode NVarchar(20)NULL
> )
>
> 2.To Insert Something to Lorry1 Table
>
> INSERT INTO Lorry1(ID,cInvCode,cCode)
> VALUES(1,N'aaa',N'1')
> INSERT INTO Lorry1(ID,cInvCode,cCode)
> VALUES(2,N'bbb',N'2')
> INSERT INTO Lorry1(ID,cInvCode,cCode)
> VALUES(3,N'ccc',N'3')
>
> 3.In Master DataBase,
> CREATE TABLE Lorry(
> ID INT NOT NULL,
> cInvcode NVarchar(20) NULL,
> )
>
> 4.To Insert Something to Lorry Table
> INSERT INTO Lorry(ID,cInvCode)
> VALUES(1,N'aaa')
> INSERT INTO Lorry(ID,cInvCode)
> VALUES(5,N'bbb')
> INSERT INTO Lorry(ID,cInvCode)
> VALUES(6,N'ccc')
>
> 5.Go to Master Database, and to execute the following sentence:
> SELECT
> Lorry.id
> cinvcode
> FROM TempDB..[Lorry1] AS TmpTotal
> LEFT JOIN Lorry ON TmpTotal.id=Lorry.id
>
> plus: you must obey the formation of the sentence,and we can find 2 bugs:
> 1. Though I didn't write "," between "Lorry.id" and "cinvcode", the Query
> Analysis says the sentence is right.
> 2. the Column "cinvcode" belongs two tables and in this sentence i didn't
> write the table name before the column, and the Query Analysis also told the
> sentence didn't have any error.
>
> I test it in many computers, and i'm sure the SQL Server 2005 has the same
> error too. Please tell me how to resolve it. Thanks.
>
>
> Lorry
>
> 2006-07-04
>
> ------------------------------------------------------------------------------------------------
>
>
> "David Portas" wrote:
>
> > Lorry Astra wrote:
> > > I found a bug about SQL Query Analysis, if u write a sql statement like this:
> > >
> > > SELECT Tmptable.col1
> > > table.col2
> > > FROM TEMPDB..[Table] AS Tmptable
> > > LEFT JOIN table ON Tmptable.ID=table.ID
> > >
> > > we should know, this sentence is wrong, 'cos it seems like something wrong
> > > between "Tmptable.col1" and "table.col2", the right formation is "
> > > Tmptable.col1 , table.col2"
> > >
> > > but SQL Query Analysis says the sentence that i feel wrong is just right, it
> > > can show a query result based on it. I think it should be a bug.
> > >
> > > Plus:
> > > the right sentence should be like this:
> > >
> > > SELECT Tmptable.col1 ,
> > > table.col2
> > > FROM TEMPDB..[Table] AS Tmptable
> > > LEFT JOIN table ON Tmptable.ID=table.ID
> > >
> >
> >
> > You haven't told us what version, edition and service pack you are
> > using. In SQL Server 2000 SP4 and SQL Server 2005 SP1 (both Dev
> > edition) your first query gives a syntax error:
> >
> > Server: Msg 102, Level 15, State 1, Line 2
> > Incorrect syntax near '.'.
> >
> > (the only modification I made was to bracket the name "table" because
> > it's a reserved word).
> >
> >
> > > Could any one tell me why? and how to resolve it by patching. Thanks.
> >
> > http://www.microsoft.com/sql/sp1.mspx
> > http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5
> >
> >
> > --
> > David Portas, SQL Server MVP
> >
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> >
> > SQL Server Books Online:
> > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > --
> >