THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

INTERCEPT In SQL 2005

I was writing a query and managed to mistype INTERSECT, I typed INTERCEPT and to my surprise the query ran, it returned 2 result set just as if INTERCEPT wasn't there at all Try it yourself

CREATE TABLE testnulls (ID INT)
INSERT INTO
testnulls VALUES (1)
INSERT INTO
testnulls VALUES (2)
INSERT INTO
testnulls VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)

SELECT * FROM #testjoin
INTERSECT
SELECT * FROM #testnulls

SELECT * FROM #testjoin
INTERCEPT
SELECT * FROM #testnulls

Feature, Bug?

Okay, it actually doesn't matter what you type between the two statements

SELECT * FROM #testjoin
sdsdsdsdsd
SELECT * FROM #testnulls

 

That runs just as if you executed the query without sdsdsdsdsd

??????

Published Tuesday, November 13, 2007 4:09 PM by Denis Gobo
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

What's the surprise? That's just the alias for the preceding table, just like you can write SELECT * FROM T1 ABC without the key word AS between T1 and ABC.

November 13, 2007 3:35 PM
 

Denis Gobo said:

Yes, I just though of that also and was going to modify the post ;-)

Boy do I feel dumb now...:-(

November 13, 2007 3:40 PM
 

Denis Gobo said:

I will make this post my start page, next time I feel smart I will open a browser window to set me straight

November 13, 2007 3:44 PM
 

James Luetkehoelter said:

Hey, I spent more time puzzled over a simple typo than I do a very complicated problem...even Einstein thought that he proved that 2+2=5 (or something like that).

November 13, 2007 6:01 PM
 

Alejandro Mesa said:

Hi Denis,

Use parenthesis to enclose each statement.

(SELECT 1 AS c1)

INTERSECT

(SELECT 1 AS c1)

GO

(SELECT 1 AS c1)

INTERCEPT

(SELECT 1 AS c1)

GO

Cheers,

AMB

November 13, 2007 7:36 PM
 

Adam Machanic said:

That should have been a teaser :-)

November 13, 2007 7:59 PM
 

Paul Nielsen said:

Very Cool. You get 3 extra "Human" points.

November 13, 2007 8:47 PM
 

Madhivanan said:

If you ask me, I would say Yes When you write select statements and use alias names, always make sure

November 14, 2007 6:38 AM
 

James Lean said:

Thanks Denis, you've solved a "problem" I had a couple of days ago which had me scratching my head.  I was executing this script:

SELECT * FROM sysfiles

sp_helpfile

I couldn't work out why I wasn't getting an error due to the missing EXEC (just got the single resultset from the first query).  I can bang my head quickly on the table and move on now... ;-)

November 14, 2007 9:43 AM
 

Denis Gobo said:

See, I knew someone somewhere would benefit from this  ;-)

November 14, 2007 10:13 AM
 

Alexander Kuznetsov said:

Yet another reason to always include AS, table names or aliases, and EXEC in the code. Good point, thanks!

November 14, 2007 10:44 AM
 

Vern Rabe said:

And another good reason to use semicolon statement terminators.

November 14, 2007 12:18 PM
 

Alexander Kuznetsov said:

I don't think a semicolon would help you in this case.

November 14, 2007 12:32 PM
 

Vern Rabe said:

Not Denis' original, but it would have helped James Lean's example.

November 14, 2007 12:46 PM
 

Denis Gobo said:

It is also fitting that I watched Idiocracy the same day

http://www.imdb.com/title/tt0387808/

That made the day complete  :-)

November 14, 2007 12:49 PM
 

Linchi Shea said:

As far as I'm concerned, they should have enforced the semi-colon statement terminators since the very beginning. That should have spared them some of the weird parser behavior or patch work, and give us a much easier time to parse T-SQL ourselves. But now it's too late to start enforcing semicolon statement terminators unless they want to break almost all apps.

November 14, 2007 2:54 PM
 

Paul Nielsen said:

I'm with you Linchi. I wish MS would enforce the semicolon, and at the same time add a feature to SSMS Query Editor that can automatically add semicolons at the end of every statement. It might get some wrong, but as in Denis' example, that would probably find code errors.

November 15, 2007 5:54 PM
 

Vern Rabe said:

I agree. MS has made a tiny step in that direction in that the statement preceding a CTE must be terminated with a semicolon.

November 15, 2007 6:05 PM
 

Kalen Delaney said:

This same error was in a newsgroup post quite a few years ago... someone posted a query from the pubs database and was surprised at the results. (It wasn't exactly the same as your query, but it had to do with the parser just thinking something was an alias). The poster thought it was a bug because the results were so werid. Before I could respond, someone for Microsoft SQL Server support replied that they had reproduced the "bug" and would be filing an official bug report!

November 17, 2007 12:14 PM
 

David Markle said:

Vern:

I don't see this as a step in the right direction -- they *have* to enforce the statement terminator in this case, othewise the WITH in the CTE will be parsed as though it's a query hint.

I'm all for some more enforcement of SQL standards.  They SHOULD have made a database option in 2000 and earlier versions which disallowed the horrendous *= and =* syntax -- that would have made it easier to migrate when they were deprecated.  Likewise, I think there should be a DB option on 2005/2008 to enforce that all statements have terminators.  That way, it would be easier to phase in the standards over time.  

November 20, 2007 6:51 AM
 

Mr db said:

LoL LoL LoL

same happend with me

i had similar problem, i was getting 1 row count for all of my tables all the time

select count(*) <tablename>

hmmm

after breaking my head for couple hours I caught the problem

most of you might have caught it right but if you are noob like me, no surprise you didn't noticed missing FROM clause

in my unit testing, i was expecting 1 row only which misguided me and led to frustation

August 9, 2008 3:30 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement