Re: A Bug about SQL Query Analysis(SQL 2000 AND SQL Server 2005)

Oh...That's my fault, Sorry Davi, I got it, that's my fault. Thank u again :)


Lorry

2006-07-04

-----------------------------------------------------------------------------------
"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
--


.

Relevant Pages

  • Re: A Bug about SQL Query Analysis(SQL 2000 AND SQL Server 2005)
    ... LEFT JOIN Lorry ON TmpTotal.id=Lorry.id ... cinvcode is correctly interpreted as an *alias* for the column Lorry.id ... therefore the query is perfectly legal in standard SQL syntax. ... you are still using the RTM version of SQL Server ...
    (microsoft.public.sqlserver.mseq)
  • Re: A Bug about SQL Query Analysis(SQL 2000 AND SQL Server 2005)
    ... You could follow my way to know the bug. ... 1.In TempDB DataBase, ... write the table name before the column, and the Query Analysis also told the ... I test it in many computers, and i'm sure the SQL Server 2005 has the same ...
    (microsoft.public.sqlserver.mseq)