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

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

Post by TG9ycnkgQX » Wed, 05 Jul 2006 11:47:02


y 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:

 
 
 

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

Post by TG9ycnkgQX » Wed, 05 Jul 2006 15:31:02

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


Lorry

2006-07-04

-----------------------------------------------------------------------------------
"Lorry Astra" wrote:


 
 
 

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

Post by David Port » Wed, 05 Jul 2006 15:43:03

That's a bit different to your original example. There is no bug here.
In the following query:

SELECT
Lorry.id
cinvcode
FROM TempDB..[Lorry1] AS TmpTotal
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. It's
equivalent to:

SELECT Lorry.id AS cinvcode
FROM TempDB..[Lorry1] AS TmpTotal
LEFT JOIN Lorry ON TmpTotal.id=Lorry.id

The AS keyword is optional.

Having said that, you are still using the RTM version of SQL Server
2000 so I strongly recommend you upgrade it.

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://www.yqcomputer.com/ (en-US,SQL.90).aspx