May 22, 2008 at 1:46 am
Standard and expected behaviour by SQL :/ SQL devs just have to be aware of it, not really an issue for MS to fix (as there isn't one):cool:
May 22, 2008 at 4:43 am
Agreed!
It's too late to change it, IMO it was a bad idea from the beginning. Still would rather see a warning or error message, rather than silent truncation.
May 26, 2008 at 4:26 pm
huh, weird. I definitely expected an error.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 10:20 am
rbarryyoung (5/26/2008)
huh, weird. I definitely expected an error.
Me too. I think I would rather have a warning or error given, but I do see the points others have made against this. This question and the discussion that followed enlightened me on how SQL Server deals with parameters in different situations.
This was a good question and it prompted a great discussion (which is a refreshing change from other QOTDs where the discussion was all complaints).
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
June 1, 2008 at 5:22 am
It is not the most intuitive thing to happen...
June 9, 2008 at 3:02 pm
Ryan Riley (5/20/2008)
This is shocking to me. I don't mean to pick on you, Jon, but 1) why would you want an error--or even a warning--telling you something you should already know and 2) why would you write a procedure accepting a limited number of characters without knowing the correct limit? This seems to be only an argument for those who don't like to think, test, or check their logic before launching their code into the wild. I'm no SQL guru or anything, but if the code didn't truncate without warning, I'd have to wonder whether anything I did worked right. This just seems too elementary.
You would want an error/warning to help you when you test your code. If you have somehow (shockingly) made the mistake of passing a parameter a value of incompatible type, then this warning should help you to catch the bug more quickly and easily. Allowing SQL to silently truncate things for you is a recipe for unmaintainable, incomprehensible and/or buggy code...truly shocking.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 26, 2008 at 12:27 am
a good question and great discussion. thanks all
September 25, 2008 at 11:37 am
Nisha (5/19/2008)
You're right, Iggy.. its not a glitch from what I can see.I've faced this problem once before so I knew exactly what the question was about.
You can't pass a larger value ( varchar(13) to a varchar(10) ) and expect it to return anything but a truncated value (of varchar(10) itself ) even though the return specifies ( varchar(12) )
Anyway.. have a great day! 🙂
This is true of almost every weakly typed programming language I know of. Stuffing a string var can only stuff to the max string stuffing capacity, and it will not overstuff the string container nor do many languages throw string stuffing errors... does this stuff make sense? (grin).
SSIS, however, does make accomodations for catching string truncation or ignoring it... which leads me to use the data transformation bit almost every time I bring data from another source besides MS SQL Server.
March 4, 2010 at 10:17 am
James Goodwin (5/20/2008)
Pascal is a Procedural Language.Lisp is a Functional Language.
SQL is a Declarative Language.
SQL is actually a procedural language, and not a declarative language. At least two reasons: (1) you can write a number of statements to make an SQL programme and the meaning of that programme depends on the order of the statements (that's the definition of procedural, for heaven's sake). (2) SQL includes numerous statements whose sole function is to have side effects - not to describe the result of the total computation. So it is not declarative.
The only way you could argue that SQL is not a programming language would be to find a way in which it is not complete, (in the programming sense, which may not be exactly the same as Turing complete)
Well, it certainly is Turing complete (provided you have unbounded storage available, which is the same sense as that in which other programming languages can be said to be Turing Complete. And it certainly is not model-theoretically complete (deductively complete, if you prefer that phrase): Kurt Godel proved 60-odd years ago that no language able to describe integer arithmetic could be complete in that sense (the halting problem is an alternative formulation of that property of programming systems). So currently there can be no sense in which SQL is missing some sort of completeness that other programming languages possess, and unless someone disproves Church's thesis (that every effectively computable function is a recursive function) and then devises a programming language that computes some non-recursive effectively computable function and demonstrates that SQL can't compute it that's going to remain true for ever. And I (along with every other mathematician in the world I guess) don't believe that's ever going to happen (but we could of course be wrong:alien:).
(What's the betting that someone who thinks they know what a recursive function is will come up with integer addition as an example of a non-recursive effectively computable function :hehe: -- probably I should know better than to use technical terms like "recursive" without including a full definition in a forum like this :laugh:).
edit to fix spelling (English spelling is awful/impossible:(, whether US version or UK version; maybe I should add something like "Tha mi sgith de litreachadh na Beurla" to my sig).
Tom
March 4, 2010 at 10:59 am
It's a good question in that it's spurred an interesting discussion.
I think a lot of people arguing about what SQL should do about truncations like this are starting from the wrong point. As a general rule for languages with a sufficiently weak type system to include unparametrised coercions, there needs to be a definition of what contexts those coercions are allowed to occur in. A language could specify that parameter-free coercions can occur only where the programmer explicitly asks for one (that of course is obviously true for coercions with parameters, as the programmer has to specify the parameters somehow), but anyone except an extreme strong-typing purist (whom I would probably regard as a nut) will reject that approach (you can see why: having to use many different notations for the number we usualy write as 0, depending on whether it's tinyint, smallint, int, bigint, decimal, bit, money, smallmoney, float or real, and of course having to wriote an explicit coercion to get from '0' or N'0' to one of those is just not acceptable). SQL does have a marker for parameter-free coercions, for use where the target type is not 100% clear from the context: CAST. So we should start by looking at what "cast('01234567890ABC' as varchar(10))' means and where we are allowed to put it. Well, it means '01234567890', and we can write it an=ywhere we could write '01234567890'. I would conclude from that that for cconsistency, for a coherent set of rules, we can write '01234567890ABC' anywhere we can write '01234567890' - and SQL is inconsistent in this respect - we can't assign '01234567890ABC' to a column to which we can assign '01234567890', but in every other context they are exchhangeable. Poor language design. Does it need fixing? In my view no. There is no perfect programming language, why shouldn't SQL have its little idiosyncracies? But if we did want to fix it, what would be the right change to make? Most people so far who want a change have wanted to change so that '1234567890ABC' always causes a truncation error if used where it meeds to be '01234567890'. (Someone asked that we be notified if somone put that suggestion on connect,. so that we could vote against it: Yes please, I would want to vote against it to). I think a better approach would be to (a) change the truncation error to a warning message that is logged but doesn't fail the statement and (b) change CAST from ebing a fairly meaningless piece of syntax to something that suppresses those warning messages. Of course doing that would also require a review of what other error messages (if any) caused by slightly dodgy coercions should be downgraded to warnings, and a review of whether cast should suppress all "dodgy coercion" warnings. I think that would be a large undertaking, and there are far more important things to do to SQL than pike around with trivia like that.
Tom
March 4, 2010 at 2:18 pm
Tom.Thomson (3/4/2010)
James Goodwin (5/20/2008)
Pascal is a Procedural Language.Lisp is a Functional Language.
SQL is a Declarative Language.
Well, to paraphrase a famous line "It depends on what the meaning of the words "Is A" is." 😀
If by "<language> Is A <language-type>" we mean that it meets the qualifications for that language-type, then all of these are true. But then it would also be true that SQL is a procedural language and that Lisp is an OO language (in fact Lisp qualifies as so many different language, it's hardly worth discussing).
If on the other hand, "<language> Is A <language-type>" means "purely of that type and unpolluted by the elements and influences of other language-types", then none of these statements is true (though Pascal is pretty close). Indeed, there is considerable doubt as to whether it is even possible to make a definitive distinction between Functional and Declarative languages on that basis. And on that basis, Lisp couldn't be called anything, except Lisp.
In general, I think that <language> "Is A" <language-type> is just too strong a phrase for any language in real-world use. Maybe something like "Is A Type Of", or "Has The Qualities Of" would be better.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 3:34 pm
Tom.Thomson (3/4/2010)
SQL is actually a procedural language, and not a declarative language. At least two reasons: (1) you can write a number of statements to make an SQL programme and the meaning of that programme depends on the order of the statements (that's the definition of procedural, for heaven's sake). (2) SQL includes numerous statements whose sole function is to have side effects - not to describe the result of the total computation. So it is not declarative.
Heh, heh. I think you may be my new favorite poster, Tom. 🙂
You know, way back when I wrote "There Must Be 15 Ways To Lose Your Cursors", Part 1[/url], and Part 2[/url], I wondered if anyone was going to catch my hand-waving over my statements that "SQL is a Declarative Language". I was not 100% correct about that, SQL in fact must be procedural because of things that it has to address that the Relational Model either did not or could not (at least at the time). And no, not because of Cursors or WHILE loops, which today we could easily do without and originally weren't even in the SQL spec.
No, it's exactly these two things that you point out Tom, but which no one thought to mention then. And I was dreading having to muddy the waters of that message then and it's discussions with having to explain the nuance and subtleties of these necessary compromises and why they shouldn't affect my point (which was "stop using Cursors, because they're unnecessary proceduralisms").
To be clear about my position: there are parts of SQL that are (or can be) Declarative: any single-statement (non-recursive) query or DML. And then there are parts that are not: variables, any order dependent statements, any multiple statements that have at least one DML, etc.
To address your two points:
(1) Yes. Not only can you write procedurally in SQL, but you must and indeed, SQL must be designed not only to allow it, but to require it. Why? One word: Transactions. (OK, yes, there are other reasons, but transactions are the big one and there's no getting away from them). The essence of the problem is in the very definition of a transaction, an externally atomic change of the data-state of the database.
So why is that a problem? (heh, here's where we get the boards really heated up...), because Relational Theory as formulated by Codd, has no such thing as "state change". Yep, I know, some wit is going to say "but didn't Gray write papers on Transactions in Relational Theory back in the 70's and 80's?" He sure did, but the problem with that is that they cannot be formally integrated into Relational Theory as Codd formulated it because it breaks the central thesis and most important aspect of Relational Theory, the mapping to Predicate Logic and Set Theory.
And that is because in Predicate Logic, there is no such thing as a "change of state". In mathematical logic, Truth is unchangeable, immutable and eternal, it can never change.
Yes, you can formulate a definition of "state-change", but only by formulating an artificial definition of "state" with a temporal component mandatorily welded to it. But that's not Predicate Logic, that's a synthetic subset of Predicate Logic that loses almost all of its power and efficacy. And more importantly, that is not how Codd mapped Relational Theory to Predicate Logic.
And this all plays back into this discussion, and "Declarative" languages and why SQL (unlike Relational Theory) must have non-declarative elements. Because, AFAIK, know one has yet come with a way to express and manage temporal state-change (ie, "transactions") in a Declarative way. Now I do not accept that temporal order (or "procedure"), necessarily means "non-declarative" (at least as I define it), but so far I haven't seen or heard of any way around it.
And neither had the folks who defined SQL, which is why we have proceduralisms and probably always will.
Nonetheless, there's no need for proceduralisms (cursors, loops) to implement a query or most DMLs.
OK, on to your other point:
(2) SQL includes numerous statements whose sole function is to have side effects - not to describe the result of the total computation. So it is not declarative.
Hmm, I am not sure that I am clear on what you mean here. If you mean "any DML", then I would disagree that their effects were "side-effects" rather than intended effects, or that any intended effect other than returning computation is necessarily non-declarative. In a larger scope with temporal dependence, that's true, but on it's own, I see nothing that automatically makes it non-declarative.
If you were referring to something else, then I probably agree, but I'd like an example to be sure. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 3:43 pm
Tom.Thomson (3/4/2010)
(What's the betting that someone who thinks they know what a recursive function is will come up with integer addition as an example of a non-recursive effectively computable function :hehe:
Well, not now that you've said that. 😛 Though yes, I expect that there's plenty of folks who do not realize how Peano's Induction Axiom works.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 3:50 pm
Tom.Thomson (3/4/2010)
edit to fix spelling (English spelling is awful/impossible:(, whether US version or UK version; maybe I should add something like "Tha mi sgith de litreachadh na Beurla" to my sig).
Hmm, you might want to fix that line up too. I can't make heads or tails of it, and its driving my spell-checker crazy! 😀 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 5, 2010 at 7:39 am
RBarryYoung (3/4/2010)
Tom.Thomson (3/4/2010)
edit to fix spelling (English spelling is awful/impossible:(, whether US version or UK version; maybe I should add something like "Tha mi sgith de litreachadh na Beurla" to my sig).Hmm, you might want to fix that line up too. I can't make heads or tails of it, and its driving my spell-checker crazy! 😀 😀
Well, I could translate it: "I'm pissed off with English spelling" (or maybe "orthography" is a better translation than "spelling") but that doesn't really convey the sense of despair I suffer when confronted with this language where the connection between spelling and phonetics is tenuous, to say the least.:-):-D
Tom
Viewing 15 posts - 61 through 75 (of 79 total)
You must be logged in to reply to this topic. Login to reply