Dr. Doom Strikes again



  •  I was browsing through the marvel online library and suddenly it seems that Dr. Doom has decided to test it's death ray on them.

    Hell of an SQL query kindly reformated by me:

       1  /* fetch generic dc list */
       2  SELECT DISTINCT 
    3 s.series_id,
    4 i.issue_id,
    5 s.series_name as sname,
    6 i.issue_number as inum,
    7 s.series_start_year as syear
    8 FROM 9 `catalog`.`series` s
    10 JOIN `catalog`.`issues` i
    11 ON s.series_id = i.series_id
    12 LEFT JOIN `catalog`.`collections` col
    13 ON i.Issue_id = col.Issue_id
    14 LEFT JOIN `catalog`.`story` st
    15 ON col.story_id = st.story_id
    16 LEFT JOIN `marvel_content`.`character_relations` cr
    17 ON (
    18 cr.content_id = st.story_id
    19 AND cr.content_type = 'comic_story' 20 AND cr.appearance_type IS NOT NULL
    21 )
    22 LEFT JOIN `marvel_content`.`characters` c
    23 ON c.character_id = cr.character_id
    24 LEFT JOIN `catalog`.`creator_relations` cre_r
    25 ON st.story_id = cre_r.story_id
    26 LEFT JOIN `catalog`.`creators` cre
    27 ON cre.creator_id = cre_r.creator_id
    28 LEFT JOIN `marvel_content`.`graphic_usage` gu
    29 ON gu.content_id = st.story_id
    30 AND gu.content_type = 'comic_story' 31 LEFT JOIN `marvel_content`.`graphic` g
    32 ON g.graphic_id = gu.graphic_id
    33 JOIN `marvel`.`dotcomics_issues` dc
    34 ON dc.catalog_id = i.issue_id
    35 JOIN marvel_content.content_publication_zones cpz0 36 ON cpz0.content_id = dc.dotcomics_issue_id
    37 AND cpz0.content_type = 'digitalcomic' 38 JOIN marvel_content.publication_zones pz0 39 ON pz0.id = cpz0.publication_zone_id
    40 AND pz0.name = 'marvel_site_zone' 41 JOIN marvel_content.content_publication_zones cpz1 42 ON cpz1.content_id = i.issue_id
    43 AND cpz1.content_type = 'comic_issue' 44 JOIN marvel_content.publication_zones pz1 45 ON pz1.id = cpz1.publication_zone_id
    46 AND pz1.name = 'marvel_site_zone' 47 JOIN marvel_content.content_publication_zones cpz2 48 ON cpz2.content_id = s.series_id
    49 AND cpz2.content_type = 'comic_series' 50 JOIN marvel_content.publication_zones pz2 51 ON pz2.id = cpz2.publication_zone_id
    52 AND pz2.name = 'marvel_site_zone' 53 WHERE 54 s.series_name = 4
    55 AND Series_start_year = 2004 56 AND dc.qa_by <> 0 57 AND cpz0.active = 1 58 AND '2010-06-10 14:03:45' BETWEEN cpz0.start_date AND cpz0.end_date
    59 AND cpz1.active = 1 60 AND '2010-06-10 14:03:45' BETWEEN cpz1.start_date AND cpz1.end_date
    61 AND cpz2.active = 1 62 AND '2010-06-10 14:03:45' BETWEEN cpz2.start_date AND cpz2.end_date
    63 ORDER BY 64 i.Series_name,
    65 syear,
    66 i.Issue_number


  •  As much as I hate"frist", everybody MUST do ONE in his/her life. So here's my first frist.

     

    Also, RE the SQL query: SHAZAM!



  • Would you please point out the WTF? I'm too lazy to read through it all and look for it.



  • @pbean said:

    Would you please point out the WTF? I'm too lazy to read through it all and look for it.
    The WTF is using an image containing nothing other than text?



  • @pbean said:

    Would you please point out the WTF? I'm too lazy to read through it all and look for it.

    Let me guess.... Line 65 - sort on column alias instead of s.series_start_year? Weird quotes?  If this is Oracle, lowercase object names?  Underscores instead of PascalCase?

    Nothing big really jumps out at me.



  • I mean, it's a hellishly long query, across 3 schemas (assuming this is SQL Server, or possibly databases if not) and a dozen table, with 3 sets of almost identical self joins, but I don't see anything wrong with it. I might put the self join section into views, but I don't think it would make that much difference.



  •  For those who keep wondering about what kind of DB this is about (quoted from the image):
    "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'"



  • @toth said:

    I mean, it's a hellishly long query, across 3 schemas (assuming this is SQL Server, or possibly databases if not) and a dozen table, with 3 sets of almost identical self joins, but I don't see anything wrong with it.

    How about the query being sent in full-text to an END USER!?



  • @locallunatic said:

     For those who keep wondering about what kind of DB this is about (quoted from the image):
    "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'"

    Ah, yes, good point. Missed that somehow. So I assume that the two-part name is database.table? It's been a while since I've worked with MySQL.



  • @blakeyrat said:

    @toth said:
    I mean, it's a hellishly long query, across 3 schemas (assuming this is SQL Server, or possibly databases if not) and a dozen table, with 3 sets of almost identical self joins, but I don't see anything wrong with it.

    How about the query being sent in full-text to an END USER!?

    Well, yes, obviously there's that. But I was just addressing the query itself, which is what I assumed the OP was trying to point out (since he went to the trouble of formatting it and uploading it).



  • @bstorer said:

    The WTF is using an image containing nothing other than text?
     

    TRWTF s that it's a screenshot of subpixel antialiased text, indicating that it was right there on the OP's screen.



  • Or maybe it's the comment "Fetch Generic DC list" and then a query joining a load of tables with 'Marvel' in the name?

    Zad



  • @blakeyrat said:

    @toth said:
    I mean, it's a hellishly long query, across 3 schemas (assuming this is SQL Server, or possibly databases if not) and a dozen table, with 3 sets of almost identical self joins, but I don't see anything wrong with it.

    How about the query being sent in full-text to an END USER!?

     

    Well, my SQL isn't that good(I have to wait another year till my university teaches me that and my school-sql just plain sucked), but I doubt there's anything in there you can use? I don't see any Missing input checking or something?



  • I guess, the WTF is that you think that query is long?

    The query I am currently working on has several hundred lines. It generates a tree with all budgets in a multinational company, with sums over all departments. Departments e.g. have ordered Desks for 100.000$, computers for 10.000 Yen and stuff like that. All those assets are multiplied on the fly with the "correct" exchange ratio, summed up by department and all. Of course, controllers only see budgets if they have sufficient rights. And it calculates a couple more things.e.g. the above desk example is wildly simplified, since assets can be paid by installments and each installment has to be multiplied with the correct exchange ratio.



  • @dhromed said:

    @bstorer said:

    The WTF is using an image containing nothing other than text?
     

    TRWTF s that it's a screenshot of subpixel antialiased text, indicating that it was right there on the OP's screen.

    That's what I'm saying.  Was copy/paste somehow more difficult than taking a screenshot, uploading it, and inserting it into the post?  It's not like the font or exact layout of the text is important, either.


  • @Zadkiel said:

    Or maybe it's the comment "Fetch Generic DC list" and then a query joining a load of tables with 'Marvel' in the name?

    Zad

    The real WTF is that Marvel will print TPBs that they only ship to local comics shops-- they don't sell them online, or direct, and they only print a couple hundred copies. Part of some retardedly-misguided effort to get people to walk into comics shops, I guess?

    Meanwhile, people who want the books but don't have a LCS nearby (or refuse to be seen dead in one, because their local one is so shitty), can't get a copy except by waiting a month and hitting ebay. Where they all seem to end up. Of course, where would you expect them to end up... "let's print a book then make it nearly impossible to actually buy!!!"

    Do they still do that? That is the real WTF.



  • @bstorer said:

    Was copy/paste somehow more difficult than taking a screenshot, uploading it, and inserting it into the post?
     

    I still entertain the posibility that the OP is aware of this but this screenshot was all that the OP had, HOWEVER that has the retroactive WTF of failing to mention why one is throwing a screenshot of text to such a scrutinous pedantic pack of wolves such as ourselves.



  • @SQLDave said:

    Also, RE the SQL query: SHAZAM!

     

    Comic Mismatch Error:  SHAZAM! is a DC comic, not Marvel.

     



  •  @dtobias said:

    @SQLDave said:

    Also, RE the SQL query: SHAZAM!

     

    Comic Mismatch Error:  SHAZAM! is a DC comic, not Marvel.

     

    Took you guys long enough.

     Actually, I was quoting Gomer Pyle.



  • Only 16 joins, no subqueries, no recursion... And it probably doesn't even take half an hour to run. If only all SQL queries could be this easy to read and debug.



  • @blakeyrat said:

    The real WTF is that Marvel will print TPBs that they *only* ship to local comics shops-- they don't sell them online, or direct, and they only print a couple hundred copies. Part of some retardedly-misguided effort to get people to walk into comics shops, I guess?

    Meanwhile, people who want the books but don't have a LCS nearby (or refuse to be seen dead in one, because their local one is so shitty), can't get a copy except by waiting a month and hitting ebay. Where they all seem to end up. Of course, where would you expect them to end up... "let's print a book then make it nearly impossible to actually buy!!!"

    Do they still do that? That is the real WTF.

    It's the collectibles industry.  Pretty much nothing here is a WTF.  The more available something is, the less collectible it is.  Do you understand that?



  • @belgariontheking said:

    It's the collectibles industry.  Pretty much nothing here is a WTF.  The more available something is, the less collectible it is.  Do you understand that?

    No.

    But that aside, I wanted to *read* the book, not "collect" it. They're books, not trading cards. Maybe they should have a checkbox that said, "I don't give a shit about collecting", and when I used it they'd send me the book with a torn-up cover smeared with jam. Or something, I dunno.

    It's still retarded for a *publishing* company to make it hard to buy their own books.


Log in to reply