Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 sql server bug?

Author  Topic 

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 08:49:26
A friend of mine was doing some sql stuff and stumbled accross this:
http://vidmar.net/weblog/archive/2006/08/17/3044.aspx

Is this a bug or a "feature"?
because behavior like this is weirding me out!



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 08:59:31
No, it is not a bug.

When ID is 3 or more, the SET operation for AnotherID does not happen. Thus AnotherID still has old value.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 09:02:17
ok yes... but shouldn't the declare part clear the previous value?
you can't do this in c++ or c# or any other language i know of.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 09:04:21
But it is strange, since you can reproduce the bevahiour without a cursor.
DECLARE @t TABLE (Id int)

INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (2)
INSERT INTO @t VALUES (3)
INSERT INTO @t VALUES (4)

DECLARE @Id int

select @id = min(id) from @t


WHILE @id is not null
BEGIN
PRINT 'Id = ' + ISNULL(CAST(@Id AS varchar), 'NULL')
DECLARE @AnotherId int
IF @Id <= 2
SET @AnotherId = @Id
PRINT 'AnotherId = ' + ISNULL(CAST(@AnotherId AS varchar), 'NULL')

select @id = min(id)
from @t
where id > @id

END


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 09:06:31
yes, cursor is irrelevant



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 09:08:11
As you would expect,

declare @DECLARE @AnotherId int

last, after end, gives an error

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 09:10:26
yup tried that too, putting it in front of course works
It's just weird...



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 09:13:05
More simplified...
DECLARE @x int
--DECLARE @AnotherId int

select @x = 3

while @x > 0
BEGIN
DECLARE @AnotherId int
SET @AnotherId = @x

-- DECLARE @x int
-- DECLARE @AnotherId int

PRINT 'AnotherId = ' + ISNULL(CAST(@AnotherId AS varchar), 'NULL')

select @x = @x - 1
END
Uncommenting any of the two rows gives an error.

The thing that comes to my mind is that a WHILE LOOP creates a scope if its own.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 09:17:20
i don't think so.... because then putting DECLARE @AnotherId int in front of the while shouldn't work
in the while's scope.

variables are valid only in a scope.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 09:20:22
Did you see my last post? Uncommenting --DECLARE @AnotherId int gives an error. Exactly as it supposed to do if WHILE creates a scope.

Also declaring @AnotherID twice in the WHILE gives an error.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 09:27:41
uncommenting any of the 2 rows in your code should produce an error and it correctly does it.
The problem here is acctually the order of declaring variables
in C# it's like so:

int i = 0;
while(i<4)
{
int k; // here k will be 0 every time the loop goes in it. it will be redeclared, sql's doesn't
k = i
i++
}

and i'd assume that sql's while would behave similarly.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 09:32:57
Neither does VB redeclare variables.

SQL
DECLARE @x int

select @x = 5

while @x > 0
BEGIN
DECLARE @AnotherId int
if @x % 2 = 0 SET @AnotherId = isnull(@AnotherId, 5) - 1

PRINT 'AnotherId = ' + ISNULL(CAST(@AnotherId AS varchar), 'NULL')

select @x = @x - 1
END
Output from SQL
AnotherId = NULL
AnotherId = 4
AnotherId = 4
AnotherId = 3
AnotherId = 3
VB
Private Sub Command1_Click()

Dim x As Long

x = 5

While x > 0

Dim s As Long

If x Mod 2 = 0 Then s = s - 1

Debug.Print x, s

x = x - 1

Wend

End Sub
Output from VB is
 5             0 
4 -1
3 -1
2 -2
1 -2

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 09:49:08
ok... well i learned something new today:
in c# this won't build:

int i = 0;
while(i<4)
{
int k;
if (i < 2)
{
k = i;
}
Console.WriteLine("i: " + i.ToString());
Console.WriteLine("k: " + k.ToString()); // errors out with: Use of unassigned local variable 'k'
i++;
}


this will build and going through with the debugger it behaves the same as VB and SQL.

int i = 0;
while (i < 4)
{
int k;
if (i < 2)
{
k = i;
}
i++;
}


Thinking about it it does make some sense with memory management etc,
but it's not really intuitive if you ask me.




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-17 : 11:30:01
Looks like it is just a matter of the DECLARE occuring before a statement the uses the declared variable.

Obviously the 1=2 condition in the code below is not true, but the variable is declared anyway.

if 1=2
begin
declare @x int
set @x = 1
Print '1=2'
end

select x=@x

Result:

x
-----------
NULL

(1 row(s) affected)




CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 11:33:49
well i've found a definite answer.
A variable is declared only once and every manipulation of it must be done with set/select.
any subsequent run over the same declare @var int line does nothing as the variable is already declared.

all variables are declared in the batch like in VB (ignores if's)and not like in C# (doesn't ignore if)




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-17 : 11:36:27
>>Obviously the 1=2 condition in the code below is not true, but the variable is declared anyway.

Then isnt this a strange?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 11:39:17
no it's not anymore



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 11:42:53
I think it is a "feature" to help programmers if they declare variables in the wrong place.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 11:48:24
well as i dislike VB i'll call it a bug



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-17 : 11:56:06
quote:
Originally posted by spirit1

well as i dislike VB i'll call it a bug



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp



Planning on opening a case with Microsoft to get a bug fix?



CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 12:26:42
nope

someone who likes VB will argue it's a feature... so what's the use...
just knowing this is fine by me.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
    Next Page

- Advertisement -