RE: OT Rant: SQL Server
If you properly reference all fields, you'll see that it does throw an error. Try [table].USERID and you'll see. Since you don't specify a table, USERID is ambiguous and will allow USERID from the other table in the query. Also, if both tables contained USERID, you'd get an ambiguous column error. Rob -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Thursday, May 03, 2007 10:19 AM To: CF-Talk Subject: Re: OT Rant: SQL Server On 5/3/07, Dave Watts <[EMAIL PROTECTED]> wrote: > > > > The subquery: > > > > select distinct USERID > > from ADSPRD.dbo.research_request_log > > where submitted > '2007-01-01' > > > > Don't blame SQL Server, it's doing exactly what you told it to do. Since > USERID is in the parent query, you are running a correlated subquery. > > http://www.databasejournal.com/features/mssql/article.php/3485291 That doesn't make sense to me. I've done correlated subqueries plenty of times, and always referenced something in the where clause to the parent query. We do this a lot with EXISTS clauses. But it makes no sense why this works even as a correlated subquery, because "select userid from research_request_log" would imply that I want to select the field "userid" FROM the table named "research_request_log" It just doesn't make any sense! If the glove don't fit.. you must acquit! Rick Did I mention that it doesn't make any sense? ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276929 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: OT Rant: SQL Server
> That doesn't make sense to me. I've done correlated > subqueries plenty of times, and always referenced something > in the where clause to the parent query. We do this a lot > with EXISTS clauses. That's normally how people do correlated subqueries, but you can reference columns in the SELECT clause as well as the WHERE clause. I'm not sure why you'd want to do that, offhand, but there you go. > But it makes no sense why this works even as a correlated > subquery, because "select userid from research_request_log" > would imply that I want to select the field "userid" FROM the > table named "research_request_log" In a subquery, if you reference something that doesn't exist in the tables or views used by the subquery itself, the query analyzer will look for them in the parent query; if they exist there, you end up with a correlated subquery. It's kind of similar to what happens when you refer to calculated columns in a query - they don't exist in your tables or views, you're making them up. In a correlated subquery, the references to the parent query can be thought of as calculated columns. > It just doesn't make any sense! If the glove don't fit.. > you must acquit! We're not going to hear about you driving to Redmond in a white Bronco, are we? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276927 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT Rant: SQL Server
You do seem to be right about what it's doing.. Because the following query - exactly the same except I'm using table aliases in the subquery - does not work. select email from WEBREPORTS.dbo.WEBALLUSERS where userid in (select distinct X.USERID from ADSPRD.dbo.research_request_log X where X.submitted > '2007-01-01') and email is not null Rick ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276923 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT Rant: SQL Server
On 5/3/07, Dave Watts <[EMAIL PROTECTED]> wrote: > > > > The subquery: > > > > select distinct USERID > > from ADSPRD.dbo.research_request_log > > where submitted > '2007-01-01' > > > > Don't blame SQL Server, it's doing exactly what you told it to do. Since > USERID is in the parent query, you are running a correlated subquery. > > http://www.databasejournal.com/features/mssql/article.php/3485291 That doesn't make sense to me. I've done correlated subqueries plenty of times, and always referenced something in the where clause to the parent query. We do this a lot with EXISTS clauses. But it makes no sense why this works even as a correlated subquery, because "select userid from research_request_log" would imply that I want to select the field "userid" FROM the table named "research_request_log" It just doesn't make any sense! If the glove don't fit.. you must acquit! Rick Did I mention that it doesn't make any sense? ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276921 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT Rant: SQL Server
On 5/3/07, Dawson, Michael <[EMAIL PROTECTED]> wrote: > We started getting tons of errors on our web site, so I checked into the > CFCatch details. It said our SQL Servers just shut down. I checked the > event logs and it said something like: > > 2007-05-03 09:30 SQL Server Service stopping. Reason: Condemned to hell. > 2007-05-03 09:30 SQL Server Search Service stopping. Reason: Condemned > to hell. > > Thanks a lot, Rick! haha, sorry about that ;) (I'm slow, it took me a minute to figure out why you were blaming me for that!) Rick ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276920 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT Rant: SQL Server
We started getting tons of errors on our web site, so I checked into the CFCatch details. It said our SQL Servers just shut down. I checked the event logs and it said something like: 2007-05-03 09:30 SQL Server Service stopping. Reason: Condemned to hell. 2007-05-03 09:30 SQL Server Search Service stopping. Reason: Condemned to hell. Thanks a lot, Rick! -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: 03 May 2007 14:47 To: CF-Talk Subject: OT Rant: SQL Server I used the following query to retrieve the email addresses for a mailing, and didn't notice that it returned 750 records. select email from WEBREPORTS.dbo.WEBALLUSERS where userid in (select distinct USERID from ADSPRD.dbo.research_request_log where submitted > '2007-01-01') and email is not null The subquery: select distinct USERID from ADSPRD.dbo.research_request_log where submitted > '2007-01-01' Is not even a valid query. There is no USERID column in that table. It does not execute when run by itself. But for some reason, the whole query still runs, and just ignores the fact that the subquery isn't valid. D'Oh! DAMN SQL SERVER! DAMN SQL SERVER TO HELL! ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276914 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT Rant: SQL Server
> I used the following query to retrieve the email addresses > for a mailing, and didn't notice that it returned 750 records. > > select email from WEBREPORTS.dbo.WEBALLUSERS where userid in > (select distinct USERID from ADSPRD.dbo.research_request_log > where submitted > '2007-01-01') and email is not null > > The subquery: > > select distinct USERID > from ADSPRD.dbo.research_request_log > where submitted > '2007-01-01' > > Is not even a valid query. There is no USERID column in that > table. It does not execute when run by itself. > > But for some reason, the whole query still runs, and just > ignores the fact that the subquery isn't valid. > > D'Oh! > > DAMN SQL SERVER! DAMN SQL SERVER TO HELL! Don't blame SQL Server, it's doing exactly what you told it to do. Since USERID is in the parent query, you are running a correlated subquery. http://www.databasejournal.com/features/mssql/article.php/3485291 Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276913 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: OT Rant: SQL Server
:-O *Slaps Rick for speaking ill of the Microsoft Corporation ;-) -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: 03 May 2007 14:47 To: CF-Talk Subject: OT Rant: SQL Server I used the following query to retrieve the email addresses for a mailing, and didn't notice that it returned 750 records. select email from WEBREPORTS.dbo.WEBALLUSERS where userid in (select distinct USERID from ADSPRD.dbo.research_request_log where submitted > '2007-01-01') and email is not null The subquery: select distinct USERID from ADSPRD.dbo.research_request_log where submitted > '2007-01-01' Is not even a valid query. There is no USERID column in that table. It does not execute when run by itself. But for some reason, the whole query still runs, and just ignores the fact that the subquery isn't valid. D'Oh! DAMN SQL SERVER! DAMN SQL SERVER TO HELL! ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276911 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
