- Feature Articles
- CodeSOD
- Error'd
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Agreed - not a WTF at all. For all we know, "88712A" is a valid appdoc_num.
Admin
I give up. I can't begin to imagine why they thought that was a good idea. Nothing like taking a language that even relatively good programmers don't understand well, making some absurd, useless, syntactical change and making the change even more confusing to read/maintain than the original was.
I challenge someone to give me a good justification for that nonsense. (I'm sure that are several justifications, but I'm looking for a GOOD one. )
Admin
Implicit conversions so all those VB programmers will have an easy transition when learning how to royally destroy a SQL database.
Admin
I think the { were for the .net string.format statements:
string.Format("SELECT TOP {0} * FROM Table", 5) == "SELECT TOP 5 * FROM Table"
Admin
Sadly, I think you're correct. If code won't even run on another database because some idjit's VB app uses implicit conversion all over the place, they're less apt to switch to Sybase or anything else that runs T-SQL. Not that they really could anyway, if they were using user-defined functions, TOP, or all those other neat extensions.
The difference is that I wish Sybase would pick up some of those extensions. This one is just stupid.
// livin' in Sybase world.
Admin
Exactly. This wouldn't be nearly as WTF if it didn't mention how full of himself the kid was.
Admin
You can use curly braces in odbc sql. It's used for date conversion functions etc. For example :
select * from tableBlat where dateTimeColumn > {d '1999-10-02'}
Admin
Did Ted shoot Jesus or the duck?
Admin
You can, but I wouldn't recommend it. What's probably happenning here is that id is being implicitly converted to a string for the comparison. So, you might if you queried for '100', you might find yourself being returned id 99 unexpectedly. I've not confirmed this, but consider yourself warned.
Admin
how does 100 gets implicitly converted to 99? now THAT is a WTF. Everyone knows that 100 converts to 42.
Admin
So now when you 'ORDER BY' ints, you get the correct ...
0
1
10
3
42
9
etc.... ha ha ha
Admin
Including this one ;-P
Admin
Ted shot the duck. Dick Cheney would have winged Jesus.
Admin
Incorrect; it converts the string to an integer. Consider the following:
SELECT * FROM users WHERE id > 'a'
If you were correct, that would work. But in reality, it throws "Syntax error converting the varchar value 'a' to a column of data type int".
Admin
I believe that works in ORACLE, too. If I remember correctly from my DB days (Oracle 5?) single quotes should always work around literal operands and was in fact quite useful when building dynamic SQL queries 'the old way' (this is back when dynamic SQL meant string concatenation...) -- you could put quotes around all operands without regard to the type of the column.
I wouldn't be surprised if ORACLE did this specifically to cater to concatenated SQL building. Thank god we now have driver abstractions and parameterized queries...
And by the way, I don't really see this as a WTF. Just another youngster starting out who thinks his code can't possibly be the problem, and maybe a typo that wasn't spotted. Still, he'll learn do debug first, blame later after this.
Admin
Excuse me, but how in the hell did SQL with CURLY BRACES make it into production code?
At least, the only time I've had to debug while physically AT a server terminal was in a production setting...
Maybe this kid works for one of those places that don't unit test, smoke test, or IQ test.
See you in the bar,
Ribbly
Admin
And we all know how difficult it is to give up ziggurats.
Admin
We need more lumber
Admin
Yes: Perl allows general switchouts, it's one of the reasons it's easy to create obfuscated code.
s/this/that/;
s,this,that,;
s[this][that];
It also means if you're, say, switching forward slashes,
s{/}{\}; is the same as s/\//\\/;
Also, why would we be assuming appdoc_num is the PK? I see no real evidence it is.
Admin
Looks like a typo to me.
... and as we all have to eat a little humble pie now and then to remind us that no, we are not the smartest guys in the world...
I don't think I'd call this a WTF at all... rather an example of a junior developer who will hopefull not end up on this blog in the future.
Admin
Sooner you than me. The only one I can think of is Forth, and I wouldn't want to be embedding SQL in that... *shudder*
Admin
In the world of data, a number (as account number, social security number, document number) should be stored in character field; this is not an old practice but a sound maintenance practice for a handful of reasons.
A good rule of thumb is, look at a datum (let's say, 5642157). In the context of the system, how would you say it aloud: Five-Six-Four-Two... -- or -- Five Million, Six Hundred and fourty two thousand ... ?
If it's the former, then it's a number in the sense that most people use them -- synonymous with "identifier". If it's the latter, then you should not be calling it a number. Instead, you should use a quantifier like total, sequence, amount, percent, count, etc.
Admin
Agreed. :-D First post for me :-D
Admin
I program in scheme and speak with a lisp. Close enough?
Admin
The story says it's an older systems guy, which probably points to more maturity. It's not a hard & fast rule, but generally your mature software engineers have moved past the dick-swinging phase of dorkdum, since it's not very productive or dignified.
Admin
Not SQL per se, but some ODBC stuff can go into queries. For instance:
select {fn now()} as currentTime
is a valid query in SQL Server and some other dbs.
Admin
Well, it's because the companies that make them put all that Ningirsu in them.
Admin
Well, it's because the companies that make them put all that Ningirsu in them.
preview?
Admin
Fine, shit happens.
The WTF is that:
- The SQL was not in a stored proc (which the parser would have barfed on) so you would find the problem at development time
- It was not unit tested, integration tested or system tested. And we're not talking corner cases here, it only had to be executed in some way (so you would find the problem at QA time)
- The app had such poor exception handling/logging/tracing that a simple SQL syntax error was that hard to track down. I mean c'mon...something at the database level fails and you don't have a stack trace? (so you could find the problem at run time)
Shameful.Admin
It's valid, but are you sure there is not a cast going on there?
From support.microsoft.com:
In SQL Server versions 7.0 and earlier, whenever a query uses a comparison operator between a column and a literal, the data type of the column is used regardless of the precedence rules. That is, if the column and the literal have different data types, the latter is always converted to the data type of the column (as long as the conversion is valid). This behavior may lead to undesired precision reduction, string truncation, or other conversions.
In SQL Server 2000, this is no longer true. A conversion is performed according to data type precedence, as it is in any other comparison. In the case where a literal is positioned higher in the hierarchy, the comparison is made between the constant and the converted column (as opposed to previous versions) and therefore, results may differ. Consequently, existing indexes may not be useful, different execution plans may be chosen, and performance may be impacted negatively.
Note If you are using SQL Server 2005, the discussion about SQL Server 2000 also applies to SQL Server 2005. For all installations of SQL Server 2005, the default compatibility level is 90.
Admin
If you think of the database's role as storing strings of characters, then it would make sense to be able to say "... SET SocialSecurityNumber = '123456' ...", even if it *is* an integer column type. The fact that a column is an integer column does not mean "don't accept strings of characters", it means (1) sort the values numerically rather than alphabetically (2) store a 10-digit number as a binary number to save storage space and improve performance drastically and (3) only allow numerical characters (a side-effect of the storage requirements)
Admin
Beep... wrong.
The wise programmer *always* checks his/her own code first and when they suspect a problem in other parts of the system, is always very careful about how they express this.
In my experience a good programmer eschews the whole "my code", "their code" thing and concentrates on statements like "I think the problem may be in this bit of [implicitly, 'our'] code here".
If you are still talking about " by the time I get to the point of suspecting someone else's code I turn out to be right." then you still ain't getting it buddy. Either youre not yet as experienced as you might want to be or you are a slow learner.
I'll never forget the lessons that I learnt as a junior from hanging out with true coding gurus who *all* took this approach. I try to take that approach as much as possible now. Its really the best way to get things done.
If you are even consciously thinking about whether this is 'my' or 'their' code then you are not approaching the problem right.
Admin
Can we stop quoting? This is starting to hypnotize me... [:O]
Admin
>SELECT TOP 5 TITLE, PRICE
>This is probably the simplest and most elegant solution for limiting the number of >rows returned by a SQL Server query.
It's elegant, except that, in my experience, it's not fully portable. I originally learned SQL while interning for a company that rar SQL Server. Since then, I've done some work on home servers with both mysql and postgresql, and I recall the first time I discovered that mysql choked when I used "top 5" in a query. Perhaps mysql is just stupid, but it's really too bad that this method of selecting isn't accepted everywhere...
Admin
In Foxpro foo(a, b) = foo[a, b] if foo is an array
Yeah, but foxpro isn't a programming language. Duh!
Admin
That is ok because of implicit conversion which eventually will jump up and get you. Very bad form.
Admin
You've got to pick technology somewhere. You can't have everything portable to every platform without making compromises. In my experience, making SQL portable across more than a very few SQL platforms will severely limit your options. Look at the patterns for assigning a surrogate key value. Oracle uses a sequence that you must manually pull and insert into the table. MS SQL makes you insert no value and use one of several functions/variables to get the assigned value. The only portable option is to roll your own solution which will either take a bunch of work or introduce a serious locking hotspot (or a very difficult to find intermittent duplicate key problem). The best choice is to write custom tuned SQL for each platform. That way you can take the best advantage of each product. That's why they make Data Layers anyways. I tell everyone I work with that any SQL in UI code will go back for a re-write.
Admin
Admin
You were doing fine until this point. My code is much easier for me to check. It is usually at hand. OPC may not be available at all. Being lazy^Weconomical of my time, I prefer to check first what can be easily checked if it is reasonable that it might be in error. Sometimes, it may make more sense to check OPC first. (Consider the case of the author being a rookie or OPC is alpha or beta.)
Sincerely,
Gene Wirchenko
Admin
So, I am having a hard time with this.
Disclaimer, im not a db guy.
So, Did he just mistype the curly braces by accident?
Or did he think thats how you built queries in sql? Like wouldn't the first query you ever write and test pretty much tell you that curly braces are a no no? How did he get such an inflated ego, if he didn't know basic formation?
It would be like a java programmer mixing up parens and curly braces, you'd figure it out after "hello world!".
Admin
Actually you need to think about it for a minute. Jesus was a long haired middle eastern hippy guy with radical politics who spoke Aramaic. Cheney would have peppered him and had him sent to Guantamino for further questioning
Admin
I love you
Admin
Admin
Is that language Arithmetic?
Admin
What does the Orthodox Presbyterian Church (http://www.opc.org) have to do with this? Actually, scratch that. What the hell are you talking about?
Admin
What does the Orthodox Presbyterian Church (http://www.opc.org) have to do with this? Actually, scratch that. What the hell are you talking about?
Admin
<FONT face=Georgia>I think OPC == Other People's Code.</FONT>
Admin
Manni wrote: My solution: only use languages that don't support parenthesis.
Sooner you than me. The only one I can think of is Forth, and I wouldn't want to be embedding SQL in that... shudder
Don't forget brainfuck and Ook!. Try embendding SQL in these ;)
Admin
"he knew that it was definitely related to the backend, far, far behind the depths of the database servers and into the bowels of the organization's infrastructure"
I like that one. I'll see if I can use it one day.Admin
That's because MSSQL will implicitly convert between string and integral datatypes.
If it's between single quotes then it's a string, not a number.
Your point still stands, though - the number being in a string doesn't say anything about the datatype of the actual column.