SS2005 - Simple Query run as a sub-query returns different results  
Author Message
stupy1





PostPosted: Mon Mar 13 04:19:06 CST 2006 Top

SQL Server >> SS2005 - Simple Query run as a sub-query returns different results

I have tried to call MS Tech support this morning and they insist that SS2005
Standard isn't released yet. We're about to go live and I have a quote here
from Dell who say they can definately sell it to me!

Anyway, checkout the following example:
SET NOCOUNT ON
CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))
INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')
--SELECT * FROM #Problem
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
SELECT * from (
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15
DROP TABLE #Problem

I get an error:
"Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value '>500' to data type int."

I know that the IsNumeric function can return some funny results but
replacing it with LIKE '%[0..9]%' or NOT LIKE '>%' AND NOT LIKE 'Error%' does
exactly the same thing. It seems the where statement in the subquery isn't
being enumerated.
Also, making the subquery a view has the same effect.

This is running on SQL Server 2005 Standard x64 release version from my MSDN
subscription.


and 'SPAM-'

SQL Server170  
 
 
SQL





PostPosted: Mon Mar 13 04:19:06 CST 2006 Top

SQL Server >> SS2005 - Simple Query run as a sub-query returns different results What happens when you do this?

SELECT * FROM #Problem
WHERE numinastr NOT LIKE '%[a-z]%'
AND ISNUMERIC(numinastr) = 1

http://www.sqlservercode.blogspot.com/





>I have tried to call MS Tech support this morning and they insist that
>SS2005
> Standard isn't released yet. We're about to go live and I have a quote
> here
> from Dell who say they can definately sell it to me!
>
> Anyway, checkout the following example:
> SET NOCOUNT ON
> CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))
> INSERT INTO #Problem (numinastr) values ('1')
> INSERT INTO #Problem (numinastr) values ('10')
> INSERT INTO #Problem (numinastr) values ('25')
> INSERT INTO #Problem (numinastr) values ('40')
> INSERT INTO #Problem (numinastr) values ('>500')
> INSERT INTO #Problem (numinastr) values ('600')
> INSERT INTO #Problem (numinastr) values ('1000')
> INSERT INTO #Problem (numinastr) values ('error!')
> --SELECT * FROM #Problem
> SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
> SELECT * from (
> SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
> ) a where numinastr>15
> DROP TABLE #Problem
>
> I get an error:
> "Msg 245, Level 16, State 1, Line 15
> Conversion failed when converting the varchar value '>500' to data type
> int."
>
> I know that the IsNumeric function can return some funny results but
> replacing it with LIKE '%[0..9]%' or NOT LIKE '>%' AND NOT LIKE 'Error%'
> does
> exactly the same thing. It seems the where statement in the subquery
> isn't
> being enumerated.
> Also, making the subquery a view has the same effect.
>
> This is running on SQL Server 2005 Standard x64 release version from my
> MSDN
> subscription.
>

> and 'SPAM-'


 
 
David





PostPosted: Mon Mar 13 04:54:54 CST 2006 Top

SQL Server >> SS2005 - Simple Query run as a sub-query returns different results
> I have tried to call MS Tech support this morning and they insist that SS2005
> Standard isn't released yet. We're about to go live and I have a quote here
> from Dell who say they can definately sell it to me!
>
> Anyway, checkout the following example:
> SET NOCOUNT ON
> CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))
> INSERT INTO #Problem (numinastr) values ('1')
> INSERT INTO #Problem (numinastr) values ('10')
> INSERT INTO #Problem (numinastr) values ('25')
> INSERT INTO #Problem (numinastr) values ('40')
> INSERT INTO #Problem (numinastr) values ('>500')
> INSERT INTO #Problem (numinastr) values ('600')
> INSERT INTO #Problem (numinastr) values ('1000')
> INSERT INTO #Problem (numinastr) values ('error!')
> --SELECT * FROM #Problem
> SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
> SELECT * from (
> SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
> ) a where numinastr>15
> DROP TABLE #Problem
>
> I get an error:
> "Msg 245, Level 16, State 1, Line 15
> Conversion failed when converting the varchar value '>500' to data type int."
>
> I know that the IsNumeric function can return some funny results but
> replacing it with LIKE '%[0..9]%' or NOT LIKE '>%' AND NOT LIKE 'Error%' does
> exactly the same thing. It seems the where statement in the subquery isn't
> being enumerated.
> Also, making the subquery a view has the same effect.
>
> This is running on SQL Server 2005 Standard x64 release version from my MSDN
> subscription.
>

