January 13, 2016 at 7:34 am
edwardwill (1/13/2016)
See attachment.Edward
Yes, that works. That's the version he gives in the answer that is supposed to work, because the order of the table valued constructors has changed. Try the one actually mentioned in the question 🙂
EDIT: This is also why the example with APPLY is different than the similar examples given earlier with JOINs. The order in which the table valued constructor is used in the JOIN case doesn't matter. If it references the other table aliased in the join it will fail. With APPLY, the order determines whether it fails or not.
From https://msdn.microsoft.com/en-us/library/ms177634.aspx:
Using APPLY
Both the left and right operands of the APPLY operator are table expressions. The main difference between these operands is that the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function. The left_table_source can include table-valued functions, but it cannot contain arguments that are columns from the right_table_source.[/i]
Cheers!
January 13, 2016 at 7:38 am
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.
You can't see the Estimated Execution Plan of the statement, so it's not compiled for the next step - the optimization.
Igor Micev,My blog: www.igormicev.com
January 13, 2016 at 8:50 am
A different option to check if the code compiles:
declare @myint tinyint= 5;
SELECT 1;
SELECT s1.c1, s2.c1
FROM (values (s2.c1+2)) s1(c1)
CROSS APPLY
(values (@myint),(@myint+1),(@myint+2)) s2(c1);
A run-time error would show the result from SELECT 1.
declare @myint tinyint= 5;
SELECT 1;
SELECT s1.c1/0, s2.c1
FROM (values (@myint+2)) s1(c1)
CROSS APPLY
(values (@myint),(@myint+1),(@myint+2)) s2(c1);
January 13, 2016 at 10:02 am
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
😎
I can't find any option to compile a query. Parse, yes (the query parses). Execute, yes (the query fails - "The multi-part identifier "s2.c1" could not be bound.") But compile?
Generate the estimated execution plan 🙂
I did that. No compilation errors reported.
Hmmm...generates an error for me.
yeah it is the parse v. compile. I was translating "compile" into parse. It parses fine, but execution fails. Estimate plan does fail for me as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2016 at 11:57 am
SQLRNNR (1/13/2016)
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
😎
I can't find any option to compile a query. Parse, yes (the query parses). Execute, yes (the query fails - "The multi-part identifier "s2.c1" could not be bound.") But compile?
Generate the estimated execution plan 🙂
I did that. No compilation errors reported.
Hmmm...generates an error for me.
yeah it is the parse v. compile. I was translating "compile" into parse. It parses fine, but execution fails. Estimate plan does fail for me as well.
hmmmm, "com"piling on, the normalizer performs the parsing and binding, the prior being a prerequisite for the latter. The main role of the normalizer is the binding and this code fails binding and therefore never enters compilation;-)
😎
January 13, 2016 at 4:33 pm
Eirikur Eiriksson (1/13/2016)
SQLRNNR (1/13/2016)
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Jacob Wilkins (1/13/2016)
edwardwill (1/13/2016)
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
😎
I can't find any option to compile a query. Parse, yes (the query parses). Execute, yes (the query fails - "The multi-part identifier "s2.c1" could not be bound.") But compile?
Generate the estimated execution plan 🙂
I did that. No compilation errors reported.
Hmmm...generates an error for me.
yeah it is the parse v. compile. I was translating "compile" into parse. It parses fine, but execution fails. Estimate plan does fail for me as well.
hmmmm, "com"piling on, the normalizer performs the parsing and binding, the prior being a prerequisite for the latter. The main role of the normalizer is the binding and this code fails binding and therefore never enters compilation;-)
😎
😉
----------------------------------------------------
January 13, 2016 at 6:54 pm
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.
I guess it depends what you mean by compile. I'm inclined to say it's a bad question, because I've never seen a definition of compile for T-SQL in SQL Server.
The parser doesn't throw an error - so there's nothing syntactically incorrect. The parser (in SSMS) does indicate that it can't resolve the name (by underlining it with a red squiggle. So does parsing to the point where all that's needed before one generates an execution plan is resolution of names (and access to statistics and metadata for the things named) count as compiling? That's the question.
When talking about most computer languages, I mostly know what compiling means. But it doesn't mean the same thing in all cases. Many languages expect all named objects to have their type determined at compile time (I suspect all sane languages with static typing do, but wouldn't bet my life on it). Languages with non-static typing don't require named values to be staticly typed, and some strongly typed languages use strictly dynamic typing and don't permit the type of anything to be specified in the source code, so can have a system where all types are resolved at runtime. There are (or maybe have been, they are perhaps too bizarre to survive) one or two languages where dynamic typing is used and the type system is not solvable (ie it is subject to the incompleteness theorem, or the halting problem, whichever you want to call it) so careless coding can lead to infinite loops in type resolution (at run time usually, but in at least one language it could happen at compile time). So in some languages compiling means typing everything, in some languages it means determining whether there is anything that can't be typed, some others it means that things declared with a type are checked to have valid types and the use of things with declared types is (as far as can be detected at comile time) compatible with the type, and in yet others compiling has nothing at all to do with types. So there's at least one way in which "compile" means different things for different languages.
As far as storage is concerned, languages like Fortran can have filenames in the source code; compilation does not check at compile time that the filename exits. Maybe we could think of T-SQL as analagous to Fortran so that resolution of table names is something that can happen at runtime, not compile time? Maybe we know when it happens, but do we know whether what the system is doing at that time counts as compiling or not?
So maybe what we need to tell us whether there's a mixup here or not is a reference to a definition of what "compile" means in respect of T-SQL.
Of course there isn't a mixup, and the explanation is correct, there's a bug in the parser. The parser should tell us whether there is someting which prevents compilation by being invalid syntax. The explanation points out that we can do exactly what fails here just by changing the order of the text - ie by correcting the syntax. So the parser should detect it (if the explanation explains what it claims to explain).
However, that doesn't mean the parser has a bug (it does after all show the non-resolution issue); the explanation is nonsense; ""The right input is evaluated for each row from the left input ...." is clearly describing what happens at run time, so it doesn't tell us anything about compile time. Yes, clearly there is something of a mixup here!
And that doesn't mean that the "correct" answer is wrong (it's probably right), just that the explanation is pure drivel.
Tom
January 14, 2016 at 6:29 am
The question is a rough one because of the word "compile" but I think the lesson is a sound one. The statements looks fine until you consider that it won't execute. The reason why it won't run is the point and I think it's a good one.
January 14, 2016 at 7:00 am
TomThomson (1/13/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.I guess it depends what you mean by compile. I'm inclined to say it's a bad question, because I've never seen a definition of compile for T-SQL in SQL Server.
There is a lot of documentation out there that describes the process: how a batch is first compiled (as a whole), and then execution starts. If a compile-time error is anywhere in the batch, nothing executes. If a run-time error occurs, the batch starts executing, but then stops (or continues, depending on the type of error and the settings in effect).
Execute the two batches below (one by one) to see the difference - the first is a compile time error, the second is a runtime error. The first batch will return the value 1 and the message 1 row(s) affected, and then an error; the second batch will only throw an error.
SELECT 1;
SELECT Col FROM IDoNotExist;
SELECT 1;
SLECT Col FROM IDoExist;
January 15, 2016 at 3:58 am
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 15, 2016 at 7:42 am
Eirikur Eiriksson (1/12/2016)
SQLRNNR (1/12/2016)
I dunno about the "correct" answer on this one. The statements compile for me but produce an error at run-time. Seems to be a bit of a mixup here.What version of SQL Server? It fails the compilation on 2008,2008R2,2012 and 2014.
😎
it compiles fine on SQL 2014 SP1 CU2.
However it fails when run... because the code in the answer isn't the same as the code in the question
February 17, 2016 at 4:48 pm
Interesting question, thanks.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply