[BUGS] Bug (8.4beta): FailedAssertion("!(bms_is_subset(relids, qualscope))", File: "initsplan.c", Line: 915)

[Stefan Huehner]

I noticed the following bug when testing an application (openbravo 2.40) on 
postgresql 8.4:

Environment:
8.4beta
Package from: https://launchpad.net/~pitti/+archive/postgresql
recompiled for ubuntu intrepid

The following query does trigger the FailedAssertion:

SELECT ad_field.name As Name, ad_field_trl.name as columnname 
FROM ad_field left join ad_field_trl on ad_field.ad_field_id = 
ad_field_trl.ad_field_id 
and ad_field_trl.ad_language = 'en_US', 
ad_column 
WHERE ad_field.ad_column_id = ad_column.ad_column_id 
and ad_tab_id = to_number(1) and isParent='Y' 
and exists(select 1 from ad_column c, ad_field f where c.ad_column_id = 
f.ad_column_id and c.iskey='Y'
and ad_tab_id=to_number(1) and UPPER(c.columnname) = 
UPPER(ad_column.columnname));

The minimum needed table-structure and function definition (to_number) are 
attached.

The original usecase did have to_number(?) via jdbc-preparedstatement and 
passing the parameter via setString, thus using the to_number(text) function. 
But the same assertion does also happen with the query shown above..

Feel free to ask for any more needed information.

Regards,
Stefan


--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- Name: to_number(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION to_number(text) RETURNS numeric
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
BEGIN
RETURN to_number($1, 'S99999999999999D999999');
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
$_$;


ALTER FUNCTION public.to_number(text) OWNER TO postgres;

--
-- Name: to_number(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION to_number(integer) RETURNS numeric
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
BEGIN
RETURN to_number($1, 'S99999999999999D999999');
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END;
$_$;


ALTER FUNCTION public.to_number(integer) OWNER TO postgres;

--
-- Name: to_number(bigint); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION to_number(bigint) RETURNS numeric
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
BEGIN
RETURN cast($1 as numeric);
END;
$_$;


ALTER FUNCTION public.to_number(bigint) OWNER TO postgres;

--
-- Name: to_number(numeric); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION to_number(numeric) RETURNS numeric
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
BEGIN
RETURN $1;
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END;
$_$;


ALTER FUNCTION public.to_number(numeric) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: ad_column; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE ad_column (
    ad_column_id numeric(10,0) NOT NULL,
    name character varying(60) NOT NULL,
    columnname character varying(40) NOT NULL,
    ad_table_id numeric(10,0) NOT NULL,
    iskey character(1) DEFAULT 'N'::bpchar NOT NULL,
    isparent character(1) DEFAULT 'N'::bpchar NOT NULL,
    ismandatory character(1) DEFAULT 'N'::bpchar NOT NULL
);


ALTER TABLE public.ad_column OWNER TO postgres;

--
-- Name: ad_field; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE ad_field (
    ad_field_id numeric(10,0) NOT NULL,
    name character varying(60) NOT NULL,
    ad_tab_id numeric(10,0) NOT NULL,
    ad_column_id numeric(10,0)
);


ALTER TABLE public.ad_field OWNER TO postgres;

--
-- Name: ad_field_trl; Type: TABLE; Schema: public; Owner: postgres; 
Tablespace: 
--

CREATE TABLE ad_field_trl (
    ad_field_id numeric(10,0) NOT NULL,
    ad_language character varying(6) NOT NULL,
    name character varying(60) NOT NULL
);


ALTER TABLE public.ad_field_trl OWNER TO postgres;

--
-- Data for Name: ad_column; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY ad_column (ad_column_id, name, columnname, ad_table_id, iskey, isparent, 
ismandatory) FROM stdin;
\.


--
-- Data for Name: ad_field; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY ad_field (ad_field_id, name, ad_tab_id, ad_column_id) FROM stdin;
\.


--
-- Data for Name: ad_field_trl; Type: TABLE DATA; Schema: public; Owner: 
postgres
--

COPY ad_field_trl (ad_field_id, ad_language, name) FROM stdin;
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs