The following bug has been logged online:

Bug reference: 1886
Logged by: Pete Beck
Email address: pete (AT) electrostrata (DOT) com
PostgreSQL version: 8.0.1 & 8.0.3
Operating system: Linux and Windows
Description: Bug in SQL parsing
Details:

Postgres incorrectly reports a relation is being missing in a SQL.

The following query reports:

ERROR: relation "product0_" does not exist

even though the relation is specified in the query.

select
category4_.id as col_0_0_,
category4_.description as col_1_0_,
category4_.long_description as col_2_0_,
product0_.product_type_id as col_4_0_,
attributev7_.string_value as col_5_0_,
attributev7_.string_value as col_6_0_,
attributev7_.integer_value as col_7_0_
from
(
select
created,
sort_order,
product_type_id,
currency_id,
unit_cost,
product_status_id,
name,
updated,
id,
0 as clazz_
from
product
union
all select
created,
sort_order,
product_type_id,
currency_id,
unit_cost,
product_status_id,
name,
updated,
id,
1 as clazz_
from
user_product
) product0_
left outer join
(
select
created,
index,
attribute_value_id,
product_id,
updated,
name,
0 as clazz_
from
product_attribute
union
all select
created,
index,
attribute_value_id,
product_id,
updated,
name,
1 as clazz_
from
user_product_attribute
) productatt1_
on product0_.id=productatt1_.product_id,
( select
integer_value,
created,
boolean_value,
attribute_type_id,
date_value,
float_value,
string_value,
updated,
id,
0 as clazz_
from
squashed_attribute_value
union
all select
integer_value,
created,
boolean_value,
attribute_type_id,
date_value,
float_value,
string_value,
updated,
id,
1 as clazz_
from
user_squashed_attribute_value
) attributev7_
left outer join
(
select
created,
index,
attribute_value_id,
product_id,
updated,
name,
0 as clazz_
from
product_attribute
union
all select
created,
index,
attribute_value_id,
product_id,
updated,
name,
1 as clazz_
from
user_product_attribute
) productatt2_
on product0_.id=productatt2_.product_id
left outer join
(
select
created,
index,
attribute_value_id,
product_id,
updated,
name,
0 as clazz_
from
product_attribute
union
all select
created,
index,
attribute_value_id,
product_id,
updated,
name,
1 as clazz_
from
user_product_attribute
) productatt3_
on product0_.id=productatt3_.product_id,
category category4_,
product_category productcat5_,
product_category productcat6_
where
productatt1_.attribute_value_id=attributev7_.id
and productcat5_.product_id=product0_.id
and productcat5_.category_id=category4_.id
and category4_.category_type_id=4
and productcat6_.product_id=product0_.id
and productatt1_.name='description'
and productatt1_.index=1
and productatt2_.name='long_description'
and productatt2_.index=1
and productatt3_.name='icon_id'
and productatt3_.index=1
and productcat6_.category_id=190;


Here is a schema which you can use in an empty database to reproduce the
error:

--
-- TOC entry 1512 (class 1259 OID 76062319)
-- Dependencies: 2070 2071 5
-- Name: base_object; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE base_object (
created timestamp with time zone DEFAULT now() NOT NULL,
updated timestamp with time zone DEFAULT now() NOT NULL
);


--

CREATE TABLE attribute (
attribute_value_id integer NOT NULL,
"index" integer NOT NULL,
name text NOT NULL
)
INHERITS (base_object);



--
-- TOC entry 1521 (class 1259 OID 76062363)
-- Dependencies: 2081 2082 2083 5 1512
-- Name: attribute_type; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE attribute_type (
id serial NOT NULL,
description text NOT NULL,
jndi_name text NOT NULL
)
INHERITS (base_object);



CREATE TABLE category (
id serial NOT NULL,
name text NOT NULL,
description text NOT NULL,
long_description text,
sort_order integer DEFAULT 0 NOT NULL,
active boolean DEFAULT true NOT NULL,
category_type_id integer,
icon_id integer
)
INHERITS (base_object);



--
-- TOC entry 1730 (class 1259 OID 77561774)
-- Dependencies: 2446 2447 2448 5 1512
-- Name: category_type; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE category_type (
id serial NOT NULL,
description text NOT NULL
)
INHERITS (base_object);



-- TOC entry 1542 (class 1259 OID 76062538)
-- Dependencies: 2117 2118 2119 2120 5 1512
-- Name: product; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE product (
id serial NOT NULL,
currency_id integer,
product_status_id integer,
name text NOT NULL,
product_type_id integer,
unit_cost double precision NOT NULL,
sort_order integer DEFAULT 1
)
INHERITS (base_object);



--
-- TOC entry 1543 (class 1259 OID 76062548)
-- Dependencies: 2121 2122 5 1535
-- Name: product_attribute; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE product_attribute (
product_id integer NOT NULL
)
INHERITS (attribute);



--
-- TOC entry 1747 (class 1259 OID 78906909)
-- Dependencies: 2479 2480 5 1512
-- Name: product_category; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE product_category (
product_id integer NOT NULL,
category_id integer NOT NULL
)
INHERITS (base_object);



--
-- TOC entry 1538 (class 1259 OID 76062520)
-- Dependencies: 2111 2112 2113 5 1512
-- Name: product_status; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE product_status (
id serial NOT NULL,
status text
)
INHERITS (base_object);



--
-- TOC entry 1540 (class 1259 OID 76062529)
-- Dependencies: 2114 2115 2116 5 1512
-- Name: product_type; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE product_type (
id serial NOT NULL,
description text
)
INHERITS (base_object);



-- TOC entry 1749 (class 1259 OID 78906939)
-- Dependencies: 2483 2484 5 1512
-- Name: related_category; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

-- TOC entry 1737 (class 1259 OID 77561849)
-- Dependencies: 2457 2458 2459 5 1512
-- Name: squashed_attribute_value; Type: TABLE; Schema: public; Owner:
appserver; Tablespace:
--

CREATE TABLE squashed_attribute_value (
id serial NOT NULL,
attribute_type_id integer,
boolean_value boolean,
date_value timestamp without time zone,
float_value double precision,
integer_value integer,
string_value text
)
INHERITS (base_object);



-- TOC entry 1750 (class 1259 OID 78907025)
-- Dependencies: 2485 2486 5 1512
-- Name: user_attribute; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE user_attribute (
attribute_value_id integer NOT NULL,
"index" integer NOT NULL,
name text NOT NULL
)
INHERITS (base_object);



--
-- TOC entry 1726 (class 1259 OID 77561692)
-- Dependencies: 2439 2440 2441 2442 5 1512
-- Name: user_product; Type: TABLE; Schema: public; Owner: appserver;
Tablespace:
--

CREATE TABLE user_product (
id serial NOT NULL,
currency_id integer,
product_status_id integer,
name text NOT NULL,
product_type_id integer,
unit_cost double precision NOT NULL,
sort_order integer DEFAULT 1
)
INHERITS (base_object);



--
-- TOC entry 1751 (class 1259 OID 78907032)
-- Dependencies: 2487 2488 5 1750
-- Name: user_product_attribute; Type: TABLE; Schema: public; Owner:
appserver; Tablespace:
--

CREATE TABLE user_product_attribute (
product_id integer NOT NULL
)
INHERITS (user_attribute);



--
-- TOC entry 1733 (class 1259 OID 77561815)
-- Dependencies: 2451 2452 2453 5 1512
-- Name: user_squashed_attribute_value; Type: TABLE; Schema: public; Owner:
appserver; Tablespace:
--

CREATE TABLE user_squashed_attribute_value (
id serial NOT NULL,
attribute_type_id integer,
boolean_value boolean,
date_value timestamp without time zone,
float_value double precision,
integer_value integer,
string_value text
)
INHERITS (base_object);

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Fri, 16 Sep 2005, Pete Beck wrote:

I believe this error is correct.

It looks to me like you have:
A Left join B on condition, C left join D on condition2
where condition2 refers to A.

IIRC, in standard SQL, the scope for condition contains A and B and the
scope for condition2 contains C and D but not A.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Well spotted! :-)
It looks like the bug must actually be with Hibernate, which generated
the offending code.

I'll take it up with those guys.

Thanks for your help!

Pete

Stephan Szabo wrote:
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match