• (nodebb)

    "No one is empowered to fix it or address the root cause"

    That's the real WTF

  • (nodebb) in reply to DocMonster

    Yeah, I was going to say the same thing, especially if "No one is empowered to ..." means "No one is allowed to ..."

  • Sole Purpose Of Visit (unregistered)

    I see things like this (magic numbers and all), and I wonder what all the other queries in the system look like. Hard to imagine this one is uniquely bad, particularly if there's a LOTFIL code generator used all over the place,.

  • No one (unregistered)

    How about the WTF of a reader blocking other readers/writers

  • Steve (unregistered)

    So many WTFs ...

    1. Ten INNER JOINS to the same table on the same parameter
    2. WHERE 1=1 And of course TRWTF
    3. MySQL being used in (presumably) a production environment
  • Hanzito (unregistered)

    It will affect the number of rows returned if the join query isn't unique. If FL2.FLFILTYP = 'A1' AND FL2.FLLOTNBR = SALLSTDM.SLLOTNBR returns 2 rows. Then it'll multiple the output by 2^10. Now image it returns 10 rows. That would take a while.

  • CommonJoe (unregistered)

    Why wouldn't it affect the count? Every further join could reduce the total amount of rows if there is nothing to match it with. If the last table alone is empty, the count would be 0.

  • (nodebb)

    I feel the need to confess that I am prone to do the same thing. -> joining <sometable> n+1 times always seems to be easier than to faff around with PIVOT and so far -IME- does not have any ..bad|negative|whatever.. outcomes.

    /of course (what, me? !NEVER!) one would do something sensible with the joins itself and not blindly doing something but shite happens. //also I feel the need to mention that I am not not the creator of aforementioned :wtf:, I cannae remember the last time I used mySQL so this neither affected not effected me ;-)

  • (nodebb)

    It's a good thing that table and column names can't be longer than 8 characters or I'd accuse this whole schema of being needlessly miserable, even before this particular query turned up.

  • Dave K (unregistered) in reply to dkf

    dkf I can't tell you the NBR of times I've come across this TYP of problem.

  • WTFGuy (unregistered)

    When Remy wrote this:

    In an ideal world, someone would track down the source of the query, drag it out behind the barn and be done with it.

    I believe Remy actually meant "they/them " or "he/him" in place of "it".

  • Appalled (unregistered)

    WHERE 1=1 is NOT a WTF. It is an old trick for building dynamic queries from user supplied filters where there could be zero or one or more where clauses to tack on to the base query. It has no impact at all.

  • Sole Purpose Of Visit (unregistered) in reply to Appalled

    Except for the fact that there's more than zero filters in this case. You'd kinda think an automated query generator would recognise that simple case.

  • I'm not a robot (unregistered) in reply to No one
    How about the WTF of a reader blocking other readers/writers
    At least in some versions, MySQL with InnoDB performs very poorly when there are a lot of queries (more precisely, CPU cores) simultaneously reading the same non-primary-key index - and once it starts to slow down, that increases the chance of even more queries coming along before the existing ones finish and slowing it down further, making for a nice death spiral. Maybe that's what's happening here.
  • WTFGuy (unregistered)

    As to the blocking reader, the story tells us this ghastly query is part of a larger procedure. So I'm betting its something like this:

    Lock table(s) (or the whole DB) "so nothing changes"
Perform some querie(s) to gather metadata into @ vars
Perform this WTF query to determine @fullCount
Armed with all those @vars, do some other queries or updates or transformations that depend on the @vars
Unlock the table(s)/DB.

    If you (IOW that dev or DBA shop) don't know what a transaction is, this may be the only way to avoid dirty reads leading to dirty writes.

  • (nodebb) in reply to WTFGuy

    I believe Remy actually meant "they/them " or "he/him" in place of "it".

    Or maybe even "she/her". The first programmer I ever met was a woman. Er. My own mother.

  • Barry Margolin (github) in reply to CommonJoe

    Since all of the joins are with the same table and the same ON condition, the number of rows in each join will be the same. If the last one is zero rows, so is the first one.

    However, a join creates a cross product, so if each join is with multiple rows, the size of the result will multiply, not shrink.

  • Ryan (unregistered)

    I have to admit this is the first time I've read one of these where I involuntarily said the WTF phrase out loud.

  • Yikes (unregistered)

    Agree that it will multiply the row counts! In which case, "ew, dat's nastay!"

    If he can't fix the query generator, is it possible to interject some sort of preprocessing stage at the database where it recognizes the string pattern in the query and replaces it with the intended line?

  • WTFGuy (unregistered)

    If we assume this is a special case of a more general query built in response to some user selections, I wonder how it performs if the 10 joins are each on different conditions still against the same table? IOW, what if each of the Fx.FLFILTYP = "A1" comparisons are different constants, not all "A1"?

    Still gonna be ugly.

    I don't work with MySQL, but I also wonder if count(*) is less efficient than count(1), or count(ThePKField)?

  • (nodebb) in reply to Steve_The_Cynic

    I'll tell you more: the first known programmer to live was a woman.

  • (nodebb) in reply to Sole Purpose Of Visit

    You'd kinda think an automated query generator would recognise that simple case.

    The code is marginally simpler with the 1 = 1 case.

    Firstly, if there are no filters, you don't haver to omit the WHERE clause from the query you are building.

    Secondly, you don't need the special code to detect whether this is the first filter or not. For every filter including the first one, you can add AND (filter-n) to the where clause.

  • He, Always (unregistered) in reply to Steve_The_Cynic

    That's the problem with kowtowing to political-agenda speech.

    "He" is the proper singular/third-person pronoun, period. Not "they," not "he/she"...

    As Shakespeare would have said, "Save your face from the putrid gash and grow a pair."

  • (nodebb) in reply to dkf

    I smell a converted DB2 database - why else would the column names be no more than 8 characters?

  • Jeremy (unregistered)
    Comment held for moderation.
  • JustADBA (unregistered)

    The 1=1 clause indicates that this was not generated by an ORM. Someone actually created this query by hand, for…. reasons.

  • smf (unregistered) in reply to He, Always
    Comment held for moderation.
  • (nodebb) in reply to He, Always

    Except singular they has been around for centuries before all the gender stuff even became a thing.

    https://public.oed.com/blog/a-brief-history-of-singular-they/

Leave a comment on “Query Lockup”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article