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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
