Is it a SQL's bug?

There is not a field named as 'CRSID' in the table TMatch but the SQL Server
would return all the records in table tcrsmgr:
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT CRSID
FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')))This is correct behavior because the TCRSMGR.CRSID is used in the subquery.
To avoid ambiguity, qualify column names in the subquery with the desired
table name or alias like the example below. In this case, you'll get an
error because the TMatch.CRSID column doesn't exist.
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
Hope this helps.
Dan Guzman
SQL Server MVP
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>|||This is expected behavior. The inner reference to CRSID does
not include a table alias or table name. As a result, it is resolved
as TMatch.CRSID if that column exists, and if not, to
TCRSMGR.CRSID, if that column exists (which it does -
if it did not, you would get an error).
You now have a correlated subquery, and for each row of
TCRSMGR, that correlated subquery is
SELECT TCRSMGR.CRSID FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')
So long as CRSID has at least one row for which
tDATE = dbo.fDateOf('2005-7-20'), then the WHERE
clause of the entire query is true, and so all rows of
TCRSMGR will be returned.
Outer references must always be valid in subqueries,
or it would be impossible to write a correlated subquery.
For example, no one thinks it's a bug that this works (to
select the biggest order for each employee)
select OrderID, OrderDate, OrderTotal
from Orders as O1
where OrderTotal = (
select max(OrderTotal)
from Orders as O2
where O2.EmployeeID = O1.EmployeeID
)
The reference to O1.EmployeeID is perfectly valid.
Here, the O1 alias is required to avoid ambiguity, but
aliases can be omitted when there is no chance of
ambiguity, and unfortunately in your case, omitting the
alias caused a programming error to go unnoticed.
Here's another example that might not seem so surprising
if not useful:
select * from T
where thisColumn = (
select T.thisColumn
)
You would expect this to return all rows of T with
non-null thisColumn values. Though there is not
even a table mentioned in the subquery, the reference
to T.thisColumn is valid and correlates with the rows
of the outer query. Since thisColumn would not be
ambiguous here, the same query can be written as
select * from T
where thisColumn = (
select thisColumn
)
or, if table X has at least one row,
select * from T
where thisColumn = (
select thisColumn from X
)
The moral of the story? In queries that refer to more
than one table, if not always, qualify columns with the
table you think they come from.
Had you done this here, and written
SELECT *
FROM dbo.TCRSMGR
WHERE (dbo.TCRSMGR.CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
you would have caught the programming error. Most all programming
languages are like this, in allowing inner declarations to override outer
ones, while allowing all outer declarations to be visible within sub-blocks,
if there is no shadowing inner declaration.
int i, j;
...
{
int i, k;
// you can refer to i, j and k here. j refers to the variables declared
// in the outer block, and i and k refers to the variable declared in
// the inner block.
Steve Kass
Drew University
Half Nitto wrote:

>There is not a field named as 'CRSID' in the table TMatch but the SQL Serve
r
>would return all the records in table tcrsmgr:
>SELECT *
>FROM dbo.TCRSMGR
>WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>
>|||Have a look at
http://toponewithties.blogspot.com/...es_archive.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>

is it a bug?

it is the second time i am writing this problem.
I have a view named x_vw and while ? exec it as
select * from x_vw where field1='a1'
it returns the results which are field1='a1' but when I exec the query as
select * from x_vw
it returns all results but eccept the results which are field1='a1'
select * from X_VW where SiparisNo='a1'
returns data I want but the same data doesnt exists in the resultset of
select * from X_VW
its funny that I find a stupid solution for a stupid problem:))
select * from X_VW where SiparisNo like '%%'
I'm starving for explanation
is it a bug?
if so how can I report a bug to Microsoft?
CREATE view X_VW
as
SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
sd.EvrakNo AS 'SiparisNo',
sd.MalKodu,
s.MalAdi,
SUM(sd.Miktar) AS 'Siparis',
dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'iptalMiktar',
dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'Kars?lanan',
SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu
) as 'Kalan',
dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGiris
',
sh.EkSipNo,sh.aciklama,sh.SiparisTip,
s.kod1,
s.kod2,
s.kod3,
s.kod4,
s.kod5,
gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizkod
',
sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
FROM gamet2003..SIP_D sd
inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.EvrakNo
= sd.EvrakNo
inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
c.HesapKodu
inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
sd.MalKodu
left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
and exists(select 1 from
(
select x.sirketkod,x.evrakno
from
(
select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp
where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
sp.oldsirano=th.sirano
and (sp.beklet is null or sp.beklet=0)
group by th.sirketkod,th.evrakno
having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
union all
select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
where (td.beklet is null or td.beklet=0)
group by td.sirketkod,td.oldevrakno
having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
)X
group by x.sirketkod,x.evrakno
)y
where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
null or sd.beklet=0)
group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.kod
3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkodHi
Call Microsoft PSS.
http://support.microsoft.com/common/international.aspx
You will be expected to be able to give them sufficient data to reproduce
the problem.
If it is not a bug, you will be charged for the support.
Regards
Mike
"POKEMON" wrote:

> it is the second time i am writing this problem.
> I have a view named x_vw and while ? exec it as
> select * from x_vw where field1='a1'
> it returns the results which are field1='a1' but when I exec the query as
> select * from x_vw
> it returns all results but eccept the results which are field1='a1'
> select * from X_VW where SiparisNo='a1'
> returns data I want but the same data doesnt exists in the resultset of
> select * from X_VW
> its funny that I find a stupid solution for a stupid problem:))
> select * from X_VW where SiparisNo like '%%'
> I'm starving for explanation
> is it a bug?
> if so how can I report a bug to Microsoft?
>
> CREATE view X_VW
> as
> SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
> sd.EvrakNo AS 'SiparisNo',
> sd.MalKodu,
> s.MalAdi,
> SUM(sd.Miktar) AS 'Siparis',
> dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'iptalMiktar',
> dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'Kars?lanan',
> SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKo
du) as 'Kalan',
> dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGir
is',
> sh.EkSipNo,sh.aciklama,sh.SiparisTip,
> s.kod1,
> s.kod2,
> s.kod3,
> s.kod4,
> s.kod5,
> gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
> Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizk
od',
> sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
> FROM gamet2003..SIP_D sd
> inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.Evrak
No
> = sd.EvrakNo
> inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
> c.HesapKodu
> inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
> sd.MalKodu
> left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
> s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
> WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
> and exists(select 1 from
> (
> select x.sirketkod,x.evrakno
> from
> (
> select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
> gamet2003..SIP_D sp
> where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
> sp.oldsirano=th.sirano
> and (sp.beklet is null or sp.beklet=0)
> group by th.sirketkod,th.evrakno
> having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
> union all
> select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
> left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
> sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
> left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
> sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
> where (td.beklet is null or td.beklet=0)
> group by td.sirketkod,td.oldevrakno
> having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
> )X
> group by x.sirketkod,x.evrakno
> )y
> where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
> null or sd.beklet=0)
> group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
> sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.k
od3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkod
>|||Hi POKEMON,
Your current view is very complex, too complex to analyse over a
newsgroup (and missing DDL and sample data). But still: It sounds like a
bug. The big question is: is this a known / documented bug or a new bug.
There are some known issues relating to parallellism. I would start by
adding the query hint OPTION (MAXDOP 1) when querying the view. So for
example:
select * from X_VW option (maxdop 1)
And of course, make sure you are running the latest SQL-Server service
pack (3a).
If money is not an issue, then you can contact Microsoft Support (as
posted by Mike). Otherwise, it makes sense to isolate the problem. You
can do this by removing all columns, UDFs, joins, etc. that do not
influence the problem.
For example: does the problem still remain if all UDFs are removed? Does
the problem still remain if the entire EXISTS subquery is removed? Does
the problem still remain if all outer joins are removed? Does the
problem still remain if you rewrite "gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp" as an INNER JOIN? Does the problem still remain if
you remove all OR operators by rewriting all "<something> is null or
<something> = 0" to "<something> is null"?
In the end, Microsoft will need a script to reproduce the problem.
HTH,
Gert-Jan

Is is possible to change the "name" of an instance

theHi,
Have a named instance of SQL Server, but have discovered that the application for which the SQL Server was installed will not work with a named instance. So, is there a way to change the instance to become a default one, or will it require a full instance
to create the default instance ?
Thanks
Best bet is to install a default instance, then detach your databases on the
named instance and attach them to the default instance.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:BD6F0DF8-457F-47D6-B39D-F75420357363@.microsoft.com...
> theHi,
> Have a named instance of SQL Server, but have discovered that the
> application for which the SQL Server was installed will not work with a
> named instance. So, is there a way to change the instance to become a
> default one, or will it require a full instance to create the default
> instance ?
> Thanks
|||Thanks for that Richard. Pretty much what I figured.
We are actually using an alias to get round the problem, as only a couple of machines need to attach directly to the database.
"Richard Waymire [MSFT]" wrote:

> Best bet is to install a default instance, then detach your databases on the
> named instance and attach them to the default instance.
> --
> Richard Waymire, MCSE, MCDBA
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:BD6F0DF8-457F-47D6-B39D-F75420357363@.microsoft.com...
>
>