EXPLAIN EXTENDED

As many of you know, I actively participate on Stack Overflow, the leading Q/A website for software developers.

Kind folks on Stack Overflow have made their data open for examination, and anyone can query their database using this web interface at data.stackexchange.com.

Many of the questions and answers there are illustrated with links to XKCD, the web comics created by Randall Munroe.

So I decided to see which of those comics best illustrate quirks and oddities we keyboard warriors have to deal with in our daily routine.

The query itself is quite simple:

SELECT  link, cnt, id [Post Link], score
FROM    (
        SELECT  link, id, score,
                COUNT(*) OVER (PARTITION BY link) cnt,
                ROW_NUMBER() OVER (PARTITION BY link ORDER BY score DESC) rn
        FROM    (
                SELECT  id, score,
                        'http' + SUBSTRING(start, 1, PATINDEX('%/%', SUBSTRING(start, 13, 30)) + 12)
                FROM    (
                        SELECT  id, score,
                                SUBSTRING(body, PATINDEX('%://xkcd.com/[0-9]%', body), 20) start
                        FROM    (
                                SELECT  TOP 400000
                                        id, body, score
                                FROM    posts
                                ORDER BY
                                        id DESC
                                UNION
                                SELECT  TOP 4000000
                                        postId, text, score
                                FROM    comments
                                ORDER BY
                                        id DESC
                                ) q
                        WHERE   body LIKE '%://xkcd.com/[0-9]%'
                        ) q
                ) q (id, score, link)
        ) q
WHERE   rn = 1
ORDER BY
        cnt DESC

The TOP clauses are there to limit the query size, as their hosting provider does now allow fulltext indexing, and without them the query would just time out. This query only searches recent comments and posts, so some famous and top-voted posts might not be present here. Still, it's a good sample.

You can see all results here, and we'll just discuss the top 5 entries.

#5. Random Number (10 links)

Of course the questions which link to it deal with random number generators of any kind and fallacies in their implementation.

The top voted post:

Is 161803398 A 'Special' Number? Inside of Math.Random()

For some (probably good) reason, the Golden Ratio seems a good choice to seed a random number generator.

It's interesting that one of the referenced papers, Pseudo Random Number Generators in Programming Languages, displays the XKCD comic right on its first page.

#4. goto (10 links)

goto is a construct in some programming languages which allows to make an unconditional jump to an arbitrary point within a program or function, skipping all currently executing loops or statement blocks. Many developers (and apparently velociraptors) consider this bad programming practice.

The top voted comment goes on this post:

Getting Parse Error Unexpected "

, where the author asks not to pick on his gotos, to no avail. The top comment linked to the comic with 5 upvotes.

#3. Password Strength (11 links)

Instead of using random character sequences for passwords (which are hard to remember), Randall suggests using passphrases in natural language, which are both more secure and easier to memorize.

The top voted comment is on a post which asks for a regexp to validate passwords with enough complexity:

Complex password validation using regex

This regexp would not allow using such passphrases. The comment author subtly points to this fact with an XKCD link.

#2. Wisdom of the Ancients (16 links)

It's so frustrating when you are searching for a rare and specific problem on the internet, and find a single post which addresses the exactly same problem but does not mention how has it been solved, if at all.

The top voted comment goes on this post:

Class bytes found but defineClass()failed for error when deploying EAR

The guy who had posted it apparently spent much time searching for a solution, and when he had finally solved the problem, he was so kind as to post both the problem and the solution on Stack Overflow.

The comment is just a thank you with a link to the comic. And judging by the comment and the number of upvotes, it helped at least three people.

Good job, seriously! All do as Jarrod Roberson does.

And the winner with astonishing 96 links is, of course

#1. Exploits of a Mom (96 links)

A cunning mom named her son so that entering his name into a database not protected against SQL injection would ruin it.

This is actually not unheard of, and there is a real story you may find fun to read: Searching for a customer whose last name is Null kills the application.

Also, people or Irish descent are apparently pretty much irritated with the wrong SQL injection handling techniques as they try to submit their surnames on websites.

The top voted comment goes on this question:

SQL Injectable Webpage

whose author in fact tries to write code vulnerable to SQL injection on purpose, but fails.