Saturday, February 16, 2008

Obfuscated SQL contest

On Oracle Community in the Database Developers Group a discussion started about having an Obfuscated SQL contest. It was not immediately clear to me what exactly was meant by this, but further answers explained it like this:

I believe the goal can be summarized as "Show off cool language features, bugs or advanced usage while being as unreadable as possible" .
Drawings are very popular, but are strictly optional.


or:

* To write the most Obscure/Obfuscated SQL query.
* To show the importance of SQL programming style, in an ironic way.
* To stress SQL techniques with unusual code.
* To illustrate some of the subtleties of the SQL language.
* To provide a safe forum for poor SQL code.


It looked like fun to me (maybe because I am responsible for standards and guidelines in my daily work and I want to break free?), so I wrote something which contains a lot of different techniques and is really obscure. It's the first contestant I think and I really hope I will see more queries from the usual suspects.

Of course it can be obscured more, but I have to draw the line somewhere. Note that I enhanced the query slightly to reduce the list of hexadecimal codes and to introduce a regexp_replace, an until clause and grouping sets. Ok, here it is:

rwijk@ORA11G>                             select
2 replace(extract(xmlagg(xmlelement(
3 "ILoveSQL",y||',')order by z),'/ILoveSQL/text()'),
4 ',')x from(select row_number()over(partition by b order
5 by null)z,b,case when to_char(b||row_number()over(partition
6 by b order by null),'fmxxx')in('22f','223','222','220','21f'
7 ,'21c','21b','21a','213','211','20b','207','205','204','203',
8 '202','1ff','37' ,'33','1c9','1c5','1c3',
9 '1bf','1bb','1b7' ,'1b3','1b1','1ab',
10 '1a9','1a5','1a1' ,'19f','31','2b',
11 '166','165','164'
12 ,'15b','15a','158'
13 ,'157','14f','14e'
14 ,'14d','14c','149'
15 ,'145','142','141'
16 ,'140','13d','13c'
17 ,'137','22','21',
18 '20','101','fd',
19 'fb','f7','f3',
20 'ef','eb','e9',
21 'e5','e3','dd',
22 'd9','d7','1d',
23 '17','9f','8d',
24 '89','83','7d'
25 ,
26 '7a','79'
27 ,'78','6f','f','b'
28 )then chr(sqrt(power
29 (2,10))) else
30 regexp_substr(a,
31 chr(46)
32 ,
33 row_number
34 ()over(partition by b order by null)
35 ,1,'i')end y from(select concat(s||t||r||a||n||g||e
36 ,w||o||d) a,rownum b from(select s,k,i from ( select s,k,i
37 from user_users where ln(34e5)=exp(ascii('@')-2e2) model dimension
38 by(0 i,-3 k)measures(external_name s)rules iterate(12345)until(ascii
39 (s[iteration_number ,-8])-ascii(s[0,-8])=20)
40 (s[iteration_number ,for k from -10 to -2
41 increment 2]= regexp_replace(lpad(
42 chr(iteration_number
43 +decode( sign(
44 iteration_number-6),-1,
45 65,0,73,76)),decode(mod
46 (cv(k),4),0,3,-2,5),chr
47 (iteration_number+101
48 -decode (sign(
49 iteration_number-6),
50 -1,36,0,28,25))),
51 '(.'||decode(mod
52 (cv(k),4),0,null
53 ,-2,'{5}')||chr
54 (41),'\1 '))))
55 pivot(max(s)for
56 i in (6 s,1 t,5
57 r,9 a,7 n,2 g,0
58 e,8 w,4 o,3 d))
59 )
60 ,table
61 (cast(multiset(select
62 null from dual connect
63 by rownum<=length(a))as
64 sys.dbms_debug_vc2coll)
65 ))group by grouping
66 sets((b))
67 /

X
----------------------------------------------------------------------------

OOO BBBB FFFFF U U SSSS CCCC AAA TTTTT EEEEE DDDD
O O B B F U U S C A A T E D D
O O BBBB FFF U U SSS C AAAAA T EEEEE D D
O O B B F U U S C A A T E D D
OOO BBBB F UUU SSSS CCCC A A T EEEEE DDDD

5 rijen zijn geselecteerd.

5 comments:

  1. Didn't some one write "Simplicity is the ultimate form of elegance and sophistication" as the strapline to their blog?

    ;-)

    But, very nice code...

    ReplyDelete
  2. I just knew that comment would come ;-)

    ReplyDelete
  3. Rob,

    I have been following your activity for quite a while on forums.oracle.com, but your input on oracle community contest is really amazing. Not only the code itself, but the style, and the output also!!

    Congratulations, hats off, you're really good.

    All the best,
    Michael

    ReplyDelete
  4. Rob, That gets my vote!
    Just one question: where can I get the t-shirt??

    ReplyDelete
  5. ok, made my own shirt ... posted in the
    Oracle Community photos collection. hope you like;-)

    ReplyDelete