- 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
<font size="2">they should just ban all code.
obviously, it's evil.</font>
Admin
This strikes me as code that was intentionally written to be difficult to understand, whether for fun/amusement or to show off the authors 1337ness.
Admin
Don't ban all the code... just ban features that make our jobs easier :)
Admin
Hey, wasn't this discussed in a a previous post?
Admin
<font size="2">
Doesn't code make your job easier?
Try writing software without it.</font>
Admin
Thats it, make it a requirement that every piece of software a programmer writes be in machine code with no use of 3rd party libaries. (Some OS calls are permitted...)
Admin
That discussion prompted me to post this one :-). It kinda ... sorta makes sense why they used ACOS(-1) ... I'll break down a predicate:
NVL(BCTRY_ID, TO_CHAR(ACOS(-1))) = DECODE(MOD(FLOOR(VLOOPER /4), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))
If IsNull(BCTRY_ID) Then
LeftOperand = PI
Else
LeftOperand = BCTRY_ID
If Floor(VLOOPER/2) Mod 2 = 0 Then
RightOperand = 0
Else
RightOperand = PI
What still gets me is WTF is with the Floor/Mod nonsense. Any ideas?
Admin
For some people, giving them the job of writing software makes my job much harder :(
Admin
I guessed. I was surprised to see this actually used in practice rather than an acedemic exercise at code obfuscation.
My guess the floor/mod stuff is the same as saying "Every fourth iteration". I'm PL/SQL ignorant (I've never had the pleasure of using Oracle), but what does NVL do, and why use PI?
Admin
That has a name around here. "Doing Negative Work".
Admin
NVL (value1, value2) is like isnull in SQL Server.
if value1 is null then
return value2
else
return value1
end
This procedure needs an isbull () function.
Admin
Or rather creates a pattern like TTFFTTFF [:$]
Admin
I've never used ACOS, but I have used ASIN in T-SQL:
CREATE FUNCTION DistanceBetween
-- given the latitude and longitude of two points on the surface of the earth
-- this returns the distance in miles between them as the crow flies
-- The first point is (@lata, @longa)
-- The second point is (@latb, @longb)
(
@lata float,
@longa float,
@latb float,
@longb float
)
RETURNS float
AS
BEGIN
RETURN
( (
6367 *
0.6214
) *
(
2 *
Asin(
Sqrt(
(
(
1 -
Cos(
2 *
(
(
(
0.017453293 *
@lata
) -
(
0.017453293 *
@latb
)
) /
2
)
)
) /
2 +
Cos(
0.017453293 *
@latb
) *
Cos(
0.017453293 *
@lata
) *
(
1 -
Cos(
2 *
(
(
(
0.017453293 *
@longa
) -
(
0.017453293 *
@longb
)
) /
2
)
)
) /
2
)
)
)
)
)
END
Admin
That's what we used to call "smart-assery".
vlooper is a loop variable going from 0 to 7.
MOD(FLOOR(VLOOPER /4), 2), therefore, will produce 0,0,0,0,1,1,1,1, in that order.
MOD(FLOOR(VLOOPER /2), 2) will produce 0,0,1,1,0,0,1,1.
And MOD(FLOOR(VLOOPER), 2) will produce 0,1,0,1,0,1,0,1.
I can't comment further, because I don't know VB and thus don't have any idea what DECODE() will do.
Admin
Can someone summarize this for those of us unfamiliar with PL/SQL?
Admin
Why did I type VB? I meant, I don't know what DECODE does in SQL. Sorry. :)
Admin
It's essentially going through and for three columns in a database (which appear to be flags), trying all combinations of those three columns until it finds one that exists. To do this, he uses some rather obfuscated math tricks.
The fact that this could have been done with a single select and not specifying the values in those three columns seems to have escaped the original programmer.
Admin
How is that even remotely readable?
And your radius of the earth is wrong, it's 6378 km
Admin
See, just one more reason not to have constants in your code. What if the radius of the earth changes in the future? Clearly he needs a table to hold the radius of various planets, so that he can pull this information from them.
Admin
you have 3 fields in the database (bctry_id, bbusu_id, bextsys_id) and three search parameters (vctry_id, vbusu_id, vextsys_id) for these fields and want to find the best match.
(There is a bug in the procedure, vctry_id is compared twice and vbusu_id never)
One or more of the database fields can be NULL, meaning that the rule is more general.
So first it checks for a match on all search parameters, then for a rule with two matches and one NULL. The decode(mod(floor(.... magic is for deciding if you look for a match at the corresponding field or for NULL. ACOS(-1) is a arbitrary replacement for NULL, so you can compare it (NULL=NULL doesn't work). Interestingly, the procedure does not determine the best rule, only if it exists, so
select count(*) into vcnt from b2r_business_fule_usage
where bbusrl_nm = vbus_rule
and (bctry_id = vctry_id or bctry_id is null)
and (bbusu_id = vbusu_id or bbusu_id is null)
and (bextsys_id = vextsys_id or bextsys_id is null);
would probably do the job just as well (the count result might differ since this statement checks all 8 cases at once).
Admin
DECODE is like a switch-case: the first argument is the value to check, after that each pair of values consists of a value to compare, and a value to return if they match, and the last argument is the default value to return.
The NVL statement returns BEXTSYS_ID if it's there, or pi if it's not. The DECODE is used here like a ?: operator - it returns VEXTSYS_ID if the loop is at a certain point (every other iteration in this case), or pi otherwise).I think I figured out how this thing works (sort of)...
So it is alternating checking to see if BEXTSYS_ID is VEXTSYS_ID or null (i.e. pi) for each iteration. Since there are 3 comparisons like this, it is doing this for 3 fields, with different loop iteration checkers (MOD 2, MOD 4...).
This guy definitely had too much time on his hands, that's for sure.
--Daniel T
Admin
Frighteningly enough, I actually understand what this code is doing. And the comments are accurate, if not quite detailed enough.
Here is a slightly tweaked code to make it easier on T-SQL minds:
<FONT face="Courier New" size=2>FUNCTION BUS_RULE_FL
(VCTRY_ID IN varchar2, VBUSU_ID IN varchar2,
VEXTSYS_ID IN number, VBUS_RULE IN varchar2)
RETURNS NUMBER AS
-- return TRUE (1) or FALSE (0) whether the supplied business rule
-- is selected for the supplied values of CTRY, LNG, EXTSYS
VLOOPER NUMBER := 0;
VCNT NUMBER := 0;
BEGIN
-- this loop will try all combinations of matching ctry/busu/extsys columns to either
-- supplied values or null until a match is found, or all combinations exhausted
WHILE VLOOPER < 8 AND VCNT = 0 LOOP
SELECT COUNT(*)
INTO VCNT
FROM B2R_BUSINESS_FULE_USAGE
WHERE BBUSRL_NM = VBUS_RULE
AND isnull(BCTRY_ID, TO_CHAR(ACOS(-1)))
= (floor(vlooper / 4) % 2 == 0) ? vctry_id : to_char(PI)
-- vlooper = 0, 1, 2, 3
= DECODE(MOD(FLOOR(VLOOPER /4), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))
AND isnull(BBUSU_ID, TO_CHAR(ACOS(-1)))
= DECODE(MOD(FLOOR(VLOOPER /2), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))
-- vlooper = 0, 1, 4, 5
AND isnull(BEXTSYS_ID, ACOS(-1))
= DECODE(MOD(VLOOPER, 2), 0, VEXTSYS_ID, ACOS(-1));
-- vlooper = 0, 2, 4, 6
VLOOPER := VLOOPER + 1;
END LOOP;</FONT>
WTF #1: ACOS(-1).
Basically, the purpose of ACOS(-1) was to pick a value (pi) that could never be a valid country/business unit/external system ID, and replace NULLs with that value. The reason for this is obvious to experienced SQL users: under standard SQL semantics, (NULL=NULL) is false, but (ACOS(-1)=ACOS(-1)) is true.
Personally, I'd have just used -1 or 'invalid' or something like that, but hey.
WTF #2: the MOD(VLOOPER) crap
This is a bit more involved. The mapping goes like this:
country ID: iterations 0, 1, 2, 3
business unit ID: iterations 0, 1, 4, 5
external system ID: iterations 0, 2, 4, 6
There are eight iterations, 0 through 7.
If a particular iteration is in the above list for an item, that item is checked exactly; otherwise, a passed NULL matches anything. This probably won't make much sense without a detailed example to go with it.
Iteration 0, the first attempt, will try matching all three inputs to their respective columns on the table.
If there is an exact match, this code is very efficient and returns the number of exact rules (which I expect would be 1) immediately.
On the next iteration, country ID and business unit ID are matched exactly against the parameters, but, if the function is passed NULL for external system ID, that will still match a non-NULL value in that column (because 1 is not in external system ID's list above). This means that
BUS_RULE_FL('country1', 'unit1', NULL, 'ruleX') will return the number of rules defined for country1 on unit1, regardless of the external system ID they are set for.
Iteration 2: Country ID and external system ID are the only two with 2 in them. This is very similar to iteration 1, except that
BUS_RULE_FL('country1', NULL, 'extsys1', 'ruleX') will return the number of rules defined for country1 on extsys1, regardless of the business unit they are set for.
Iteration 3: Only Country ID matches this one. This provides the functionality that
BUS_RULE_FL('country1', NULL, NULL, 'ruleX') will return the number of rules for country1 in any business unit and any external system.
Iteration 4: Provides BUS_RULE_FL(NULL, 'unit1', 'extsys1', 'ruleX') to count the number of countries for which unit1 and extsys1 apply to ruleX.
Iteration 5: Provides BUS_RULE_FL(NULL, 'unit1', NULL, 'ruleX'). I think you understand what it counts.
Iteration 6: Provides BUS_RULE_FL(NULL, NULL, 'extsys1', 'ruleX').
Iteration 7: Provides BUS_RULE_FL(NULL, NULL, NULL, 'ruleX'), a catch-all that matches any country, any business unit, and any external system for ruleX.
Phew! catches breath.
I must say, this is actually a rather clever piece of code. It is deftly handling the REAL wtf, which is the fact that NULL is being used to mean two different things; one meaning is "match an actual NULL in the corresponding column", while the other is "match anything". It reminds me of a screwup where we sent out about 5000 devices that were accidentally printed with the wrong serial numbers -- serial numbers that overlapped existing IDs in our system. The fix for that was a huge WTF, too.
Admin
and WHAT THE FSCKING HELL IS WRONG WITH THIS FORUM SOFTWARE?
ED: I fixed your post. You were going and doing all fancy stuff like marking it up with [code] blocks. It doesnt like that.
Admin
When you wrote this code were you?
A) Drunk
B) On Crack
C) Both A and B
D) Buggering the nearest LISP book
Admin
You beat me, ammoQ...
Admin
The earth is an ellipsoid, not a sphere. Therefore the radius varies depending on your latitude. To compensate for this, you need to use Vincenty's Inverse Geodesic formula, rather than the Great Circle formula quoted.
So you are both wrong, by up to about 1%.
Admin
*g* better luck next time (maybe I have an unfair advantage because here in Austria it's already 22:23, so I'm not interrupted by real work)
Admin
I could point out the concept that many people feel it's bad to do complex math within your database server as your coding language of choice will always perform it faster. This is even more important when you look at the past where the database server is far more overloaded than your app server, but now that I/O has improved somewhat, this isn't as much of an issue as it use to be.
Admin
I see no reason why Java or C# or C++ on an app server would calculate ACOS faster (or slower) than the database (unless the Oracle guys wrote the ACOS alorithm in PL/SQL themself)
Admin
You're all wrong. The radius varies even more than that because of differences in altitude. The top of Mt. Everest (8,850 m above local sea level) vs. the bottom of the Mariana trench (10,911 m below local sea level) is significantly different points on the outside of the "oblate spheroid" that the earth really is. Now, granted, I'm referring to the fact that earth itself refers to the masses of land/soil and not inclusive of the ocean specifically. Had you intended it to refer to the planet, it would have been capitalized "Earth."
(data taken from Wikipedia http://en.wikipedia.org/wiki/Earth)
Admin
Oh dear.
I tried to read this code. I really did. But halfway through my overflow register keeps getting ticked and I fail.
Admin
Actually I stole / inherited that code. But I have run it, and it works well enough for my purposes.
The worst thing about it IMHO is that it multiplies something by two, and then immediately divides it by two again.
Admin
After some spur-of-the-moment research the 6367 number is defended by Wikipedia:
http://en.wikipedia.org/wiki/Great_circle_distance
"The shape of the Earth more closely resembles a flattened spheroid with extreme values for the radius of curvature of 6336 km at the equator and 6399 km at the poles. Using a sphere with a radius of 6367 km results in an error of up to about 0.5%."
Admin
I completely failed to understand this code. I didn't even really try.
Where in Vienna, ammoQ?
Admin
I think that is a little beside the point (in fine dwtf tradition). One of the most valuable take-home messages of The Elements of Programming Style is "say what you mean". This is db drudgework, not geometry... I would have left a copy of TEPS in my co-worker's Christmas stocking if I'd discovered this in his oeuvre. And congratulations to Maurits for outdoing a wtf in its own thread.
Admin
Ah... yea, right.
Having seen some nasty PL/SQL over the years, I'm not surprised that someone tried junk like that. Now the arc cosine stuff is just stupid.
Now I have to ask on the great circle stuff. Why would this be in the database? I mean why would someone have the database do your application's math.
Admin
so I can select all the stores within a 5 mile radius of a given zip code in a store locator system...
The alternative is to get all of the stores in the state, and in the surrounding states, and then sorting them in the application.
Admin
I don't see why you shouldn't do a lot of math in the database, particularly in the case of aggregation and compiling statistics, where it shines.
However, this WTF does not support that reason.
Admin
Travis, I've had some misgivings about this for a while but have only brought myself to write about it today. Your avatar, when not studied carefully, appears to be a picture of you with an oversized green dildo in your mouth. I'm not sure if it's deliberate but I think your avatar is meant to say "I like giant cocktails" but appears to say "I like giant cock"
Admin
CB: Perhaps you are just projecting.
Admin
Y
Admin
That's meant to be "thumb up" emoticon.
Excellent analysis, sir.
Admin
Dear god.
It is looking for rows in the datbase where VCTRY_ID is matched by BCTRY_ID, BBUSU_ID, BEXTSYS_ID and counting them. The acos(-1) is used to turn nulls into something that will work with a string equals. This code loops through the permutations of a three-bit number. 000, 001, 010, 011, 100 etc to handle the possible combinations of null/non-null values. But a simple nvl(the column, acos(1)) on each of the three columns will achieve the same thing.
Of course, the basic conceptual problem is that he is treating NULL as some particular value, when it is actually meant to mean the absence of a value
Admin
While I am at it: say a table has 3 key columns: A, B, C. You want to index the table such that a search on any combination of A, B and C can use an index. How many indexes do you need?
3 indexes will do: ABC, BC, CA
What's the general rule?
Admin
What? What if you want to search on A and B? Or just A?
Why not just have indexes A, B and C?
Admin
<FONT face="Courier New">-- given the latitude and longitude of two points on the surface of the earth
-- this returns the distance in miles between them as the crow flies
-- The first point is (@lata, @longa)
-- The second point is (@latb, @longb)</FONT>
To do this, convert the two points into unit vectors on the unit sphere, take their dot-product, take the acos of that, and multiply it by the radius of the earth. This uses the fact that the dot product of two unit vectors is equal to the cosine of the angle between them. I'll pretend that the trig functions use degrees.
Admin
We use the fact that (in oracle sql, anyway), indexes are sorted in the order that the columns in the index appear. In index serch simply involves doing a binary chop to find the start and end of the range you are interested int. So if you want to find all "a=3", then the index "a, b, c" can be used. In fact, "a, b, c" can be used to directly look up a, ab, and abc. If you want to find "a=3 AND c=2", a separate A and B index will not do the job on its own - you'd have to do a merge. And a "A, B, C" index is also no good. But any index with A and B in the first and second columns (in either order) is fine.
With our simple example, the indexed used for the various searches are
WHERE A = 1 AND B = 1 AND C = 1 : use index "A, B, C"
WHERE A = 1 AND B = 1 : use index "A, B, C"
WHERE A = 1 AND C = 1 : use index "C, A"
WHERE A = 1 : use index "A, B, C"
WHERE B = 1 AND C = 1 : use index "B, C"
WHERE B = 1 : use index "B, C"
WHERE C = 1 : use index "C, A"
There is a straightforwqard rule for how many indexes you need for N columns, but I'm not sure about the algorithm to generate them.
Admin
You're thinking of an OLAP cube, right? But never mind that, let's look at Foon's question as a math riddle.
For N keys, you first need to cover (all keys but #1) through (all keys but #N), so that's N indexes. To avoid duplication of subsets, order these as
(#2 through #N)
(#3 through #N + #1)
(#4 through #N + #1 through #2)
...
(#N-1 through #N + #1 through #N-3)
(#N + #1 through #N-2)
(#1 through #N-1)
Then append #1 to the first one, to cover the (all keys) case.
For 4 keys, this gives us BCDA, CDA, DAB, ABC. We also need to cover AC and BD.
Hypothesis: For N keys, max (K=1 to N) C(N,K) indexes is sufficient, where C(N,K) is the standard "number of unordered subsets of K elements out of an N-element set" combination function.
Exercise: Prove or disprove this hypothesis.
Admin
Seperate A, B and C indexes will still work - the DB simply needs to do a union on the results from each index. Regardless, your original question was what was required to use "an index", which that fills just fine, even if the DB only uses an index for one column.
Admin
I pity the business fule!