Create your own SQL WTF

Posted in Code, Computers & Technology, Humour by Elliott Back on August 17th, 2005.

Have you ever fealt jealous reading the Daily WTF, just knowing that you have it in you to do so much worse? Really, the code samples they post there are almost acceptable sometimes ( thedailywtf.com/forums/41025/ShowPost.aspx ). So, without further ado let me present a comment SQL use case that we can turn into the biggest SQL coding mistake ever seen:

SELECT
    co.order_day,
    co.order_id,
    cust.customer_name,
    cust.customer_id,
    SUM(item.cost * item.qty)
FROM
    customer_orders co
JOIN
   order_items item
ON
    co.order_id = item.item_id
JOIN
   customers cust
ON
    co.customer_id = cust.customer_id
WHERE
    order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')
GROUP BY
    co.order_day,
    co.order_id,
    cust.customer_name,
    cust.customer_id

That’s a pretty reasonable summery query over the last month, so lets see how we can make it worse. The first sql deoptimization we can do is make those joins a lot worse:

SELECT
    co.order_day,
    co.order_id,
    cust.customer_name,
    cust.customer_id,
    SUM(item.cost * item.qty)
FROM
    customer_orders co
FULL OUTER JOIN
    order_items item
ON
    co.order_id = item.item_id
FULL OUTER JOIN
    customers cust
ON
    co.customer_id = cust.customer_id
WHERE
    order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')
    AND NOT NULL co.order_day
    AND NOT NULL co.order_id
    AND NOT NULL cust.customer_name
    AND NOT NULL cust.customer_id
    AND NOT NULL item.cost
    AND NOT NULL item.qty
GROUP BY
    co.order_day,
    co.order_id,
    cust.customer_name,
    cust.customer_id

We compensate for the outer join by disallowing nulls. But, that makes me wonder–why selecting so many columns all at once? It would be better if the oracle database only had to deal with one of them at a time!

SELECT
(SELECT
    co.order_day,
FROM
    customer_orders co
WHERE
    order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')
    AND NOT NULL co.order_day) as order_day,
(SELECT
    co.order_id
FROM
    customer_orders co
WHERE
    order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')
    AND NOT NULL co.order_id

    AND NOT NULL co.order_day) as order_id,
(SELECT
    cust.customer_name,
FROM
    customer_orders co
FULL OUTER JOIN
    customers cust
ON
    co.customer_id = cust.customer_id
WHERE
    order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')
    AND NOT NULL co.order_day
    AND NOT NULL cust.customer_name
    AND NOT NULL cust.customer_id) as customer_name,
(SELECT
    cust.customer_id,
FROM
    customer_orders co
FULL OUTER JOIN
    customers cust
ON
    co.customer_id = cust.customer_id
WHERE
    order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')
    AND NOT NULL co.order_day
    AND NOT NULL cust.customer_name
    AND NOT NULL cust.customer_id) as customer_id,
(SELECT
    SUM(item.cost * item.qty)
FROM
    customer_orders co
FULL OUTER JOIN
    order_items item
ON
    co.order_id = item.item_id
WHERE
    order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')
    AND NOT NULL co.order_day
    AND NOT NULL co.order_id
    AND NOT NULL item.cost
    AND NOT NULL item.qty) as total
FROM
    customer_orders co

That’s gonna be great–but what if we cache the columns we want, and then just join them to an id-only table? That would rock!!

SELECT
    *
FROM
  customer_orders id_table
JOIN
(SELECT
(SELECT
    co.order_day,
FROM
    customer_orders co
WHERE
    NOT NULL co.order_day) as order_day,
(SELECT
    co.order_id
FROM
    customer_orders co
WHERE
    NOT NULL co.order_id
    AND NOT NULL co.order_day) as order_id,
(SELECT
    cust.customer_name,
FROM
    customer_orders co
FULL OUTER JOIN
    customers cust
ON
    co.customer_id = cust.customer_id
WHERE
    NOT NULL co.order_day
    AND NOT NULL cust.customer_name
    AND NOT NULL cust.customer_id) as customer_name,
(SELECT
    cust.customer_id,
FROM
    customer_orders co
FULL OUTER JOIN
    customers cust
ON
    co.customer_id = cust.customer_id
WHERE
    NOT NULL co.order_day
    AND NOT NULL cust.customer_name
    AND NOT NULL cust.customer_id) as customer_id,
(SELECT
    SUM(item.cost * item.qty)
FROM
    customer_orders co
FULL OUTER JOIN
    order_items item
ON
    co.order_id = item.item_id
WHERE
    NOT NULL co.order_day
    AND NOT NULL co.order_id
    AND NOT NULL item.cost
    AND NOT NULL item.qty) as total
FROM
    customer_orders co) cache
ON
    id_table.order_id = cache.order_id
WHERE
    id_table.order_day BETWEEN to_date('20050601', 'yyyymmdd')
    AND to_date('20050630', 'yyyymmdd')

Well, I could go on, but are you tired yet?

This entry was posted on Wednesday, August 17th, 2005 at 7:17 pm and is tagged with day co, customer id, customer name, oracle database, customer orders, nulls, code samples, use case, aspx, sql, wtf, oracle, mistake. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback.