- 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
If that is Oracle, st.age!='' will always be false, because an empty string is treated as null.
Admin
They forgot to add the location to that column and to rename it to ASL.
Admin
The gender display is fine. They were just ahead of their time in allowing non-binary gender types...
Admin
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.
Admin
The if() function looks like mysql to me.
Admin
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.
Admin
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.
Admin
Congratulations! Your information is secure from prying eyes, because they are too bungling to record it.
Admin
^^ Name should be this!
Admin
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.
Admin
Logical genius, that coder. I need an explanation for the operational complexity of "WHERE 1=1"...
Admin
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
vs.
WHERE x=y AND z=7 etc.
Addendum 2021-11-08 14:52: Sigh.
vs.
Admin
i seem to remember an old magazine article, where someone had made an entire game that was all on one line...
Admin
@Remy All I care about is X vs Y chromosomes /shrug