> and 'SPAM-'

Try:

SELECT numinastr
FROM (
SELECT
CASE WHEN numinastr NOT LIKE '%[^0-9]%'
THEN numinastr END AS numinastr
FROM #Problem WHERE numinastr NOT LIKE '%[^0-9]%'
) AS A
WHERE numinastr>15 ;

Even though your query may seem to work in SQL Server 2000 this isn't a
bug and nor is it a change to documented behaviour. The evaluation
order of expressions and subqueries is always undefined and will be
determined at compile time by the query optimizer. If your code assumes
a particular order of execution then it can break over any engine
enhancements or any other changes that cause a new plan to be compiled.
Unfortunately it can be hard to spot in advance where those problems
might occur.

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

 
 
Joel





PostPosted: Mon Mar 13 05:53:27 CST 2006 Top

SQL Server >> SS2005 - Simple Query run as a sub-query returns different results SQL,

Replacing your code in the sub-query produces the same error. I think that
David Portas has hit the nail on the head in his post below...see my reply
there.

Cheers,

Joel
 
 
JoelMansford





PostPosted: Mon Mar 13 05:58:27 CST 2006 Top

SQL Server >> SS2005 - Simple Query run as a sub-query returns different results David,

Thanks for your response, what you say makes sense and certainly your code
works. I guess this is because the expression is now in the SELECT list
rather than in the WHERE part.
However, I have to say I find this very worrying as I often query a view. I
have always made the assumption that if I do
SELECT * FROM vwMyView
and then I do
SELECT * FROM vwMyView WHERE Name LIKE 'A%'

then what I see in the first query is exactly the dataset that is queried in
the second query. However it seems this isn't the case. I'm now wondering
how I will reliably re-write many of my queries.
Do you see my point?

Regards,

Joel.
PS. Someone had told me that SS2005 is now free to evaluate expressions in
any order whereas SS2000 was more restricted.




> > I have tried to call MS Tech support this morning and they insist that SS2005
> > Standard isn't released yet. We're about to go live and I have a quote here
> > from Dell who say they can definately sell it to me!
> >
> > Anyway, checkout the following example:
> > SET NOCOUNT ON
> > CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))
> > INSERT INTO #Problem (numinastr) values ('1')
> > INSERT INTO #Problem (numinastr) values ('10')
> > INSERT INTO #Problem (numinastr) values ('25')
> > INSERT INTO #Problem (numinastr) values ('40')
> > INSERT INTO #Problem (numinastr) values ('>500')
> > INSERT INTO #Problem (numinastr) values ('600')
> > INSERT INTO #Problem (numinastr) values ('1000')
> > INSERT INTO #Problem (numinastr) values ('error!')
> > --SELECT * FROM #Problem
> > SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
> > SELECT * from (
> > SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
> > ) a where numinastr>15
> > DROP TABLE #Problem
> >
> > I get an error:
> > "Msg 245, Level 16, State 1, Line 15
> > Conversion failed when converting the varchar value '>500' to data type int."
> >
> > I know that the IsNumeric function can return some funny results but
> > replacing it with LIKE '%[0..9]%' or NOT LIKE '>%' AND NOT LIKE 'Error%' does
> > exactly the same thing. It seems the where statement in the subquery isn't
> > being enumerated.
> > Also, making the subquery a view has the same effect.
> >
> > This is running on SQL Server 2005 Standard x64 release version from my MSDN
> > subscription.
> >

> > and 'SPAM-'
>
> Try:
>
> SELECT numinastr
> FROM (
> SELECT
> CASE WHEN numinastr NOT LIKE '%[^0-9]%'
> THEN numinastr END AS numinastr
> FROM #Problem WHERE numinastr NOT LIKE '%[^0-9]%'
> ) AS A
> WHERE numinastr>15 ;
>
> Even though your query may seem to work in SQL Server 2000 this isn't a
> bug and nor is it a change to documented behaviour. The evaluation
> order of expressions and subqueries is always undefined and will be
> determined at compile time by the query optimizer. If your code assumes
> a particular order of execution then it can break over any engine
> enhancements or any other changes that cause a new plan to be compiled.
> Unfortunately it can be hard to spot in advance where those problems
> might occur.
>
> --
> 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
> --
>
>