- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Moorland is effective as well.
Admin
This looks like auto-generated SQL to me, probably by some reporting tool, or a JPA tool like Hibernate generating queries from the JPA Criteria API. Or a reporting tool using the JPA Criteria API - it's turtles all the way down!
And somebody thought it's a useful query and copied it as-is, adding a few extra tweaks. That would explain the non-existing formatting..
Admin
The precedence of OR makes those equivalent, but of course, it's handy for a maintainer to actually be able to see that.
Admin
Except for the poor formatting and the old style joins, this is reminiscent of some of the queries I have written. I've come up with some real winners. This is a minor demo.
Admin
If you have to maintain queries that long, you'll learn that ANSI join is godsend. It enables you to conveniently, cleanly do multiline comment to the query, then add them back one by one by just moving the starting comment mark. This makes the style extremely efficient for debugging why a query is duplicate rows instead of using the evil "distinct" statement to mask the problem.
Admin
The only time I find them useful is when I'm intentionally performing a Cartesian product of several tables. Even then,
CROSS JOINis cleaner.Admin
SELECT "posts"."id" AS t0_r0, "posts"."user_id" AS t0_r1, "posts"."topic_id" AS t0_r2, "posts"."post_number" AS t0_r3, "posts"."raw" AS t0_r4, "posts"."cooked" AS t0_r5, "posts"."created_at" AS t0_r6, "posts"."updated_at" AS t0_r7, "posts"."reply_to_post_number" AS t0_r8, "posts"."reply_count" AS t0_r9, "posts"."quote_count" AS t0_r10, "posts"."deleted_at" AS t0_r11, "posts"."off_topic_count" AS t0_r12, "posts"."like_count" AS t0_r13, "posts"."incoming_link_count" AS t0_r14, "posts"."bookmark_count" AS t0_r15, "posts"."avg_time" AS t0_r16, "posts"."score" AS t0_r17, "posts"."reads" AS t0_r18, "posts"."post_type" AS t0_r19, "posts"."vote_count" AS t0_r20, "posts"."sort_order" AS t0_r21, "posts"."last_editor_id" AS t0_r22, "posts"."hidden" AS t0_r23, "posts"."hidden_reason_id" AS t0_r24, "posts"."notify_moderators_count" AS t0_r25, "posts"."spam_count" AS t0_r26, "posts"."illegal_count" AS t0_r27, "posts"."inappropriate_count" AS t0_r28, "posts"."last_version_at" AS t0_r29, "posts"."user_deleted" AS t0_r30, "posts"."reply_to_user_id" AS t0_r31, "posts"."percent_rank" AS t0_r32, "posts"."notify_user_count" AS t0_r33, "posts"."like_score" AS t0_r34, "posts"."deleted_by_id" AS t0_r35, "posts"."edit_reason" AS t0_r36, "posts"."word_count" AS t0_r37, "posts"."version" AS t0_r38, "posts"."cook_method" AS t0_r39, "posts"."wiki" AS t0_r40, "posts"."baked_at" AS t0_r41, "posts"."baked_version" AS t0_r42, "post_search_data"."post_id" AS t1_r0, "post_search_data"."search_data" AS t1_r1, "topics"."id" AS t2_r0, "topics"."title" AS t2_r1, "topics"."last_posted_at" AS t2_r2, "topics"."created_at" AS t2_r3, "topics"."updated_at" AS t2_r4, "topics"."views" AS t2_r5, "topics"."posts_count" AS t2_r6, "topics"."user_id" AS t2_r7, "topics"."last_post_user_id" AS t2_r8, "topics"."reply_count" AS t2_r9, "topics"."featured_user1_id" AS t2_r10, "topics"."featured_user2_id" AS t2_r11, "topics"."featured_user3_id" AS t2_r12, "topics"."avg_time" AS t2_r13, "topics"."deleted_at" AS t2_r14, "topics"."highest_post_number" AS t2_r15, "topics"."image_url" AS t2_r16, "topics"."off_topic_count" AS t2_r17, "topics"."like_count" AS t2_r18, "topics"."incoming_link_count" AS t2_r19, "topics"."bookmark_count" AS t2_r20, "topics"."star_count" AS t2_r21, "topics"."category_id" AS t2_r22, "topics"."visible" AS t2_r23, "topics"."moderator_posts_count" AS t2_r24, "topics"."closed" AS t2_r25, "topics"."archived" AS t2_r26, "topics"."bumped_at" AS t2_r27, "topics"."has_summary" AS t2_r28, "topics"."vote_count" AS t2_r29, "topics"."archetype" AS t2_r30, "topics"."featured_user4_id" AS t2_r31, "topics"."notify_moderators_count" AS t2_r32, "topics"."spam_count" AS t2_r33, "topics"."illegal_count" AS t2_r34, "topics"."inappropriate_count" AS t2_r35, "topics"."pinned_at" AS t2_r36, "topics"."score" AS t2_r37, "topics"."percent_rank" AS t2_r38, "topics"."notify_user_count" AS t2_r39, "topics"."subtype" AS t2_r40, "topics"."slug" AS t2_r41, "topics"."auto_close_at" AS t2_r42, "topics"."auto_close_user_id" AS t2_r43, "topics"."auto_close_started_at" AS t2_r44, "topics"."deleted_by_id" AS t2_r45, "topics"."participant_count" AS t2_r46, "topics"."word_count" AS t2_r47, "topics"."excerpt" AS t2_r48, "topics"."pinned_globally" AS t2_r49, "categories"."id" AS t3_r0, "categories"."name" AS t3_r1, "categories"."color" AS t3_r2, "categories"."topic_id" AS t3_r3, "categories"."topic_count" AS t3_r4, "categories"."created_at" AS t3_r5, "categories"."updated_at" AS t3_r6, "categories"."user_id" AS t3_r7, "categories"."topics_year" AS t3_r8, "categories"."topics_month" AS t3_r9, "categories"."topics_week" AS t3_r10, "categories"."slug" AS t3_r11, "categories"."description" AS t3_r12, "categories"."text_color" AS t3_r13, "categories"."read_restricted" AS t3_r14, "categories"."auto_close_hours" AS t3_r15, "categories"."post_count" AS t3_r16, "categories"."latest_post_id" AS t3_r17, "categories"."latest_topic_id" AS t3_r18, "categories"."position" AS t3_r19, "categories"."parent_category_id" AS t3_r20, "categories"."posts_year" AS t3_r21, "categories"."posts_month" AS t3_r22, "categories"."posts_week" AS t3_r23, "categories"."email_in" AS t3_r24, "categories"."email_in_allow_strangers" AS t3_r25, "categories"."topics_day" AS t3_r26, "categories"."posts_day" AS t3_r27 FROM "posts" LEFT OUTER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" LEFT OUTER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" WHERE ("posts"."deleted_at" IS NULL) AND (post_search_data.search_data @@ TO_TSQUERY('german', 'liebe:*')) AND "topics"."deleted_at" IS NULL AND (topics.visible) AND (topics.archetype <> 'private_message') AND ((categories.id IS NULL) OR (NOT categories.read_restricted) OR (categories.id IN (270))) AND (posts.post_number = 1 OR posts.topic_id = 7173154) ORDER BY CASE WHEN topics.id = 7173154 THEN 0 ELSE 1 END, CASE WHEN topics.id = 7173154 THEN posts.post_number ELSE 999999 END, TS_RANK_CD(TO_TSVECTOR('german', topics.title), TO_TSQUERY('german', 'liebe:*')) DESC, TS_RANK_CD(post_search_data.search_data, TO_TSQUERY('german', 'liebe:*')) DESC, topics.bumped_at DESC LIMIT 6; SELECT u.id AS user_id, topics.id AS topic_id, topics.created_at, highest_post_number, last_read_post_number, c.name AS category_name, tu.notification_level FROM users u INNER JOIN user_stats AS us ON us.user_id = u.id FULL OUTER JOIN topics ON 1=1 LEFT JOIN topic_users tu ON tu.topic_id = topics.id AND tu.user_id = u.id LEFT JOIN categories c ON c.id = topics.category_id WHERE u.id IN (-2) AND topics.archetype IN ('regular') AND (("topics"."deleted_at" IS NULL AND tu.last_read_post_number < topics.highest_post_number AND COALESCE(tu.notification_level, 1) >= 2) OR ("topics"."deleted_at" IS NULL AND topics.created_at >= GREATEST(CASE WHEN COALESCE(u.new_topic_duration_minutes, 2880) = -1 THEN u.created_at WHEN COALESCE(u.new_topic_duration_minutes, 2880) = -2 THEN COALESCE(u.previous_visit_at,u.created_at) ELSE ('2014-06-16 13:52:48.210894'::timestamp - INTERVAL '1 MINUTE' * COALESCE(u.new_topic_duration_minutes, 2880)) END, us.new_since) AND tu.last_read_post_number IS NULL AND COALESCE(tu.notification_level, 2) >= 2)) AND (topics.visible OR u.admin OR u.moderator) AND topics.deleted_at IS NULL AND ( category_id IS NULL OR NOT c.read_restricted OR category_id IN ( SELECT c2.id FROM categories c2 JOIN category_groups cg ON cg.category_id = c2.id JOIN group_users gu ON gu.user_id = u.id AND cg.group_id = gu.group_id WHERE c2.read_restricted )) AND NOT EXISTS( SELECT 1 FROM category_users cu WHERE cu.user_id = u.id AND cu.category_id = topics.category_id AND cu.notification_level = 0) ORDER BY topics.bumped_at DESC LIMIT 500Admin
I hope you feel a little dirty after writing that.
Admin
https://www.riking.org/webm/bad-horse-letter.webm
Admin
Yes, I was sorry about what happened to Jeph Jaques' web site, too.
Admin
Just add some
NOLOCKhints and you'll feel dirty reading it, too!Admin
wait.... what did i miss about QC?
Admin
Or alternatively Oracle fixed a bug in 11.2 but the application depends on the wrong result being returned, which can result in an interesting conversation with developers/project managers.
Admin
Not QC, walmart.horse, which he gave up over the weekend, because Walmart is tone-deaf and threatened him with legal action, as if anyone would think walmart.horse would actually lead to trademark dilution.
Admin
it used the name walmart. so they sued.
lawyers are not known for having a sense of humor.
also...
/me sighs in relief
i would have missed QC
Admin
Walmart should know better than to think they have to do that.
Popehat had an excellent post some months back on why that behavior is not necessary to prevent trademark dilution.
Admin
it may not be necessary to prevent trademark dilution but it can be billed to the client as such and corporate lawyers love generating billable hours. particularly for doing things that are not technically necessary bu that the client won't question.
Admin
What was he doing with it?
Admin
Yes, but consider the source. Walmart's not generally known for frivolously spending money.
Admin
famous they are for that. this is true. but what about their legal council?
Admin
According to this article, it had a picture of a horse in front of a Walmart and played some silly music.
You can see the original contents of the site (minus the music) here.
Admin
Admin
someone should teach them about create temporary table
Admin
foxyshadis, you are correct, I parsed it wrong. But guys like me misreading it are a good case to use parentheses for clarity. :smile:
Admin
He put a picture of a walmart storefront with a horse sort of photobombing it.
Since Walmart doesn't sell horses or horse-related stuff, it seems odd how anyone could be confused that that was a Walmart website.
Admin
Who even believes that? (That using ANSI joins prevents you from excluding things in the WHERE, that is.)
That's just...*deluded*.Admin
Unless there's a whole panel of lawyers sitting there working on this, you'd be talking about a legal counsel.
Admin
Late to the party, but I feel it should be pointed out that -- strictly speaking -- comma joins are ANSI joins. They're just ANSI-89 joins. ANSI-92 introduced the improved JOIN syntax.
Admin
Ah, someone with real-world experience, I see.