Obfuscated SQLPosted: April 5, 2008
I’m a closet C programmer, and an avid follower of the Obfuscated C Contest . SQL is an interesting and diverse language. No reason why database professionals can’t have their own obfuscated code.
So I shared my idea. And the renowned community organizer Eddie Awad made it an official contest! And the wonderful SQL gurus all contributed the scariest queries you’ve ever seen, and now it is time to vote!.
Rob van Wijk deserves a special reward for having the first entry – less than a day after the contest was announced! And as Paul Gallagher noted, it is pretty enough to be on a t-shirt. I think that Rob’s entry set the tone for the rest of the competition – XML, regexps, chr, ascii, connect by and model became prerequisites for any self respecting obfuscated query. Rob also included use of pivot,multiset, messages in the code (‘I LOVE SQL’) and silly math tricks that I enjoyed much (where ln(34e5)=exp(ascii(‘@’)-2e2) is so much nicer than where 1=0 ). I was a bit disappointed that I managed to decipher most of the query and its tricks in an hour or two. Next year we expect worse 🙂
Shoblock thought that OTN is full of obfuscated queries and stepped up with an example. I beg to differ. Not any plain old horrible query deserves to be an obfuscated entry. A true obfuscated query must be horrible and cleverly impressive at the same time. It should make you say “wow” as well as “yuck” and hopefully help you learn something other than how not to write code while you read it.
Laurent Schneider raised the bets by submitting a query that runs on 10.1.0.5 only and contains no white spaces. It deserves a reward for most tricks with fewest characters and also for scariest query. I would not want to meet this monster in my source control on a dark night. I cheated a bit and got an in-depth tour of the query from the master himself. I hope Laurent will blog his explanation, but if he won’t I’ll do it. For now, I’ll point out the use of double connect by, dburitype and stats_mode that make the query unique. I’ll also mention that the obscure version was chosen not just to annoy me. 10.1 is the only version where one can use the model clause and still have group by sort the results of the query.
Frank Zhou gets the award for the most useful query. While the other entries only print nice messages, Zhou’s entry solves four different problems! As far as pure obfuscation goes, I think this entry is less impressive than others. Of course, the good and clear formating also helped in making this just too readable to be obfuscated.
Volder’s entry arrived just in time before the deadline, and it is both pretty and clever. I think it deserves the overall winner award. The query contains all the required tricks and then adds new ones. It uses comments for prettier formatting, it uses XQuery, it uses subquery factoring and nested groups in regular expression. I’m still working on understanding all of it, but I’m sure in few hours I’ll get it and be a better person for it (if the headache won’t get me first). Volder, any chance you’ll be finishing the layout so we can see the turtle? The formatting is still great even without the turtle. The use of lots of horizontal spaces to separate the sun from the tree makes it very difficult to real.
I just remembered that no prize was mentioned. Any suggestions? A bottle of Stoli Crystal maybe?