September 25, 2012 at 2:52 pm
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
September 25, 2012 at 2:58 pm
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
September 25, 2012 at 4:02 pm
If one of these tables is actually a view, please post the DDL for the view.
September 25, 2012 at 4:08 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2012 at 6:19 am
w.unidade is int and b.unidade varchar
September 26, 2012 at 6:27 am
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
September 26, 2012 at 7:20 am
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
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
September 26, 2012 at 7:35 am
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
September 26, 2012 at 7:57 am
Dear Evil,
Let me see and understand you.
If we have a query follow:
selectw.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
September 26, 2012 at 8:06 am
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
September 26, 2012 at 8:26 am
antonio.estima 4150 (9/26/2012)
...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
Put the filter into the OPENQUERY();
FROM OPENQUERY(BS,'select * from PDTABLE WHERE SUBSTRING(nomeint,1,1) = ''P''')
...where it will safely run when/where you expect it to.
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
September 26, 2012 at 11:05 am
antonio.estima 4150 (9/26/2012)
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?
Yes, but, 2k5 could do the same thing, it just used different optimization pathing depending on the statistics. Neither of them enforced order without using the OPTION (FORCE ORDER) on the query. The old engine just preferred to do it one way for this particular query and dataset, and the new engine prefers to do it the other.
As Gail reinforced, there is no ordering of a query without manual intervention. OPENQUERY is another way I'd forgotten to enforce this, though it's not one I'd typically prefer to use... but I forget why I came to that decision.
Basically, for a while there you got lucky and the plan stayed in the way you needed it without direct intervention.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2012 at 12:18 pm
Dear Gail,
As this query came from sql server 2000 environment, I thought the run update statisics in sql server 2008 r2 enviroment. That would solve this issue?
Thanks for your help.
Regards,
Antonio Estima
September 26, 2012 at 12:24 pm
Probably not.
You absolutely should update your statistics, but that's not going to change the root fact that there is no guaranteed order of query processing. Even if it does temporarily fix the problem, you still have a piece of code prone to errors because it assumes something that does not exist.
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
September 27, 2012 at 1:47 am
Evil Kraig F (9/26/2012)
...OPENQUERY is another way I'd forgotten to enforce this, though it's not one I'd typically prefer to use... but I forget why I came to that decision.....
Craig, interesting - putting the filter into the OPENQUERY would potentially reduce the number of rows returned "down the wire" - what disadvantage could there be?
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply