The following bug has been logged online:
Bug reference: 1886
Logged by: Pete Beck
Email address: pete@electrostrata.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);
