wrong result set

[Peter]

Hello all,

I have the following t-sql batch:
create procedure stp_test
(
@p_date1 as datetime = null,
@p_date2 as datetime = null
)
as begin
set @p_date1 = isnull(@p_date1, <some expression>)
set @p_date2 = isnull(@p_date2, <some other expression>)

select
<a lot of columns>
from
<some table>
inner join <some other table> on <expression>
inner join <dirived table> on <expression>
where
date1 <= @p_date1 and
date2 <= @p_date2 and
(
date1 >= @p_date1 or
date2 >= @p_date2
)
end
go

exec stp_test

This gives a WRONG resultset.

When I replace the variables with hardcoded values in the right format, the
returned result set is CORRECT, as follows

where
date1 <= 'hard coded date value 1' and
date2 <= 'hard coded date value 2' and
(
date1 >= 'hard coded date value 1' or
date2 >= 'hard coded date value 2'
)

When I elimate the derived table with a temporary table, the returned result
set is CORRECT

When I store the parameters in a local variable, and use the local variable,
the returned result set is CORRECT, as follows

create procedure stp_test
(
@p_date1 as datetime = null,
@p_date2 as datetime = null
)
as begin
declare @l_date1 datetime
declare @l_date2 datetime

set @l_date1 = @p_date1
set @l_date2 = @p_date2

set @l_date1 = isnull(@l_date1, <some expression>)
set @l_date2 = isnull(@l_date2, <some other expression>)

select
<a lot of columns>
from
<some table>
inner join <some other table> on <expression>
inner join <dirived table> on <expression>
where
date1 <= @l_date1 and
date2 <= @l_date2 and
(
date1 >= @l_date1 or
date2 >= @l_date2
)
end
go

When I put less columns in the select list, the returned result set is
CORRECT, it doesnt make sense wich columns I remove from the select list.

The tables are not small (500.000 rows) and also is the result set. I use
this construction elsewhere, on other table combinations, but dont have
problems. So the content of the data makes difference.

Seems to me as a bug.

My question is: Can I say the derived table is instable in SQL server and
causes the problem of the wrong result set here?

Peter

[David Portas]

Peter wrote:

Hello all,

I have the following t-sql batch:
create procedure stp_test
(
@p_date1 as datetime = null,
@p_date2 as datetime = null
)
as begin
set @p_date1 = isnull(@p_date1, <some expression>)
set @p_date2 = isnull(@p_date2, <some other expression>)

select
<a lot of columns>
from
<some table>
inner join <some other table> on <expression>
inner join <dirived table> on <expression>
where
date1 <= @p_date1 and
date2 <= @p_date2 and
(
date1 >= @p_date1 or
date2 >= @p_date2
)
end
go

exec stp_test

This gives a WRONG resultset.

When I replace the variables with hardcoded values in the right format, the
returned result set is CORRECT, as follows

where
date1 <= 'hard coded date value 1' and
date2 <= 'hard coded date value 2' and
(
date1 >= 'hard coded date value 1' or
date2 >= 'hard coded date value 2'
)

When I elimate the derived table with a temporary table, the returned result
set is CORRECT

When I store the parameters in a local variable, and use the local variable,
the returned result set is CORRECT, as follows

create procedure stp_test
(
@p_date1 as datetime = null,
@p_date2 as datetime = null
)
as begin
declare @l_date1 datetime
declare @l_date2 datetime

set @l_date1 = @p_date1
set @l_date2 = @p_date2

set @l_date1 = isnull(@l_date1, <some expression>)
set @l_date2 = isnull(@l_date2, <some other expression>)

select
<a lot of columns>
from
<some table>
inner join <some other table> on <expression>
inner join <dirived table> on <expression>
where
date1 <= @l_date1 and
date2 <= @l_date2 and
(
date1 >= @l_date1 or
date2 >= @l_date2
)
end
go

When I put less columns in the select list, the returned result set is
CORRECT, it doesnt make sense wich columns I remove from the select list.

The tables are not small (500.000 rows) and also is the result set. I use
this construction elsewhere, on other table combinations, but dont have
problems. So the content of the data makes difference.

Seems to me as a bug.

My question is: Can I say the derived table is instable in SQL server and
causes the problem of the wrong result set here?

Peter

