The Daily WTF: Curious Perversions in Information Technology

2006-01-31 Reply Admin

skicow:
That's also assuming that the consultants can write basic SQL and recognize that they incorrectly joined a few tables.

It's not just consultants, either.

I worked with a firm where the development staff (all CS from top rated schools) decided to implement their own object relational mapping atop SQL. They implemented object inheritance by modeling the is-a relationship with two tables and a join. Eight levels of inheritance = eight tables to join in one query.

The part catalog built atop this mess took days to import a couple thousand SKU's. to get it to meet the 500,000 SKU client requirement, the consulting wing of the firm rewrote the thing in a sensible manner.

tSQL · 2006-01-31 Reply Admin

I remember when Adam showed me that row count when he submitted this WTF! He had explained in further detail ....

ConsultantAdam (3:06:28 PM): I have a tiny screen shot to show you
tSQL (3:06:59 PM): you can email
ConsultantAdam (3:07:46 PM): en route
tSQL (3:11:05 PM): row count?
ConsultantAdam (3:11:54 PM): ever see that in scientific notation before?
tSQL (3:12:06 PM): row counts, no
tSQL (3:12:19 PM): but i'm not surprised
ConsultantAdam (3:12:49 PM): the query was written by an outside consultant.
tSQL (3:14:19 PM): do you just have that much data?
ConsultantAdam (3:14:25 PM): there are no tables in the system over 40 million rows, let alone 3 billion
tSQL (3:14:44 PM): no tables, so where is this data?
ConsultantAdam (3:14:50 PM): the query is attempting to de-normalize everything for future reports
ConsultantAdam (3:15:20 PM): one of our programmers was trying to figure out, why after running for 24 hours.
ConsultantAdam (3:15:27 PM): it was taking so long
tSQL (3:17:04 PM): de-normalize?
ConsultantAdam (3:17:43 PM): the opposite of normal
ConsultantAdam (3:18:03 PM): do a big join, then save the results into a table. then report off that table
ConsultantAdam (3:18:05 PM): totaly gay
tSQL (3:18:32 PM): so make a big 'everything table'
ConsultantAdam (3:18:39 PM): yeah
tSQL (3:19:28 PM): so account 001 has 50 records, one for every service of account 001 001 servA 001 servB 001 servC
ConsultantAdam (3:20:20 PM): yeah. master detail are joined, then results saved.
ConsultantAdam (3:20:26 PM): then you run the fucker everynight.
ConsultantAdam (3:20:40 PM): which is a problem, since it finally blew up after 24 hours.
tSQL (3:21:09 PM): and doubles the size of your database too?
ConsultantAdam (3:21:36 PM): it seemed to be 50 times the size
tSQL (3:21:49 PM): duplicating all the data in your database
ConsultantAdam (3:22:16 PM): not duplicate, multiples of it
tSQL (3:22:19 PM): so what where you talking about when you said 'no tables in the system'?
ConsultantAdam (3:23:12 PM): the biggest table is maybe 20 million rows.
ConsultantAdam (3:23:32 PM): the estimated row count on the group by clause, was 3.7 billion rows
tSQL (3:24:09 PM): sql server?
ConsultantAdam (3:24:14 PM): yes
tSQL (3:24:56 PM): i have a monthly db that grows to 230g every month
tSQL (3:25:11 PM): i don't want that guy working with me
ConsultantAdam (3:25:25 PM): no kidding

OneFactor · 2006-01-31 Reply Admin

Jeff S:
FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS. If you think you need a full outer join, then you are doing something wrong.

All right, I'm always eager to learn how I could have done things better. I needed to write a query into existing "orders". For simplicity sake lets say there were order line items with foreign keys to the product table and the order table. In addition, the line item had a flag saying that it was either of type "maintenance" or type "equipment". The underlying schema was not under my control but I could write whatever query I wanted.

The idea is a customer could, for any product on the order, purchase maintenance and/or equipment. If they purchased both, there would be two order line items in the database for the order for that product.

I needed to produce a resultset which would for each product in each order give one line. If there was equipment without maintenance, it would have one row indicating such was the case. If there was maintenance without equpment, it would have one row indicating such was the case. If there was maintenance and equipment, it would have one row indicating both were present.

This sounds like a perfect candidate to do a full join between order line items on the same order where the product id matches and one is of type equipment and one is of type maintenance.

select eq.id, ma.id, nvl(eq.id_order, ma.id_order)
from orderlineitems eq full join orderlineitems ma on
eq.id_order = ma.id_order and eq.id_product = ma.id_product and eq.type = 1 and ma.type = 2

This was my first full outer join (well actually it was an oracle left union right thing) and I'd be more than happy to see how eliminating the full outer join helps me fulfill the requirements in a better fashion.

ObySamKenoby · 2006-01-31 Reply Admin

..... Now I come to the point for which I posted here: the consuntive of the month's bills.
Once in a month we started this jewel that took from 1 to 6 hours, depending on the actual month. January was fast enough, November and December were a kill.

More and more times I tried to understand WHY that happened, diving into spaghetti-like histeric code, until I understood that:
- for every customer group the whole purchase archives were copied in a temporary MySQL table;
- there were two types of discounts. For each type the temporary table was queryed (SELECT * FROM, naturally), each row read and, if eligible, the transaction was crossed with the result of another SELECT in another table and the result added in a keyed text file (the key was Product code, there were about 8000 of them);
- the text file was then re-read and the codes divided in various article groups (another keyed text file);
- the second text file was re-read and the data saved in another table (let's call it table2)
- the temporary table was deleted;
- the cicle continues;
- table2 was re-read and the data printed.

I never had enough courage to touch it (and i was dissuased to try from my bosses, too - that was a "Joe's work" (fantasy name)), anyhow one time I researched the same data for another scope (in the marvellous system no code reuse, please) and the whole procedure (with intensive array use and optimization, plus two or three "WHERE" here and there) took about 8 minutes on December data.

After two months I left off.

Thanks consultants.

Samuele

2006-01-31 Reply Admin

El Duderino:
Cartisian Products are a bitch, ain't they!

So is orthography.

Moron.

2006-01-31 Reply Admin

If I understand your schema and requirement correctly (I'm not much of an Oracle guy), the following will work:

select id_order,
max(case when type = 1 then id else null end) as [Equipment],
max(case when type = 2 then id else null end) as [Maintance]
from orderlineitems
where type in (1, 2)
group by id_order

triso · 2006-01-31 Reply Admin

Anonymous:
...I have to wonder whose these people are that manage to get hired without a clue. Seriously - is it just because consulting companies are outsourcing themselves to the lowest bidder?

Perhaps but I always thought it was because the consultants dress well, can communicate well and have good people-skills. None of which are technical geek traits.

2006-01-31 Reply Admin

Anonymous:
If I understand your schema and requirement correctly (I'm not much of an Oracle guy), the following will work:

select id_order,
max(case when type = 1 then id else null end) as [Equipment],
max(case when type = 2 then id else null end) as [Maintance]
from orderlineitems
where type in (1, 2)
group by id_order

And what, pray tell, is the maximum value of null?

2006-01-31 Reply Admin

Null values are always ignored in aggregate statements.

OneFactor · 2006-01-31 Reply Admin

Anonymous:

If I understand your schema and requirement correctly (I'm not much of an Oracle guy), the following will work:

select id_order,
max(case when type = 1 then id else null end) as [Equipment],
max(case when type = 2 then id else null end) as [Maintance]
from orderlineitems
where type in (1, 2)
group by id_order

Throw an id_product into the group by clause and I think you've got it. Thanks.

2006-01-31 Reply Admin

Was this, by chance a product called CommerceCenter / Prophet21? Sounds like what I've seen (and submitted a few WTFs for).

-F

Mike Dimmick · 2006-01-31 Reply Admin

Anonymous:
So, my immediate response to this is... WTF costs 100% if this estimated cost was a mere 35%!?

The whole query costs 100%. The 35% figure here is for the proportion of the overall query time spent sorting the data.

marvin_rabbit · 2006-01-31 Reply Admin

Anonymous:
So, my immediate response to this is... WTF costs 100% if this estimated cost was a mere 35%!?

We just have to understand the units. I think they mean 35% of the heat death of the universe.

2006-01-31 Reply Admin

Anonymous:
When I worked as an intern I was still figuring out the magical join clause. I experimented until I understood them fully. One of my experiments used a full outer join on two tables, one of which had ~100,000 records. While the query ran I went to the gas station to fill up my car. When I got back (15 minutes) the query was just finishing and I had a couple million rows returned. Yikes I thought.

Full outer joins are a bad thing children...

Oh, i wish i had never read this. All those memories I had finally sucessfully repressed.

At a former gig, I was in charge of all our DBs, and every month or so, some joker would teach themselves how to join on the production hardware. They'd notice it was taking a while and so go do whatever people do when their brains fail them. And while they were gone, pages would go off, managers would run into my office screaming "the site is down, the site is down" and I'd be hunting down the idiot that locked up our servers.

Of course, the real WTF was that developers were allowed to hit production servers at will. Yeah, and DBA's get a bad name for wanting to control their environment.

ammoQ · 2006-01-31 Reply Admin

Jeff S:

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS. If you think you need a full outer join, then you are doing something wrong. Any JOIN that potentially returns a null in *every* column, including all key columns (albeit on different rows), is a complete mess. You literally must wrap every column with a COALESCE() function, and any further processing of those results cannot use any indexes since all columns are now wrapped in functions.

Full outer joins are usefull to show the differences between two (similar) tables. But I agree they are required very rarely.

2006-01-31 Reply Admin

Anonymous:
I sure hope Adam got a raise. As a consultant myself, I have to wonder whose these people are that manage to get hired without a clue. Seriously - is it just because consulting companies are outsourcing themselves to the lowest bidder?

I'm a consultant myself and went to do some work for an international manufacturer of white good and during the course of my time there they told me that the person previous to me who had been supplied large consulting company (rearrange the letters BMI to get a clue) who was charging them 4 figures a day had tried to tell them that a flat file would be more efficient than their database. It turned out that the guy didn't have a clue about SQL and was jsut trying to blag his way through.

They told the large consulting company that they no longer required their "expertise" and looked locally for a local company (mine) instead since they figured that the small sompany is more likely to have someone who knows what they're talking about. I was e-mailed a few months after I'd finished my work there to tell me that the project had gone live and to thank me for my services. That really made me smile.

Maurits · 2006-01-31 Reply Admin

Jeff S:
Maurits:
Jeff S:

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS. If you think you need a full outer join, then you are doing something wrong.

Pshaw.

Disagree? Do you have an example?

Sure...

CREATE VIEW
    PotentialCouples
AS
SELECT
    *
FROM
    Men INNER JOIN Women
       ON
    Men.CompatibilityCode = Women.CompatibilityCode

CREATE VIEW
    IncompatiblePeople
AS
SELECT
    *
FROM
    Men FULL OUTER JOIN Women
       ON
    Men.CompatibilityCode = Women.CompatibilityCode
WHERE
    Men.ID IS NULL AND Women.ID IS NULL

chrismcb · 2006-01-31 Reply Admin

GoatCheez:
27,210,205,000 rows is a LOT of rows...

My former coworker used to bemoan how small an int was (one night he complained he had overflown an int32... trying to keep track of the number of 32 bit colors!) He LOVED int64's... now I think I know where he used to work.

Jeff S · 2006-01-31 Reply Admin

Maurits:
Jeff S:
Maurits:
Jeff S:

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS. If you think you need a full outer join, then you are doing something wrong.

Pshaw.

Disagree? Do you have an example?

Sure...

CREATE VIEW
    PotentialCouples
AS
SELECT
    *
FROM
    Men INNER JOIN Women
       ON
    Men.CompatibilityCode = Women.CompatibilityCode

CREATE VIEW
    IncompatiblePeople
AS
SELECT
    *
FROM
    Men FULL OUTER JOIN Women
       ON
    Men.CompatibilityCode = Women.CompatibilityCode
WHERE
    Men.ID IS NULL AND Women.ID IS NULL

uh .. I don't think you understand full outer joins... what is that supposed to be returning?

Did you try it?

create table Men (ID int identity, CompatCode char(1))

create table Women (ID int identity, CompatCode char(1))

go

insert into Men (CompatCode)
select 'a' union all
select 'b' union all
select 'c'

insert into Women (CompatCode)
select 'b' union all
select 'c' union all
select 'd'

go

SELECT
    *
FROM
    Men FULL OUTER JOIN Women
       ON
    Men.compatCode = Women.compatCode
WHERE
    Men.ID IS NULL AND Women.ID IS NULL

returns 0 results.

(do you want an OR in the WHERE clause?)

Maurits · 2006-01-31 Reply Admin

Er, that should be
Men.ID IS NULL OR Women.ID IS NULL

Jeff S · 2006-01-31 Reply Admin

Maurits:
Er, that should be
Men.ID IS NULL OR Women.ID IS NULL

Actually, you have correctly found a situation where one might use FULL OUTER JOINS -- if you have a really bad database schema and don't care having a true set of non-null key columns in your results, then I suppose you can go ahead and write FULL JOINS as much as you want.

Jeff S · 2006-01-31 Reply Admin

With a "real" schema, all you need is a LEFT OUTER JOIN and it returns meaningful results and it is, of course, much more flexible. (i.e., you can do same-sex matches or whatever you want):

create table People(ID int identity, sex char(1), compatCode char(1))

insert into People (Sex, CompatCode)
select 'm','a' union all
select 'm','b' union all
select 'm','c' union all
select 'f','b' union all
select 'f','c' union all
select 'f','d'

go

SELECT *
FROM People
LEFT OUTER JOIN People Match
  ON People.compatCode = Match.CompatCode and
     People.Sex != Match.Sex
WHERE
  Match.ID is null

And, again, we have a much more meaningful, efficient, and clear SELECT statement as well.

brazzy · 2006-01-31 Reply Admin

triso:
Anonymous:
...I have to wonder whose these people are that manage to get hired without a clue. Seriously - is it just because consulting companies are outsourcing themselves to the lowest bidder?
Perhaps but I always thought it was because the consultants dress well, can communicate well and have good people-skills. None of which are technical geek traits.

Yup. That in combination with managers that have no technical skill or knowledge whatsoever, will not listen to anyone who does, and do not learn from past experience.

JohnO · 2006-01-31 Reply Admin

Jeff S:
Maurits:
Jeff S:

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS. If you think you need a full outer join, then you are doing something wrong.

Pshaw.

Disagree? Do you have an example?

Yes I do. A margin variance report. Full outer join works quite well.

Maurits · 2006-01-31 Reply Admin

Jeff S:
With a "real" schema,

Your proposed schema works well until you start adding gender-specific fields.

Jeff S · 2006-01-31 Reply Admin

JohnO:

Jeff S:
Maurits:
Jeff S:

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS. If you think you need a full outer join, then you are doing something wrong.

Pshaw.

Disagree? Do you have an example?

Yes I do. A margin variance report. Full outer join works quite well.

Oh yeah! margin variance reports! didn't think of that; that cleared it up; of course you should use a FULL OUTER JOIN for that. How silly of me.

(perhaps an actual SELECT might be a little more useful in a techinical discussion, don't you think?)

Jeff S · 2006-01-31 Reply Admin

Maurits:
Jeff S:

With a "real" schema,

Your proposed schema works well until you start adding gender-specific fields.

Actually, no, my "proposed schema" works pretty well. I think we can do something I read about called "relating" tables or something like that, I forget the details, it sounded pretty cool though. I think you use XML or something.

Jeff S · 2006-01-31 Reply Admin

Jeff S:
Maurits:
Jeff S:

With a "real" schema,

Your proposed schema works well until you start adding gender-specific fields.

Actually, no, my "proposed schema" works pretty well. I think we can do something I read about called "relating" tables or something like that, I forget the details, it sounded pretty cool though. I think you use XML or something.

Sorry for the sarcasm, but I get frusted with some of these database "designs" I see and deal with every day ....

Hopefully you can see that if you don't put your common entities into the same table (i.e, customers) then you will have to have a "AddMan" stored proc, and an "AddWomen" stored proc, and then a "EditMan", "EditWoman","SelectAvailableMan" view,"SelectAvailableWomen" view, etc... everything in your database that deals with a "customer" is duplicated.

Unless, of course, you decide to UNION ALL your Man and Woman tables, and add a column Sex, and then you can write things generically and just pass in a 'W' or 'M' parameter ... That would work great! oh wait -- or we can just design the table that way originally!

And, of course, should any specific section of your app need to deal *only* with men or *only* with women, then you store those specific attributes in a related table (1:1 or whatever you need) and have your code deal with that directly.

But common attributes for those entities are stored in the same table.

Right?

JohnO · 2006-01-31 Reply Admin

Anonymous:
Disclaimer: I started out my career as a professional software developer (trained in Computer Science) with eight years of (successful) experience. I have since moved into a consulting firm.

The reality of consulting firms is that knowing how to run a query analyzer and interpret the results (or horrors, look at the query itself) isn't the kind of skill that's going to justify the margins that they need to pay their bills. These days, moving deep technical work to offshore labor for is a big part of how consulting firms compete on price. (Considering that consultancies drive a lot of offshoring, it's natural that they do it themselves.)

The same thing goes for consulting firm employees: technical work doesn't pay the bills where they work, so it doesn't help people succeed in the hierarchy (which is very strong in consultancies). The ability to interview a client team, understand their business process, and work with them to write a 100 page specifcation for a business application is going to be far more useful than the ability to actually code the app. (which will probably be done in India.). While this typically implies that the on the ground technical skills of your consultants aren't what you'd like, aren't you glad your company isn't paying $200/hour for a SQL skills? (I haven't worked with anybody where I work now that I'd trust with a copy of Eclpse or even Visual Basic)

(Of course, if your management is expecting coding miracles (or even adequacy) out of $200/hour consultants, then you're f***ed anyway... and for reasons likely to be more significant than most of the code shown on this website).

What's funny is how this "offshoring" is really just a new way for consultants to overcharge. We are going through a merger (really an acquisition) and we are the bigger player. The company we are merging with has done some offshoring. The funny thing is, they tell us how they have 1 manager and 45 programmers in India working on one program. And.. they still spent $30M on a 3-5M job. Anyone that's ever done any real development know there's not 45 people working on it. There's probably 5 people doing 90% of the work and really only 10 total. But they bill for 45 heads at a lower rate. The 45 sounds big and impressive. Makes it sound like you really are getting your money worth. Just a new way to bilk dumbass non-technical people out of their cash.

Don't forget you still need technical people to "interface" with the Indian team -- gather specs and oversee what comes back. You also need them to provide on-site support. You have a minimum 24 hour turnaround on any work you want done there. Oh, and you need high dollar business execs to oversee the contracts. In the end, you save very little money, if any.

Don't get me wrong, I am a pure free-trader and I belive for monolithic, non-business applications, you actually could save some money offshoring. But you have to be smart about it and apply it where it makes sense.

JohnO · 2006-01-31 Reply Admin

Jeff S:

Maurits:
Er, that should be
Men.ID IS NULL OR Women.ID IS NULL

Actually, you have correctly found a situation where one might use FULL OUTER JOINS -- if you have a really bad database schema and don't care having a true set of non-null key columns in your results, then I suppose you can go ahead and write FULL JOINS as much as you want.

Jeff, you really don't know what you are talking about on this issue. FOJs really do have application in the real world in very limited circumstances. You can have a perfectly proper scheme and this be the case. Have you ever worked with a database that involved time? Something could have been the yesterday and not there today, vice versa, and there both days. A perfect case for a FOJ to generate a variance. You coalesce the numerical values to 0 and coalesce the atrributes (date, transaction #, etc) to one side or the other of the join. If you really can't understand this from a description, I will post you a perfectly valid scheme with all non-null keys that utilizes a FOJ.

JohnO · 2006-01-31 Reply Admin

substitute schema for scheme in the above post :)

Jeff S · 2006-01-31 Reply Admin

JohnO:
Jeff S:

Maurits:
Er, that should be
Men.ID IS NULL OR Women.ID IS NULL

Actually, you have correctly found a situation where one might use FULL OUTER JOINS -- if you have a really bad database schema and don't care having a true set of non-null key columns in your results, then I suppose you can go ahead and write FULL JOINS as much as you want.

Jeff, you really don't know what you are talking about on this issue. FOJs really do have application in the real world in very limited circumstances. You can have a perfectly proper scheme and this be the case. Have you ever worked with a database that involved time? Something could have been the yesterday and not there today, vice versa, and there both days. A perfect case for a FOJ to generate a variance. You coalesce the numerical values to 0 and coalesce the atrributes (date, transaction #, etc) to one side or the other of the join. If you really can't understand this from a description, I will post you a perfectly valid scheme with all non-null keys that utilizes a FOJ.

I thnk I understand the description -- but definitely help me out since I have clearly demonstrated that I have no clue what I am talking about. If you could post a small sample like what I did earlier (some create tables, inserts, then your select w/ the full outer join) so I can take a look that would definitely be helpful. Keep it nice and simple.

But remember -- you agreed to the rules. A good schema and results that don't have random Nulls all over the place.

2006-01-31 Reply Admin

Jeff S:
Maurits:
Er, that should be
Men.ID IS NULL OR Women.ID IS NULL

Actually, you have correctly found a situation where one might use FULL OUTER JOINS -- if you have a really bad database schema and don't care having a true set of non-null key columns in your results, then I suppose you can go ahead and write FULL JOINS as much as you want.

When you are already in hell you might as well hit the hot tub, the water is always hot.

Database servers should have some type of interactive IQ test that pops up when somebody tries to create a table.

2006-01-31 Reply Admin

JohnO:
Anyone that's ever done any real development know there's not 45 people working on it.

That's just plain wrong. Anyone that's ever worked on large complex software would have been in a team of around that size or larger.

I'm currently on project that has 43 people developing a single system. The largest project at my site has 200 programmers working on it (and it's the only one they are assigned to).

foxyshadis · 2006-01-31 Reply Admin

brazzy:
triso:
Anonymous:
...I have to wonder whose these people are that manage to get hired without a clue. Seriously - is it just because consulting companies are outsourcing themselves to the lowest bidder?
Perhaps but I always thought it was because the consultants dress well, can communicate well and have good people-skills. None of which are technical geek traits.

Yup. That in combination with managers that have no technical skill or knowledge whatsoever, will not listen to anyone who does, and do not learn from past experience.

Nah, just gullibility. Even a very smart, technical, but gullible exec can be conned into all kinds of bad deals.

JohnO:
Don't get me wrong, I am a pure free-trader and I belive for monolithic, non-business applications, you actually could save some money offshoring. But you have to be smart about it and apply it where it makes sense.

Are you trying to tell me that Fortune could possibly be wrong? But offshore consulting companies would never lie about that. They even offered to have 200 of their finest programmers XML-integrate my entire workflow into a neo-flash paradigm at less than $10/hr each! We've already scrapped the old system and we go live tomorrow!

Maurits · 2006-01-31 Reply Admin

OK, here's another attempt to justify FULL OUTER JOINS

Suppose I have "things" and "containers" and want an inventory report that shows me:
1) What things are in which containers
2) What containers are empty
3) Which things are not in a container

A FULL OUTER JOIN seems to be natural:

create table things (
    thingid int primary key,
    thingname varchar(50),
    incontainerid int null
)

create table containers (
    containerid int primary key,
    containername varchar(50)
)

insert into containers (containerid, containername)
values (1, 'Big Box')

insert into containers (containerid, containername)
values (2, 'Little Box')

insert into containers (containerid, containername)
values (3, 'Empty Box')

insert into things (thingid, thingname, incontainerid)
values (1, 'Big Thing', 1)

insert into things (thingid, thingname, incontainerid)
values (2, 'Another Big Thing', 1)

insert into things (thingid, thingname, incontainerid)
values (3, 'Little Thing', 2)

insert into things (thingid, thingname, incontainerid)
values (4, 'Very Big Thing', NULL)

-- show everything and where it is
-- include empty containers
-- also include things that are not in a container
select
    *
from
    containers full outer join
    things on
        containers.containerid = things.incontainerid
order by
    containername, -- this puts uncontained things first
    thingname

drop table things
drop table containers

JohnO · 2006-01-31 Reply Admin

Jeff S:
JohnO:
Jeff S:

Maurits:
Er, that should be
Men.ID IS NULL OR Women.ID IS NULL

Actually, you have correctly found a situation where one might use FULL OUTER JOINS -- if you have a really bad database schema and don't care having a true set of non-null key columns in your results, then I suppose you can go ahead and write FULL JOINS as much as you want.

Jeff, you really don't know what you are talking about on this issue. FOJs really do have application in the real world in very limited circumstances. You can have a perfectly proper scheme and this be the case. Have you ever worked with a database that involved time? Something could have been the yesterday and not there today, vice versa, and there both days. A perfect case for a FOJ to generate a variance. You coalesce the numerical values to 0 and coalesce the atrributes (date, transaction #, etc) to one side or the other of the join. If you really can't understand this from a description, I will post you a perfectly valid scheme with all non-null keys that utilizes a FOJ.

I thnk I understand the description -- but definitely help me out since I have clearly demonstrated that I have no clue what I am talking about. If you could post a small sample like what I did earlier (some create tables, inserts, then your select w/ the full outer join) so I can take a look that would definitely be helpful. Keep it nice and simple.

But remember -- you agreed to the rules. A good schema and results that don't have random Nulls all over the place.

Simplified from real-life but captures the essence:

CREATE TABLE Margin (

SnapshotDate datetime not null,

FlowDate datetime not null,

TransactionID int not null,

Amount money not null

)

ALTER TABLE Margin ADD

CONSTRAINT PK_Margin PRIMARY KEY CLUSTERED (SnapshotDate, FlowDate, TransactionID)

GO

DECLARE @BaseLine datetime,

@Current datetime

SELECT @BaseLine = '01/30/2006'

SELECT @Current = '01/31/2006'

SELECT SnapshotDate = COALESCE(BL.SnapshotDate, CT.SnapshotDate),

FlowDate = COALESCE(BL.FlowDate, CT.FlowDate),

TransactionID = COALESCE(BL.TransactionID, CT.TransactionID),

Amount = COALESCE(CT.Amount, 0) - COALESCE(BL.Amount, 0)

FROM ( SELECT * FROM Margin WHERE SnapshotDate= @BaseLine )BL

FULL OUTER JOIN ( SELECT * FROM Margin WHERE SnapshotDate= @Current ) CT

ON CT.SnapshotDate = BL.SnapshotDate

AND CT.FlowDate = BL.FlowDate

AND CT.TransactionID = BL.TransactionID

JohnO · 2006-01-31 Reply Admin

Anonymous:
JohnO:
Anyone that's ever done any real development know there's not 45 people working on it.

That's just plain wrong. Anyone that's ever worked on large complex software would have been in a team of around that size or larger.

I'm currently on project that has 43 people developing a single system. The largest project at my site has 200 programmers working on it (and it's the only one they are assigned to).

I love the selective quoting here. I am talking about specialized business app development. I worked in the telecom industry a while back. I know all about big teams and I've been a part of them. Are you really a developer? How long have you been out of school? How many different industries have you worked in? Do you disagree that in any large organization, about half the people do nothing, about 1/4 do a little something, and the remaining 1/4 do 90% of the work?

Jeff S · 2006-01-31 Reply Admin

Maurits:
OK, here's another attempt to justify FULL OUTER JOINS

Suppose I have "things" and "containers" and want an inventory report that shows me:
1) What things are in which containers
2) What containers are empty
3) Which things are not in a container

A FULL OUTER JOIN seems to be natural:

create table things (
    thingid int primary key,
    thingname varchar(50),
    incontainerid int null
)

create table containers (
    containerid int primary key,
    containername varchar(50)
)

insert into containers (containerid, containername)
values (1, 'Big Box')

insert into containers (containerid, containername)
values (2, 'Little Box')

insert into containers (containerid, containername)
values (3, 'Empty Box')

insert into things (thingid, thingname, incontainerid)
values (1, 'Big Thing', 1)

insert into things (thingid, thingname, incontainerid)
values (2, 'Another Big Thing', 1)

insert into things (thingid, thingname, incontainerid)
values (3, 'Little Thing', 2)

insert into things (thingid, thingname, incontainerid)
values (4, 'Very Big Thing', NULL)

-- show everything and where it is
-- include empty containers
-- also include things that are not in a container
select
    *
from
    containers full outer join
    things on
        containers.containerid = things.incontainerid
order by
    containername, -- this puts uncontained things first
    thingname

drop table things
drop table containers

I got excited at first, but then I looked at your "specs" and the results. huh? What kind of results are those? You want to include *both* empty containers *and* things not in containers, all in the same resultset, along with things in containers? With NULLS all over the place?

I guess I stand by my earlier statement about bad schemas and/or meaningless results being great times to use FULL joins.

Jeff S · 2006-01-31 Reply Admin

JohnO:

Simplified from real-life but captures the essence:

CREATE TABLE Margin (

SnapshotDate datetime not null,

FlowDate datetime not null,

TransactionID int not null,

Amount money not null

)

ALTER TABLE Margin ADD

CONSTRAINT PK_Margin PRIMARY KEY CLUSTERED (SnapshotDate, FlowDate, TransactionID)

GO

DECLARE @BaseLine datetime,

@Current datetime

SELECT @BaseLine = '01/30/2006'

SELECT @Current = '01/31/2006'

SELECT SnapshotDate = COALESCE(BL.SnapshotDate, CT.SnapshotDate),

FlowDate = COALESCE(BL.FlowDate, CT.FlowDate),

TransactionID = COALESCE(BL.TransactionID, CT.TransactionID),

Amount = COALESCE(CT.Amount, 0) - COALESCE(BL.Amount, 0)

FROM ( SELECT * FROM Margin WHERE SnapshotDate= @BaseLine )BL

FULL OUTER JOIN ( SELECT * FROM Margin WHERE SnapshotDate= @Current ) CT

ON CT.SnapshotDate = BL.SnapshotDate

AND CT.FlowDate = BL.FlowDate

AND CT.TransactionID = BL.TransactionID

Can you hook me up with some sample data (INSERT statements if possible) and results based on that data? I cannot make sense of the business logic you are trying to apply with this statement. My first instinct is that no joins at all are needed, just a simple GROUP BY, but it is hard to tell what you are trying to do.

(by the way-- that's another bad attribute of FULL OUTER JOINS -- they make no sense when you try to read the SELECT statement as well. If it turns out that what you are trying to return is actually logical, the correct statement will be much shorter and much more clear in addition to being more efficient).

Jeff S · 2006-01-31 Reply Admin

Maurits:
OK, here's another attempt to justify FULL OUTER JOINS

Suppose I have "things" and "containers" and want an inventory report that shows me:
1) What things are in which containers
2) What containers are empty
3) Which things are not in a container

Again, I posted too quick. I shoudl have responded to these specs first.

I hope you can see that those are at least 2 separate reports, right? Either you are returning all things, and which container they happen to be in (1 + 3) or you are returning all containers, including any things that might be in them (2 + 3). No logical result combines all 3 ! Surely you can see this?

These can be on the same page or whatever (i.e., subreports), but they are *completely* different -- differnt columns need to be returned, different ways of grouping/sort, etc.

If you try to stuff this all into 1 big SELECT and just fill in the blanks with NULLS everywhere it makes absolutely no sense whatsoever.

RogerC · 2006-01-31 Reply Admin

SeekerDarksteel:
Bah, real men query every table separately then piece it all together in the code!

I think you used to work for me. Larry, is that you?

2006-01-31 Reply Admin

triso:
Anonymous:
...I have to wonder whose these people are that manage to get hired without a clue. Seriously - is it just because consulting companies are outsourcing themselves to the lowest bidder?
Perhaps but I always thought it was because the consultants dress well, can communicate well and have good people-skills. None of which are technical geek traits.

and, they know how to use initial caps?

But the thought is well meant.

2006-01-31 Reply Admin

BlackTigerX:
Anonymous:

I've seen this type of stuff before.

This is simplifed of course.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

Off course they got back duplicate rows, so the programmer changed it to be

select unique col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower. This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

aaaaarrrrghhh... the googles!!! they do nothing!!

why is people using "where" to join?

Could all you DB experts take a break from your full outer join war and enlighten me as to why the inner join syntax is preferable to using a where clause?

Thanks,
H.Q.

(Apologies to Gene W. and his critics...)

2006-01-31 Reply Admin

Ahh, Grasshopper

The ways of the database programmer are slimy and murky. The details of how things work seem to be arcane facts and figures.

It's much better to understand the problem.

And, a "DATABASE" is really just a collection of files, related by a series of pointers to entities within those files.

T'is better to deal with flat files, where what goes in equals what comes out.

You see, Grasshopper, that is why we invented the "Slo-Mo", so that we could stop time to figure out what those poor DB-Adminstrators-who-would-really like-to-be-programmers should do next.

Sorry bout that, Chief

Arancaytar · 2006-01-31 Reply Admin

Wow.

2006-02-01 Reply Admin

BlackTigerX:
Anonymous:

I've seen this type of stuff before.

This is simplifed of course.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

Off course they got back duplicate rows, so the programmer changed it to be

select unique col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower. This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

aaaaarrrrghhh... the googles!!! they do nothing!!

why is people using "where" to join?

Because you cannot use inner joins (or outer joins for that matter) with some DBs. Oracle 8 anyone?

felix · 2006-02-01 Reply Admin

SeekerDarksteel:
Bah, real men query every table separately then piece it all together in the code!

If that's supposed to be sarcasm, it's misplaced. When such an approach brings execution time down from 15 min. to 10 sec. (that's 90 times!), I'd say it's a perfectly appropriate one.

Cheers,
Felix

P.S. Yes, I speak from experience. No, I'm not very good with SQL. I'm not ashamed of it. I tend to get jobs done - I think it's the only thing that matters.

ammoQ · 2006-02-01 Reply Admin

My system does warehouse management and talks to the ERP system. Both systems have a stock table; in a perfect world, both tables would contain identical information. Unfortunately, because of bugs and what-not, there are some differences.

create table wms_stock (

  product number(6),

  quantity number(10)

);


create table erp_stock (

  product number(6),

  quantity number(10)

);

A report on the differences:

select nvl(wms.product, erp.product), wms.quantity, erp.quantity

  from wms_stock wms full outer join erp_stock erp

   on wms.product = erp.product

  where nvl(wms.quantity,0)<>nvl(erp.quantity,0);

(Of course you may ask "why have two stock tables at all" but, well, the ERP (e.g. SAP) won't allow our system to write into their tables)

Maurits · 2006-02-01 Reply Admin

Jeff S:
I hope you can see that those are at least 2 separate reports, right? Either you are returning all things, and which container they happen to be in (1 + 3) or you are returning all containers, including any things that might be in them (2 + 3). No logical result combines all 3 ! Surely you can see this?

That seems to be one of your postulates, that all "logical results" have a single core table which drives the query, and that the other joined tables are satellites.

Operating from this postulate, your proposition (that RIGHT OUTER JOINS and FULL OUTER JOINS are symptoms of a mistake) does, in fact, follow.

My "pshaw" then is directed not at the arguments you use to go from your postulate to your proposition, but rather at the postulate itself.

Reporting All Through The Night

Leave a comment on “Reporting All Through The Night”