Refactoring problem

[prev] [thread] [next] [Date index for 2005/01/22]

I'm working on refactoring some of our code and I'm not getting the answer
I would expect. Can anyone help ?

There are six tables in play in this problem

               funds
                | |
      .---------. .--------.
      |                    |
funds_groups    doc_types_funds_groups
      |                    |
      .--------.  .--------.
               |  |
              groups
                |
           users_groups
                |
              users


Since we know that a user can't have access to a fund unless they have
access to at least one doc_type in that fund, the funds_groups table is
redundant and I'm trying to refactor our CDBI reusing code so that we can
remove it. Using psql, the following two queries return exactly the same
answer:

SELECT DISTINCT fg.fund_name
FROM funds_groups fg
NATURAL JOIN users_groups ug
WHERE ug.username='test-a@bb';

SELECT DISTINCT fg.fund_name
FROM doc_types_funds_groups fg
NATURAL JOIN users_groups ug
WHERE ug.username='test-a@bb';

This CDBI code gives the right answer & passes its tests:

Funds->set_sql(funds_by_user => <<'');
SELECT DISTINCT fg.fund_name AS fund_name
           FROM funds_groups fg
   NATURAL JOIN users_groups ug
          WHERE ug.username=?

But this fails and instead returns nothing:

Funds->set_sql(funds_by_user => <<'');
SELECT DISTINCT fg.fund_name AS fund_name
           FROM doc_types_funds_groups fg
   NATURAL JOIN users_groups ug
          WHERE ug.username=?

I don't quite understand why this should be nor how to go about debugging
it. Unfortunately, I didn't write this code and the guy who did knows
*much* more about sql than I do so I've probably missed something obvious.

Any ideas gratefully received.

Simon.