Please could you post some working code to reproduce the problem
(CREATE TABLE and INSERTs included). Also tell us what version, edition
and service pack you are using. That's essential information if you
think you've found a bug. "Wrong result" is also not a good description
of the problem. Show us or explain what result you expected and what
you actually got.

--
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/m...S,SQL.90).aspx
--

[Peter]

David,

I am not able now to post insert and the final code. It is too much for this
newsgroup. When I have to put real data, I have to encrypt it also. Maybe
then the behavior als changing too.

I dont get an errormessage. The problem is, not all results are in the
resultset I expected. My expectation is correct, because, if I change
something as i stated, the resultset is like expected.

I use MS SQL Server 2000 sp4. No hotfixes.

Thank you David.

"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...

Peter wrote:

Hello all,

I have the following t-sql batch:
create procedure stp_test
(
@p_date1 as datetime = null,
@p_date2 as datetime = null
)
as begin
set @p_date1 = isnull(@p_date1, <some expression>)
set @p_date2 = isnull(@p_date2, <some other expression>)

select
<a lot of columns>
from
<some table>
inner join <some other table> on <expression>
inner join <dirived table> on <expression>
where
date1 <= @p_date1 and
date2 <= @p_date2 and
(
date1 >= @p_date1 or
date2 >= @p_date2
)
end
go

exec stp_test

This gives a WRONG resultset.

When I replace the variables with hardcoded values in the right format,
the
returned result set is CORRECT, as follows

where
date1 <= 'hard coded date value 1' and
date2 <= 'hard coded date value 2' and
(
date1 >= 'hard coded date value 1' or
date2 >= 'hard coded date value 2'
)

When I elimate the derived table with a temporary table, the returned
result
set is CORRECT

When I store the parameters in a local variable, and use the local
variable,
the returned result set is CORRECT, as follows

create procedure stp_test
(
@p_date1 as datetime = null,
@p_date2 as datetime = null
)
as begin
declare @l_date1 datetime
declare @l_date2 datetime

set @l_date1 = @p_date1
set @l_date2 = @p_date2

set @l_date1 = isnull(@l_date1, <some expression>)
set @l_date2 = isnull(@l_date2, <some other expression>)

select
<a lot of columns>
from
<some table>
inner join <some other table> on <expression>
inner join <dirived table> on <expression>
where
date1 <= @l_date1 and
date2 <= @l_date2 and
(
date1 >= @l_date1 or
date2 >= @l_date2
)
end
go

When I put less columns in the select list, the returned result set is
CORRECT, it doesnt make sense wich columns I remove from the select list.

The tables are not small (500.000 rows) and also is the result set. I use
this construction elsewhere, on other table combinations, but dont have
problems. So the content of the data makes difference.

Seems to me as a bug.

My question is: Can I say the derived table is instable in SQL server and
causes the problem of the wrong result set here?

Peter

Please could you post some working code to reproduce the problem
(CREATE TABLE and INSERTs included). Also tell us what version, edition
and service pack you are using. That's essential information if you
think you've found a bug. "Wrong result" is also not a good description
of the problem. Show us or explain what result you expected and what
you actually got.

--
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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Peter (so*****@someplace.com) writes:

My question is: Can I say the derived table is instable in SQL server and
causes the problem of the wrong result set here?

No, that would be to jump to conclusion. The cause for the problem is
likely to be much more specific. But since you did not include the actual
code, nor the underlying tables, there is not information enough to
recreate the problem. (And most likely tables and queries would not be
sufficient, as the data volumes may be required to get the incorrect
query plan that you apparently get.)

If you get this problem with SQL 2005, I suggest that you try extract
the tables to a database of its own, and ensure that the problem appears
there. Then submit a bug on http://lab.msdn.microsoft.com/ProductFeedback/.
If the data is not sensitive, you could attach the database to the bug.
Else just say in the error report that the database is available on
request. Obviously, the bug report should include information about what is
wrong about the result set.

If you get this problem with SQL 2000, I don't think there much that can
be done about it, since there apparently is a workaround. But if you are
able to reproduce the problem with a minimal set of data, so that you could
compose a script that creates tables, populates them and creates the
procedure you can post it to the newsgroup if you like. If nothing else,
people here can investigate if the problem is in SQL 2005 as well.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx