• Alistair (unregistered)

    If that is Oracle, st.age!='' will always be false, because an empty string is treated as null.

  • (nodebb)

    They forgot to add the location to that column and to rename it to ASL.

  • Richard Smithers (unregistered)

    The gender display is fine. They were just ahead of their time in allowing non-binary gender types...

  • Kuli (unregistered)

    I wonder how they can have the aggregating sum() functions without any GROUP BY or OVER clause.

    Bonus points for renaming the email column to email.

  • DaveD (unregistered) in reply to Alistair

    The if() function looks like mysql to me.

  • Jay (unregistered)

    It also sounds like they have a data-entry problem if they have to run all those "REPLACE" statements. I guess you could replace it with a "CASE" statement, but ultimately all that would do is make it more readable. You could add a lookup table and then deal with string comparison look ups. I wonder what that would do to performance?

    That also assumes this field only contains one of those. It's possible that each row could contain a string of any applicable key-words, in which case, the above solutions would not apply.

    Also since they never included a table for the "su" alias, it would fail.

    "st.return+1 as Visits". I would need to see what this field is and how it's being used.

    "st.email AS email". I point this out because the 3 fields before it are not aliased, so it's inconsistent and in this case redundant.

    "LEFT JOIN location AS l ON sr.location_id=l.id". The location table is never used in the SELECT clause and the "sr" alias is never defined.

    "WHERE 1=1". This is from a template where you insert a condition depending on what you need to report. This and the 2 undefined aliases makes me think the "Contractor" has no idea how the schema works and just took pieces of existing queries and stuck them together.

  • DaveD (unregistered) in reply to Kuli

    This appears to be mysql, which can allow aggregate functions without GROUP BY (or a GROUP BY without specifying all the non-aggregate columns). The results can be surprising.

  • Saved by the Dumb(bell) (unregistered)

    Congratulations! Your information is secure from prying eyes, because they are too bungling to record it.

  • Saved by the (Dumb)Bell (unregistered)

    ^^ Name should be this!

  • Airdrik (unregistered) in reply to Jay

    wrt "a data entry problem" - take a second look at the column names involved. They are deriving the Login Type column using the social_network column (presumably because this is one of those sites that supports authentication using any of those platforms; though 'password', 'free' and 'custom' are odd choices for social networks, so there's another wtf lurking in there); and then also including the social_network column 3 lines down. Therefore we must assume that the social_network column is correctly populated and that it is a simple mapping from the name of the social network to the login type (which as you pointed out would be better handled by Case matching or a separate lookup table).

    I definitely agree that the contractor had no idea what they were doing when they assembled this monstrosity.

  • Quirkafleeg (unregistered)

    Logical genius, that coder. I need an explanation for the operational complexity of "WHERE 1=1"...

  • (nodebb) in reply to Quirkafleeg

    Some people do that as the first line of a WHERE statement so that all of the following " AND x=y" clauses line up nicely, and the first (real) clause can be commented out for debugging without having to edit the next line to remove its AND.

    WHERE 1=1 AND x=y AND z=7 etc


    WHERE x=y AND z=7 etc.

    Addendum 2021-11-08 14:52: Sigh.

    WHERE 1=1
        AND x=y
        AND z=7


    WHERE x=7
        AND z=7
  • Strong Steroids (unregistered)
    Comment held for moderation.
  • eric bloedow (unregistered)

    i seem to remember an old magazine article, where someone had made an entire game that was all on one line...

  • shabnam.f74 (unregistered)
    Comment held for moderation.
  • Oopsie Daisies (unregistered)

    @Remy All I care about is X vs Y chromosomes /shrug

Leave a comment on “A Replacement Operation”

Log In or post as a guest

Replying to comment #:

« Return to Article