Comment On Relational Trigonometry

they should just ban all code.

obviously, it's evil.

  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.

Don't ban all the code... just ban features that make our jobs easier :)

Hey, wasn't this discussed in a a previous post?

Anonymous:

Don't ban all the code... just ban features that make our jobs easier :)

Doesn't code make your job easier?

Try writing software without it.

John Smallberries:

Anonymous:

Don't ban all the code... just ban features that make our jobs easier :)

Doesn't code make your job easier?

Try writing software without it.

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...)

Mike R:

Hey, wasn't this discussed in a a previous post?

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?

For some people, giving them the job of writing software makes my job much harder :(

Alex Papadimoulis:

Mike R:

Hey, wasn't this discussed in a a previous post?

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?

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?

That has a name around here.  "Doing Negative Work".

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.

Mike R:

My guess the floor/mod stuff is the same as saying "Every fourth
iteration".

Or rather creates a pattern like TTFFTTFF [:$]

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

Alex Papadimoulis:

What still gets me is WTF is with the Floor/Mod nonsense. Any ideas?

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.

 

Can someone summarize this for those of us unfamiliar with PL/SQL?

Why did I type VB? I meant, I don't know what DECODE does in SQL. Sorry. :)

 

Matt B:

Can someone summarize this for those of us unfamiliar with PL/SQL?

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.

 

Maurits:

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
                                    ) -
           ...Massive Snippage

 

How is that even remotely readable?

And your radius of the earth is wrong, it's 6378 km

Anonymous:

How is that even remotely readable?

And your radius of the earth is wrong, it's 6378 km

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.

Matt B:

Can someone summarize this for those of us unfamiliar with PL/SQL?

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).

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.



I think I figured out how this thing works (sort of)...

NVL(BEXTSYS_ID, ACOS(-1)) = DECODE(MOD(VLOOPER, 2), 0, VEXTSYS_ID, ACOS(-1));

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).



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

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:

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;


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.

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.

When you wrote this code were you?



A) Drunk

B) On Crack

C) Both A and B

D) Buggering the nearest LISP book

Maurits:

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 *



... snip Pabst drinking/Crack-smoking code ...


   )
    )
)
END

You beat me, ammoQ...

Anonymous:

How is that even remotely readable?

And your radius of the earth is wrong, it's 6378 km

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%.

Anonymous:

You beat me, ammoQ...

*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)

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.

travisowens:

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.

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)

Anonymous:

Anonymous:

How is that even remotely readable?

And your radius of the earth is wrong, it's 6378 km

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%.

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)

Oh dear.

I tried to read this code. I really did. But halfway through my overflow register keeps getting ticked and I fail.

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.

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%."

I completely failed to understand this code. I didn't even really try.



Where in Vienna, ammoQ?

travisowens:

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.  ... now that I/O has improved somewhat, this isn't as much of an issue as it use to be.

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.

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.

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.

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.

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"

CB: Perhaps you are just projecting.

Oliver Klozoff:

Frighteningly enough, I actually
understand what this code is doing.  And the comments are
accurate, if not quite detailed enough.

...

Y

dreifus:

Oliver Klozoff:

Frighteningly enough, I actually
understand what this code is doing.  And the comments are
accurate, if not quite detailed enough.

...

Y

That's meant to be "thumb up" emoticon.

Excellent analysis, sir.

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

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?

Foon:

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?

What? What if you want to search on A and B? Or just A?



Why not just have indexes A, B and C?

-- 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)

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.

return radus_of_earth * 2pi/360 * acos (

   sin(lata) * sin(latb) /* y coordinate */

  + cos(lata)*cos(lona) * cos(latb)*cos(lonb) /* z coordinate */

  + cos(lata)*sin(lona) * cos(latb)*sin(lonb) /* x coordinate */

);

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.

Anonymous:

Foon:

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?

What? What if you want to search on A and B? Or just A?



Why not just have indexes A, B and C?

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.

Foon:

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.

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.

Alex Papadimoulis:

FROM B2R_BUSINESS_FULE_USAGE

I pity the business fule!