Обсуждение: Weird results when using schemas

Hi,

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"
 id |  code  |  name  |      description       |
mod_date          | mod_user
----+--------+--------+------------------------+----------------------------+----------
  1 | alanya | Alanya | Alanya, the nice city. | 2005-11-21
00:00:40.502701 |        0
  2 | bodrum | Bodrum | bodrum                 | 2005-11-21
00:03:53.786452 |        0
(2 rows)

Note the WARNING that "city" is missing in the FROM clause. I don't
understand this as it seems to be there. The result is that postgres
added the "city" table without a join condition, which makes the query
return second row which is not the one I was after.

When I set my search path and repeat the exact same query but without
schemas, it works OK:

shs=> set search_path to shs,public;
SET
shs=> SELECT
city.id,city.code,city.name,city.description,city.mod_date,city.mod_user
FROM (city AS city INNER JOIN object_city_relationship ON
object_city_relationship.city_id = city.id INNER JOIN object AS object
ON object_city_relationship.object_id = object.id)  WHERE object.id = 1;
 id |  code  |  name  | description |          mod_date          | mod_user
----+--------+--------+-------------+----------------------------+----------
  2 | bodrum | Bodrum | bodrum      | 2005-11-21 00:03:53.786452 |        0
(1 row)


I'm using PostgreSQL 8.0.4  For reference, I've included the table
definitions and contents below.

Does anyone have an idea what is causing this behaviour?

Regards,
Geert


CREATE TABLE shs.city (id INTEGER DEFAULT NULL,code CHARACTER
VARYING(64) DEFAULT NULL,name CHARACTER VARYING(64) DEFAULT
NULL,description TEXT DEFAULT NULL,mod_date TIMESTAMP WITHOUT TIME ZONE
DEFAULT NULL,mod_user INTEGER DEFAULT NULL,_refcount INTEGER NOT NULL
DEFAULT 0,PRIMARY KEY (id));

CREATE TABLE shs.object (id INTEGER DEFAULT NULL,code CHARACTER
VARYING(64) DEFAULT NULL,object_type CHARACTER VARYING(16) DEFAULT
NULL,title CHARACTER VARYING(128) DEFAULT NULL,description TEXT DEFAULT
NULL,price DECIMAL(10,2) DEFAULT NULL,acceptance_date DATE DEFAULT
NULL,object_size INTEGER DEFAULT NULL,lot_size INTEGER DEFAULT
NULL,construction_year INTEGER DEFAULT NULL,number_of_rooms INTEGER
DEFAULT NULL,number_of_bathrooms INTEGER DEFAULT NULL,distance_to_sea
INTEGER DEFAULT NULL,distance_to_center INTEGER DEFAULT
NULL,lift_present BOOLEAN DEFAULT NULL,swimming_pool_present BOOLEAN
DEFAULT NULL,balcony_present BOOLEAN DEFAULT NULL,airco_present
CHARACTER VARYING(16) DEFAULT NULL,mod_date TIMESTAMP WITHOUT TIME ZONE
DEFAULT NULL,mod_user INTEGER DEFAULT NULL,_refcount INTEGER NOT NULL
DEFAULT 0,PRIMARY KEY (id));

CREATE TABLE shs.object_city_relationship (object_id INTEGER DEFAULT
NULL,city_id INTEGER DEFAULT NULL,FOREIGN KEY (object_id) REFERENCES
shs.object (id) ON DELETE RESTRICT,FOREIGN KEY (city_id) REFERENCES
shs.city (id) ON DELETE RESTRICT,PRIMARY KEY (object_id,city_id));

And the data contents:

shs=> select * from shs.city;
 id |  code  |  name  |      description       |
mod_date          | mod_user | _refcount
----+--------+--------+------------------------+----------------------------+----------+-----------
  1 | alanya | Alanya | Alanya, the nice city. | 2005-11-21
00:00:40.502701 |        0 |         2
  2 | bodrum | Bodrum | bodrum                 | 2005-11-21
00:03:53.786452 |        0 |         2
(2 rows)

shs=> select * from shs.object;
 id |  code  | object_type | title | description |  price   |
acceptance_date | object_size | lot_size | construction_year |
number_of_rooms | number_of_bathrooms | distance_to_sea |
distance_to_center | lift_present | swimming_pool_present |
balcony_present | airco_present |          mod_date          | mod_user
| _refcount

----+--------+-------------+-------+-------------+----------+-----------------+-------------+----------+-------------------+-----------------+---------------------+-----------------+--------------------+--------------+-----------------------+-----------------+---------------+----------------------------+----------+-----------
  1 | UT-001 | appartment  | test2 | test        | 10000.00 |
2005-12-31      |         100 |          |              2005
|               5 |                     |
|                    |              |
|                 |               | 2005-11-21 01:02:50.527129 |
0 |         9
(1 row)

shs=> select * from shs.object_city_relationship;
 object_id | city_id
-----------+---------
         1 |       2
(1 row)

On Mon, 21 Nov 2005, 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"
>  id |  code  |  name  |      description       |
> mod_date          | mod_user
> ----+--------+--------+------------------------+----------------------------+----------
>   1 | alanya | Alanya | Alanya, the nice city. | 2005-11-21
> 00:00:40.502701 |        0
>   2 | bodrum | Bodrum | bodrum                 | 2005-11-21
> 00:03:53.786452 |        0
> (2 rows)
>
> Note the WARNING that "city" is missing in the FROM clause. I don't
> understand this as it seems to be there.

After using "shs.city as city", I don't believe it's correct to use
shs.city in the select list as I think that name is not exported from the
FROM clause due to the correlation name.

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

Michael Fuhr wrote:
> 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.
>

Michael,

Thanks a lot for your very helpful answer (thanks to Stephan Szabo as
well). I've now changed my query as per your indications and indeed it
works ok.

Best regards,
Geert