BUG #7552: where clause gets ignored on one of view fields

andrei@tchijov.com writes:

In a view (can provide source upon request) where condition on one of
columns gets ignored by select. No errors at all. Select runs as if the
condition is not present. Where conditions on other columns work as
expected.

You really think this is sufficient detail for somebody to fix the
problem?

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

regards, tom lane

No. But the form for entering bugs is not very ... sophisticated.

Thanks for pointing me to this wiki, I will re-submit properly.

Cheers,

Andrei

On Sep 17, 2012, at 11:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hi,

I have a view (see SQL at the end of this e-mail). Query like following works without any problems on 9.0 and 9.1.

select * form v_jobs where rdms_job_number = 41771;

However, on 9.2 this query acts as it is

select * form v_jobs;

It seems that this problem occur only if I am trying to use this particular field (rdms_job_number), other fields works as they should.

PostgreSQL version number you are running:
PostgreSQL 9.2.0 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit

How you installed PostgreSQL: Following packages were downloaded from http://www.ubuntuupdates.org
postgresql-9.2_9.2.0-1~lucid_amd64.deb
postgresql-client-9.2_9.2.0-1~lucid_amd64.deb
postgresql-client-common_130~lucid_all.deb
postgresql-common_130~lucid_all.deb

Changes made to the settings in the postgresql.conf file: none

Operating system and version: Ubuntu 11.10

What program you're using to connect to PostgreSQL: psql

Is there anything relevant or unusual in the PostgreSQL server logs?: Nothing. No new messages logged when problem occur.

SQL (Please let me know if you need definitions for other tables/views involved):

--
--
--

CREATE OR REPLACE FUNCTION ensure_rdms_job_number( p_job_id INTEGER, p_rdms_job_number INTEGER, p_leads_job_number INTEGER)
RETURNS INTEGER
AS
$BODY$

BEGIN
IF ( p_rdms_job_number IS NULL OR p_rdms_job_number = 0 ) AND
( p_leads_job_number IS NOT NULL AND p_leads_job_number > 0 )
THEN
UPDATE jobs SET rdms_job_number = p_leads_job_number WHERE job_id_pk = p_job_id;
END IF;

RETURN p_job_id;
END;

$BODY$ language plpgsql;

DROP VIEW v_jobs CASCADE;

CREATE OR REPLACE VIEW v_jobs AS
SELECT
ensure_rdms_job_number(j.job_id_pk, j.rdms_job_number, ls.job_number) as job_id_pk,
j.job_status_id_fk,
j.rdms_link_no,
j.brand_id_fk,
j.job_desc,

jm.ca,
jm.cca,
jm.title1 AS client_title1,
jm.fname1 AS client_fname1,
jm.sname1 AS client_sname1,
jm.title2 AS client_title2,
jm.fname2 AS client_fname2,
jm.sname2 AS client_sname2,
ls.stradd AS client_stradd,
jm.csub AS client_suburb,
jm.cstate AS client_state,
jm.cpcode AS client_postcode,

jm.slot AS site_slot,
jm.sstrno AS site_strno,
jm.saddr1 AS site_addr1,
jm.saddr2 AS site_addr2,
jm.ssub AS site_suburb,
jm.sstate AS site_state,
jm.spcode AS site_postcode,

j.job_scheduled_date,
j.job_scheduler_id_fk,

COALESCE(j.rdms_job_number, ls.job_number) AS rdms_job_number,
est.suburb_shire_postcode_id_fk,
jm.email AS client_email,

jm.contract_value,

jm.phone1 AS client_phone1,
jm.phone2 AS client_phone2,
jm.fax AS client_fax,

s.supervisor_name

FROM jobs j
LEFT JOIN estimates est ON (j.job_id_pk = est.job_id_fk)
LEFT JOIN v_lead_system ls ON(ls.id = j.rdms_link_no)
LEFT JOIN v_jobmst jm ON (jm.job_number = COALESCE(j.rdms_job_number, ls.job_number))
LEFT JOIN construction c ON(c.job_number = ls.job_number)
LEFT JOIN supervisors s ON(c.supervisor_id = s.supervisor_id)
;

Andrei Tchijov <andrei@tchijov.com> writes:

SQL (Please let me know if you need definitions for other tables/views involved):

What we need is a self-contained test case.

regards, tom lane

I will try to provide one, but it will have to wait till weekend.

Cheers,
Andrei

On Sep 18, 2012, at 2:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: