Re: Weird results when using schemas

On Mon, Nov 21, 2005 at 01:56:38AM +0100, Geert Jansen wrote:
> I'm experiencing some weird results with SELECT queries when I recently
> switched to using schemas. Basically, the same query works OK when I set
> my search_path to include the schema, but it doesn't when I qualify the
> tables in my query.
>
> One query that demonstrates the behaviour is (apologies for the long query).
>
> shs=> SELECT
> shs.city.id,shs.city.code,shs.city.name,shs.city.description,shs.city.mod_date,shs.city.mod_user
> FROM (shs.city AS city INNER JOIN shs.object_city_relationship ON
> shs.object_city_relationship.city_id = city.id INNER JOIN shs.object AS
> object ON shs.object_city_relationship.object_id = object.id) WHERE
> object.id = 1;
> NOTICE: adding missing FROM-clause entry for table "city"

You refer to shs.city.<column> in the select list, but in the from
clause you've aliased shs.city to city. As the SELECT documentation
says,

When an alias is provided, it completely hides the actual name
of the table or function; for example given FROM foo AS f, the
remainder of the SELECT must refer to this FROM item as f not foo.

Here's a simpler example:

test=> SELECT shs.city.id, shs.city.name FROM shs.city AS city;
NOTICE: adding missing FROM-clause entry for table "city"
id | name
----+--------
1 | Alanya
2 | Bodrum
1 | Alanya
2 | Bodrum
(4 rows)

In recent versions of PostgreSQL (7.4 and later) you can disable
add_missing_from to force an error instead of a notice, and in 8.1
it's off by default. I'd recommend setting it to off to avoid these
kinds of unexpected results.

test=> SET add_missing_from TO off;
SET

test=> SELECT shs.city.id, shs.city.name FROM shs.city AS city;
ERROR: missing FROM-clause entry for table "city"

test=> SELECT city.id, city.name FROM shs.city AS city;
id | name
----+--------
1 | Alanya
2 | Bodrum
(2 rows)

Try this query:

SELECT city.id, city.code, city.name, city.description,
city.mod_date, city.mod_user
FROM shs.city AS city
INNER JOIN shs.object_city_relationship AS ocr ON ocr.city_id = city.id
INNER JOIN shs.object AS object ON ocr.object_id = object.id
WHERE object.id = 1;

Here's what I get:

id | code | name | description | mod_date | mod_user
----+--------+--------+-------------+----------------------------+----------
2 | bodrum | Bodrum | bodrum | 2005-11-21 00:03:53.786452 | 0
(1 row)

--
Michael Fuhr