Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Query Expand / Collapse
Author
Message
Posted Wednesday, May 14, 2008 10:22 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 897, Visits: 1,686
Steve Jones - Editor (5/14/2008)
I've seen queries similar, and people would write them. The fact that it compiles is a bad thing in my mind.

Someone submitted this and I think it's a good tickler to let people know that there might be developers that would write this. Perhaps not intentionally, but once it worked, they might keep writing.

The fact you wouldn't allow it doesn't mean that you shouldn't know this behavior, while buggy and perhaps not desired, is available.

I agree that it's important to be aware that this type of query does compile and return values. The discussion that this question generated and the knowledge discovered and shared are what makes this so worthwhile. Unfortunately, not everyone who looks at the Question of the Day or Articles realizes the value of reading the comments.
Post #500707
Posted Wednesday, May 14, 2008 12:00 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,526, Visits: 1,834
My gut feeling is that it's not valid syntax (so you won't find it in BOL), but somehow SQL Server managed to parse it into an executable query.

I'd love to know if anyone finds any documentation on it -- I would guess under selects in where clauses.
Post #500796
Posted Wednesday, May 14, 2008 12:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,526, Visits: 1,834
Well, I checked BOL for both WHERE clause syntax and SELECT subquery, and neither shows this "feature".

Under the WHERE clause syntax, it simply shows the option to use a (subquery) , a couple variations, like EXISTS (subquery), which to me, implies a syntactically-correct subquery.
Post #500804
Posted Wednesday, May 14, 2008 12:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 03, 2013 4:44 PM
Points: 1,336, Visits: 595
ezlikesundaymorning (5/14/2008)
OK, just plain and simple, I am going to give my 2 cents worth here. This question is pure BS. I mean has anyone here ever seen a query written like this? In my house(place of business) the person who tried to get this through me, would be ridiculed beyond belief. I mean really, did I get the question wrong, yep. Did I also think that the question was misleading, yep. Do I know a developer that would try to get this past me, nope. Lets get some real questions in here. First off I do not even let anyone write in ANSI 89 SQL. Plain and simple it is my way or the highway.


I really appreciate this comment.

I understand, you had not come across with such question ever before. But best part would have been if you have thought some thing different than usual stuff.



--www.sqlvillage.com
Post #500806
Posted Wednesday, May 14, 2008 12:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 03, 2013 4:44 PM
Points: 1,336, Visits: 595
Carla, you are right. there is no reference in BOL.

Its just a way of writing query that does not return error but does return data.


--www.sqlvillage.com
Post #500815
Posted Wednesday, May 14, 2008 12:50 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,526, Visits: 1,834
Hmmm... I dug a little deeper in BOL and found something interesting.
On the main "clauses" page under "SELECT", it shows that the FROM clause is a required clause with almost everything else optional.
[/url]


But at the bottom of that page is a link to a more complete syntax of the SELECT statement.
[url=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/dc85caea-54d1-49af-b166-f3aa2f3a93d0.htm]
(apologies if these links don't work -- I've never tried this before)

This syntax includes the WITH "CTE" syntax, and on this page, the SELECT statement shows the FROM clause as optional! Perhaps when CTEs were introduced in SQL 2005, this changed the "rules".
Post #500838
Posted Thursday, May 15, 2008 1:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:06 AM
Points: 1,204, Visits: 915
Jason Miller (5/14/2008)
Syntactically correct, but it doesn't produce meaningful results. I agree with the previous posts, it compiles and executes, but what is it trying to provide?


Listen to me, I try to learn something new everyday and that is the reason why I answer these questions but sorry, today I learned nothing. Who writes queries like this?!


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #501076
Posted Thursday, May 15, 2008 4:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
Carla Wilson (5/14/2008)
This syntax includes the WITH "CTE" syntax, and on this page, the SELECT statement shows the FROM clause as optional! Perhaps when CTEs were introduced in SQL 2005, this changed the "rules".
The FROM clause is definitely optional since otherwise
SELECT getdate()

or (slightly more usefully)
;with number as (
select 1 as n union all
select 3)
select n from number

would fail to parse. What isn't clear is how the parser processes field references in a subquery without a FROM clause. As the execution plan shows, it's clearly not obvious and my reaction would be always to include the FROM clause both to make the query clear and so that the behaviour was predictable (and explicable :)).

Searching further, I found the explanation of the FROM clause, SS2K5 or SS2K8, both state
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).
So the query given, while it parses and runs, is supposed to be invalid syntax, but somehow slips through.

The topic "Subquery Fundamentals" for SS2K, SS2K5 and SS2K8 all state
A subquery nested in the outer SELECT statement has the following components:

A regular SELECT query including the regular select list components.

A regular FROM clause including one or more table or view names.

An optional WHERE clause.

An optional GROUP BY clause.

An optional HAVING clause.
So the requirement to have a FROM clause hasn't changed recently!

Maybe it ought to be logged as a bug with the workaround "don't do this!" :)


Derek
Post #501144
Posted Thursday, May 15, 2008 8:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:55 AM
Points: 1,012, Visits: 440
I'm the DBA for a large medical system in the midwest and we purchase many of our solutions versus doing in-house builds. While I'd flogg my developers for coding in this manner I'll tell you right now that I've seen this query structure in play quite a bit more than I'd like to coming from the commercial side. It's quite amazing how many decent app developers out there still fumble the ball when it comes to DB calls and coding. Arg! Good question from the standpoint that (whether good or bad) this stuff is still floating around out there.

- Tim Ford, SQL Server MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Post #501348
Posted Thursday, May 15, 2008 1:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:16 PM
Points: 1,343, Visits: 373
One problem I see with the question is that it does not specify the requried version or database to be used. The fact that the results are nonsense and the answer does not reference this fact leads me to put this into the poor question category. Fortunately since this question is similar to one we had in the past I was able to answer it without trying to run it.

-- Mark D Powell --
Post #501598
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse