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)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
