Re: Getting non_NULL right-side values on a non-matching join? - Mailing list pgsql-general

From Ken Tanzer
Subject Re: Getting non_NULL right-side values on a non-matching join?
Date
Msg-id CAD3a31XswL5ygrOU7sW2f305pYZ0n=Udx9-WQPifxYck92DWAw@mail.gmail.com
Whole thread Raw
In response to Re: Getting non_NULL right-side values on a non-matching join?  (Kevin Grittner <kgrittn@ymail.com>)
List pgsql-general
On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Ken Tanzer <ken.tanzer@gmail.com> wrote:

> In doing a left join with a particular view as the right table,
> and non-matching join criteria, I am getting values returned in a
> few fields.  All the rest are NULL.  I would expect all the right
> side values to be NULL.

What is the output of executing?:

SELECT version();

I think I'm current on 9.2.5 / CentOs 6.4.

ag_spc=> SELECT version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)

[spc@hosting agency_code]$ rpm -qi postgresql92
Name        : postgresql92                 Relocations: (not relocatable)
Version     : 9.2.5                             Vendor: (none)
Release     : 1PGDG.rhel6                   Build Date: Wed 09 Oct 2013 06:02:22 AM PDT
Install Date: Tue 15 Oct 2013 06:22:39 PM PDT      Build Host: koji-sl6-x86-64-pg92
Group       : Applications/Databases        Source RPM: postgresql92-9.2.5-1PGDG.rhel6.src.rpm
Size        : 5279557                          License: PostgreSQL
Signature   : DSA/SHA1, Wed 09 Oct 2013 06:02:31 AM PDT, Key ID 1f16d2e1442df0f8
URL         : http://www.postgresql.org/
Summary     : PostgreSQL client programs and libraries
...


Don't know if this is useful information, but I was surprised that the problem continues even wrapping the view as a subquery, and then even if the subquery has a client_id IS NOT NULL clause:

ag_spc=> SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,move_in_type,annual_income,monthly_income_total,dependent_count FROM (SELECT -1 AS client_id) foo LEFT OUTER JOIN (SELECT * FROM rent_info WHERE client_id IS NOT NULL) boo USING (client_id);
 foo_id | ri_id | move_in_type | annual_income | monthly_income_total | dependent_count 
--------+-------+--------------+---------------+----------------------+-----------------
     -1 |       | Move-in      |             0 |                    0 |               0
(1 row)

I'm happy to provide whatever additional information is helpful--just let me know.  Thanks.

Ken



--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: corruption issue after server crash - ERROR: unexpected chunk number 0
Next
From: Tom Lane
Date:
Subject: Re: Getting non_NULL right-side values on a non-matching join?