Obfuscation contest

12 thoughts on “Obfuscation contest”

1. Polarski bernard April 7, 2008 at 08:54

   Objection, your honor : The query should be formated and have a least some sort of meaning like the one that produce a yearly calandar like unix command ‘cal’ (this one is really impressive). Otherwise it is going to turn at who can pile the more regexp and math functions.
2. Laurent Schneider Post authorApril 7, 2008 at 10:01

   🙂 I used math function quite economically to translate a list of numbers to a hex string ! One of the objective was to show obscure techniques. But well, I could do cal too :
   
select * from (
select * from dual model dimension by (0 day, 1 week)
measures (0 c)
(
c[for day from 0 to 6 increment 1, for week from 0 to 4 increment 1]=
1+trunc(trunc(sysdate,'MM'),'IW')-trunc(sysdate,'MM')+cv(day)+7*cv(week),
c[any,any]=case when c[cv(),cv()] between
1 and extract(day from last_day(sysdate)) then c[cv(),cv()] end))
pivot (max(c) for day in (
0 as mon,1 as tue,2 as wed,3 as thu,4 as fri,5 as sat,6 as sun))
order by week;


   but is this really obscure?
3. Polarski bernard April 7, 2008 at 12:27

   hummm… lemme think….really…..hummm…hard….hard…. Yes it is not evident. The construct within the rule section is not cristal clear and I am lucky to have found the excellent tutorial of Joe on the caluse model http://www.sqlsnippets.com/en/topic-11663.html.
   And even, this query is the soft version using the 11g pivot. I saw one without it for 10g with a bunch of connect by. Was fun, I gave up when I met the second connect by.

   However, I vote for this query over the lsc2, this one has a palatable goal.
   is it possible to do an inverse of matrix using the pivot and model clause ? that would be usefull, awfully complicated so that you put it in the interview under the nose of the candidate and ask what the query does.
4. Laurent Schneider Post authorApril 7, 2008 at 13:46

   I have not done this yet. It is surely possible, but I do not know myself how to calculate inverse matrix.

   To multiply matrix I could imagine :
   SQL> create table m1 as
select 1 x, 1 y, 10 val from dual
union all select 1,2,20 from dual
union all select 2,1,30 from dual
union all select 2,2,40 from dual
union all select 3,1,50 from dual
union all select 3,2,60 from dual
union all select 4,1,70 from dual
union all select 4,2,80 from dual;
Table created.
SQL> create table m2 as
select 1 x, 1 y, 100 val from dual
union all select 1,2,200 from dual
union all select 1,3,300 from dual
union all select 2,1,400 from dual
union all select 2,2,500 from dual
union all select 2,3,600 from dual;
Table created.
SQL> select * from m1 pivot (max(val) for y in (1,2))

   X 1 2
   ---------- ---------- ----------
   1 10 20
   2 30 40
   3 50 60
   4 70 80
   SQL> select * from m2 pivot (max(val) for y in (1,2,3))

   X 1 2 3
   ---------- ---------- ---------- ----------
   1 100 200 300
   2 400 500 600
   SQL> select * from (
   select * from dual model
   reference
   m1 on (select * from m1) dimension by (x,y) measures (val)
   reference
   m2 on (select * from m2) dimension by (x,y) measures (val)
   reference
   dim1 on (select max(x) x,max(y) y, 0 dummy from m1) dimension by (dummy) measures (x,y)
   reference
   dim2 on (select max(x) x,max(y) y, 0 dummy from m2) dimension by (dummy) measures (x,y)
   dimension by (1 x,1 y) measures (0 val)
   rules iterate (100) until (dim2.x[0]=iteration_number+1)
   (val[for x from 1 to dim1.x[0] increment 1, for y from 1 to dim2.y[0] increment 1]=
   nvl(val[cv(),cv()],0)+m2.val[iteration_number+1,cv(y)]*m1.val[cv(x),iteration_number+1] ))
   pivot (max(val) for y in (1,2,3))

   X 1 2 3
---------- ---------- ---------- ----------
1 9000 12000 15000
2 19000 26000 33000
3 29000 40000 51000
4 39000 54000 69000

5. Laurent Schneider Post authorApril 7, 2008 at 14:08

   I could add
   val[1,1]=1/decode(dim2.x[0],dim1.y[0],null,0),
   to get 1/0 error when number of columns of m1 differs from number of rows in m2
6. Polarski bernard April 7, 2008 at 15:16

   Hooo…….a pivot feeded by 2 nested loop out of 5 dimensions… we are reaching new hight of pain. The query is not made of cryptic or exotic functions, i can undertand every single word apart though I have hard time to understand the whole picture. The worst query are the ones made of simple words but still you don’t understand: the longer you look, the more idiot you feel.

   Back home, I will tell wife that it took me 5 hours to understand 10 lines.

7. Pingback: Log Buffer #92: a Carnival of the Vanities for DBAs

8. Pingback: First International NoCoug SQL Challenge « I’m just a simple DBA on a complex production system

9. Pingback: Log Buffer #92: A Carnival of the Vanities for DBAs

10. James Su March 23, 2015 at 00:43

    Please join and play!

    https://community.oracle.com/thread/3689097

    The Obfuscated SQL Contest at the PL/SQL Challenge
    This question is Not Answered.
    Steven Feuerstein-Oracle
    Super Elite
    Steven Feuerstein-Oracle 20-Mar-2015 16:28
    For those of you who do not know about, the PL/SQL Challenge (plsqlchallenge.com) is a website that offers weekly and monthly quizzes on SQL, PL/SQL, Database Design and Logic.

    Reading doc (and discussion threads and books and …) and watching videos are relatively passive ways of learning. Taking a quiz directly tests your knowledge, exposing areas that need more attention and reinforcing expertise you already have.

    So I encourage you to sign up at plsqlchallenge.com and give it a try.

    But I write today with a specific and unusual “challenge”: as a complement to our weekly “serious” quizzes, Chris Saxon, Oracle Developer Advocate for SQL and the Database Design quizmaster, has launched the Obfuscated SQL Contest.

    Here’s the description from the site:

    Daylight savings changes are coming in across the world!
    Scheduling meetings with colleagues across the globe is always a challenge. With different countries switching on different dates (and some not switching at all!) through March this is even harder.
    To help us navigate this, we’d like a SQL query that returns the current date and time for different timezones.
    There is, however, a twist. We don’t want plain or boring or readable SQL.
    We want the most ingenious, creative, quirky and downright weird SQL you can muster to perform this task.
    And thus begins the Obfuscated SQL Contest.
    The challenge:
    Write a single SQL statement that returns the current date and time for all the following locations:
    Bangalore, London, New York, Chicago and Denver.
    The rules:
    The output must match this format (all on one line):
    BAN – DD-MON-YYYY HH24:MI:SS; LON – DD-MON-YYYY HH24:MI:SS; NEW – DD-MON-YYYY HH24:MI:SS; CHI – DD-MON-YYYY HH24:MI:SS; DEN – DD-MON-YYYY HH24:MI:SS
    Maximum length the SQL statement can be is 4,000 characters (after removing unnecessary whitespace)
    Supply the minimum version of Oracle required to execute the query and (if relevant) the maximum
    You cannot create any additional objects yourself. However you can use any objects supplied with the default installation of Oracle Enterprise Edition
    The judging panel will pick a winner or winners based on the following criteria:
    Using little-known or rarely-used features of Oracle
    Exploiting features for something other than their intended purpose
    Making use of multiple features within a single query
    Quirky, funny or otherwise ingenious SQL code

    We’ve already gotten some really hard-to-understand submissions. Can you do WORSE? 🙂 And, sure, this is a discussion forum, so I suppose you could simply answer this post with your solution. But then you will not be in the competition for winner of the VERY WORST solution.

    plsqlchallenge.com
11. Laurent Schneider Post authorMarch 23, 2015 at 16:51

    Done 🙂
12. James Su March 24, 2015 at 19:20

    I read your code, it’s amazing!

Comments are closed.