SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong


Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong

Author
Message
antonio.estima 4150
antonio.estima 4150
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 165
Hello,

We have a problem in the query within the attached file. When I run in sql server 2000 it returns the result without errors, but when I run the SQL Server 2008 R2 SP2 it returns the error message: Msg 245, Level 16, State 1, Line 1 Conversion failed converting the varchar value When 'PA 'to data type int.
When I comment the line of the join "and w.unidade = b.Unidade" in SQL Server 2008 r2 sp2 works without showing any error. Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


What could is happening? Someone help us?

Query :
Regards,

Antonio Estima
Attachments
query_sql2008r2sp2.txt (19 views, 720 bytes)
tim_harkin
tim_harkin
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 920
antonio.estima 4150 (9/25/2012)
Hello,

We have a problem in the query within the attached file. When I run in sql server 2000 it returns the result without errors, but when I run the SQL Server 2008 R2 SP2 it returns the error message: Msg 245, Level 16, State 1, Line 1 Conversion failed converting the varchar value When 'PA 'to data type int.
When I comment the line of the join "and w.unidade = b.Unidade" in SQL Server 2008 r2 sp2 works without showing any error. Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


What could is happening? Someone help us?

Query :
Regards,

Antonio Estima


What data types are these: w.unidade, b.Unidade
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1604 Visits: 8586
If one of these tables is actually a view, please post the DDL for the view.



And then again, I might be wrong ...
David Webb
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8707 Visits: 7660
antonio.estima 4150 (9/25/2012)
Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


Your query is executing in a different pattern in the two engines. WHERE clause does not fire prior to joins or any other operation. The entire statement is taken as a whole once you're done and works from there.

You got lucky for a long time in 2k5 and this just never altered the order in the engine on you. You cannot guarantee the WHERE clause firing first without using a few tricks. For an example of the most frustrating version of this process, do a google search on WHERE ISNUMERIC() error. It's quite common in EAV builds too.

Short version, you have two ways out of this. You have to procedurally control the query. The first is dumping only restricted data into a temp table and then using the temp table from there on out (my preferred method). The second is OPTION ( FORCE ORDER), in which you use a subquery for your WHERE clause'd table, then link the subquery as a table to the rest, like so:

select
a.*,
b.*
FROM
(SELECT * from tblA WHERE SomeCol <> 'PA') AS a
JOIN
tblB
ON a.SomeCol = b.SomeCol
OPTION ( FORCE ORDER)



You've overloaded a column, and your best option here is to clean up the source data and remove these workarounds eventually. There is no 'good' method to fix this.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
antonio.estima 4150
antonio.estima 4150
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 165
w.unidade is int and b.unidade varchar
antonio.estima 4150
antonio.estima 4150
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 165
Dear David,

The all objects in this query are not views. They are tables.

The question is this query work very well in sql server 2000 enviroment when put it in sql server 2008 R2 SP2 enviroment don't work very well.

Regards,

Antonio Estima
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16666 Visits: 19557
antonio.estima 4150 (9/25/2012)
... Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" ...


This doesn't make sense.
SUBSTRING (nomeint, 1,1) = 'P' would include "PA"


Here's the query, reformatted:

select   
w.Unidade,
w.Data,
w.Interface,
w.NomeInterface

from (
select
c.unidade,
c.Data,
c.Interface,
c.NomeInterface
from cad_interface c
inner join sam.dbo.Unidade u
on (c.unidade = u.codigounidade)
where left(interface,6) = 'Inform'
and unidade = 999
and data between 20120701 and 20120702
) as w

left outer join (
select
dataint Data,
left(nomeint,11) COLLATE Latin1_General_CI_AS 'Interface',
SUBSTRING(nomeint,4,2) Unidade
from openquery(BS,'select * from PDTABLE')
where SUBSTRING(nomeint,1,1) = 'P'
) as b
on (w.data = b.data
and w.unidade = b.Unidade
and w.nomeinterface = left(b.interface,8))

order by w.data, w.unidade



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89421 Visits: 45284
Evil Kraig F (9/25/2012)
antonio.estima 4150 (9/25/2012)
Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


Your query is executing in a different pattern in the two engines. WHERE clause does not fire prior to joins or any other operation. The entire statement is taken as a whole once you're done and works from there.

You got lucky for a long time in 2k5 and this just never altered the order in the engine on you. You cannot guarantee the WHERE clause firing first without using a few tricks. For an example of the most frustrating version of this process, do a google search on WHERE ISNUMERIC() error. It's quite common in EAV builds too.


This.

The order that a query is processed is not guaranteed. The where clause is not necessarily processed left to right, or right to left, or any other order. It depends on the exec plan created for the query.

Short version, you got lucky on SQL 2000, you need to go and fix your code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


antonio.estima 4150
antonio.estima 4150
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 165
Dear Evil,

Let me see and understand you.

If we have a query follow:

select w.Unidade,
w.Data,
w.Interface,
w.NomeInterface
from ( select c.unidade,
c.Data,
c.Interface,
c.NomeInterface
from cad_interface c
inner join sam.dbo.Unidade u
on(c.unidade=u.codigounidade)
where left(interface,6)='Inform'
and unidade = 999
and data between 20120701 and 20120702 ) as w
left outer join
( select dataint Data,
left(nomeint,11) COLLATE Latin1_General_CI_AS 'Interface',
SUBSTRING(nomeint,4,2) Unidade
from openquery(BS,'select * from PDTABLE')
where SUBSTRING(nomeint,1,1) = 'P' ) as b
on(w.data = b.data
and w.unidade = b.Unidade
and w.nomeinterface = left(b.interface,8))
order by w.data, w.unidade


In SQL Server 2K or 2K5 the WHERE clause where SUBSTRING(nomeint,1,1) = 'P' is run first in the subquery and then it does the join. However, in SQL Server 2K8 the engine return all informations and then after it does the join?

Thanks for help

Antonio Estima
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89421 Visits: 45284
Probably not.

The order that the query will be processed in is not defined or guaranteed. The SQL 2008 query optimiser is just producing a plan where the conversion is done before the non-numeric values are filtered out.

The query optimiser changes in every version, therefore plans can change as well

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search